SQL SINTAX ???

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
SergioFeitoza
Posts: 18
Joined: Thu 29 Sep 2016 14:41

SQL SINTAX ???

Post by SergioFeitoza » Sun 29 Sep 2019 21:36

I use MyDAC and a MySql database. My code is working well except for one thing. I have a form in which there is a dbgrid in which I collect the coordinates of a certain component that I can move with the mouse. The coordinates are the variables leftdim and topdim in the code below. You may see the Table and fields Left and Top in the figure. I added the code below which may be useful to some of you. It was working with FireDac but when I changed to MyDac the following occur.
When I move the component with the mouse ( code ControlMouseUp below) the coordinates Left and Top are saved in the memory and appear in the DBgrid. However if I close the code they are saved in the database. The reason is that the part of the code below give an error when UPDATING . It is the classical SQL SINTAX showed in the figure . I tested many-many times and could not identify where the sintax is wrong. I use MYSQL 8 Community version and is updated.
Just for information, If I type the coordinates in the DBgrid and save them they are saved normally in the data base.
Please help me

procedure TForm700x900.ControlMouseUp(Sender: TObject; Button: TMouseButton;
Shift: TShiftState; X, Y: integer);
var
pt1: TPoint; leftDim,topDim,IDnum:integer; ctrl : TWinControl;
begin

with TWinControl(Sender) do
begin
GetCursorPos(PT1);
LabelScreen.Caption := ' X='+IntToStr(pt1.x)+', Y='+IntToStr(pt1.y);
ctrl := FindVCLWindow(PT1) ;
if ctrl <> nil then
begin
Caption := ctrl.Name;
if ctrl is TDBedit then
begin
leftDim:= TDBedit(ctrl).left;
topDim:= TDBedit(ctrl).Top;
dbeNumStr := StringReplace(Caption, 'L_', '',[rfReplaceAll, rfIgnoreCase]);
end;
end;
end;
///////////////////// Here is the error //////
dmsfcd.posicoesDBE.Locate('dbeNum',dbeNumStr, []);
IDnum:= dmsfcd.posicoesDBE.FieldByName('dbeNum').AsInteger; // this is a defined integer
dmsfcd.posicoesDBE.SQL.Clear;
dmsfcd.posicoesDBE.SQL.text:= 'UPDATE posicoesdbe SET Left = :left, Top = :top WHERE ID = :id';
dmsfcd.posicoesDBE.ParamByName('id').Value := IDnum; // this is a defined integer
dmsfcd.posicoesDBE.ParamByName('left').Value := leftDim; // this is a defined integer
dmsfcd.posicoesDBE.ParamByName('top').Value := topDim; // this is a defined integer
dmsfcd.posicoesDBE.ExecSQL;
////////////////////////////////////
F0D.DBgrid_DBedits.Repaint;
end;

end;

http://www.cognitor.com.br/TableandErrorMessage.png

ViktorV
Devart Team
Posts: 2794
Joined: Wed 30 Jul 2014 07:16

Re: SQL SINTAX ???

Post by ViktorV » Mon 30 Sep 2019 06:45

To solve your problem, please replace Left in your query with `Left`. For example:

Code: Select all

dmsfcd.posicoesDBE.SQL.text:= 'UPDATE posicoesdbe SET `Left` = :left, Top = :top WHERE ID = :id';

SergioFeitoza
Posts: 18
Joined: Thu 29 Sep 2016 14:41

Re: SQL SINTAX ???

Post by SergioFeitoza » Mon 30 Sep 2019 12:03

Thank you Viktor
With this modification, I do not get an error when doing the line dmsfcd.posicoesDBE.ExecSQL;
The corrected line was
dmsfcd.posicoesDBE.SQL.text:= 'UPDATE posicoesdbe SET `Left` = :left, `Top` = :top WHERE `ID` = :id';

When doing the command above the itens disappeared from the DGgrid. So I added the lines below and worked but when I close the program the new data is not saved

It is like missing a RefreshRecord. Could you please comment

dmsfcd.posicoesDBE.Locate('dbeNum',dbeNumStr, []);
IDnum:= dmsfcd.posicoesDBE.FieldByName('dbeNum').AsInteger; // this is a defined integer
dmsfcd.posicoesDBE.SQL.Clear;
dmsfcd.posicoesDBE.SQL.text:= 'UPDATE posicoesdbe SET `Left` = :left, `Top` = :top WHERE `ID` = :id';
dmsfcd.posicoesDBE.ParamByName('id').Value := IDnum; // this is a defined integer
dmsfcd.posicoesDBE.ParamByName('left').Value := leftDim; // this is a defined integer
dmsfcd.posicoesDBE.ParamByName('top').Value := topDim; // this is a defined integer
dmsfcd.posicoesDBE.ExecSQL;

dmsfcd.posicoesDBE.SQL.text:= 'SELECT * from posicoesdbe ';
dmsfcd.posicoesDBE.ExecSQL;

ViktorV
Devart Team
Posts: 2794
Joined: Wed 30 Jul 2014 07:16

Re: SQL SINTAX ???

Post by ViktorV » Mon 30 Sep 2019 14:22

To give you the most detailed answer possible, we need a small sample demonstrating that behavior. Please compose and send it to us, along with scripts for creating and populating database objects via the e-support form: https://devart.com/company/contactform.html. Which version of MySQL do you use?

SergioFeitoza
Posts: 18
Joined: Thu 29 Sep 2016 14:41

Re: SQL SINTAX ???

Post by SergioFeitoza » Mon 30 Sep 2019 17:14

Dear Viktor.
Thank you for your answer. There is no problem for me in sending the code . The difficult thing is to separate it from the rest of the big problem in which it is inserted. This would take me some two days. I will do some trials more alone and if I do not succeed I will do what you suggest.. In this forum, how can I send an attachment to you ( to send the form and unit) ?

A simple question that may solve my problem.
As I am using a TmyQuery, how can I replace these two lines of code by a query text?
They work in Firedac for years but by some reason, the "grammar" is not understood by MyDAC and give an error (the same which was corrected by your previous suggestion)

if F0D.ShowOnly_Y_DBedits.checked then
dmsfcd.posicoesDBE.Filter := 'MyFieldA = ' + quotedStr(dmsfcd.cds_Proj.FieldByName('MyFieldA).AsString)
+ ' AND ' + MyFieldB = ' + quotedStr('Y')
else dmsfcd.posicoesDBE.Filter := 'MyFieldA = ' + quotedStr(dmsfcd.cds_Proj.FieldByName('MyFieldA').AsString);

SergioFeitoza
Posts: 18
Joined: Thu 29 Sep 2016 14:41

Re: SQL SINTAX ???

Post by SergioFeitoza » Tue 01 Oct 2019 15:35

The query below does not return any value. I use only a MyQuerY component. Where is the syntax error?

dmsfcd.PosicoesDBE.Connection:=dmsfcd.ConnectionSFC;
dmsfcd.PosicoesDBE.Open;
dmsfcd.ds_posicoesDBE.DataSet:= dmsfcd.posicoesDBE ;

dmsfcd.posicoesDBE.Locate('dbeNum',dbeNumStr, []);
IDnum:= dmsfcd.posicoesDBE.FieldByName('dbeNum').AsInteger; // this is a defined integer
dmsfcd.posicoesDBE.DisableControls;
dmsfcd.posicoesDBE.Edit;
dmsfcd.posicoesDBE.FieldValues['Left'] := leftDim;
dmsfcd.posicoesDBE.FieldValues['top'] := topDim;

dmsfcd.posicoesDBE.SQL.Clear;
dmsfcd.posicoesDBE.SQL.text:= 'UPDATE posicoesdbe SET `Left` = :leftx, `Top` = :topx WHERE ID = :idx ';
dmsfcd.posicoesDBE.ParamByName('idx').Value := IDnum; // this is a defined integer
dmsfcd.posicoesDBE.ParamByName('leftx').Value := leftDim; // this is a defined integer
dmsfcd.posicoesDBE.ParamByName('topx').Value := topDim; // this is a defined integer
dmsfcd.posicoesDBE.ExecSQL;
dmsfcd.posicoesDBE.EnableControls;

SergioFeitoza
Posts: 18
Joined: Thu 29 Sep 2016 14:41

Re: SQL SINTAX ???

Post by SergioFeitoza » Tue 01 Oct 2019 15:37

Something more or less like this worked well with FireDac.
Is there a possibility that MyDac do not like that fiel names are LEFT OR TOP ?

ViktorV
Devart Team
Posts: 2794
Joined: Wed 30 Jul 2014 07:16

Re: SQL SINTAX ???

Post by ViktorV » Wed 02 Oct 2019 07:44

In the example you specified, nothing can return, because you set the text of the UPDATE query in the SQL property.
We do not quite understand the essence of your question. If we understood correctly, you can use the following code to solve your problem:

Code: Select all

dmsfcd.PosicoesDBE.Connection:=dmsfcd.ConnectionSFC;
dmsfcd.PosicoesDBE.Open;
dmsfcd.ds_posicoesDBE.DataSet:= dmsfcd.posicoesDBE ;

dmsfcd.posicoesDBE.Locate('dbeNum',dbeNumStr, []);
IDnum:= dmsfcd.posicoesDBE.FieldByName('dbeNum').AsInteger; // this is a defined integer
dmsfcd.posicoesDBE.DisableControls;
dmsfcd.posicoesDBE.Edit;
dmsfcd.posicoesDBE.FieldValues['Left'] := leftDim;
dmsfcd.posicoesDBE.FieldValues['top'] := topDim;
dmsfcd.posicoesDBE.Post;
In this case, if the TMyQuery.SQLUpdate property is set to an empty string, MyDAC will automatically generate the update request you need and execute it.
If we understand something wrong, please compile and send us using the contact form https://www.devart.com/company/contactform.html a full example that demonstrates the behavior you specified, including scripts for creating and filling database objects.

SergioFeitoza
Posts: 18
Joined: Thu 29 Sep 2016 14:41

Re: SQL SINTAX ???

Post by SergioFeitoza » Wed 02 Oct 2019 19:16

Dear Viktor.
It is working well with your text below.
Actually , in the very beginning of this post, my text (as used before replacing Firedac by MyDac) was like your text below except for the line

dmsfcd.ds_posicoesDBE.DataSet:= dmsfcd.posicoesDBE ;

This line was defined before in another form. Also because I included after all, the sentence EnableControls. It was not working amd by some reason, I went for the solution of writing the SQL text (that I think should work but does not work)
Anyway problem solved and lets go ahead. With this post I learned about some good resources of MyDac.

A suggestion: to create a much more simplified demo for MyDac / UniDac using the basic types of SQL texts ( SELECT, UPDATE, INSERT ...). Your MyDacDemo is great but it is complex to extract the simple concepts from inside it.
Regards , thank again and till a next time

ViktorV
Devart Team
Posts: 2794
Joined: Wed 30 Jul 2014 07:16

Re: SQL SINTAX ???

Post by ViktorV » Fri 04 Oct 2019 09:55

Thank you for the interest to our product.
It is good to see that the problem has been solved.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.

Post Reply