TSQLQuery - can't execute SQL statement loaded at runtime.
lazarus / 16-Databases

----------------------------------------------------------------------
AKCarlow · 2014-07-11 22:52

Using:
OS-X 10.6.8
Lazarus 1.2.4, FPC 2.6.4
MySQL 5.6.19 (32-bit)

qSundry is TSQLQuery; textSundry is TDBText; lShareCount and
lConnCount are TLabels. The SQL statements being assigned to
qSundry.SQL work when I use them in the SQL Editor in the Object
Inspector, returning the expected values. The SQL statements are
copied from the SQL editor and pasted into the unit code, with the
single quotes escaped - the statement shows correctly in the
ShowMessage. But it crashes with "You have an error in your SQL
syntax".

Can anyone help?

Code: [Select]procedure TfMain.FormClick(Sender: TObject);
begin
qSundry.Active:=false;
qSundry.SQL.Clear;
qSundry.SQL.Add('''select count(ShareNo) from Shares'';');
showMessage(qSundry.SQL.Text);
qSundry.Active:=true;
lShareCount.Caption := lShareCount.Caption + textSundry.Caption;
qSundry.Active:=false;
qSundry.SQL.Clear;
qSundry.SQL.Add('''select count(ConnectionNo) from Connections'';');
qSundry.Active:=true;
lConnCount.Caption := lConnCount.Caption + textSundry.Caption;
end;

----------------------------------------------------------------------
ttomas · 2014-07-11 23:22

Change
Code: [Select]qSundry.SQL.Add('''select count(ShareNo) from
Shares'';');[code][/code]
to
Code: [Select]qSundry.SQL.Add('select count(ShareNo) from Shares;');

----------------------------------------------------------------------
jdlinke · 2014-07-11 23:27

I'm not completely sure I'm understanding your question, but what
about using QuotedStr(); rather than trying to manually escape the
quotes?

----------------------------------------------------------------------
AKCarlow · 2014-07-11 23:47

@ttomas - thanks, but that makes no difference

----------------------------------------------------------------------
AKCarlow · 2014-07-11 23:48

@jdlinke - thanks, but that makes no difference

----------------------------------------------------------------------
jdlinke · 2014-07-12 00:20

I tried both of the following using SQLite with success in my own
tests just now. Not sure if MySql has some other requirement, but I
don't believe it should:

Code: [Select]SQLQuery1.SQL.Text := 'Select Count(*) from DATA';
and
Code: [Select]SQLQuery1.SQL.Add('Select Count(*) from DATA;');

----------------------------------------------------------------------
jdlinke · 2014-07-12 00:27

One more thing


You have:
Code: [Select]qSundry.SQL.Add('''select count(ConnectionNo) from
Connections'';');
Which results in an SQL Statement of:
'select count(ConnectionNo) from Connections';

But that end quote is in the wrong place for a proper SQL Statement.
The Statement should result in:
'select count(ConnectionNo) from Connections;'


ttomas' post above should have corrected the issue. If I try your
statement and then his, yours fails but his works.

----------------------------------------------------------------------
AKCarlow · 2014-07-12 00:38

count(*) did it! Thanks for the help.

(I had already fixed the misplaced quote, but since we were crashing
before that it didn't matter. Now using it without the escaped 's.)

----------------------------------------------------------------------
jdlinke · 2014-07-12 00:43

No problem. Glad it helped!