How to insert a blob data type

Question

I use the following code to insert into a blob field:

---

MySql.Data.MySqlClient.MySqlConnection conn; MySql.Data.MySqlClient.MySqlCommand cmd; conn = new MySql.Data.MySqlClient.MySqlConnection(); cmd = new MySql.Data.MySqlClient.MySqlCommand(); string SQL; int FileSize; byte[] rawData; FileStream fs; conn.ConnectionString = "server=192.168.1.104;uid=root;" + "pwd=root;database=cady234;"; fs = new FileStream(@"d:\Untitled.gif", FileMode.Open, FileAccess.Read); FileSize = (int)fs.Length; rawData = new byte[FileSize]; fs.Read(rawData, 0, FileSize); fs.Close(); conn.Open(); string strFileName = "test name"; SQL = "INSERT INTO file (file_name, file_size, file) VALUES ('" + strFileName + "', "+FileSize+", '"+rawData+"')"; cmd.Connection = conn; cmd.CommandText = SQL; cmd.ExecuteNonQuery(); conn.Close(); 

---

The insertion is OK, but the image does not appear when using "Open Value in Viewer":

Answer 1

Binary data will not be properly passed to your insertion since you are using string concatenation - you will get rawData.ToString() , which will probably just print the type name (hence your binary data is 13 bytes long compared to file size> 3000 bytes ); try this instead:

 byte[] rawData = File.ReadAllBytes(@"d:\Untitled.gif"); FileInfo info = new FileInfo(@"d:\Untitled.gif"); int fileSize = Convert.ToInt32(info.Length); using(MySqlConnection connection = new MySqlConnection("server=192.168.1.104;uid=root;pwd=root;database=cady234;")) { using(MySqlCommand command = new MySqlCommand()) { command.Connection = connection; command.CommandText = "INSERT INTO file (file_name, file_size, file) VALUES (?fileName, ?fileSize, ?rawData);"; MySqlParameter fileNameParameter = new MySqlParameter("?fileName", MySqlDbType.VarChar, 256); MySqlParameter fileSizeParameter = new MySqlParameter("?fileSize", MySqlDbType.Int32, 11); MySqlParameter fileContentParameter = new MySqlParameter("?rawData", MySqlDbType.Blob, rawData.Length); fileNameParameter.Value = "test name"; fileSizeParameter.Value = fileSize; fileContentParameter.Value = rawData; command.Parameters.Add(fileNameParameter); command.Parameters.Add(fileSizeParameter); command.Parameters.Add(fileContentParameter); connection.Open(); command.ExecuteNonQuery(); } } 

I have presented several concepts for you here; firstly, if you are going to immediately download all binary data, just use the static File.ReadAllBytes method - this is much less code.

Secondly, there is no need to use the full namespace every time - use the using directive

Thirdly, (a little vaguely) there is also a using statement in C #. This ensures that any object that implements IDisposable is correctly cleaned after itself. In the case of a connection, it will explicitly call Close and Dispose if the command succeeds or fails.

Finally, I customized your request. Parameters are useful for many reasons; they help protect against SQL Injection , in which case they must also ensure that your data types are being processed correctly. You can learn more about SqlParameter (which, for example, MySqlParameter is a database implementation but uses the same principles).

Tested with MySQL 5.5.15, MySQL Connector 5.2.7 runs under .Net 4

Answer 2

How about this:

This works great for me.
Exepte I found out that this is wrong for me.

 <?php // csv invoeren naar pos. $CSV = "uitvoer.csv"; // The CSV file has only 2 colums; The "Reference" and the image name (in my case the barcode with "thumb_" in front of it. $username = "Username"; $password = "Passwwoorrdd"; $database = "POS"; $counter = 0; // Create connection $conn = new mysqli("localhost", $username, $password, $database); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } echo "Connected successfully <br>"; //$mysqli->select_db(); ini_set('max_execution_time', 1000); //300 seconds = 5 minutes if (($handle = fopen($CSV, "r")) !== FALSE) { while (($data = fgetcsv($handle, 1050, ",")) !== FALSE) { // this loops through each line of your csv, putting the values into array elements $counter++; $IDEE = $data[0]; // It seems that after opening the image the $data is mixed up. $imag = "photos/".$data[1]; // where "photos/' the folder is where this php file gets executed (mostly in /var/www/ of /var/www/html/) $fh = fopen($imag, "r"); $data = addslashes(fread($fh, filesize($imag))); fclose($fh); echo " Ref: ".$IDEE." ----".$counter."----<br>"; // If there will be a time-out. You could erase the part what is already done minus 1. $sql = "UPDATE PRODUCTS SET IMAGE='".$data."' WHERE CODE=$IDEE"; 

// PRODUCTS table with IMAGE. This table has more data. But I only need to update IMAGE. The rest is already inserted.

 if ($conn->query($sql) === TRUE) { echo "Tabel <b>products</b> updated successfully<br>"; } else { echo "<br>Error updating tabel <b>Products</b>: " . $conn->error; Exit(); } } fclose($handle); } ?>