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
Thread
Insert blob data using prepared statementsManasi Save26 Jul
  • Re: Insert blob data using prepared statementsMySQL)7 Aug