Assigning value to BLOB parameter (IB vs mssql vs Oracle)

tonymeadors · Post by **tonymeadors** » Wed 28 Sep 2011 17:43

Background:
We are moving a suite of tools from D7 to D2010 and changing from dbexpress connections to unidac-based connections.
With the same software we support IB, FB, MSSQL & Oracle.
The bear is dealing with all the datatype differences and trying to
how to write code that works for all the dbs.

------------------------------
q: tuniquery;
s: string;

First just want to fill a BLOB with the contents of a string var

s:='hello to the entire world.';
q.SQL.add('update email_pending set body_data= :data where id=10');
q.parambyname('data').asblob:= bytesof(s);
q.ExecSQL;

//Interbase WORKS
//sql server WORKS
//oracle DOESNT WORK - EMPTIES THE BLOB

--(try again)

s:='hello to the entire world.';
q.SQL.add('update email_pending set body_data= :data where id=10');
q.parambyname('data').asblob:= bytesof(s);
q.parambyname('data').paramtype:=ptInput;

q.ExecSQL;

//Interbase WORKS
//sql server WORKS
//oracle WORKS only if the paramtype is set

> I am looking for the most consistent code across databases.
> Is this the accepted method? Using bytesof() and having
> to add an additional line in the Oracle case?

------------------------------------------------

Now filling the BLOB with the contents of a file

s:='C:\bd\delphi2010\work\myimages\cash.bmp';
q.SQL.add('update email_pending set body_data= :data where id=10');
q.parambyname('data').loadfromfile(s,ftblob);
q.parambyname('data').paramtype:=ptInput;

q.ExecSQL;

//Interbase WORKS
//sql server WORKS
//oracle DOESNT WORK field still empty

--(try again)

s:='C:\bd\delphi2010\work\myimages\cash.bmp';
q.SQL.add('update email_pending set body_data= :data where id=10');
q.parambyname('data').loadfromfile(s,ftORAblob);
q.parambyname('data').paramtype:=ptInput;

q.ExecSQL;

//Interbase DOESNT WORK
//sql server DOESNT WORK
//oracle WORKS only if paramtype is manually set

> Again I am looking for the most consistent code across databases.
> Is this the accepted method? Literally Coding a different datatype
> when connected to an Oracle db?

> In the past, With the dbexpress tsqlquery and such we would
> use xxx.asblob:= 'anystringatall' for all three cases,
> and ftorablob was not needed for oracle file load.

Thank you for your knowledge and time,
tonyM

AlexP · Post by **AlexP** » Fri 30 Sep 2011 11:13

Hello,

I cannot reproduce the problem.
Please try using the latest UniDAC version 4.0.1 and check if the problem persists.

tonymeadors · Post by **tonymeadors** » Fri 30 Sep 2011 18:01

I should focus on one example at a time...

Using Unidac components 4.01 connected to Oracle 10g
uniconnection <- uniquery

The following works with IB and SQL server but
not with Oracle.

The questions are,
1) should it?
2) is there one code line to fill the blob parameter that works
for all these dbs or is this a case of needing to conditionally
do it differently for Oracle?

s:='hello to the entire world.';
q.SQL.add('update email_pending set body_data= :data where id=10');
q.parambyname('data').asblob:= bytesof(s);
q.ExecSQL;

//Oracle has the blob "cleared" when this is executed

thanks,
tonyM

AlexP · Post by **AlexP** » Mon 03 Oct 2011 10:07

Hello,

We've reproduced this problem on using the Direct mode, and we will try to fix it in the nearest future.
For the time being you can either use the OCI mode or specify the parameter type explicitly.