Thread: ODBC 6.4 + PostgreSQL 6.5 = Can't browse tables
I've recently upgraded both pieces of software (DB, & ODBC), and since then, I can't browse tables in the DB using the ODBC driver.
For example, open Access 97, use 'Get External Data -> Link Tables -> ODBC, etc' and when the tables list comes up, it is completely empty. This even occurs if I have selected 'Show System Tables' in the ODBC configuration. I have appended the resultant psqlodbc.log file to the end of this message. As a side note, accessing the database via JDBC from within NetBeans (on the workstation) seems to work fine, as does psql on the server.
Other (possibly) relevant info:
Workstation: NT Workstation 4.0, Service Pack 4
Server (i386): RedHat Linux 6.0, Postgres 6.5
Any ideas/help appreciated.
================================
Chay Wesley (chay@ci.danville.ky.us)
Director of Financial Information Services
City of Danville, Kentucky
http://www.danville-ky.com
http://ci.danville.ky.us
Chay Wesley (chay@ci.danville.ky.us)
Director of Financial Information Services
City of Danville, Kentucky
http://www.danville-ky.com
http://ci.danville.ky.us
psqlodbc.log :
=================================================================================================
conn=155203152, SQLDriverConnect( in)='DSN=JobShop;', fDriverCompletion=1
DSN info: DSN='JobShop',server='mis2.ci.danville.ky.us',port='5432',dbase='jobshop',user='postgres',passwd='blablabla'
readonly='1',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
conn_settings=''
translation_dll='',translation_option=''
Global Options: Version='06.40.0006', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=0, use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1
extra_systable_prefixes='dd_;', conn_settings=''
conn=155203152, query=' '
conn=155203152, query='set DateStyle to 'ISO''
conn=155203152, query='set geqo to 'OFF''
conn=155203152, query='set ksqo to 'ON''
conn=155203152, query='select oid from pg_type where typname='lo''
[ fetched 0 rows ]
conn=155203152, SQLDriverConnect(out)='DSN=JobShop;DATABASE=jobshop;SERVER=mis2.ci.danville.ky.us;PORT=5432;UID=postgres;PWD=blablabla;READONLY=1;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='
conn=155203152, query='SELECT Config, nValue FROM MSysConf'
ERROR from backend during send_query: 'ERROR: msysconf: Table does not exist.'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'
------------------------------------------------------------
hdbc=155203152, stmt=155254848, result=0
manual_result=0, prepare=0, internal=0
bindings=0, bindings_allocated=0
parameters=0, parameters_allocated=0
statement_type=0, statement='SELECT Config, nValue FROM MSysConf'
stmt_with_params='SELECT Config, nValue FROM MSysConf'
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_CUR09410040'
----------------QResult Info -------------------------------
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: msysconf: Table does not exist.'
------------------------------------------------------------
henv=155197000, conn=155203152, status=1, num_stmts=16
sock=155197016, stmts=155197064, lobj_type=-999
---------------- Socket Info -------------------------------
socket=352, reverse=0, errornumber=0, errormsg='(null)'
buffer_in=155209488, buffer_out=155213592
buffer_filled_in=42, buffer_filled_out=0, buffer_read_in=42
conn=155203152, query='select relname, usename, relhasrules from pg_class, pg_user where relkind = 'r' and relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner)order by relname'
[ fetched 0 rows ]
conn=155203152, SQLDisconnect
DSN info: DSN='JobShop',server='mis2.ci.danville.ky.us',port='5432',dbase='jobshop',user='postgres',passwd='blablabla'
readonly='1',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
conn_settings=''
translation_dll='',translation_option=''
Global Options: Version='06.40.0006', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=0, use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1
extra_systable_prefixes='dd_;', conn_settings=''
conn=155203152, query=' '
conn=155203152, query='set DateStyle to 'ISO''
conn=155203152, query='set geqo to 'OFF''
conn=155203152, query='set ksqo to 'ON''
conn=155203152, query='select oid from pg_type where typname='lo''
[ fetched 0 rows ]
conn=155203152, SQLDriverConnect(out)='DSN=JobShop;DATABASE=jobshop;SERVER=mis2.ci.danville.ky.us;PORT=5432;UID=postgres;PWD=blablabla;READONLY=1;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='
conn=155203152, query='SELECT Config, nValue FROM MSysConf'
ERROR from backend during send_query: 'ERROR: msysconf: Table does not exist.'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'
------------------------------------------------------------
hdbc=155203152, stmt=155254848, result=0
manual_result=0, prepare=0, internal=0
bindings=0, bindings_allocated=0
parameters=0, parameters_allocated=0
statement_type=0, statement='SELECT Config, nValue FROM MSysConf'
stmt_with_params='SELECT Config, nValue FROM MSysConf'
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_CUR09410040'
----------------QResult Info -------------------------------
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: msysconf: Table does not exist.'
------------------------------------------------------------
henv=155197000, conn=155203152, status=1, num_stmts=16
sock=155197016, stmts=155197064, lobj_type=-999
---------------- Socket Info -------------------------------
socket=352, reverse=0, errornumber=0, errormsg='(null)'
buffer_in=155209488, buffer_out=155213592
buffer_filled_in=42, buffer_filled_out=0, buffer_read_in=42
conn=155203152, query='select relname, usename, relhasrules from pg_class, pg_user where relkind = 'r' and relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner)order by relname'
[ fetched 0 rows ]
conn=155203152, SQLDisconnect
==============================================
End of pgsqlodbc.log.
<p>Chay Wesley wrote: <blockquote type="CITE"><style></style><font face="Arial"><font size="-1">I've recently upgradedboth pieces of software (DB, & ODBC), and since then, I can't browse tables in the DB using the ODBC driver.</font></font><fontface="Arial"><font size="-1">For example, open Access 97, use 'Get External Data -> Link Tables-> ODBC, etc' and when the tables list comes up, it is completely empty. This even occurs if I have selected 'ShowSystem Tables' in the ODBC configuration. I have appended the resultant psqlodbc.log file to the end of this message. As a side note, accessing the database via JDBC from within NetBeans (on the workstation) seems to work fine, asdoes psql on the server.</font></font> <br /> <font face="Arial"><font size="-1">Other (possibly) relevant info:</font></font><fontface="Arial"><font size="-1"> Workstation: NT Workstation 4.0, Service Pack 4</font></font><fontface="Arial"><font size="-1"> Server (i386): RedHat Linux 6.0, Postgres 6.5</font></font> <font face="Arial"><fontsize="-1">Any ideas/help appreciated.</font></font><font face="Arial"><font size="-1">================================</font></font><br/><font face="Arial"><font size="-1">Chay Wesley (<a href="mailto:chay@ci.danville.ky.us">chay@ci.danville.ky.us</a>)</font></font><br/><font face="Arial"><font size="-1">Directorof Financial Information Services</font></font><br /><font face="Arial"><font size="-1">City of Danville,Kentucky</font></font><br /><font face="Arial"><font size="-1"><a href="http://www.danville-ky.com">http://www.danville-ky.com</a></font></font><br/><font face="Arial"><font size="-1"><ahref="http://ci.danville.ky.us">http://ci.danville.ky.us</a></font></font> <font face="Arial"><font size="-1">psqlodbc.log:</font></font><font face="Arial"><font size="-1">=================================================================================================</font></font><font face="Arial"><fontsize="-1">conn=155203152, SQLDriverConnect( in)='DSN=JobShop;', fDriverCompletion=1</font></font><br /><fontface="Arial"><font size="-1">DSN info: DSN='JobShop',server='mis2.ci.danville.ky.us',port='5432',dbase='jobshop',user='postgres',passwd='blablabla'</font></font><br /><fontface="Arial"><font size="-1"> readonly='1',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'</font></font><br/><font face="Arial"><font size="-1"> conn_settings=''</font></font><br /><font face="Arial"><font size="-1"> translation_dll='',translation_option=''</font></font><br/><font face="Arial"><font size="-1">Global Options: Version='06.40.0006',fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190</font></font><br/><font face="Arial"><font size="-1"> disable_optimizer=1, ksqo=1,unique_index=0, use_declarefetch=0</font></font><br /><font face="Arial"><font size="-1"> text_as_longvarchar=1,unknowns_as_longvarchar=0, bools_as_char=1</font></font><br /><font face="Arial"><font size="-1"> extra_systable_prefixes='dd_;', conn_settings=''</font></font><br /><font face="Arial"><font size="-1">conn=155203152,query=' '</font></font><br /><font face="Arial"><font size="-1">conn=155203152, query='set DateStyleto 'ISO''</font></font><br /><font face="Arial"><font size="-1">conn=155203152, query='set geqo to 'OFF''</font></font><br/><font face="Arial"><font size="-1">conn=155203152, query='set ksqo to 'ON''</font></font><br /><fontface="Arial"><font size="-1">conn=155203152, query='select oid from pg_type where typname='lo''</font></font><br /><fontface="Arial"><font size="-1"> [ fetched 0 rows ]</font></font><br /><font face="Arial"><font size="-1">conn=155203152, SQLDriverConnect(out)='DSN=JobShop;DATABASE=jobshop;SERVER=mis2.ci.danville.ky.us;PORT=5432;UID=postgres;PWD=blablabla;READONLY=1;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='</font></font><br /><fontface="Arial"><font size="-1">conn=155203152, query='SELECT Config, nValue FROM MSysConf'</font></font><br /><fontface="Arial"><font size="-1">ERROR from backend during send_query: 'ERROR: msysconf: Table does not exist.'</font></font><br/><font face="Arial"><font size="-1">STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Errorwhile executing the query'</font></font><br /><font face="Arial"><font size="-1"> ------------------------------------------------------------</font></font><br/><font face="Arial"><font size="-1"> hdbc=155203152, stmt=155254848, result=0</font></font><br /><font face="Arial"><font size="-1"> manual_result=0, prepare=0, internal=0</font></font><br /><font face="Arial"><font size="-1"> bindings=0, bindings_allocated=0</font></font><br /><font face="Arial"><font size="-1"> parameters=0, parameters_allocated=0</font></font><br /><font face="Arial"><font size="-1"> statement_type=0, statement='SELECT Config, nValue FROM MSysConf'</font></font><br /><font face="Arial"><fontsize="-1"> stmt_with_params='SELECT Config, nValue FROM MSysConf'</font></font><br /><fontface="Arial"><font size="-1"> data_at_exec=-1, current_exec_param=-1, put_data=0</font></font><br/><font face="Arial"><font size="-1"> currTuple=-1, current_col=-1, lobj_fd=-1</font></font><br/><font face="Arial"><font size="-1"> maxRows=0, rowset_size=1, keyset_size=0,cursor_type=0, scroll_concurrency=1</font></font><br /><font face="Arial"><font size="-1"> cursor_name='SQL_CUR09410040'</font></font><br /><font face="Arial"><font size="-1"> ----------------QResult Info -------------------------------</font></font><br /><font face="Arial"><fontsize="-1">CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: msysconf: Table does not exist.'</font></font><br/><font face="Arial"><font size="-1"> ------------------------------------------------------------</font></font><br/><font face="Arial"><font size="-1"> henv=155197000, conn=155203152, status=1, num_stmts=16</font></font><br /><font face="Arial"><font size="-1"> sock=155197016, stmts=155197064, lobj_type=-999</font></font><br /><font face="Arial"><font size="-1"> ---------------- Socket Info -------------------------------</font></font><br /><font face="Arial"><fontsize="-1"> socket=352, reverse=0, errornumber=0, errormsg='(null)'</font></font><br /><fontface="Arial"><font size="-1"> buffer_in=155209488, buffer_out=155213592</font></font><br /><font face="Arial"><fontsize="-1"> buffer_filled_in=42, buffer_filled_out=0, buffer_read_in=42</font></font><br /><fontface="Arial"><font size="-1">conn=155203152, query='select relname, usename, relhasrules from pg_class, pg_user whererelkind = 'r' and relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner)order by relname'</font></font><br/><font face="Arial"><font size="-1"> [ fetched 0 rows ]</font></font><br /><font face="Arial"><fontsize="-1">conn=155203152, SQLDisconnect</font></font> <font face="Arial"><font size="-1">==============================================</font></font><fontface="Arial"><font size="-1">End of pgsqlodbc.log.</font></font> </blockquote><p><br/>The log shows that 0 rows are coming back. Are you sure you have any tables?<br />Try running the exact query above in the psql monitor and see what happens. <p>Byron
If I do a 'Select * from pg_class;', I get 58 rows returned, 2 of which are names of tables that I expect to see from the ODBC end.
However, if I do a 'Select * from pg_user;', I get 0 rows returned.
How do entries make it into pg_user? I previously ran 'createuser' from the command line, and have also 'granted' permissions on the tables.
Chay Wesley
----- Original Message -----From: Byron NikolaidisTo: Chay WesleySent: Friday, June 25, 1999 2:36 PMSubject: Re: [INTERFACES] ODBC 6.4 + PostgreSQL 6.5 = Can't browse tables
The log shows that 0 rows are coming back. Are you sure you have any tables?
Try running the exact query above in the psql monitor and see what happens.Byron
Upon further investigation:
If, in the following query that is being passed to Postgresql from the ODBC driver, the table named 'pg_user' were to be replaced the table named 'pg_shadow', then the query would work, returning 72 rows.
query='select relname, usename, relhasrules from pg_class, pg_user where relkind = 'r' and relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner)order by relname'
[ fetched 0 rows ]
[ fetched 0 rows ]
Where to go from here.....??
Perhaps I'm overlooking an ODBC configuration problem.
Chay Wesley
<p>Chay Wesley wrote: <blockquote type="CITE"><style></style><font face="Arial"><font size="-1">If I do a 'Select * frompg_class;', I get 58 rows returned, 2 of which are names of tables that I expect to see from the ODBC end.</font></font><fontface="Arial"><font size="-1">However, if I do a 'Select * from pg_user;', I get 0 rows returned.</font></font> <fontface="Arial"><font size="-1">How do entries make it into pg_user? I previously ran 'createuser'from the command line, and have also 'granted' permissions on the tables.</font></font> <font face="Arial"><fontsize="-1">Chay Wesley</font></font> </blockquote><p><br />Don't do a select * from pg_class.... Do theexact entire query that you see in the logfile (select relname, usename .... where relkind ....) and see what you get.<p>Also, if you do a "\d" in the monitor, what do you get? <p>Byron
<blockquote style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px;
PADDING-RIGHT:0px"><p><font face="Arial" size="2"><font face="Courier" size="2">jobshop=> </font></font><p><font
face="Courier"size="2">jobshop=> select relname, usename, relhasrules from pg_class, pg_user where relk<br />ind =
'r'and relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner)<br />order by relname;<br
/>relname|usename|relhasrules<br/>-------+-------+-----------<br />(0 rows)</font><p><font face="Courier"
size="2">jobshop=></font><p><fontface="Courier" size="2">jobshop=> \d<br />Couldn't find any tables, sequences or
indices!<br/>jobshop=></font><p><font face="Courier" size="2">jobshop=> \d joblistings <- The name of one of
mytables.</font><p><font face="Courier" size="2">Table = joblistings<br
/>+----------------------------------+----------------------------------+-------+<br/>|
Field | Type | Length|<br
/>+----------------------------------+----------------------------------+-------+<br/>|
listingid | int4 not null default nextval('j | 4 |<br />| officeid |
int4 | 4 |<br />| posteddate | date
| 4 |<br />| enabled | bool | 1 |<br />|
description | text | var |<br />| numpositions |
int2 | 2 |<br />| locationdescription | text
| var |<br />| extrahtml | text | var |<br
/>+----------------------------------+----------------------------------+-------+<br/>Index:
joblistings_listingid_key</font><p><fontface="Arial" size="2"><font face="Courier" size="2">jobshop=>
</font></font><p> <p> <p>Don't do a select * from pg_class.... Do the exact entire query that you see in the logfile
(selectrelname, usename .... where relkind ....) and see what you get. <p>Also, if you do a "\d" in the monitor, what
doyou get? <p>Byron </blockquote>