Trying to use tblobfield value as blob parameter

[tonymeadors]

oracle10g Direct <- tuniconnection <-tuniquery

We can store a literal string into an Oracle Blob field
with:

s:='hello to the entire world.';
q.SQL.add('update test set blob1= :blob1 ');
q.parambyname('blob1').asblob:= bytesof(s);
q.parambyname('blob1').paramtype:=ptInput;
q.ExecSQL;

//oracle WORKS if the paramtype is explictly set


** BUT we can't seem to use a field value in the same way.

q.sql.add('select logo_graphic from logos where id=28');
q.open;

q2.sql.add('update test set blob2= :blob2');
q2.parambyname('blob2').asblob:= tblobfield(q.fieldbyname('logo_graphic')).value;
q2.parambyname('blob2').paramtype:=ptInput;
q2.execsql;

This reliably clears the table field under oracle while
it works OK for IB,FB,SQL Server.
(yes the selected field definitely provides bytes of data)

We have tried:
-using .asbytes rather than .value
-explicitly assigning a datatype of ftOraBlob instead
-explicitly setting bound to true
-and on and on.


Our goal as always is to employ a code idiom that works
for all our database types.

If anyone has sucessfully assigned a tblobfield value into
and oracle parameter and it arrives in the blob of a table table,
please give us a hint...

thanks,

tonyM

[tonymeadors]

Seems if the selected blob (begin shoved into parameter)
is tiny, like less that 5k, it works.
If its large - over 40k or so it seems to clear the table field.


We have seen this in the past with oracle blobs,
ie. working fine unless they exceed some size.

I seem to remember in the old days we would set
BlobSize to get around this.

Might we be able to specify something similar
under SPECIFICOPTIONS?


The following works for tiny graphics:

q.SQL.add('select logo_graphic from logos where id=28');
q.open;

q2.SQL.add('update ap_master set blob_string= :data ');
q2.ParamByName('data').asBlob:= tbytes( TBLOBFIELD(Q.FieldByName('LOGO_GRAPHIC').asbytes ) ) ;
q2.ParamByName('data').paramtype:=ptInput;
q2.ExecSQL;

[AlexP]

Hello,

We have fixed the problem with BLOB parameters in the direct mode, this fix will be included in the next version of the product.

[tonymeadors]

Awesome.

Thanks for your work,

tonyM

[tonymeadors]

Our Oracle 10g testing of the 4.0.2 still produces the odd effect
with larger graphics (tuniquery).

Recap:
Seems that when the selected blob (begin shoved into param)
is tiny, like less that 5k, it works.
If its large - over 40k or so it seems to clear the table field.

The following works for tiny graphics:

q.SQL.add('select logo_graphic from logos where id=28');
q.open;

q2.SQL.add('update ap_master set blob_field = :data ');
q2.ParamByName('data').asBlob:= bytesof(Q.FieldByName('LOGO_GRAPHIC').asstring ) ) ;
q2.ParamByName('data').paramtype:=ptInput;
q2.ExecSQL;

Two things:

- Any workaround you can suggest will suffice for now - but this sort of action (blobfield into param) is quite common in our database tool set.

- Our 60-day trial is about up and we definitely wish to purchase
the team license. Unidac is working quite well for us.
We only need reassurance that there is a workaround for this
issue before purchase.

Again, we do appreciate all you do,

Sincerely,
tonyM

[AlexP]

Hello,

Use the following code to insert data into BLOB fields:

Code: Select all

q2.SQL.add('update ap_master set blob_field = :data '); 
q2.ParamByName('data').paramtype:=ptInput; 
q2.ParamByName('data').DataType := ftOraBlob;
q2.ParamByName('data').AsBlobRef.AsBytes := bytesof(Q.FieldByName('LOGO_GRAPHIC').asstring ) ) ; 
q2.ExecSQL;

[tonymeadors]

Alex and team - Thank you,

That seems to work fine in the lastest
release 4.0.2 (direct mode to oracle using tuniquery).

Consider us a happy customer. ClearCycle Inc.
(25 years of insurance/banking software experience)

We will move to purchase the team license today.

We look forward to working with you - really.

sincerely,
tonyM
ClearCycle Inc.

[AlexP]

Hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.