Re: Bug when looking for foreign keys

This bug may be fixed in cvs.
Please try the snapshot dll at
    http://www.geocities.jp/inocchichichi/psqlodbc/ .

regards,
Hiroshi Inoue
    http://www.geocities.jp/inocchichichi/psqlodbc/

Luiz Angelo Daros de Luca wrote:
>
> Hello,
>
>     I used Ms Visio to reverse engeneer my database and generate
> documentation. The problem is that it returns an error when it tries to
> get the foreign keys. By generating the log, I found the problem. The
> last comma of the FROM statement is absent. My version is the last one
> in homepage.
>
> SELECT       pt.tgargs,              pt.tgnargs,
> pt.tgdeferrable,            pt.tginitdeferred,
> pp1.proname,            pp2.proname,       pc.oid,
> pc1.oid,                pc1.relname,            pn.nspname FROM
> pg_catalog.pg_class pc,             pg_catalog.pg_proc
> pp1,                 pg_catalog.pg_proc pp2,
> pg_catalog.pg_trigger pt1,          pg_catalog.pg_trigger
> pt2,              pg_catalog.pg_proc pp,     pg_catalog.pg_trigger
> pt,                pg_catalog.pg_class pc1,
> pg_catalog.pg_namespace pn          pg_catalog.pg_namespace pn1
> 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='CamposConfiguracao') AND (pn1.oid = pc.relnamespace) AND
> (pn1.nspname = 'public') 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))
>
> Fixed become:
>
> SELECT       pt.tgargs,              pt.tgnargs,
> pt.tgdeferrable,            pt.tginitdeferred,
> pp1.proname,            pp2.proname,       pc.oid,
> pc1.oid,                pc1.relname,            pn.nspname FROM
> pg_catalog.pg_class pc,             pg_catalog.pg_proc
> pp1,                 pg_catalog.pg_proc pp2,
> pg_catalog.pg_trigger pt1,          pg_catalog.pg_trigger
> pt2,              pg_catalog.pg_proc pp,     pg_catalog.pg_trigger
> pt,                pg_catalog.pg_class pc1,
> pg_catalog.pg_namespace pn    ,      pg_catalog.pg_namespace pn1
> 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='CamposConfiguracao') AND (pn1.oid = pc.relnamespace) AND
> (pn1.nspname = 'public') 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))
>
>
>
> -- Luiz Angelo Daros de Luca Federal University of Santa Catarina
> Florianopolis - SC - Brazil luizd@inf.ufsc.br ICQ : 19290419 I Know,
> "Where you wanted to go today", but I decided to stop here instead! MS
> Windows
>
>   ------------------------------------------------------------------------
> PGAPI_Columns: table='DadosAgoraSemanaNormalizados',field_name='SEMANA_TemperaturaMediaDia',type=701,name='float8'
> PGAPI_Columns: table='DadosAgoraSemanaNormalizados',field_name='SEMANA_UmidadeMinimaDia',type=701,name='float8'
> PGAPI_Columns: table='DadosAgoraSemanaNormalizados',field_name='SEMANA_UmidadeMaximaDia',type=701,name='float8'
> PGAPI_Columns: table='DadosAgoraSemanaNormalizados',field_name='SEMANA_UmidadeMediaDia',type=701,name='float8'
> PGAPI_Columns: table='DadosAgoraSemanaNormalizados',field_name='SEMANA_CargaMinimaDia',type=701,name='float8'
> PGAPI_Columns: table='DadosAgoraSemanaNormalizados',field_name='SEMANA_CargaMaximaDia',type=701,name='float8'
> PGAPI_Columns: table='DadosAgoraSemanaNormalizados',field_name='SEMANA_CargaMediaDia',type=701,name='float8'
> conn=838154376, query='select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname, c.relhasrules,
n.nspnamefrom pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class d, pg_catalog.pg_am a,
pg_catalog.pg_namespacen where d.relname = 'DadosAgoraSemanaNormalizados' and n.nspname = 'public' and n.oid =
d.relnamespaceand d.oid = i.indrelid and i.indexrelid = c.oid and c.relam = a.oid order by i.indisprimary desc,
i.indisunique,n.nspname, c.relname' 
>     [ fetched 0 rows ]
> conn=838154376, query='select u.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen,
a.atttypmod,a.attnotnull, c.relhasrules, c.relkind from pg_catalog.pg_namespace u, pg_catalog.pg_class c,
pg_catalog.pg_attributea, pg_catalog.pg_type t where u.oid = c.relnamespace and (not a.attisdropped) and c.oid=
a.attrelidand a.atttypid = t.oid and (a.attnum > 0) and c.relname = 'Experimento' and u.nspname = 'public' order by
u.nspname,c.relname, attnum' 
>     [ fetched 7 rows ]
> PGAPI_Columns: table='Experimento',field_name='id_Experimento',type=20,name='int8'
> PGAPI_Columns: table='Experimento',field_name='id_Configuracao',type=20,name='int8'
> PGAPI_Columns: table='Experimento',field_name='desligar',type=16,name='bool'
> PGAPI_Columns: table='Experimento',field_name='atualizacaoCliente',type=26,name='oid'
> PGAPI_Columns: table='Experimento',field_name='programaCliente',type=26,name='oid'
> PGAPI_Columns: table='Experimento',field_name='versaoCliente',type=701,name='float8'
> PGAPI_Columns: table='Experimento',field_name='prioridade',type=701,name='float8'
> conn=838154376, query='select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname, c.relhasrules,
n.nspnamefrom pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class d, pg_catalog.pg_am a,
pg_catalog.pg_namespacen where d.relname = 'Experimento' and n.nspname = 'public' and n.oid = d.relnamespace and d.oid
=i.indrelid and i.indexrelid = c.oid and c.relam = a.oid order by i.indisprimary desc, i.indisunique, n.nspname,
c.relname'
>     [ fetched 1 rows ]
> conn=838154376, query='select u.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen,
a.atttypmod,a.attnotnull, c.relhasrules, c.relkind from pg_catalog.pg_namespace u, pg_catalog.pg_class c,
pg_catalog.pg_attributea, pg_catalog.pg_type t where u.oid = c.relnamespace and (not a.attisdropped) and c.oid=
a.attrelidand a.atttypid = t.oid and (a.attnum > 0) and c.relname = 'Individuos_184' and u.nspname = 'public' order by
u.nspname,c.relname, attnum' 
>     [ fetched 5 rows ]
> PGAPI_Columns: table='Individuos_184',field_name='fitness',type=701,name='float8'
> PGAPI_Columns: table='Individuos_184',field_name='cliente',type=1043,name='varchar'
> PGAPI_Columns: table='Individuos_184',field_name='iniciado_em',type=1114,name='timestamp'
> PGAPI_Columns: table='Individuos_184',field_name='terminado_em',type=1114,name='timestamp'
> PGAPI_Columns: table='Individuos_184',field_name='chave',type=25,name='text'
> conn=838154376, query='select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname, c.relhasrules,
n.nspnamefrom pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class d, pg_catalog.pg_am a,
pg_catalog.pg_namespacen where d.relname = 'Individuos_184' and n.nspname = 'public' and n.oid = d.relnamespace and
d.oid= i.indrelid and i.indexrelid = c.oid and c.relam = a.oid order by i.indisprimary desc, i.indisunique, n.nspname,
c.relname'
>     [ fetched 1 rows ]
> conn=838154376, query='SELECT   pt.tgargs,              pt.tgnargs,             pt.tgdeferrable,
pt.tginitdeferred,             pp1.proname,            pp2.proname,            pc.oid,                 pc1.oid,
      pc1.relname,            pn.nspname FROM pg_catalog.pg_class pc,                 pg_catalog.pg_proc pp1,
     pg_catalog.pg_proc pp2,                 pg_catalog.pg_trigger pt1,              pg_catalog.pg_trigger pt2,
    pg_catalog.pg_proc pp,          pg_catalog.pg_trigger pt,               pg_catalog.pg_class pc1,
pg_catalog.pg_namespacepn              pg_catalog.pg_namespace pn1 WHERE       pt.tgrelid = pc.oid AND pp.oid =
pt.tgfoidAND pt1.tgconstrrelid = pc.oid AND pp1.oid = pt1.tgfoid AND pt2.tgfoid = pp2.oid AND pt2.tgconstrrelid =
pc.oidAND ((pc.relname='CamposConfiguracao') AND (pn1.oid = pc.relnamespace) AND (pn1.nspname = 'public') AND
(pp.pronameLIKE '%ins') AND (pp1.proname LIKE '%upd') AN 
 D
> (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))'
> ERROR from backend during send_query: 'ERROR:  parser: parse error at or near "pg_catalog" at character 402'
> STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while executing the query'
>                  ------------------------------------------------------------
>                  hdbc=838154376, stmt=838185648, result=838149000
>                  manual_result=0, prepare=0, internal=0
>                  bindings=0, bindings_allocated=0
>                  parameters=0, parameters_allocated=0
>                  statement_type=0, statement='SELECT    pt.tgargs,              pt.tgnargs,
pt.tgdeferrable,               pt.tginitdeferred,              pp1.proname,            pp2.proname,            pc.oid,
              pc1.oid,                pc1.relname,            pn.nspname FROM pg_catalog.pg_class pc,
pg_catalog.pg_procpp1,                 pg_catalog.pg_proc pp2,                 pg_catalog.pg_trigger pt1,
pg_catalog.pg_triggerpt2,              pg_catalog.pg_proc pp,          pg_catalog.pg_trigger pt,
pg_catalog.pg_classpc1,                pg_catalog.pg_namespace pn              pg_catalog.pg_namespace pn1 WHERE
pt.tgrelid= pc.oid AND pp.oid = pt.tgfoid AND pt1.tgconstrrelid = pc.oid AND pp1.oid = pt1.tgfoid AND pt2.tgfoid =
pp2.oidAND pt2.tgconstrrelid = pc.oid AND ((pc.relname='CamposConfiguracao') AND (pn1.oid = pc.relnamespace) AND
(pn1.nspname= 'public') 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))'
>                  stmt_with_params='SELECT       pt.tgargs,              pt.tgnargs,             pt.tgdeferrable,
         pt.tginitdeferred,              pp1.proname,            pp2.proname,            pc.oid,
pc1.oid,               pc1.relname,            pn.nspname FROM pg_catalog.pg_class pc,
pg_catalog.pg_procpp1,                 pg_catalog.pg_proc pp2,                 pg_catalog.pg_trigger pt1,
pg_catalog.pg_triggerpt2,              pg_catalog.pg_proc pp,          pg_catalog.pg_trigger pt,
pg_catalog.pg_classpc1,                pg_catalog.pg_namespace pn              pg_catalog.pg_namespace pn1 WHERE
pt.tgrelid= pc.oid AND pp.oid = pt.tgfoid AND pt1.tgconstrrelid = pc.oid AND pp1.oid = pt1.tgfoid AND pt2.tgfoid =
pp2.oidAND pt2.tgconstrrelid = pc.oid AND ((pc.relname='CamposConfiguracao') AND (pn1.oid = pc.relnamespace) AND
(pn1.nspname= 'public') 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))'
>                  data_at_exec=-1, current_exec_param=-1, put_data=0
>                  currTuple=-1, current_col=-1, lobj_fd=-1
>                  maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
>                  cursor_name='SQL_CUR31F5B2B0'
>                  ----------------QResult Info -------------------------------
>                  fields=859429696, manual_tuples=0, backend_tuples=0, tupleField=0, conn=0
>                  fetch_count=0, num_total_rows=0, num_fields=0, cursor='(NULL)'
>                  message='ERROR:  parser: parse error at or near "pg_catalog" at character 402', command='(NULL)',
notice='(NULL)'
>                  status=7, inTuples=0
> CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:  parser: parse error at or near "pg_catalog" at
character402' 
>             ------------------------------------------------------------
>             henv=838147864, conn=838154376, status=1, num_stmts=16
>             sock=838147920, stmts=838147992, lobj_type=-999
>             ---------------- Socket Info -------------------------------
>             socket=1012, reverse=0, errornumber=0, errormsg='(NULL)'
>             buffer_in=838165360, buffer_out=838169464
>             buffer_filled_in=1, buffer_filled_out=0, buffer_read_in=1
> STATEMENT ERROR: func=PGAPI_ForeignKeys, desc='', errnum=7, errmsg='ERROR:  parser: parse error at or near
"pg_catalog"at character 402' 
>                  ------------------------------------------------------------
>                  hdbc=838154376, stmt=838185272, result=838188656
>                  manual_result=1, prepare=0, internal=0
>                  bindings=838173592, bindings_allocated=15
>                  parameters=0, parameters_allocated=0
>                  statement_type=-2, statement='(NULL)'
>                  stmt_with_params='(NULL)'
>                  data_at_exec=-1, current_exec_param=-1, put_data=0
>                  currTuple=-1, current_col=-1, lobj_fd=-1
>                  maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
>                  cursor_name=''
>                  ----------------QResult Info -------------------------------
>                  fields=859396536, manual_tuples=859395952, backend_tuples=0, tupleField=0, conn=0
>                  fetch_count=0, num_total_rows=0, num_fields=0, cursor='(NULL)'
>                  message='(NULL)', command='(NULL)', notice='(NULL)'
>                  status=0, inTuples=0
> CONN ERROR: func=PGAPI_ForeignKeys, desc='', errnum=110, errmsg='ERROR:  parser: parse error at or near "pg_catalog"
atcharacter 402' 
>             ------------------------------------------------------------
>             henv=838147864, conn=838154376, status=1, num_stmts=16
>             sock=838147920, stmts=838147992, lobj_type=-999
>             ---------------- Socket Info -------------------------------
>             socket=1012, reverse=0, errornumber=0, errormsg='(NULL)'
>             buffer_in=838165360, buffer_out=838169464
>             buffer_filled_in=1, buffer_filled_out=0, buffer_read_in=1
> conn=838154376, query='SELECT   pt.tgargs,              pt.tgnargs,             pt.tgdeferrable,
pt.tginitdeferred,             pp1.proname,            pp2.proname,            pc.oid,                 pc1.oid,
      pc1.relname,            pn.nspname FROM pg_catalog.pg_class pc,                 pg_catalog.pg_proc pp1,
     pg_catalog.pg_proc pp2,                 pg_catalog.pg_trigger pt1,              pg_catalog.pg_trigger pt2,
    pg_catalog.pg_proc pp,          pg_catalog.pg_trigger pt,               pg_catalog.pg_class pc1,
pg_catalog.pg_namespacepn              pg_catalog.pg_namespace pn1 WHERE       pt.tgrelid = pc.oid AND pp.oid =
pt.tgfoidAND pt1.tgconstrrelid = pc.oid AND pp1.oid = pt1.tgfoid AND pt2.tgfoid = pp2.oid AND pt2.tgconstrrelid =
pc.oidAND ((pc.relname='CamposDoBancoDeDados') AND (pn1.oid = pc.relnamespace) AND (pn1.nspname = 'public') AND
(pp.pronameLIKE '%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))'
> ERROR from backend during send_query: 'ERROR:  parser: parse error at or near "pg_catalog" at character 402'
> STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while executing the query'