TSQLQuery - can't execute SQL statement loaded at runtime.

[AKCarlow]

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]

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]

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

[AKCarlow]

@ttomas - thanks, but that makes no difference

[AKCarlow]

@jdlinke - thanks, but that makes no difference

[jdlinke]

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]

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]

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]

No problem. Glad it helped!