List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:August 7 2010 2:39pm
Subject:Re: Insert blob data using prepared statements
View as plain text  
On 7/26/2010 2:30 AM, Manasi Save wrote:
> Hi All,
> I need to insert Blob data in my table using prepared statements. But 
> Whenever I try to insert it using prepared statement it is giving me 
> mysql syntax error.
> Here's the prepared statement :-
> SET @stmt = Concat(Insert into ',mydb,'.MyTable(MyData, MyID)
>      Select ','"',Inputdata,'"',',',InputID,';');
> Prepare stmt1 From @stmt;
> Execute stmt1;
> Deallocate prepare stmt1;
> The executing statement looks like this :-
> Insert into `mydb`.MyTable(MyData, MyID)
> Select ** STREAM DATA **, 1;
> This gives me an error saying mysql syntax near ** STREAM 
> DATA..................
> Can anyone give me any example how to insert blob data in database with 
> prepared statement.

First, have you tried using INSERT ... VALUES ... instead of INSERT ... 
SELECT ... ?

Second, have you tried passing the STREAM data into the EXECUTE command 
as a parameter? One of the nice things about prepared statements is 
their ability to substitute data into the statement at runtime. For 
example, your statement could be

'INSERT INTO `mydb`.MyTable(MyID, MyDATA) VALUES (?,?)'

and your execute could be

EXECUTE stmt1 (1, 'stream data');

Depending on how you connect, you may also be able to bind one of those 
? parameters to a variable in your code. That would completely eliminate 
the need to copy and escape your data into a quoted string literal.

Third, you must always be aware of the max_allowed_packet size for the 
connection you are on. If you attempt to send a command larger than that 
size, the server will forcibly disconnect your session under the 
impression that you are attempting to sabotage the machine by sending 
queries that are too large.

Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
Insert blob data using prepared statementsManasi Save26 Jul
  • Re: Insert blob data using prepared statementsMySQL)7 Aug