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
> 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.
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN