Page 1 of 1
Batch Updates Problem
Posted: Mon 29 May 2017 04:53
by 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'.
Re: Batch Updates Problem
Posted: Mon 29 May 2017 06:49
by 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.
Re: Batch Updates Problem
Posted: Mon 29 May 2017 09:48
by ViktorV
joongtang wrote: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.
Re: Batch Updates Problem
Posted: Tue 30 May 2017 07:03
by joongtang
ertank, You're right. But Error occurs even if the parameter is correctly inserted.
mariadb version is 5.5.5-10.1.21
Re: Batch Updates Problem
Posted: Tue 30 May 2017 14:25
by ViktorV
Thank you for the information. We have reproduced and fixed the issue. This fix will be included in the next build of UniDAC.
Re: Batch Updates Problem
Posted: Wed 31 May 2017 08:22
by joongtang
Thanks ^^
Re: Batch Updates Problem
Posted: Wed 31 May 2017 10:11
by ViktorV
Thank you for being interested in our products.
Feel free to contact us if you have any further questions about our products.