Update, params in "where"
lazarus / 16-Databases

----------------------------------------------------------------------
mmelwin · 2021-02-04 20:47

This query works fine:

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
SQL1.SQL.Add('update table set aaa='1' where
bbb=:VAL1');SQL1.ParamByName('VAL1').AsString:='2';
But how do you use null here?
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
SQL1.SQL.Add('update table set aaa='1' where
bbb=:VAL1');SQL1.ParamByName('VAL1').Clear;
This won't work because it can't be "=null" but "is null"

----------------------------------------------------------------------
balazsszekely · 2021-02-04 20:56

@mmelwin
But how do you use null here?

Like this:
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
SQL1.SQL.Add('update table set aaa='1' where
bbb=:VAL1');SQL1.Params.ParamValues['VAL1'] := null;

----------------------------------------------------------------------
dseligo · 2021-02-04 22:56

Quote from: GetMem on February 04, 2021, 09:56:01 pmLike this:
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
SQL1.SQL.Add('update table set aaa='1' where
bbb=:VAL1');SQL1.Params.ParamValues['VAL1'] := null;That won't work.

I dynamically create SQL string, something like:
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 if bIsNull
then sExpr:='bbb is null' else sExpr:='bbb=:VAL1';SQL1.SQL.Add('update
table set aaa=''1'' where '+sExpr);If not bIsNull then
SQL1.ParamByName('VAL1').AsString:='2';

----------------------------------------------------------------------
egsuh · 2021-02-05 01:22

I tried several ways, but failed to do that. I create SQL statement
dynamically in similar way as you did.

----------------------------------------------------------------------
dseligo · 2021-02-05 10:41

Quote from: egsuh on February 05, 2021, 02:22:57 amI tried several
ways, but failed to do that. I create SQL statement dynamically in
similar way as you did.
I'm not sure what are you saying. Did you succeed or do you still need
help?

----------------------------------------------------------------------
egsuh · 2021-02-06 00:52

QuoteI'm not sure what are you saying. Did you succeed or do you still
need help?
I did not succeed. I do not need help as my solution is working, but
if there are other solutions I'll welcome them and assess whether I
can apply them to my applications.

----------------------------------------------------------------------
dseligo · 2021-02-06 08:52

This is in fact problem with SQL query, not with FPC/Lazarus.

You could write you SQL query like this:

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
SQL1.SQL.Add('update table set aaa=''1'' '+'where ((bbb=:VAL1 and
:VAL1 is not null) or (bbb is null and :VAL1 is null))');You could
probably omit 'and :VAL1 is not null'.

And now it should work both for
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
SQL1.ParamByName('VAL1').AsString:='2';
and

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
SQL1.ParamByName('VAL1').Clear;