| Bug #5624 | mysqlparameter not working for blob fields | ||
|---|---|---|---|
| Submitted: | 17 Sep 2004 5:08 | Modified: | 17 Sep 2004 16:47 |
| Reporter: | Nate Jones | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 1.0.0 Beta | OS: | Windows (Windows XP) |
| Assigned to: | CPU Architecture: | Any | |
[17 Sep 2004 14:26]
Svetoslav Milenov
This is not a bug. You have 2 errors in your code:
1. your command is SqlClient.SqlCommand, while it should be MySqlClinet.MySqlCommand
2. The parameter escape character in MySql is "?", not "@".
This works:
MySqlConnection con = new MySqlConnection("xxxxx");
MySqlCommand com = new MySqlCommand("INSERT INTO test.pictest (pictest.PicArray, pictest.PicID) VALUES (?picarray, ?picid)", con);
byte[] picarray = new byte[1000];
picarray.Initialize();
com.Parameters.Add("?picarray", picarray);
com.Parameters.Add("?picid", 1);
con.Open();
com.ExecuteNonQuery();
con.Close();
Cheers
[17 Sep 2004 16:27]
Nate Jones
Ok, It's working now that I changed the escape Parameters over to using the '?' instead of the '@'. Thanks Svetoslav!! btw, the sqlclient.sqlcommand, was just a typo in here. I had it the right way in my code, thanks!!
[17 Sep 2004 16:47]
MySQL Verification Team
According your last post.
Description: When you use parameterized queries (insert queries using mysqlparameter objects) that you insert binary data (a byte array buffer) into a blob field in the database, nothing is written to the database. You can use the mysqldataadapter.update function to put binary data into a blob field, but is extremely inconvenient. The database version is version 4.0.18 How to repeat: Create a blob field in the database, and try using a parameterized query to insert the a picture into the database. Something like this: cmdA = New SqlClient.SqlCommand("UPDATE Pictures SET BinaryData=@Pic WHERE PictureID=@PicID", MySqlConnection) cmdA.CommandType = CommandType.Text cmdA.Parameters.Add(New MySqlParameter("@Pic", PicByteArrayBufferVar)) cmdA.Parameters.Add(New MySqlParameter("@PicID", 4)) cmdA.ExecuteNonQuery() this will attempt to use Parameters to insert binary data into a blob field in the database...it doesn't work with the MySQL Connector/Net data provider. You can use the same code above with the ODBC.Net data provider, and it inserts the binary data just fine. You can calso use the .update method of a dataadapter using the MySQL Connector/Net data provider and it works fine. Just something wrong with using mysqlparameter Suggested fix: Not sure. I haven't looked at the Connector/Net source code yet to find out why it works using the datareader.update method, but not parameters. Any help would be greatly appreciated.