Batch Updates Problem

[joongtang]

Unidac 7.0.2 Batch Update have some problem.

Test scheme is like this.
CREATE TABLE TABLE1 (
COLUMN1 varchar(10) NOT NULL,
COLUMN2 int(11) DEFAULT NULL,
PRIMARY KEY (COLUMN1)
)

Here is the code that It's works.

Qry.Close;
Qry.SQL.Text := 'UPDATE TABLE1 SET COLUMN2 = :COLUMN2 WHERE COLUMN1 = :COLUMN1 ';

Qry.Params[0].DataType := ftInteger;
Qry.Params[1].DataType := ftString;

Qry.Params.ValueCount := 2;

...

Qry.Execute(Qry.Params.ValueCount);

But this code throws an EMySqlException.
Qry.SQL.Text := ' UPDATE TABLE1 SET COLUMN2 = :COLUMN2 WHERE COLUMN1 = :COLUMN1 ';
---------------------// insert space!!

Here is error message

Code: Select all

#42000 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?1 WHERE COLUMN1 = ?'0000003302;UPDATE TABLE1 SET COLUMN2 = ?1 WHERE COLUMN1 = ?' at line 1'.

[ertank]

Hello,

I believe your below two lines should be switched:

Code: Select all

Qry.Params[0].DataType := ftInteger;
Qry.Params[1].DataType := ftString;

to

Code: Select all

Qry.Params[0].DataType := ftString;
Qry.Params[1].DataType := ftInteger;

You seem to mix parameters. Please test below code which works just fine. (You will need additional Memo1 object on your form to be able to compile and run it)

Code: Select all

  UniQuery1.Close;
  UniQuery1.SQL.Text := 'UPDATE TABLE1 SET COLUMN2 = :COLUMN2 WHERE COLUMN1 = :COLUMN1 ';

  Memo1.Lines.Add('Param1 index from 1st SQL: ' + UniQuery1.ParamByName('COLUMN2').Index.ToString());
  Memo1.Lines.Add('Param2 index from 1st SQL: ' + UniQuery1.ParamByName('COLUMN1').Index.ToString());
  Memo1.Lines.Add('Param1 name from 1st SQL: ' + UniQuery1.Params[0].Name);
  Memo1.Lines.Add('Param2 name from 1st SQL: ' + UniQuery1.Params[1].Name);
  UniQuery1.ParamByName('COLUMN1').DataType  := ftString;
  UniQuery1.ParamByName('COLUMN2').DataType  := ftInteger;
  UniQuery1.ParamByName('COLUMN1').AsString  := 'Test1';
  UniQuery1.ParamByName('COLUMN2').AsInteger := 3;
  UniQuery1.ExecSQL();


  UniQuery1.SQL.Text := ' UPDATE TABLE1 SET COLUMN2 = :COLUMN2 WHERE COLUMN1 = :COLUMN1 ';

  Memo1.Lines.Add('Param1 index from 2nd SQL: ' + UniQuery1.ParamByName('COLUMN2').Index.ToString());
  Memo1.Lines.Add('Param2 index from 2nd SQL: ' + UniQuery1.ParamByName('COLUMN1').Index.ToString());
  Memo1.Lines.Add('Param1 name from 2nd SQL: ' + UniQuery1.Params[0].Name);
  Memo1.Lines.Add('Param2 name from 2nd SQL: ' + UniQuery1.Params[1].Name);
  UniQuery1.ParamByName('COLUMN1').DataType  := ftString;
  UniQuery1.ParamByName('COLUMN2').DataType  := ftInteger;
  UniQuery1.ParamByName('COLUMN1').AsString  := 'Test2';
  UniQuery1.ParamByName('COLUMN2').AsInteger := 5;
  UniQuery1.ExecSQL();

Pay attention to that COLUMN2 parameter has Parameter Index number 0 (zero) and COLUM1 has Parameter index number 1.

[ViktorV]

Unidac 7.0.2 Batch Update have some problem.

Test scheme is like this.
CREATE TABLE TABLE1 (
COLUMN1 varchar(10) NOT NULL,
COLUMN2 int(11) DEFAULT NULL,
PRIMARY KEY (COLUMN1)
)

Here is the code that It's works.

Qry.Close;
Qry.SQL.Text := 'UPDATE TABLE1 SET COLUMN2 = :COLUMN2 WHERE COLUMN1 = :COLUMN1 ';

Qry.Params[0].DataType := ftInteger;
Qry.Params[1].DataType := ftString;

Qry.Params.ValueCount := 2;

...

Qry.Execute(Qry.Params.ValueCount);

But this code throws an EMySqlException.
Qry.SQL.Text := ' UPDATE TABLE1 SET COLUMN2 = :COLUMN2 WHERE COLUMN1 = :COLUMN1 ';
---------------------// insert space!!

Here is error message

Code: Select all

#42000 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?1 WHERE COLUMN1 = ?'0000003302;UPDATE TABLE1 SET COLUMN2 = ?1 WHERE COLUMN1 = ?' at line 1'.

Please specify the exact MariaDB version you are using.

[joongtang]

ertank, You're right. But Error occurs even if the parameter is correctly inserted.

mariadb version is 5.5.5-10.1.21

[ViktorV]

Thank you for the information. We have reproduced and fixed the issue. This fix will be included in the next build of UniDAC.

[joongtang]

Thanks ^^

[ViktorV]

Thank you for being interested in our products.
Feel free to contact us if you have any further questions about our products.