Recent

Author Topic: TSQLQuery - can't execute SQL statement loaded at runtime.  (Read 3884 times)

AKCarlow

  • New Member
  • *
  • Posts: 45
TSQLQuery - can't execute SQL statement loaded at runtime.
« on: July 12, 2014, 12:52:50 am »
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;
Using: OS-X 10.6.8, Lazarus 1.2.4, FPC 2.6.4,  MySQL 5.6.19 (32-bit)

ttomas

  • Full Member
  • ***
  • Posts: 198
Re: TSQLQuery - can't execute SQL statement loaded at runtime.
« Reply #1 on: July 12, 2014, 01:22:30 am »
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

  • Jr. Member
  • **
  • Posts: 62
  • Just old me
Re: TSQLQuery - can't execute SQL statement loaded at runtime.
« Reply #2 on: July 12, 2014, 01:27:52 am »
I'm not completely sure I'm understanding your question, but what about using QuotedStr(); rather than trying to manually escape the quotes?
Lazarus 1.2.4 32-bit version on Windows 8.1 64-bit, Windows 7 64-bit, and Windows XP 32-bit

Currently developing TimberLog and the VirtualDBScroll Component Package

AKCarlow

  • New Member
  • *
  • Posts: 45
Re: TSQLQuery - can't execute SQL statement loaded at runtime.
« Reply #3 on: July 12, 2014, 01:47:36 am »
@ttomas - thanks, but that makes no difference
Using: OS-X 10.6.8, Lazarus 1.2.4, FPC 2.6.4,  MySQL 5.6.19 (32-bit)

AKCarlow

  • New Member
  • *
  • Posts: 45
Re: TSQLQuery - can't execute SQL statement loaded at runtime.
« Reply #4 on: July 12, 2014, 01:48:56 am »
@jdlinke - thanks, but that makes no difference
Using: OS-X 10.6.8, Lazarus 1.2.4, FPC 2.6.4,  MySQL 5.6.19 (32-bit)

jdlinke

  • Jr. Member
  • **
  • Posts: 62
  • Just old me
Re: TSQLQuery - can't execute SQL statement loaded at runtime.
« Reply #5 on: July 12, 2014, 02:20:31 am »
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;');
Lazarus 1.2.4 32-bit version on Windows 8.1 64-bit, Windows 7 64-bit, and Windows XP 32-bit

Currently developing TimberLog and the VirtualDBScroll Component Package

jdlinke

  • Jr. Member
  • **
  • Posts: 62
  • Just old me
Re: TSQLQuery - can't execute SQL statement loaded at runtime.
« Reply #6 on: July 12, 2014, 02:27:57 am »
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.
« Last Edit: July 12, 2014, 02:36:00 am by jdlinke »
Lazarus 1.2.4 32-bit version on Windows 8.1 64-bit, Windows 7 64-bit, and Windows XP 32-bit

Currently developing TimberLog and the VirtualDBScroll Component Package

AKCarlow

  • New Member
  • *
  • Posts: 45
Re: TSQLQuery - can't execute SQL statement loaded at runtime.
« Reply #7 on: July 12, 2014, 02:38:51 am »
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.)

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

jdlinke

  • Jr. Member
  • **
  • Posts: 62
  • Just old me
Re: TSQLQuery - can't execute SQL statement loaded at runtime.
« Reply #8 on: July 12, 2014, 02:43:45 am »
No problem. Glad it helped!  :)
Lazarus 1.2.4 32-bit version on Windows 8.1 64-bit, Windows 7 64-bit, and Windows XP 32-bit

Currently developing TimberLog and the VirtualDBScroll Component Package

 

TinyPortal © 2005-2018