Re: bug in info.c file - incorrect SQL - Mailing list pgsql-odbc

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
Index: info.c
===================================================================
RCS file: /usr/local/cvsroot/psqlodbc/psqlodbc/info.c,v
retrieving revision 1.90
diff -c -c -r1.90 info.c
*** info.c    2 Jan 2003 15:33:19 -0000    1.90
--- info.c    2 Jan 2003 22:22:43 -0000
***************
*** 1668,1674 ****
              " from pg_namespace u, pg_class c, pg_attribute a, pg_type t"
              " where u.oid = c.relnamespace"
              " and (not a.attisdropped)"
!       " and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0)",
              "a.atttypmod");
      else
          sprintf(columns_query, "select u.usename, c.relname, a.attname, a.atttypid"
--- 1668,1674 ----
              " from pg_namespace u, pg_class c, pg_attribute a, pg_type t"
              " where u.oid = c.relnamespace"
              " and (not a.attisdropped)"
!             " and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0)",
              "a.atttypmod");
      else
          sprintf(columns_query, "select u.usename, c.relname, a.attname, a.atttypid"
***************
*** 3087,3093 ****
      if (!bError && continueExec)
      {
          if (conn->schema_support)
!             sprintf(query, "select OID from pg_class where relname = '%s' and pg_namespace.oid = relnamespace and
pg_namespace.nspname= '%s'", serverTableName, serverSchemaName); 
          else
              sprintf(query, "select OID from pg_class where relname = '%s'", serverTableName);
          if (res = CC_send_query(conn, query, NULL, CLEAR_RESULT_ON_ABORT), res)
--- 3087,3093 ----
      if (!bError && continueExec)
      {
          if (conn->schema_support)
!             sprintf(query, "select OID from pg_class, pg_namespace where relname = '%s' and pg_namespace.oid =
relnamespaceand pg_namespace.nspname = '%s'", serverTableName, serverSchemaName); 
          else
              sprintf(query, "select OID from pg_class where relname = '%s'", serverTableName);
          if (res = CC_send_query(conn, query, NULL, CLEAR_RESULT_ON_ABORT), res)
***************
*** 3154,3160 ****
      if (!bError && continueExec)
      {
          if (conn->schema_support)
!             sprintf(query, "select attrelid, attnum from pg_class, pg_attribute "
                  "where relname = '%s' and attrelid = pg_class.oid "
                  "and (not attisdropped) "
                  "and attname = '%s' and pg_namespace.oid = relnamespace and pg_namespace.nspname = '%s'",
serverTableName,serverColumnName, serverSchemaName); 
--- 3154,3160 ----
      if (!bError && continueExec)
      {
          if (conn->schema_support)
!             sprintf(query, "select attrelid, attnum from pg_class, pg_attribute, pg_namespace "
                  "where relname = '%s' and attrelid = pg_class.oid "
                  "and (not attisdropped) "
                  "and attname = '%s' and pg_namespace.oid = relnamespace and pg_namespace.nspname = '%s'",
serverTableName,serverColumnName, serverSchemaName); 
***************
*** 3449,3456 ****
                  "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') "
--- 3449,3456 ----
                  "AND pt2.tgfoid = pp2.oid "
                  "AND pt2.tgconstrrelid = pc.oid "
                  "AND ((pc.relname='%s') "
!                 "AND (pn.oid = pc.relnamespace) "
!                 "AND (pn.nspname = '%s') "
                  "AND (pp.proname LIKE '%%ins') "
                  "AND (pp1.proname LIKE '%%upd') "
                  "AND (pp2.proname LIKE '%%del') "
***************
*** 3826,3833 ****
                  "    AND pc2.oid = pt.tgrelid "
                  "    AND ("
                  "         (pc.relname='%s') "
!                 "    AND  (pg_namespace.oid = pc.relnamespace) "
!                 "    AND  (pg_namespace.nspname = '%s') "
                  "    AND  (pp.proname Like '%%upd') "
                  "    AND  (pp1.proname Like '%%del')"
                  "    AND     (pt1.tgrelid = pt.tgconstrrelid) "
--- 3826,3833 ----
                  "    AND pc2.oid = pt.tgrelid "
                  "    AND ("
                  "         (pc.relname='%s') "
!                 "    AND  (pn.oid = pc.relnamespace) "
!                 "    AND  (pn.nspname = '%s') "
                  "    AND  (pp.proname Like '%%upd') "
                  "    AND  (pp1.proname Like '%%del')"
                  "    AND     (pt1.tgrelid = pt.tgconstrrelid) "