[Solved] Error on update to PGSQL 9

[MOVNet]

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

[MarkMLl]

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

MarkMLl

[dsiders]

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 + '''';

[MarkMLl]

Well spotted.

MarkMLl

[MOVNet]

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
 

[dsiders]

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;

[Zvoni]

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

Not only simpler, it's the recommended way and best practice

[MOVNet]

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