SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL query on PostgreSQL problems

    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 :

    Code:
    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 :

    Code:
    SELECT * FROM partners WHERE partner_id='{$partner_id}'
    Ok now i know this is a problem for PgSQL and when i do

    Code:
    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: ""

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •