Insert an image from a remote PC

[Ithilien]

Hi all,

i'm trying to save an image in a DB from a remote PC. I'm using C++ Builder 6.0 and i don't know why the field of the blob image is null when i insert from a different PC that localhost.

I save the jpg file into a temp file called temp.bmp and then i load using LOAD_FILE(filename) and save. When i log in in localhost, there are no problems, but from a remote insertion, it's not possible. The code is the following:

Code: Select all

TJPEGImage *j = new TJPEGImage;
j->LoadFromFile( OpenPictureDialog->FileName );
j->DIBNeeded();

Graphics::TBitmap *b = new Graphics::TBitmap();
b->Assign(j);
b->SaveToFile("C:\temp.bmp");
delete b;
delete j;

AnsiString insert = "INSERT INTO planta VALUES ("+ id +",LOAD_FILE('C:\temp.bmp'))";
MyTable->SQL->Clear();
MyTable->SQL->Add(insert);
MyTable->Execute();

The user has grants:

grant all on DB to user@'%' with grant option;

Someone can tell me why it happens?

Thanks

[Antaeus]

You code will work only if you temp.bmp file and MySQL server is located on the same computer. This happen because of the way you provide file name to the server. Please take a look at the Pictures demo of MyDAC. It shows how to insert BLOB values from a remote computer.

[Ithilien]

Excuse me Antaeus, but i can't see it. I only can see:

Code: Select all

SQLInsert.Strings = (
      'INSERT INTO MYDAC_Pictures'
      '  (MYDAC_Pictures.Name, MYDAC_Pictures.Picture)'
      'VALUES'
      '  (:Name, :Picture)')

but i can't see the form. Now, What are :Name and :Picture??

[Ithilien]

Please, if someone can type here an example or a link i would be very pleased.

Thanks!

[Antaeus]

The code below demonstrates how to insert a blob value into a table using SQL command with parameters. You should have on your form a TMyQuery object named MyQuery. It should be linked to a TMyConnection object. TMyConnection should be connected to your server.

Code: Select all

  MyQuery->SQL->Text = "INSERT INTO mydac_pictures (Name, Picture) VALUES (:name, :picture)";
  MyQuery->ParamByName("name")->AsString = "Picture1";
  MyQuery->ParamByName("picture")->AsBlobRef->LoadFromFile("d:\picture.bmp");
  MyQuery->Execute();

Use this statement to create the mydac_pictures table:

Code: Select all

CREATE TABLE mydac_pictures (
  UID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(50),
  Picture LONGBLOB
);

[Ithilien]

I can't do this:

My application has an identification menu that connect to MySQL through TMyConnection using a login and a password. Next, all my table and query objects use this MyConnection to access to DB.

The prolem i have is the following: When i use parameters, an exception is raised. I have a table called "planta" formed by an integer ( primary key ) and an image ( "plano" ). The statement to create it is this:

Code: Select all

create table planta ( id_planta integer, plano longblob, primary key(id_planta));

Next, the statement i wrote is this ( following your steps ):

Code: Select all

MyConsulta->SQL->Clear();
                                MyConsulta->SQL->Text = "INSERT INTO planta ( id, plano ) VALUES (:identificador, :plano)";
                                MyConsulta->ParamByName("identificador")->AsInteger = num_id;
                                MyConsulta->ParamByName("plano")->AsBlobRef->LoadFromFile("C:\temp.bmp");
                                MyConsulta->Execute();

Where MyConsulta is a TMyQuery object.

So, where is the problem???

[Ithilien]

num_id is an integer and temp.bmp is a correct bmp file created

[Ithilien]

The right code is ( excuse me all please ):

Code: Select all

MyConsulta->SQL->Clear();
MyConsulta->SQL->Text = "INSERT INTO planta ( id_planta, plano ) VALUES (:identificador, :plano)";                                MyConsulta->ParamByName("identificador")->AsInteger = num_id;
MyConsulta->ParamByName("plano")->AsBlobRef->LoadFromFile("C:\temp.bmp");
MyConsulta->Execute();

But doesn't work again...

[Antaeus]

Is there any error raised, or all commands are executed but the picture is not inserted? Please describe this in more detail, including the exact error message.

[Ithilien]

Antaeus, i tried to use only one parameter, it is, the id of the picture and all statements run successfully and one row is inserter into the database. But when i use the parameter picture, an exception is raised. I handle it, but if i disable the handling, the message shown is the following ( my project is called SIGE ):

Project SIGE.exe raised exception class EMySqlException with GetLastError return 10054($2746)'. Process stopped. Use Step or Run to continue.

[Ithilien]

Antaeus, i tried to use only one parameter, it is, the id of the picture and all statements run successfully and one row is inserter into the database. But when i use the parameter picture, an exception is raised. I handle it, but if i disable the handling, the message shown is the following ( my project is called SIGE ):

Project SIGE.exe raised exception class EMySqlException with 'Lost Connection to MySQL server during query Socket error on write.WSAGetLastError return 10054($2746)'. Process stopped. Use Step or Run to continue.

[Antaeus]

It looks like the problem with the max_allowed_packet parameter of MySQL.
Try to set max_allowed_packet to a value that is greater than size of your file.
Just add the following line to the [mysqld] section of my.ini and restart the server:

Code: Select all

  max_allowed_packet = 16M 

[Ithilien]

Antaeus, when one user identifies by himself a sentence is executed:

Code: Select all

MyConsulta->SQL->Clear();
MyConsulta->SQL->Add("set max_allowed_packet=1600000000");
MyConsulta->Execute();

And, in the first code i put, the picture is inserted, so it can't be possible, isn't? Unless, the sentences below only take effect to localhost...

[Ithilien]

Oh my god Antaeus, i restarted the server and it workssssssssssssssss!!!!

Thanks a lot!!!!!!!!!!!!!!!!!!!!!!

Best regards,

Carlos.