Parmetrized Query and Blobs

[alk]

using delphi 7 and oracle 10g / dbexpress oracle*net I would like to insert blobs bigger than 4K into a table.

I found some other users facing with this problems but there was no solutions to the topic. So I added to your blob-demo tool a button with the followinf code:

----
SQLQuery.Text := UPDATE ODAC_BLOB SET PIC=:PIC WHERE ID=1'

(row with ID = 1 exists by adding a big picture via the dataset)

SQLQery.ParamByName('PIC').LoadFromFile('c:\image.bmp', ftBlob);
SQLQery.ExecSQL;

----

well I know the databse can handle big pictures but if I try to insert pictrures > 4K there is nothing in the table ;o(((

The code was used for a long time with your exprtess driver for mysql and everything was fine...

thanx
alex

[Paul]

Please check the value of TParam.DataType before calling SQLQery.ExecSQL.
ftBlob constant corresponds to LONG RAW type. You must use ftOraBlob constant.

[alk]

ok, now I get an error, that the lob should be initied (insert and update). Do you have an correct example how I can upload / download something to a blob and clob?

in mysql with your dbexpress driver everything is fine and the dataset-example also works fine - so that I think that I'm and/or the expressdiver makes the difference.

Well, the reason for my trial is, that I need to use a clob for texts > 4K and this also does not work via a parametrized query. Is there an example too? I have to use a query, so I need an urgent solution ;o(

Thank you very much!!!!
Alex

[Paul]

The normal way to insert BLOB and CLOB parameters in Oracle and dbExpress is to use SQL with RETURNING clause. Please see the following example

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  s: string;
  ms: TMemoryStream;
  i: integer;
begin
  SQLConnection1.Connected := True;

  SQLQuery1.SQL.Text := 'INSERT INTO ODAC_BLOB ' +
    '  (ID, TITLE, PIC) ' +
    'VALUES ' +
    '  (:ID, :TITLE, empty_blob()) ' +
    'RETURNING ' +
    '  PIC ' +
    'INTO ' +
    '  :PIC ';

  s:='';
  for i:=0 to 1000 do
    s:=s+'0123456789';
  ms := TMemoryStream.Create;
  ms.Write(s[1], Length(s));
  SQLQuery1.Params[0].AsInteger := 4;
  SQLQuery1.Params[1].AsString := '123';
  SQLQuery1.Params[2].LoadFromStream(ms, ftOraBlob);
  ms.Free;
  SQLQuery1.ExecSQL(false);
end;