Problem using params in TIBCScript

[strThunder]

Hello!
I have some problems using Params(BLOB-type) in TIBCScript component.
IBDAC Version - 3.50.0.21, Delphi XE.
My script contains parameters like :hXXX_YYY, where XXX - is offset in some TStream and YYY - it's size (IBExpert-like metadata output).
At first:

Code: Select all

IBCScript.SQL := ScriptText;

Then I search for parameters in the script text using regilar expression, storing offset and size in variables and for each:

Code: Select all

IBCScript.Params.CreateParam(ftBlob, paramname, ptInput);
BlobStream.Seek(paramoffset, soFromBeginning);
ParamStream.Clear;
ParamStream.CopyFrom(BlobStream, paramsize);
ParamStream.Seek(0, soFromBeginning);
IBCScript.Params.ParamByName(paramname).LoadFromStream(ParamStream, ftBlob);

where ParamStream is some TMemoryStream.

And, at the end -

Code: Select all

IBCScript.Execute;

Everything seems to be ok, the code is working.
After its finished, i've looked into a database and found that all BLOBs, filled by this script IS NULL.
Is it a bug, or am doing something wrong?

[AndreyZ]

Hello,

I cannot reproduce the problem. Please try executing the following code:

Code: Select all

procedure TMainForm.BitBtnClick(Sender: TObject);
begin
var
  ps: TMemoryStream;
begin
  ps := TMemoryStream.Create;
  ps.LoadFromFile('filename'); //filename is any not big file
  IBCScript1.SQL.Text := 'insert into test_blob(id, blb) values(:id, :blb)'; // id - INTEGER, blb - BLOB SUB_TYPE 0
  IBCScript1.Params.CreateParam(ftInteger, 'id', ptInput);
  IBCScript1.Params.CreateParam(ftBlob, 'blb', ptInput);
  IBCScript1.Params.ParamByName('id').AsInteger := 1;
  IBCScript1.Params.ParamByName('blb').LoadFromStream(ps, ftBlob);
  IBCScript1.Execute;
  ps.Free;
end;

Also note that it's better to use the TIBCSQL component to execute queries that have parameters and don't return rows. You don't have to create parameters manually with the TIBCSQL component, they will be created automatically.

[strThunder]

Hello!
Thanx for so quick answer!

I've tried the code you give me. But I made some changes:
1. My script contains 3 statements:

Code: Select all

insert into test_blob(id, blb) values(2, :blb1);
insert into test_blob(id, blb) values(3, :blb2);
insert into test_blob(id, blb) values(4, :blb3);

2. Changes in the code:

Code: Select all

  for i := 1 to 3 do
  begin
    if OpenDialog1.Execute then
    begin
      ps := TMemoryStream.Create;
      ps.LoadFromFile(OpenDialog1.FileName); 
      IBCScript1.Params.CreateParam(ftInteger, 'id'+inttostr(i), ptInput);
      IBCScript1.Params.CreateParam(ftBlob, 'blb'+inttostr(i), ptInput);
      IBCScript1.Params.ParamByName('id'+inttostr(i)).AsInteger := i;
      IBCScript1.Params.ParamByName('blb'+inttostr(i)).LoadFromStream(ps, ftBlob);
      ps.Free;
    end;
  end;
  IBCScript1.Execute;

Again, everithing executed fine, but database contains:

Code: Select all

ID         BLB
1             {This record inserted ok}
2           
3           

As you can see, first record have nonempty BLB value, and others have only ID filled, but not BLB.
Next, i've tried to change source code as follows:

Code: Select all

  for i := 1 to 3 do
  begin
    if OpenDialog1.Execute then
    begin
      ps := TMemoryStream.Create;
      ps.LoadFromFile(OpenDialog1.FileName); 
      IBCScript1.Params.CreateParam(ftInteger, 'id'+inttostr(i), ptInput);
      IBCScript1.Params.CreateParam(ftBlob, 'blb'+inttostr(i), ptInput);
      IBCScript1.Params.ParamByName('id'+inttostr(i)).AsInteger := i;
      IBCScript1.Params.ParamByName('blb'+inttostr(i)).LoadFromStream(ps, ftBlob);
      ps.Free;
    end;
    IBCScript1.ExecuteNext;
  end;

And everything works and filled! I think that BLOB params becomes NULL somewhere "between" of executing statements.
Using of second procedure undesireable in my situation because of big script, contains ~100k records with BLOB fields and using of regular expressions to parse stream position of BLOB value.
Thanx again!

[AndreyZ]

You've found a correct solution. When you are trying to execute several statements with parameters, you should use the ExecuteNext method instead of Execute. In the Execute method parameters are recreated for each statement execution.

[strThunder]

But integer, string, etc... params works ok. Situation persists only with BLOB Parameters!

[AndreyZ]

You will not have problems with other types only if you don't use parameters for them in your script. Please try executing the following code:

Code: Select all

procedure TMainForm.BitBtnClick(Sender: TObject);
begin
var
  ps: TMemoryStream;
begin
  IBCScript1.SQL.Clear;
  IBCScript1.SQL.Add('insert into ibdac_blob(id, blb) values(:id1, :blb1);');
  IBCScript1.SQL.Add('insert into ibdac_blob(id, blb) values(:id2, :blb2);');
  IBCScript1.SQL.Add('insert into ibdac_blob(id, blb) values(:id3, :blb3);');
  for i := 1 to 3 do
  begin
    if OpenDialog1.Execute then begin
      ps := TMemoryStream.Create;
      ps.LoadFromFile(OpenDialog1.FileName);
      IBCScript1.Params.CreateParam(ftInteger, 'id'+inttostr(i), ptInput);
      IBCScript1.Params.CreateParam(ftBlob, 'blb'+inttostr(i), ptInput);
      IBCScript1.Params.ParamByName('id'+inttostr(i)).AsInteger := i;
      IBCScript1.Params.ParamByName('blb'+inttostr(i)).LoadFromStream(ps, ftBlob);
      ps.Free;
    end;
  end;
  IBCScript1.Execute;
end;

You will have a validation error for column ID, because it cannot contain NULL values (ID INTEGER NOT NULL).

[strThunder]

Ок, thanx for help!