Add File in MySQL DB

[RedLeader]

Hi,

I have problem when i try to add File to MySQL DB(ver. 4.1.15)

What Type should be the collumn?

 

...
void InsertFile(byte[] byData)
{
string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=127.0.0.1;" +
"DATABASE=customers;" +
"UID=root;" +
"PASSWORD=*****;" +
"OPTION=3";

OdbcConnection MyConnection = new OdbcConnection(MyConString);
MyConnection.Open();
		
string mySelectQuery = "INSERT INTO FilesDB (Path, File) VALUES ('C:\test', byData )";

OdbcCommand myCommand = new OdbcCommand(mySelectQuery,MyConnection);
myCommand.ExecuteReader();
}
....

i've tryed this code.

 

N.B. The file is Binary.

Any suggestions?

Thank you,

RL

[kejpa]

Not too sure, but I expect the Path to be the problem, I would have escaped the \

What happens if you do ....

INSERT INTO FilesDB (Path) VALUES ('C:\test')";

?

 

You seems to be in control of your database, do some testing using a database GUI...

 

HTH

/Kejpa

[RedLeader]

The Path is ok. I've tested it.

The File Field is the problem :(

[kejpa]

Hi,

reading in the Reference manual for mySQL I find that a BLOB is treated as a text field and thus you probably should add quotes and escape certain characters... Have a look at the reference manual (chapter 6.2.3.2 in my version 3.23.54)

 

HTH

/Kejpa

[RedLeader]

OK,The Column type is Blob, but what format should be the File(stream byte array)

I've tried to insert Byte Array just like i MSSQL binary field, is this OK?

Thanks

[RedLeader]

I sort this out.

don't Forget to add references

 

using MySql.Data;
//.....
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

string SQL;
UInt32 FileSize;
byte[] rawData;
FileStream fs;

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
   "pwd=12345;database=test;";

try
{
   fs = new FileStream(@"c:\image.png", FileMode.Open, FileAccess.Read);
   FileSize = fs.Length;

   rawData = new byte[FileSize];
   fs.Read(rawData, 0, FileSize);
   fs.Close();

   conn.Open();

   SQL = "INSERT INTO file VALUES(NULL, ?FileName, ?FileSize, ?File)";

   cmd.Connection = conn;
   cmd.CommandText = SQL;
   cmd.Parameters.Add("?FileName", strFileName);
   cmd.Parameters.Add("?FileSize", FileSize);
   cmd.Parameters.Add("?File", rawData);

   cmd.ExecuteNonQuery();

   MessageBox.Show("File Inserted into database successfully!",
       "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

   conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
   MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
       "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

I hope this will help to others.

source :

http://dev.mysql.com/doc/refman/4.1/en/connector-net-using-blob.html#connector-net-using-blob-writing