Quoted string bug postgres/xojo

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.

Any ideas would be much appreciated.

Post the actual code please.

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.

There is - as the error message indicates - a single quote missing at the end after …983bd.

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)

I should have mentioned that I populated the table with a single record to match the criteria, and that’s the id (“2”) that was returned.

Can you tell me what encoding the db was you created and what the column type was, for that field mine is a varchar(64).

I did it through Valentina Studio, and this is the code it used:

CREATE TABLE "public"."emails" ( 
	"id" BIGINT DEFAULT nextval('emails_id_seq'::regclass) NOT NULL UNIQUE, 
	"vemail_md5" CHARACTER VARYING( 255 ) COLLATE "pg_catalog"."default" NOT NULL,
 PRIMARY KEY ( "id" )
 );
CREATE INDEX "index_vemail_md5" ON "public"."emails" USING btree( "vemail_md5" ASC NULLS LAST );

That should have been 256, but whatever.

Encoding is UTF8.

FYI, I changed it to VarChar(64) without a difference.

Thank you for that and your help and effort verifying it independently. I will keep digging, at least I have verification I am crazy :slight_smile:

If I find anything I will post it as a follow up, thanks again.

No problem.

If it makes a difference, I am using PostgreSQL 9.3.2 and the latest Xojo.

Did you remember to update the PostgreSQL Xojo plugin when you last updated Xojo?