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
