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: 20
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: 3168
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: 20
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: 3168
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: 20
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: 20
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: 20
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: 3168
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: 20
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: 3168
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.

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

VCL x UNIGUI version performance with MyDAC

Post by SergioFeitoza » Thu 01 Apr 2021 13:16

I am writing above this my old post because I cannot find a button "CREATE A NEW POST". So, the first question is -- how can we add new questions?

My issue is “when using a MySql database, the Delphi components ( I use MyDac) work in the same way for VCL code or Unigui for web codes ?.

I use the same database components to connect – as well - the VCL Delphi version and the Unigui version.
In the VCL version, when I change a value in my screen the change is automatically done in the MySql tables. I suppose that this is the main function of the database components.
To do this in the VCL version it is sufficient to use the “Delphi” statements like
try
SQL.Text := 'SELECT * FROM t_lastcalc';
Open;
Append;
FieldByName('Fuel').AsString := tipo_projetoSTR;
FieldByName('TypeOfPlant').AsString := tipo_plantaSTR;
Post;
finally
Free;
end;

However, when doing the final tests of my Unigui code, to deploy in the web, I noticed that for the Unigui version the phases above work when the code is running but they are not sufficient to save the changes automatically in the MySql database. I do not know if I did some undue modification to provoke this.

In my Unigui version, to succeed in updating the MySql data base I need to use the classical MySql queries like:

AddNewUser := TMyQuery.Create(nil);
try
AddNewUser.Connection := uniMainModule.MyConnection;
AddNewUser.SQL.Clear;
AddNewUser.SQL.Add('INSERT INTO hpl.USERS (LOGIN,DATEREGISTER) VALUES (:LOGIN,:DATEREGISTER);');
AddNewUser.ParamByName('LOGIN').AsString := LOGIN;
AddNewUser.ParamByName('DATEREGISTER').AsDateTime := now;
AddNewUser.Execute;
finally
AddNewUser.Free;
end.

As I work with a table with some 80 columns it is hard to update the tables using MySql queries.
I think I am missing something. Otherwise, what would be the reason for not using only Delphi native components?
Could you please clarify if, in my Unigui code, I am obliged to use the MySql statements instead of – only - the components language that I use in the VCL version ?
Thanks, in advance.

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

Re: SQL SINTAX ???

Post by ViktorV » Fri 02 Apr 2021 14:01

You can create a new post by clicking the New Topic button under the MySQL Data Access Components heading in viewforum.php?f=7
Regarding your second question, about uniGUI, it's a third-party UI framework and we're not proficient with it enough to advise you. You may contact uniGUI support team or look for an answer on specialized forums.
If you have any questions about using our data access components, please send us a sample project (which doesn't use third-party components) demonstrating the issue, through the contact form: https://devart.com/company/contactform.html

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

Re: SQL SINTAX ???

Post by SergioFeitoza » Fri 02 Apr 2021 23:04

Thank you

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

Re: SQL SINTAX ???

Post by ViktorV » Mon 05 Apr 2021 07:46

Thank you for interest to our product.
Feel free to contact us if you have any further questions about our products.

Post Reply