#636 BATCH-DML bindings for MariaDB: impossible to set NULL value for field

[Max Terentiev]

Hi,

1. Its not possible to set NULL value for field using array DML with mariadb. If use it this way:

ZQuery.Params[0].IsNulls[DMLIdx]:=true;

it store 0 to target field instead of NULL. But NULL means no value while 0 - is value contains 0 :-) Its very different and affect queries logic.

1. Another problem: if ZQuery.Params[0].IsNulls[DMLIdx]:=true; adding FIRST value in DML batch - Zeos throw error: unsupported parameter type stUnknown, Index 1 To avoid this error this line must be added:

ZQuery.Params[0].SQLType:=stInteger;

Is this also bug ?

Here is complete code to reproduce problems. I use MariaDB 11.8.3, C-Connector 3.4.5 and Zeos patches rev 8389:

procedure TForm1.Button1Click(Sender: TObject);
var
DMLidx:Integer;
begin
ZConnection.Connect;
ZQuery.SQL.Text:='CREATE TABLE IF NOT EXISTS TestTable (TestID INTEGER)';
ZQuery.ExecSQL;
ZConnection.StartTransaction;
ZQuery.SQL.Text := 'INSERT INTO TestTable (TestID) VALUES (:TID)';
ZQuery.Prepare;
ZQuery.Params.BatchDMLCount := 100;

// Next line must be added to avoid error: unsupported parameter type stUnknown, Index 1
// If FIRST batch value for column set IsNulls !!
// Is this also bug ?

ZQuery.Params[0].SQLType:=stInteger; // << error whitout this line

for DMLidx := 0 to 99 do
  begin
  if Random(3)=2 then
    ZQuery.Params[0].IsNulls[DMLIdx]:=true // this add 0 instead of NULL
  else
    ZQuery.Params[0].AsIntegers[DMLIdx]:=DMLIdx;
  end;
ZQuery.ExecSQL;
ZConnection.Commit;
end;