What is wrong with this SQL.Text ?
lazarus / 16-Databases

----------------------------------------------------------------------
HatForCat · 2017-03-14 21:42

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

----------------------------------------------------------------------
magikfingerz · 2017-03-14 22:30

Just to discard it... is sqlEmails a table or an updatable view?

----------------------------------------------------------------------
rvk · 2017-03-14 22:59

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 · 2017-03-15 00:45

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
dm.sqlEmails.SQL.Text:=format('UPDATE sqlEmails SET zSent=%d WHERE
zID=%d;',   
[Integer(turnON),m.sqlEmails.FieldByName('zID').AsInteger]);

----------------------------------------------------------------------
HatForCat · 2017-03-15 15:22

Quote from: bylaardt on March 15, 2017, 01:45:22 amMySQL? 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   SET zSent
= '+#39+'0'+#39+' WHEREand   SET zSent = '+#39+'1'+#39+' WHERE

----------------------------------------------------------------------
fpcoder · 2017-03-15 19:08

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.

----------------------------------------------------------------------
mangakissa · 2017-03-16 08:00

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
MyUpdate;begin   dm.sqlEmails.SQL.Text := 'UPDATE sqlEmails SET zSent
= :newvalue WHERE zID = :id'; 
 dm.sqlEmails.parambyname('id').AsInteger :=
dm.sqlEmails.FieldByName('zID').AsInteger;  // zID is an AutoInc
Primary   if turnON then   
 dm.sqlEmails.parambyname('newvalue').AsInteger := 1  else   
 dm.sqlEmails.parambyname('newvalue').AsInteger := 0; 
 dm.sqlEmails.ExecSQL;   //transaction.commitretaining if it's not
automatic.end; 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
MyUpdate;begin   dm.sqlEmails.edit; 
 dm.sqlEmails.FieldByName('zID').AsInteger;  // zID is an AutoInc
Primary   if turnON then     dm.sqlEmails.FieldByName('zID').AsInteger
:= 1  else     dm.sqlEmails.FieldByName('zID').AsInteger := 0; 
 dm.sqlEmails.Post;   dm.sqlEmails.applyupdates; 
 //transaction.commitretaining if it's not automatic.end; 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.

----------------------------------------------------------------------
mangakissa · 2017-03-16 08:03

Quote from: rvk on March 14, 2017, 11:59:55 pmYou 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.

----------------------------------------------------------------------
rvk · 2017-03-16 09:20

Quote from: mangakissa on March 16, 2017, 09:03:22 amQuote from: rvk
on March 14, 2017, 11:59:55 pmYou 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 !!

QuoteProject 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)