Add File in MySQL DB

R

RedLeader

Active member
Joined
Mar 8, 2005
Messages
25
Location
Bugaria
Hi,
I have problem when i try to add File to MySQL DB(ver. 4.1.15)
What Type should be the collumn?

Code: 
...
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();
}
....
ive tryed this code.

N.B. The file is Binary.
Any suggestions?
Thank you,
RL
 
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
 
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
 
OK,The Column type is Blob, but what format should be the File(stream byte array)
Ive tried to insert Byte Array just like i MSSQL binary field, is this OK?
Thanks
 
I sort this out.
dont Forget to add references

C#: 
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...ng-blob.html#connector-net-using-blob-writing
 
Back
Top