public void Blobs() { executeSQL("CREATE TABLE Test (id INT, blob1 LONGBLOB, text1 LONGTEXT)"); MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?blob1, ?text1)", Connection); byte[] bytes = Utils.CreateBlob(400000); string inStr = "This is my text"; cmd.Parameters.AddWithValue("?id", 1); cmd.Parameters.AddWithValue("?blob1", bytes); cmd.Parameters.AddWithValue("?text1", inStr); cmd.Prepare(); int count = cmd.ExecuteNonQuery(); Assert.Equal(1, count); cmd.CommandText = "SELECT * FROM Test"; cmd.Prepare(); using (MySqlDataReader reader = cmd.ExecuteReader()) { Assert.True(reader.Read()); Assert.Equal(1, reader.GetInt32(0)); Assert.Equal(bytes.Length, reader.GetBytes(1, 0, null, 0, 0)); byte[] outBytes = new byte[bytes.Length]; reader.GetBytes(1, 0, outBytes, 0, bytes.Length); for (int x = 0; x < bytes.Length; x++) { Assert.Equal(bytes[x], outBytes[x]); } Assert.Equal(inStr, reader.GetString(2)); } }
public void TestMultiPacket() { int len = 20000000; suExecSQL("SET GLOBAL max_allowed_packet=64000000"); // currently do not test this with compression if (conn.UseCompression) { return; } using (MySqlConnection c = new MySqlConnection(GetConnectionString(true))) { c.Open(); byte[] dataIn = Utils.CreateBlob(len); byte[] dataIn2 = Utils.CreateBlob(len); MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, NULL, ?blob, NULL )", c); cmd.CommandTimeout = 0; cmd.Parameters.Add(new MySqlParameter("?id", 1)); cmd.Parameters.Add(new MySqlParameter("?blob", dataIn)); cmd.ExecuteNonQuery(); cmd.Parameters[0].Value = 2; cmd.Parameters[1].Value = dataIn2; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * FROM Test"; using (MySqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); byte[] dataOut = new byte[len]; long count = reader.GetBytes(2, 0, dataOut, 0, len); Assert.AreEqual(len, count); int i = 0; try { for (; i < len; i++) { Assert.AreEqual(dataIn[i], dataOut[i]); } } catch (Exception) { int z = i; } reader.Read(); count = reader.GetBytes(2, 0, dataOut, 0, len); Assert.AreEqual(len, count); for (int x = 0; x < len; x++) { Assert.AreEqual(dataIn2[x], dataOut[x]); } } } }
public void BlobBiggerThanMaxPacket() { execSQL("set max_allowed_packet=500000"); execSQL("DROP TABLE IF EXISTS test"); execSQL("CREATE TABLE test (id INT(10), image BLOB)"); MySqlConnection c = new MySqlConnection(GetConnectionString(true)); try { c.Open(); MySqlCommand cmd = new MySqlCommand("SET max_allowed_packet=500000", c); cmd.ExecuteNonQuery(); byte[] image = Utils.CreateBlob(1000000); cmd.CommandText = "INSERT INTO test VALUES(NULL, ?image)"; cmd.Parameters.AddWithValue("?image", image); cmd.ExecuteNonQuery(); Assert.Fail("This should have thrown an exception"); } catch (Exception) { Assert.AreEqual(ConnectionState.Open, c.State); } finally { if (c != null) { c.Close(); } } }
public void GetBytes() { CreateDefaultTable(); int len = 50000; byte[] bytes = Utils.CreateBlob(len); MySqlCommand cmd = new MySqlCommand( "INSERT INTO Test (id, name, b1) VALUES(1, 'Test', ?b1)", conn); cmd.Parameters.AddWithValue("?b1", bytes); cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * FROM Test"; using (MySqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); long sizeBytes = reader.GetBytes(4, 0, null, 0, 0); Assert.AreEqual(len, sizeBytes); byte[] buff1 = new byte[len / 2]; byte[] buff2 = new byte[len - (len / 2)]; long buff1cnt = reader.GetBytes(4, 0, buff1, 0, len / 2); long buff2cnt = reader.GetBytes(4, buff1cnt, buff2, 0, buff2.Length); Assert.AreEqual(buff1.Length, buff1cnt); Assert.AreEqual(buff2.Length, buff2cnt); for (int i = 0; i < buff1.Length; i++) { Assert.AreEqual(bytes[i], buff1[i]); } for (int i = 0; i < buff2.Length; i++) { Assert.AreEqual(bytes[buff1.Length + i], buff2[i]); } } // now check with sequential access using (MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { Assert.IsTrue(reader.Read()); int mylen = len; byte[] buff = new byte[8192]; int startIndex = 0; while (mylen > 0) { int readLen = Math.Min(mylen, buff.Length); int retVal = (int)reader.GetBytes(4, startIndex, buff, 0, readLen); Assert.AreEqual(readLen, retVal); for (int i = 0; i < readLen; i++) { Assert.AreEqual(bytes[startIndex + i], buff[i]); } startIndex += readLen; mylen -= readLen; } } }
public void Bug6271() { MySqlCommand cmd = null; string sql = null; // Updating the default charset for servers 8.0+. if (Connection.driver.Version.isAtLeast(8, 0, 1)) { sql = "SET NAMES 'latin1' COLLATE 'latin1_swedish_ci'"; cmd = new MySqlCommand(sql, Connection); cmd.ExecuteNonQuery(); } // Create the table again executeSQL("CREATE TABLE `Test2` (id INT unsigned NOT NULL auto_increment, " + "`xpDOSG_Name` text,`xpDOSG_Desc` text, `Avatar` MEDIUMBLOB, `dtAdded` DATETIME, `dtTime` TIMESTAMP, " + "PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=latin1"); sql = "INSERT INTO `Test2` (`xpDOSG_Name`,`dtAdded`, `xpDOSG_Desc`,`Avatar`, `dtTime`) " + "VALUES(?name, ?dt, ?desc, ?avatar, NULL)"; cmd = new MySqlCommand(sql, Connection); DateTime dt = DateTime.Now; dt = dt.AddMilliseconds(dt.Millisecond * -1); byte[] xpDOSG_Avatar = Utils.CreateBlob(13000); cmd.Parameters.AddWithValue("?name", "Ceci est un nom"); cmd.Parameters.AddWithValue("?desc", "Ceci est une description facile à plantouiller"); cmd.Parameters.AddWithValue("?avatar", xpDOSG_Avatar); cmd.Parameters.AddWithValue("?dt", dt); cmd.Prepare(); int count = cmd.ExecuteNonQuery(); Assert.Equal(1, count); cmd.CommandText = "SELECT * FROM Test2"; using (MySqlDataReader reader = cmd.ExecuteReader()) { Assert.True(reader.Read()); Assert.Equal("Ceci est un nom", reader.GetString(1)); Assert.Equal(dt.ToString("G"), reader.GetDateTime(4).ToString("G")); Assert.Equal("Ceci est une description facile à plantouiller", reader.GetString(2)); long len = reader.GetBytes(3, 0, null, 0, 0); Assert.Equal(xpDOSG_Avatar.Length, len); byte[] outBytes = new byte[len]; reader.GetBytes(3, 0, outBytes, 0, (int)len); for (int x = 0; x < xpDOSG_Avatar.Length; x++) { Assert.Equal(xpDOSG_Avatar[x], outBytes[x]); } } }
/// <summary> /// Util method for CacheServerPropertiesCausePacketTooLarge Test Method /// </summary> void InsertSmallBlobInTestTableUsingPoolingConnection() { string connStr = ConnectionSettings.ConnectionString + String.Format(";logging=true;cache server properties=true;"); using (MySqlConnection c1 = new MySqlConnection(connStr)) { c1.Open(); byte[] image = Utils.CreateBlob(7152); MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES(NULL, ?image)", c1); cmd.Parameters.AddWithValue("?image", image); cmd.ExecuteNonQuery(); } }
public void Blobs() { execSQL("DROP TABLE IF EXISTS Test"); execSQL("CREATE TABLE Test (id INT, blob1 LONGBLOB, text1 LONGTEXT)"); MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?blob1, ?text1)", conn); cmd.Prepare(); byte[] bytes = Utils.CreateBlob(400000); string inStr = "This is my text"; cmd.Parameters.AddWithValue("?id", 1); cmd.Parameters.AddWithValue("?blob1", bytes); cmd.Parameters.AddWithValue("?text1", inStr); int count = cmd.ExecuteNonQuery(); Assert.AreEqual(1, count); cmd.CommandText = "SELECT * FROM Test"; cmd.Prepare(); MySqlDataReader reader = null; try { reader = cmd.ExecuteReader(); Assert.IsTrue(reader.Read()); Assert.AreEqual(1, reader.GetInt32(0)); Assert.AreEqual(bytes.Length, reader.GetBytes(1, 0, null, 0, 0)); byte[] outBytes = new byte[bytes.Length]; reader.GetBytes(1, 0, outBytes, 0, bytes.Length); for (int x = 0; x < bytes.Length; x++) { Assert.AreEqual(bytes[x], outBytes[x]); } Assert.AreEqual(inStr, reader.GetString(2)); } catch (Exception ex) { Assert.Fail(ex.Message); } finally { if (reader != null) { reader.Close(); } } }
public void BlobBiggerThanMaxPacket() { ExecuteSQL("SET GLOBAL max_allowed_packet=" + 500 * 1024, true); ExecuteSQL("DROP TABLE IF EXISTS Test"); ExecuteSQL("CREATE TABLE Test (id INT(10), image BLOB)"); using (var c = GetConnection()) { byte[] image = Utils.CreateBlob(1000000); MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(NULL, ?image)", c); cmd.Parameters.AddWithValue("?image", image); Exception ex = Assert.Throws <MySqlException>(() => cmd.ExecuteNonQuery()); Assert.AreEqual(ex.Message, "Packets larger than max_allowed_packet are not allowed."); } }
public void UpdateDataSet() { execSQL("DROP TABLE IF EXISTS Test"); execSQL("CREATE TABLE Test (id INT NOT NULL, blob1 LONGBLOB, text1 LONGTEXT, PRIMARY KEY(id))"); execSQL("INSERT INTO Test VALUES( 1, NULL, 'Text field' )"); try { MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn); MySqlCommandBuilder cb = new MySqlCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); string s = (string)dt.Rows[0][2]; Assert.AreEqual("Text field", s); byte[] inBuf = Utils.CreateBlob(512); dt.Rows[0].BeginEdit(); dt.Rows[0]["blob1"] = inBuf; dt.Rows[0].EndEdit(); DataTable changes = dt.GetChanges(); da.Update(changes); dt.AcceptChanges(); dt.Clear(); da.Fill(dt); cb.Dispose(); byte[] outBuf = (byte[])dt.Rows[0]["blob1"]; Assert.AreEqual(inBuf.Length, outBuf.Length, "checking length of updated buffer"); for (int y = 0; y < inBuf.Length; y++) { Assert.AreEqual(inBuf[y], outBuf[y], "checking array data"); } } catch (Exception ex) { Assert.Fail(ex.Message); } }
public void TestBatchingInsertsMoreThanMaxPacket() { int blobSize = 64000; st.execSQL("DROP TABLE IF EXISTS Test"); st.execSQL("CREATE TABLE Test (id INT, img BLOB, PRIMARY KEY(id))"); int numRows = (st.maxPacketSize / blobSize) * 2; MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", st.conn); MySqlCommand ins = new MySqlCommand("INSERT INTO test (id, img) VALUES (@p1, @p2)", st.conn); da.InsertCommand = ins; ins.UpdatedRowSource = UpdateRowSource.None; ins.Parameters.Add("@p1", MySqlDbType.Int32).SourceColumn = "id"; ins.Parameters.Add("@p2", MySqlDbType.Blob).SourceColumn = "img"; DataTable dt = new DataTable(); da.Fill(dt); for (int i = 0; i < numRows; i++) { DataRow row = dt.NewRow(); row["id"] = i; row["img"] = Utils.CreateBlob(blobSize); dt.Rows.Add(row); } da.UpdateBatchSize = 0; da.Update(dt); dt.Rows.Clear(); da.Fill(dt); Assert.Equal(numRows, dt.Rows.Count); for (int i = 0; i < numRows; i++) { Assert.Equal(i, dt.Rows[i]["id"]); } }
public void BlobBiggerThanMaxPacket() { suExecSQL("SET GLOBAL max_allowed_packet=500000"); execSQL("CREATE TABLE test (id INT(10), image BLOB)"); using (MySqlConnection c = new MySqlConnection(GetConnectionString(true))) { c.Open(); byte[] image = Utils.CreateBlob(1000000); MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES(NULL, ?image)", c); cmd.Parameters.AddWithValue("?image", image); try { cmd.ExecuteNonQuery(); Assert.Fail("This should have thrown an exception"); } catch (MySqlException) { } } }
public void InsertBinary() { int lenIn = 400000; byte[] dataIn = Utils.CreateBlob(lenIn); execSQL("CREATE TABLE Test (id INT NOT NULL, blob1 LONGBLOB, PRIMARY KEY(id))"); MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?b1)", conn); cmd.Parameters.Add(new MySqlParameter("?id", 1)); cmd.Parameters.Add(new MySqlParameter("?b1", dataIn)); int rows = cmd.ExecuteNonQuery(); byte[] dataIn2 = Utils.CreateBlob(lenIn); cmd.Parameters[0].Value = 2; cmd.Parameters[1].Value = dataIn2; rows += cmd.ExecuteNonQuery(); Assert.AreEqual(2, rows, "Checking insert rowcount"); cmd.CommandText = "SELECT * FROM Test"; using (MySqlDataReader reader = cmd.ExecuteReader()) { Assert.AreEqual(true, reader.HasRows, "Checking HasRows"); reader.Read(); byte[] dataOut = new byte[lenIn]; long lenOut = reader.GetBytes(1, 0, dataOut, 0, lenIn); Assert.AreEqual(lenIn, lenOut, "Checking length of binary data (row 1)"); // now see if the buffer is intact for (int x = 0; x < dataIn.Length; x++) { Assert.AreEqual(dataIn[x], dataOut[x], "Checking first binary array at " + x); } // now we test chunking int pos = 0; int lenToRead = dataIn.Length; while (lenToRead > 0) { int size = Math.Min(lenToRead, 1024); int read = (int)reader.GetBytes(1, pos, dataOut, pos, size); lenToRead -= read; pos += read; } // now see if the buffer is intact for (int x = 0; x < dataIn.Length; x++) { Assert.AreEqual(dataIn[x], dataOut[x], "Checking first binary array at " + x); } reader.Read(); lenOut = reader.GetBytes(1, 0, dataOut, 0, lenIn); Assert.AreEqual(lenIn, lenOut, "Checking length of binary data (row 2)"); // now see if the buffer is intact for (int x = 0; x < dataIn2.Length; x++) { Assert.AreEqual(dataIn2[x], dataOut[x], "Checking second binary array at " + x); } } }
public void NetWriteTimeoutExpiring() { execSQL("CREATE TABLE Test(id int, blob1 longblob)"); int rows = 10000; byte[] b1 = Utils.CreateBlob(5000); MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (@id, @b1)", conn); cmd.Parameters.Add("@id", MySqlDbType.Int32); cmd.Parameters.AddWithValue("@name", b1); for (int i = 0; i < rows; i++) { cmd.Parameters[0].Value = i; cmd.ExecuteNonQuery(); } string connStr = GetConnectionString(true); using (MySqlConnection c = new MySqlConnection(connStr)) { c.Open(); cmd.Connection = c; cmd.Parameters.Clear(); cmd.CommandText = "SET net_write_timeout = 1"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * FROM Test LIMIT " + rows; int i = 0; try { using (MySqlDataReader reader = cmd.ExecuteReader()) { // after this several cycles of DataReader.Read() are executed // normally and then the problem, described above, occurs for (; i < rows; i++) { if (!reader.Read()) { Assert.Fail("unexpected 'false' from reader.Read"); } if (i % 10 == 0) { Thread.Sleep(1); } object v = reader.GetValue(1); } } } catch (Exception e) { bool seenEndOfStreamException = false; for (Exception nextException = e; e != null; e = e.InnerException) { if (e is System.IO.EndOfStreamException) { seenEndOfStreamException = true; } } if (!seenEndOfStreamException) { throw; } Assert.IsTrue(seenEndOfStreamException); return; } // IT is relatively hard to predict where Console.WriteLine("Warning: all reads completed!"); Assert.IsTrue(i == rows); } }
public void NetWriteTimeoutExpiring() { executeSQL("CREATE TABLE Test(id int, blob1 longblob)"); int rows = 1000; byte[] b1 = Utils.CreateBlob(5000); MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (@id, @b1)", Connection); cmd.Parameters.Add("@id", MySqlDbType.Int32); cmd.Parameters.AddWithValue("@name", b1); for (int i = 0; i < rows; i++) { cmd.Parameters[0].Value = i; cmd.ExecuteNonQuery(); } string connStr = Connection.ConnectionString; using (MySqlConnection c = new MySqlConnection(connStr)) { c.Open(); cmd.Connection = c; cmd.Parameters.Clear(); cmd.CommandText = "SET net_write_timeout = 1"; cmd.ExecuteNonQuery(); cmd.CommandText = "SELECT * FROM Test LIMIT " + rows; int i = 0; try { using (MySqlDataReader reader = cmd.ExecuteReader()) { // after this several cycles of DataReader.Read() are executed // normally and then the problem, described above, occurs for (; i < rows; i++) { Assert.False(!reader.Read(), "unexpected 'false' from reader.Read"); if (i % 10 == 0) { Thread.Sleep(1); } object v = reader.GetValue(1); } } } catch (Exception e) { Exception currentException = e; while (currentException != null) { if (currentException is EndOfStreamException) { return; } if ((Connection.ConnectionString.IndexOf("protocol=namedpipe") >= 0 || Connection.ConnectionString.IndexOf("protocol=sharedmemory") >= 0) && currentException is MySqlException) { return; } currentException = currentException.InnerException; } throw e; } // IT is relatively hard to predict where Console.WriteLine("Warning: all reads completed!"); Assert.True(i == rows); } }
public void Bug6271() { if (version < new Version(4, 1)) { return; } execSQL("DROP TABLE IF EXISTS Test2"); // Create the table again execSQL("CREATE TABLE `Test2` (id INT unsigned NOT NULL auto_increment, " + "`xpDOSG_Name` text,`xpDOSG_Desc` text, `Avatar` MEDIUMBLOB, `dtAdded` DATETIME, `dtTime` TIMESTAMP, " + "PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=latin1"); string sql = "INSERT INTO `Test2` (`xpDOSG_Name`,`dtAdded`, `xpDOSG_Desc`,`Avatar`, `dtTime`) " + "VALUES(?name, ?dt, ?desc, ?Avatar, NULL)"; MySqlCommand cmd = new MySqlCommand(sql, conn); cmd.Prepare(); DateTime dt = DateTime.Now; dt = dt.AddMilliseconds(dt.Millisecond * -1); byte[] xpDOSG_Avatar = Utils.CreateBlob(13000); cmd.Parameters.AddWithValue("?name", "Ceci est un nom"); cmd.Parameters.AddWithValue("?desc", "Ceci est une description facile à plantouiller"); cmd.Parameters.AddWithValue("?avatar", xpDOSG_Avatar); cmd.Parameters.AddWithValue("?dt", dt); int count = cmd.ExecuteNonQuery(); Assert.AreEqual(1, count); MySqlDataReader reader = null; try { cmd.CommandText = "SELECT * FROM Test2"; reader = cmd.ExecuteReader(); Assert.IsTrue(reader.Read()); Assert.AreEqual("Ceci est un nom", reader.GetString(1)); Assert.AreEqual(dt.ToString("G"), reader.GetDateTime(4).ToString("G")); Assert.AreEqual("Ceci est une description facile à plantouiller", reader.GetString(2)); long len = reader.GetBytes(3, 0, null, 0, 0); Assert.AreEqual(xpDOSG_Avatar.Length, len); byte[] outBytes = new byte[len]; reader.GetBytes(3, 0, outBytes, 0, (int)len); for (int x = 0; x < xpDOSG_Avatar.Length; x++) { Assert.AreEqual(xpDOSG_Avatar[x], outBytes[x]); } } catch (Exception ex) { Assert.Fail(ex.Message); } finally { if (reader != null) { reader.Close(); } } }
public void InsertBinary() { int lenIn = 400000; byte[] dataIn = Utils.CreateBlob(lenIn); MySqlCommand cmd2 = new MySqlCommand("SELECT * FROM Test", conn); MySqlDataReader reader2 = cmd2.ExecuteReader(); reader2.Read(); reader2.Close(); MySqlCommand cmd = new MySqlCommand("TRUNCATE TABLE Test", conn); cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Test VALUES (?id, ?b1, NULL)"; cmd.Parameters.Add(new MySqlParameter("?id", 1)); cmd.Parameters.Add(new MySqlParameter("?b1", dataIn)); int rows = cmd.ExecuteNonQuery(); byte[] dataIn2 = Utils.CreateBlob(lenIn); cmd.Parameters[0].Value = 2; cmd.Parameters[1].Value = dataIn2; rows += cmd.ExecuteNonQuery(); Assert.AreEqual(2, rows, "Checking insert rowcount"); MySqlDataReader reader = null; try { cmd.CommandText = "SELECT * FROM Test"; reader = cmd.ExecuteReader(); Assert.AreEqual(true, reader.HasRows, "Checking HasRows"); reader.Read(); byte[] dataOut = new byte[lenIn]; long lenOut = reader.GetBytes(1, 0, dataOut, 0, lenIn); Assert.AreEqual(lenIn, lenOut, "Checking length of binary data (row 1)"); // now see if the buffer is intact for (int x = 0; x < dataIn.Length; x++) { Assert.AreEqual(dataIn[x], dataOut[x], "Checking first binary array at " + x); } // now we test chunking int pos = 0; int lenToRead = dataIn.Length; while (lenToRead > 0) { int size = Math.Min(lenToRead, 1024); int read = (int)reader.GetBytes(1, pos, dataOut, pos, size); lenToRead -= read; pos += read; } // now see if the buffer is intact for (int x = 0; x < dataIn.Length; x++) { Assert.AreEqual(dataIn[x], dataOut[x], "Checking first binary array at " + x); } reader.Read(); lenOut = reader.GetBytes(1, 0, dataOut, 0, lenIn); Assert.AreEqual(lenIn, lenOut, "Checking length of binary data (row 2)"); // now see if the buffer is intact for (int x = 0; x < dataIn2.Length; x++) { Assert.AreEqual(dataIn2[x], dataOut[x], "Checking second binary array at " + x); } } catch (Exception ex) { Assert.Fail(ex.Message); } finally { if (reader != null) { reader.Close(); } } }