Insert blob data using prepared statements

--=_6jgvu1hd0rcw
Content-Type: text/plain;
charset=UTF-8
Content-Description: Plaintext Version of Message
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

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.

Thanks in advance.

--
Regards,
Manasi Save
Artificial Machines Private Limited
manasi.save@artificialmachines.com
Ph:-9833537392

--=_6jgvu1hd0rcw
Content-Type: multipart/related;
boundary="=_73ucc32e7r7k";
start="73vfojrga3cw@mail.artificialmachines.com"
Content-Transfer-Encoding: 7bit

This message is in MIME format.

--=_73ucc32e7r7k
Content-Type: text/html;
charset=UTF-8
Content-Description: HTML Version of Message
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-ID: 73vfojrga3cw@mail.artificialmachines.com

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.

 

Thanks in advance.


--
Regards,
Manasi Save
Artificial Machines Private
Limited
manasi.save@artificialmachines.com
Ph:-9833537392

--=_73ucc32e7r7k--

--=_6jgvu1hd0rcw--

Re: Insert blob data using prepared statements

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org