Trouble on trying to use BatchDML

[brunodelmondes]

Hey guys!

I'm facing a trouble to use BatchDML on Delphi 2010, Zeos SVN 7683 and Firebird 2.1.7.

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  indice : cardinal;
  price, taxes : Currency;
begin
  price := 0;
  taxes := 0;

  with ZQuery1 do
  begin
    Connection := ZConnection1;

    Active := false;
    SQL.Clear;
    SQL.Add('insert into sometable (column_string1, column_string2, column_currency1, column_currency2)');
    SQL.Add('values (:column_string1, :column_string2, :column_currency1, :column_currency2)');
    Params.BatchDMLCount := 100;

    for indice := 0 to Pred(Params.BatchDMLCount) do
    begin
      Params[0].SQLType := stUnicodeString;
      Params[1].SQLType := stUnicodeString;
      Params[2].SQLType := stCurrency;
      Params[3].SQLType := stCurrency;

      Randomize;
      price := Random(1001) / 100;
      taxes := 5.0;

      Params[0].AsUnicodeStrings[indice] := 'array ' + IntToStr(indice);
      Params[1].AsUnicodeStrings[indice] := 'product arraydml ' + IntToStr(indice);
      Params[2].AsCurrencys[indice] := price;
      Params[3].AsCurrencys[indice] := taxes;
    end;

    ExecSQL;
    ShowMessage('Rows affected ' + IntToStr(RowsAffected));
  end;
end;

When I run the above code, I get a 'Invalid Variant-Type for String-Array binding!' exception on line 4303 of ZDbcStatement unit.
What I am doing wrong?

Thank you!

[marsupilami]

Hello Bruno,

I'm facing a trouble to use BatchDML on Delphi 2010, Zeos SVN 7683 and Firebird 2.1.7.

I am not sure, if we support Batch DML on this version of Firebird because Firebird introduced its Batch API with a later version. Not sure if that was in version 3 or version 4. But maybe we do something with execute block there to simulate it. Does that also happen on Firebird 4? Maybe you could try that using an embedded server?

For the rest I will have to look. Might take a bit of time.

Best regards,

Jan

[brunodelmondes]

Hello Jan!

I am not sure, if we support Batch DML on this version of Firebird because Firebird introduced its Batch API with a later version. Not sure if that was in version 3 or version 4.

Well, I'm also not sure about this, but Zeos recognizes FB 2.1 with support for BatchDML, see the screenshots below.

[miab3]

@brunodelmondes,

And what happens when you move it:

Params[0].SQLType := stUnicodeString;
Params[1].SQLType := stUnicodeString;
Params[2].SQLType := stCurrency;
Params[3].SQLType := stCurrency;

before the For loop or delete it at all?

Michał

[brunodelmondes]

Hello Michal!

Code: Select all

Params[0].SQLType := stUnicodeString;
Params[1].SQLType := stUnicodeString;
Params[2].SQLType := stCurrency;
Params[3].SQLType := stCurrency;

Before the Loop, same " Invalid Variant-Type for String-Array binding! " error... without it occurs " Unsupported parameter type, SQLType: stCurrency/stUnicodeString. " error.

[miab3]

For me it works on:

Firebird-2.5.9.27152-0_x64,
ZEOS 8 svn 7683,
Delphi 11- Win32/Win64

These lines do not compile at all for me:
Params[0].SQLType := stUnicodeString;
Params[1].SQLType := stUnicodeString;
Params[2].SQLType := stCurrency;
Params[3].SQLType := stCurrency;

Michał

[brunodelmondes]

These lines do not compile at all for me:
Params[0].SQLType := stUnicodeString;
Params[1].SQLType := stUnicodeString;
Params[2].SQLType := stCurrency;
Params[3].SQLType := stCurrency;

It needs declaration of ZDbcIntfs unit.

[brunodelmondes]

I'm sorry guys, I have found the problem, actually Michal is right, there is no need of the code below:

Params[0].SQLType := stUnicodeString;
Params[1].SQLType := stUnicodeString;
Params[2].SQLType := stCurrency;
Params[3].SQLType := stCurrency;

I was testing with that because I have found this topic where EgonHugeist says it's important to set the parameters type.
viewtopic.php?f=50&t=129484&p=160108&hi ... nt#p160601

Well, thank you guys, and sorry for wasting your time. Be safe.