PostgreSQL - update command in SQL query must be capitalized!

Databases
1

I was trying to perform an update on a PostgreSQL database using a standard “update …” sql query. This query runs fine in pgAdmin, but when run from Xojo using either SQLExecute or SQLSelect it produces errorcode 3, which says:

syntax error at or near "?update" LINE 1: ?update production set invoiced=True where productionid=6 ^

This is actually a simplified version of the query - I was trying to eliminate everything I could to narrow down the problem.

I finally determined that only for the update command, you have to capitalize at least the “U” for it to work in Xojo. So changing the query to “Update …” works fine.

I have used many other SQL commands, such as select, insert and delete in all lowercase with no issues. Very strange. I guess this is the first time I’ve tried to use update from a standard SQLExec/SQLSelect query (my database classes use prepared statements, and just so happen to use a capitalized Update command - not sure if the prepared statement makes a difference).

So, can anyone explain this bizarre behavior?

Also, what is errorcode 3 - just a generic “syntax error” or something more?

2

I don’t think the issue is what you think it is. I just tried “update” without a problem.

3

Look at PostgreSQL error logs, perhaps there is some additional detail.

4

You are correct. I just tried changing my original code back to lowercase and it now works.

I believe the problem was pasting the original query text from pgAdmin into Xojo which caused some unseen encoding problem. I think I’ve run into this before - some unseen character or encoding gets inserted into the Xojo editor. Maybe Xojo needs to do a better job of filtering pasted input.

5

Apparently those match the errorcodes given at https://www.postgresql.org/docs/9.3/static/errcodes-appendix.html. I was unable to spot what entry 3 would correspond to though. Anyway, you just stumbled over a nasty PGAdmin4 bug. Many people still use PGAdmin3 feeling PG Admin4 is not quite there yet.

6

From my experience, I believe it inserts a hidden character/space at the beginning of the line when you cut-paste. If you go to the start of the line (UPDATE in your case) and you delete up to the P, then add the U back it will work. I stumbled over this when working on a web application a few months back.

Robert

7

[quote=367163:@Robert Litchfield]From my experience, I believe it inserts a hidden character/space at the beginning of the line when you cut-paste. If you go to the start of the line (UPDATE in your case) and you delete up to the P, then add the U back it will work. I stumbled over this when working on a web application a few months back.
[/quote]
That’s essentially what I did when I changed the lowercase “u” to an uppercase one. What I should have done after confirming that it worked was to change it back to lowercase to verify the problem still existed. But I had been racking my brain for a while on this problem and when it finally worked I didn’t care about trying to isolate it further.