* * *

Author Topic: What is wrong with this SQL.Text ?  (Read 592 times)

HatForCat

  • Full Member
  • ***
  • Posts: 230
What is wrong with this SQL.Text ?
« on: March 14, 2017, 10:42:41 pm »
I am getting an error "error near 'set' : syntax error"
Having torn out the last of my thinning hair, I am asking here...

Code: [Select]
  anID:=dm.sqlEmails.FieldByName('zID').AsString;  // zID is an AutoInc Primary
  if turnON then
    dm.sqlEmails.SQL.Text:='UPDATE sqlEmails SET zSent = 1 WHERE zID = '+anID else
    dm.sqlEmails.SQL.Text:='UPDATE sqlEmails SET zSent = 0 WHERE zID = '+anID;

Thanks
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my use on Ubuntu.

magikfingerz

  • Newbie
  • Posts: 3
Re: What is wrong with this SQL.Text ?
« Reply #1 on: March 14, 2017, 11:30:56 pm »
Just to discard it... is sqlEmails a table or an updatable view?

rvk

  • Hero Member
  • *****
  • Posts: 2389
Re: What is wrong with this SQL.Text ?
« Reply #2 on: March 14, 2017, 11:59:55 pm »
Is this the exact code?
Because it can't work like that.
On the first line dm.SqlEmails is a dataset which should be open to read zId. And in the following lines you try to set the SQL of that exact same dataset.

You can't set the SQL of an open dataset (afaik).

There should be a dm.SqlEmails.Close in between those lines.

bylaardt

  • Full Member
  • ***
  • Posts: 200
Re: What is wrong with this SQL.Text ?
« Reply #3 on: March 15, 2017, 01:45:22 am »
MySQL? Postgres? Oracle?
Is the Server on Linux?

Why those questions?
Because:
MySQL on Linux has tables and case-sensitive field names, but not on Windows;
";" Must be set in a few DBMS
Maybe BEGIN / COMMIT is missing

My first suggestion is (just a hunch):
Code: Pascal  [Select]
  1.   dm.sqlEmails.SQL.Text:=format('UPDATE sqlEmails SET zSent=%d WHERE zID=%d;',
  2.     [Integer(turnON),m.sqlEmails.FieldByName('zID').AsInteger]);

HatForCat

  • Full Member
  • ***
  • Posts: 230
Re: What is wrong with this SQL.Text ?
« Reply #4 on: March 15, 2017, 04:22:43 pm »
MySQL? Postgres? Oracle?
Is the Server on Linux?

Thank you kindly for the code example.

Funny you should ask, I have been asked that kind of stuff before and finally added everything in my Signature, but omitted the Database Server (SQLite3) so will rectify that right after posting this.

I had not thought of the method you provided, but will endeavor to use that approach in the future. Thanks very much.

After posting I went back to hammering around and found this to solve the problem. But as mentioned, your code is a much better approach.

Code: Pascal  [Select]
  1.   SET zSent = '+#39+'0'+#39+' WHERE
  2. and
  3.   SET zSent = '+#39+'1'+#39+' WHERE
  4.  
Acer-i5, 2.6GHz, 6GB, 256-SSD, Ubuntu 14.04-LTS, Lazarus 1.6.2, SQLite3 -- Retired: Programming for my use on Ubuntu.

kapibara

  • Sr. Member
  • ****
  • Posts: 430
Re: What is wrong with this SQL.Text ?
« Reply #5 on: March 15, 2017, 08:08:49 pm »
I think zSent should be created as integer in the table. Then you can just assign 0 and 1 and skip the apostrofes (#39+'0'+#39). Only strings need an apostrofe in the SQL statement.
Lazarus trunk / fpc 3.0 / Debian Stretch 64-bit

mangakissa

  • Hero Member
  • *****
  • Posts: 645
Re: What is wrong with this SQL.Text ?
« Reply #6 on: March 16, 2017, 09:00:32 am »
First you said sZent is an integer and now you said sZent is a string.
What's the structure of table exactly?
Are you sure zID is an string and not an integer?
Code: Pascal  [Select]
  1. procedure MyUpdate;
  2. begin
  3.    dm.sqlEmails.SQL.Text := 'UPDATE sqlEmails SET zSent = :newvalue WHERE zID = :id';
  4.    dm.sqlEmails.parambyname('id').AsInteger := dm.sqlEmails.FieldByName('zID').AsInteger;  // zID is an AutoInc Primary
  5.    if turnON then
  6.      dm.sqlEmails.parambyname('newvalue').AsInteger := 1
  7.   else
  8.      dm.sqlEmails.parambyname('newvalue').AsInteger := 0;
  9.    dm.sqlEmails.ExecSQL;
  10.    //transaction.commitretaining if it's not automatic.
  11. end;
  12.  
The most important thing is where did you get the value zID. If the dataset is already active, you only have to edit en post the record.
Code: Pascal  [Select]
  1. procedure MyUpdate;
  2. begin
  3.    dm.sqlEmails.edit;
  4.    dm.sqlEmails.FieldByName('zID').AsInteger;  // zID is an AutoInc Primary
  5.    if turnON then
  6.      dm.sqlEmails.FieldByName('zID').AsInteger := 1
  7.   else
  8.      dm.sqlEmails.FieldByName('zID').AsInteger := 0;
  9.    dm.sqlEmails.Post;
  10.    dm.sqlEmails.applyupdates;
  11.    //transaction.commitretaining if it's not automatic.
  12. end;
  13.  
Try to avoid using function format(). The framework is built to use params. Structures like format makes ik harder to create the right syntax of your query.
Lazarus 1.6 (32b) / FPC 3.0
Windows Vista /  10

mangakissa

  • Hero Member
  • *****
  • Posts: 645
Re: What is wrong with this SQL.Text ?
« Reply #7 on: March 16, 2017, 09:03:22 am »
You can't set the SQL of an open dataset (afaik).
Yes you can. Use property UpdateSQL. But not how HatforCat is using it, but just a query.
Lazarus 1.6 (32b) / FPC 3.0
Windows Vista /  10

rvk

  • Hero Member
  • *****
  • Posts: 2389
Re: What is wrong with this SQL.Text ?
« Reply #8 on: March 16, 2017, 10:20:47 am »
You can't set the SQL of an open dataset (afaik).
Yes you can. Use property UpdateSQL. But not how HatforCat is using it, but just a query.
Of course I meant the SQL-property HatforCat used.

But now that you brought up UpdateSQL... You CAN'T set that either when the dataset is active !!

Quote
Project project1 raised exception class 'EDatabaseError' with message:
Operation cannot be performed on an active dataset

But you're correct that using .Edit and .Post might be a better solution here.
(As far as I can see the operation is done on the same dataset)

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus