[Solved] Error on update to PGSQL 9
lazarus / 16-Databases

----------------------------------------------------------------------
MOVNet · 2024-08-16 19:01

Hi.

I'm facing an issue when I'm trying to update a table.

The database connection is OK, I can successfully select the database
but when I try to do an update I got the error shown at Error.png
(attached).

Here the code:
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        
QDB_SHOT.Close;        sql:='';        sql:=sql+'UPDATE  config ';   
   sql:=sql+'set keyaddress = ' + #39 + nkey + #39 + ' ';       
sql:=sql+'where ';        sql:=sql+'keyaddress =' + #39 + key + #39 +
' ';        QDB_Shot.Clear;        QDB_Shot.SQL.Add(sql);       
QDB_Shot.ExecSQL;        TDB_Shot.Commit;       
TDB_Shot.EndTransaction;        QDB_Shot.Close;                      
                   
The code stops at ExecSQL line.

I have no clue of what is happening. I can update the table using
PGADMIN software with the same credentials (role/user).

Can anyone help me?

Best Regards,

Marcelo

----------------------------------------------------------------------
MarkMLl · 2024-08-16 19:49

What happens when you use psql to enter the *exact* text that the
debugger shows is in your sql variable?

MarkMLl

----------------------------------------------------------------------
dsiders · 2024-08-16 20:11

Quote from: MarkMLl on August 16, 2024, 09:49:07 pmWhat happens when
you use psql to enter the *exact* text that the debugger shows is in
your sql variable?

MarkMLl

It's the unbalanced quoting inside the sql string.

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 sql :=
'update config set keyaddress=''' + nkey + ''' where keyaddress=''' +
nkey + '''';

----------------------------------------------------------------------
MarkMLl · 2024-08-16 20:32

Well spotted.

MarkMLl

----------------------------------------------------------------------
MOVNet · 2024-08-16 20:33

Hi

Thank you for the answers.

The sql variable value is correct as bellow:

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i update
config set keyaddress = '2024081601' where keyaddress ='2128781475' ; 
The quoting are done with #39 (Char code) as you can see in the code.
I get the sql value and put at PGADMIN: the update was done as expect.

I have another software with the same procedure type, and it works
fine. The difference is that the other software connect to a Postgres
14 and this one connect to a Postgres 9 database.

Regards,

Marcelo

----------------------------------------------------------------------
dsiders · 2024-08-16 21:12

Quote from: dsiders on August 16, 2024, 10:11:46 pmQuote from: MarkMLl
on August 16, 2024, 09:49:07 pmWhat happens when you use psql to enter
the *exact* text that the debugger shows is in your sql variable?

MarkMLl

It's the unbalanced quoting inside the sql string.

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 sql :=
'update config set keyaddress=''' + nkey + ''' where keyaddress=''' +
nkey + '''';
You can always use parameters to make things simpler (IMO).

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
QDB_Shot.Close;QDB_Shot.SQL.Text := 'update config set keyaddress =
:nkey where keyaddress =
:key';QDB_Shot.SQL.Params.ParamByName('nkey').AsString :=
nkey;QDB_Shot.SQL.Params.ParamByName('key').AsString :=
key;QDB_Shot.ExecSQL;TDB_Shot.Commit;TDB_Shot.EndTransaction;

----------------------------------------------------------------------
Zvoni · 2024-08-19 06:33

Quote from: dsiders on August 16, 2024, 11:12:57 pmYou can always use
parameters to make things simpler (IMO).
Not only simpler, it's the recommended way and best practice

----------------------------------------------------------------------
MOVNet · 2024-08-19 12:22

Hi everyone!

Thank you dsiders, I rewrote my code using Parameters and it worked!
I really didn't understand why it solved the issue. As I said, I have
some other applications that don't use Parameters and they work with
no issues. But the important is that my application is working now.

The drawback is that I use to write the "sql" variable to a log file,
and using parameters I will not have the exact sql command in the log.
I solved this by writing the Parameters values in another line of my
log.

Allow me to do a small correction on your code:
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
QDB_Shot.Close;QDB_Shot.SQL.Text := 'update config set keyaddress =
:nkey where keyaddress =
:key';QDB_Shot.Params.ParamByName('nkey').AsString :=
nkey;QDB_Shot.Params.ParamByName('key').AsString :=
key;QDB_Shot.ExecSQL;TDB_Shot.Commit;TDB_Shot.EndTransaction; 
Thank you guys for the help in this issue.

Best Regards,

Marcelo