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(at)inf(dot)ufsc(dot)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.nspname from pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class d, pg_catalog.pg_am a, pg_catalog.pg_namespace n where d.relname = 'DadosAgoraSemanaNormalizados' 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 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_attribute a, pg_catalog.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) 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.nspname from pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class d, pg_catalog.pg_am a, pg_catalog.pg_namespace n 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_attribute a, pg_catalog.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) 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.nspname from pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class d, pg_catalog.pg_am a, pg_catalog.pg_namespace n 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_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') 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_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))'
> 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_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))'
> 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 character 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
> 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" at character 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_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='CamposDoBancoDeDados') 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))'
> 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'