BRUCE Momjian - Re: bug in info.c file - incorrect SQL

Bruce, thanks for the quick response.
 
I have been using the msi compiled version.  My co-worker could not
successfully compile the modified code because the source did not
contain the files setup.c and psqlodbc.rc.  
Can you send me a new msi version, or will you have the new version on
the web site soon?
Thanks again
 

    -----Original Message----- 
    From: Bruce Momjian 
    Sent: Thu 1/2/2003 2:29 PM 
    To: Jim Holliday 
    Cc: pgsql-odbc@postgresql.org 
    Subject: Re: [ODBC] bug in info.c file - incorrect SQL
    
    


    Good catch, patch attached. I found the problems you saw, plus a
few
    cases where pg_namespace was referenced in the WHERE but not
FROM ---
    not a big problem, but clearer if we specify it both places.
Patch
    applied.
    
    
------------------------------------------------------------------------
---
    
    Jim Holliday wrote:
    > For the ODBC driver psqlodbc.dll version 7.02.0003 there is an
error in
    > the info.c.  In 2 places the SQL contains an alias in the FROM
clause
    > for the pg_namespace table and then references the table in
the where
    > clause.  The below cut from the info.c file should replace all
instances
    > on pg_namespace. in the where clause with pn.
    >
    >       This should be done in both places.
    >       I modified this code, but was unable to compile a new
version of
    > the odbc driver.
    >       
    >       When you re-compile, could you reply email to me please.
    >       Thanks
    >       
    >       >    sprintf(tables_query, "SELECT pt.tgargs, "
    >       >     "  pt.tgnargs, "
    >       >     "  pt.tgdeferrable, "
    >       >     "  pt.tginitdeferred, "
    >       >     "  pp1.proname, "
    >       >     "  pp2.proname, "
    >       >     "  pc.oid, "
    >       >     "  pc1.oid, "
    >       >     "  pc1.relname, "
    >       >     "  pn.nspname "
    >       >     "FROM pg_class pc, "
    >       >     "  pg_proc pp1, "
    >       >     "  pg_proc pp2, "
    >       >     "  pg_trigger pt1, "
    >       >     "  pg_trigger pt2, "
    >       >     "  pg_proc pp, "
    >       >     "  pg_trigger pt, "
    >       >     "  pg_class pc1, "
    >       >     "  pg_namespace pn "
    >       >     "WHERE pt.tgrelid = pc.oid "
    >       >     "AND pp.oid = pt.tgfoid "
    >       >     "AND pt1.tgconstrrelid = pc.oid "
    >       >     "AND pp1.oid = pt1.tgfoid "
    >       >     "AND pt2.tgfoid = pp2.oid "
    >       >     "AND pt2.tgconstrrelid = pc.oid "
    >       >     "AND ((pc.relname='%s') "
    >       >     "AND (pg_namespace.oid = pc.relnamespace) "
    >       >     "AND (pg_namespace.nspname = '%s') "
    >       >     "AND (pp.proname LIKE '%%ins') "
    >       >     "AND (pp1.proname LIKE '%%upd') "
    >       >     "AND (pp2.proname LIKE '%%del') "
    >       >     "AND (pt1.tgrelid=pt.tgconstrrelid) "
    >       >     "AND (pt1.tgconstrname=pt.tgconstrname) "
    >       >     "AND (pt2.tgrelid=pt.tgconstrrelid) "
    >       >     "AND (pt2.tgconstrname=pt.tgconstrname) "
    >       >     "AND (pt.tgconstrrelid=pc1.oid) "
    >       >     "AND (pc1.relnamespace=pn.oid))",
    >       >     fk_table_needed, schema_needed);
    >       >   }
    >      
    >
    >
    [ Type application/ms-tnef treated as attachment, skipping... ]
    
    >
    > ---------------------------(end of
broadcast)---------------------------
    > TIP 6: Have you searched our list archives?
    >
    > http://archives.postgresql.org
    
    --
      Bruce Momjian                        |
http://candle.pha.pa.us
      pgman@candle.pha.pa.us               |  (610) 359-1001
      +  If your life is a hard drive,     |  13 Roberts Road
      +  Christ can be your backup.        |  Newtown Square,
Pennsylvania 19073