MySQL query on PostgreSQL problems

[djomla]

Hi to all,

I have problems with querys on PgSQL. I was working up till now on MySQL database. And query's where tested just on MySQL server, i had no idea that we would be trasfering our code to PgSQL

Ok here is sample table describing this problem :

CREATE TABLE "public"."partners" (
  "partner_id" INTEGER DEFAULT nextval('partners_seq'::regclass) NOT NULL, 
  "partner_name" VARCHAR(100) DEFAULT NULL::character varying
)

Ok, now the query that is working on MySQL :

SELECT * FROM partners WHERE partner_id='{$partner_id}'

Ok now i know this is a problem for PgSQL and when i do

SELECT * FROM partners WHERE partner_id={$partner_id}

Without " ' " sign, it works, but what when $partner_id doesn't exist.
On mysql it worked, query returned null but it didn't gave me any errors like now i'm experiencing.

Now, there is huge amount of code to recode for this is there any other way to resolve this?

Edit: Error is this => ERROR: invalid input syntax for integer: ""

[r937]

i think you have just discovered how dangerous mysql's "loose typing" can be

of course, you now realize that you cannot compare a numeric column to a string, at least not without conversion

mysql does the conversion for you "silently" and other databases simply won't do it

it's too bad that so few mysql developers realize the pitfalls of not coding to SQL standards

you've learned a valuable lesson

[djomla]

Hahaha bless you

My luck is that i was writing completely object oriented so i need to ad just small checkings in my interfaces and it will be ok. Damn, this realy is a valuable lesson