Recent

Author Topic: [Solved] Error on update to PGSQL 9  (Read 1298 times)

MOVNet

  • Newbie
  • Posts: 3
[Solved] Error on update to PGSQL 9
« on: August 16, 2024, 09:01:43 pm »
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][+][-]
  1.         QDB_SHOT.Close;
  2.         sql:='';
  3.         sql:=sql+'UPDATE  config ';
  4.         sql:=sql+'set keyaddress = ' + #39 + nkey + #39 + ' ';
  5.         sql:=sql+'where ';
  6.         sql:=sql+'keyaddress =' + #39 + key + #39 + ' ';
  7.         QDB_Shot.Clear;
  8.         QDB_Shot.SQL.Add(sql);
  9.         QDB_Shot.ExecSQL;
  10.         TDB_Shot.Commit;
  11.         TDB_Shot.EndTransaction;
  12.         QDB_Shot.Close;                                            
  13.  

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
« Last Edit: August 20, 2024, 12:39:19 pm by MOVNet »

MarkMLl

  • Hero Member
  • *****
  • Posts: 7888
Re: Error on update to PGSQL 9
« Reply #1 on: August 16, 2024, 09:49:07 pm »
What happens when you use psql to enter the *exact* text that the debugger shows is in your sql variable?

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Logitech, TopSpeed & FTL Modula-2 on bare metal (Z80, '286 protected mode).
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

dsiders

  • Hero Member
  • *****
  • Posts: 1260
Re: Error on update to PGSQL 9
« Reply #2 on: August 16, 2024, 10:11:46 pm »
What 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][+][-]
  1. sql := 'update config set keyaddress=''' + nkey + ''' where keyaddress=''' + nkey + '''';
Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

MarkMLl

  • Hero Member
  • *****
  • Posts: 7888
Re: Error on update to PGSQL 9
« Reply #3 on: August 16, 2024, 10:32:29 pm »
Well spotted.

MarkMLl
MT+86 & Turbo Pascal v1 on CCP/M-86, multitasking with LAN & graphics in 128Kb.
Logitech, TopSpeed & FTL Modula-2 on bare metal (Z80, '286 protected mode).
Pet hate: people who boast about the size and sophistication of their computer.
GitHub repositories: https://github.com/MarkMLl?tab=repositories

MOVNet

  • Newbie
  • Posts: 3
Re: Error on update to PGSQL 9
« Reply #4 on: August 16, 2024, 10:33:06 pm »
Hi

Thank you for the answers.

The sql variable value is correct as bellow:

Code: Text  [Select][+][-]
  1. update config set keyaddress = '2024081601' where keyaddress ='2128781475' ;
  2.  

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
 
« Last Edit: August 16, 2024, 10:39:21 pm by MOVNet »

dsiders

  • Hero Member
  • *****
  • Posts: 1260
Re: Error on update to PGSQL 9
« Reply #5 on: August 16, 2024, 11:12:57 pm »
What 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][+][-]
  1. sql := 'update config set keyaddress=''' + nkey + ''' where keyaddress=''' + nkey + '''';

You can always use parameters to make things simpler (IMO).

Code: Pascal  [Select][+][-]
  1. QDB_Shot.Close;
  2. QDB_Shot.SQL.Text := 'update config set keyaddress = :nkey where keyaddress = :key';
  3. QDB_Shot.SQL.Params.ParamByName('nkey').AsString := nkey;
  4. QDB_Shot.SQL.Params.ParamByName('key').AsString := key;
  5. QDB_Shot.ExecSQL;
  6. TDB_Shot.Commit;
  7. TDB_Shot.EndTransaction;
Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

Zvoni

  • Hero Member
  • *****
  • Posts: 2718
Re: Error on update to PGSQL 9
« Reply #6 on: August 19, 2024, 08:33:43 am »
You can always use parameters to make things simpler (IMO).
Not only simpler, it's the recommended way and best practice
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

MOVNet

  • Newbie
  • Posts: 3
Re: Error on update to PGSQL 9
« Reply #7 on: August 19, 2024, 02:22:54 pm »
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][+][-]
  1. QDB_Shot.Close;
  2. QDB_Shot.SQL.Text := 'update config set keyaddress = :nkey where keyaddress = :key';
  3. QDB_Shot.Params.ParamByName('nkey').AsString := nkey;
  4. QDB_Shot.Params.ParamByName('key').AsString := key;
  5. QDB_Shot.ExecSQL;
  6. TDB_Shot.Commit;
  7. TDB_Shot.EndTransaction;
  8.  

Thank you guys for the help in this issue.

Best Regards,

Marcelo



 

TinyPortal © 2005-2018