I have a basic sqlselect of a text/varchar field that works fine in postgres at the command line:
SELECT id from emails where vemail_md5=‘f6cb5b374808419ff6fc55b73a1983bd’ order by id
When run from xojo I get the following database error:
ERROR: unterminated quoted string at or near “'f6cb5b374808419ff6fc55b73a1983bd”
Is there some new magic required in Xojo for queries like this that are 100% valid and run fine in psql? I have tried changing them to double quotes just to see if they are being converted by Xojo somehow, but that produces a DB error of invalid character, I have tired using 2 single quotes, but that fails too.
This is the first time I have seen Xojo fail with SQL that works on a command line.
It is very old db code, very stable. On the order of
sql = “SELECT id from emails where vemail_md5=‘f6cb5b374808419ff6fc55b73a1983bd’ order by id”
rs=db.sqLSelect(sql)
The problem is not the code, it is the actual SQL, not working in xojo, but fine at the command line in psql. The SQL posted is the exact SQL out of the debugger, which when pasted into psql works great, in Xojo produces that Error.
Exactly my problem, but the SQL it is running against that CLEARLY has the closing quote, and runs fine in psql. There is something happening in the plugin or some parsing in there possibly.
Actual SQL being run: “SELECT id from emails where vemail_md5=‘f6cb5b374808419ff6fc55b73a1983bd’ order by id” I have verified this in the debugger, there is something weird going on in the plugin or encoding, etc.
I can’t reproduce your results. I created a test database with your fields, then copied and pasted the code above directly into my code and got a result.
dim db as new PostgreSQLDatabase
db.DatabaseName = "tester"
db.Host = "127.0.0.1"
db.UserName = "******"
db.Password = "******"
if not db.Connect() then
AddToResult "Couldn't connect"
return
end if
dim sql as string
dim rs as RecordSet
sql = "SELECT id from emails where vemail_md5='f6cb5b374808419ff6fc55b73a1983bd' order by id"
rs=db.sqLSelect(sql)