0

Let's me make a quick introduction; I have 2 server database servers that I used the second DB server for backing up one of the first DB server's table.

Problem is the table has the column stores BLOB value, didn't know what it stored type file, so I tried to use a byte array to keep backup data in the 2nd table and recovery this data to the 1st table when it needed.

Here how I worked,

First, I select data from the 1st table and backup (insert) to the 2nd table

//inserting byte[] to 2nd db
String tmpBatchInsert = String.Format(@"INSERT INTO 2nd_table (VERSION, PRODUCTID, ROUTE) VALUES ('{0}',{1},@route);", version, tmpInsertValue);
MySqlConnection connMySQL2 = new MySqlConnection(connString);
MySqlCommand cmdMySQL2 = new MySqlCommand(tmpBatchInsert, connMySQL2);
cmdMySQL2.Parameters.AddWithValue("@route", routeByte);
connMySQL2.Open();
try
{
    cmdMySQL2.ExecuteNonQuery();
}
catch
{
    MessageBox.Show("Found data duplicate in an active database, skip saving..", "Data duplicate!");
}
connMySQL2.Close();

It works perfectly (I checked by a look at stored length value of ROUTE, BLOB value) img: Blob data and its length on the 2nd table But when I tried to recovery to the 1rt table using the similar code but changing insert table.

String tmpBatchInsert = String.Format(@"INSERT INTO 1st_table (PRODUCTID, ROUTE) VALUES ({0},@route);", tmpInsertValue);
MySqlConnection connMySQL2 = new MySqlConnection(connString2);
MySqlCommand cmdMySQL2 = new MySqlCommand(tmpBatchInsert, connMySQL2);
cmdMySQL2.Parameters.Add("@route", MySqlDbType.Blob).Value = routeByte;
connMySQL2.Open();
cmdMySQL2.ExecuteNonQuery();
//MessageBox.Show("Found data duplicate in active database, skip saving..", "Data duplicate!");
connMySQL2.Close();

It stored wrong Blob value. img2: Blob's length on 1st table

I wonder what I missed? Please to any suggestion, Thank you.

I got this idea from this and Converting to Byte Array after reading a BLOB from SQL in C#

What I have tried?

I was debugged via Visual Studio 2017 and found out get value from 2nd is still correct. code line and quick watch data to query method to insert data to the 1st table

I thought it might be casting when I getting the data like this thread but it still unsolved my problem. :(

EncodP
  • 1
  • 2

1 Answers1

-1

You used {1} instead of {0} in the String.Format--> The parameter is not handed over to the INSERT statement

This would be the right String.Format with the 0 instead of the 1.

String.Format(@"INSERT INTO 1st_table (PRODUCTID, ROUTE) VALUES ({0},@route);", tmpInsertValue);
Florian R.
  • 9
  • 1