Thread: Problems with OLE DB connection to my Postgesql database with ODBC drivers

Problems with OLE DB connection to my Postgesql database with ODBC drivers

From
"Phillip J. Allen"
Date:
Hi all,

I am trying to get the new ArcView 8.1 to open up my data in a
PostgreSQL linux server.  I am using PostgreSQL 7.1.2 and
ODBC-PostgreSQL 7.01.00.09.  Now I have set up my “System DNS” properly
and all works fine with connecting MS Access97 to the PG database.

Now the new ArcView 8.1 does not use ODBC directly but “OLE DB”.  From
within the ArcCatalog when I setup my connection I select “OLE DB
Provider for ODBC Providers” and select the “System DNS” that I know
works properly.  With this “good” DNS connection, after connecting to
the database when I look at the available tables in my database all the
tables names appears with a “.” Before them (.tablename).  Then when I
try to browse the table I get an error.  My limited knowledge of sql
logs can see that it is try to pass the query “Select * from
.tablename;”.

Is there a problem with the OLE DB and ODBC drivers?  Has anyone ever
accessed Postgresql with OLE DB before?  If I link the Postgresql tables
into a MS Access database and try to open a Selection query of the pg
tables all works fine.  This is a solution for the short term but I
would like to avoid using a middle layer of MS Access.

Any help will be surely appreciated.

Below I have added my odbc logs and psqlodbc logs.

Thanks,

Phillip J. Allen
Consulting Geochemist/Geologist
Lima Peru
e-mail: paallen@attglobal.net

My pgsqlodbc.log
Global Options: Version='07.01.0009', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=256, max_longvarchar_size=8190
                disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=1
                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=0
                extra_systable_prefixes='', conn_settings=''
conn=94137712, query=' '
conn=94137712, query='set DateStyle to 'ISO''
conn=94137712, query='set geqo to 'OFF''
conn=94137712, query='set ksqo to 'ON''
conn=94137712, query='BEGIN'
conn=94137712, query='declare SQL_CUR059D0D60 cursor for select oid from
pg_type where typname='lo''
conn=94137712, query='fetch 100 in SQL_CUR059D0D60'
    [ fetched 0 rows ]
conn=94137712, query='close SQL_CUR059D0D60'
conn=94137712, query='END'
conn=94137712, query='BEGIN'
conn=94137712, query='declare SQL_CUR059D0D60 cursor for select
version()'
conn=94137712, query='fetch 100 in SQL_CUR059D0D60'
    [ fetched 1 rows ]
    [ PostgreSQL version string = 'PostgreSQL 7.1.2 on
i686-pc-linux-gnu, compiled by GCC 2.96' ]
    [ PostgreSQL version number = '7.1' ]
conn=94137712, query='close SQL_CUR059D0D60'
conn=94137712, query='END'
conn=94137712,

PGAPI_DriverConnect(out)='DSN=mhc_biblio;DATABASE=mhc_biblio;SERVER=MHCGEO01A;PORT=5432;UID=paallen;PWD=pjallen;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;UNKNOWNSIZES=0;MAXVARCHARSIZE=256;MAXLONGVARCHARSIZE=8190;DEBUG=1;COMMLOG=1;OPTIMIZER=1;KSQO=1;USEDECLAREFETCH=1;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=0;BOOLSASCHAR=0;PARSE=1;CANCELASFREESTMT=1;EXTRASYSTABLEPREFIXES='

conn=94137712, PGAPI_Disconnect
Global Options: Version='07.01.0009', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=256, max_longvarchar_size=8190
                disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=1
                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=0
                extra_systable_prefixes='', conn_settings=''
conn=94137712, query=' '
conn=94137712, query='set DateStyle to 'ISO''
conn=94137712, query='set geqo to 'OFF''
conn=94137712, query='set ksqo to 'ON''
conn=94137712, query='BEGIN'
conn=94137712, query='declare SQL_CUR059D0D60 cursor for select oid from
pg_type where typname='lo''
conn=94137712, query='fetch 100 in SQL_CUR059D0D60'
    [ fetched 0 rows ]
conn=94137712, query='close SQL_CUR059D0D60'
conn=94137712, query='END'
conn=94137712, query='BEGIN'
conn=94137712, query='declare SQL_CUR059D0D60 cursor for select
version()'
conn=94137712, query='fetch 100 in SQL_CUR059D0D60'
    [ fetched 1 rows ]
    [ PostgreSQL version string = 'PostgreSQL 7.1.2 on
i686-pc-linux-gnu, compiled by GCC 2.96' ]
    [ PostgreSQL version number = '7.1' ]
conn=94137712, query='close SQL_CUR059D0D60'
conn=94137712, query='END'
conn=94137712,

PGAPI_DriverConnect(out)='DSN=mhc_biblio;DATABASE=mhc_biblio;SERVER=MHCGEO01A;PORT=5432;UID=paallen;PWD=pjallen;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;UNKNOWNSIZES=0;MAXVARCHARSIZE=256;MAXLONGVARCHARSIZE=8190;DEBUG=1;COMMLOG=1;OPTIMIZER=1;KSQO=1;USEDECLAREFETCH=1;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=0;BOOLSASCHAR=0;PARSE=1;CANCELASFREESTMT=1;EXTRASYSTABLEPREFIXES='

conn=94137712, PGAPI_Disconnect
Global Options: Version='07.01.0009', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=256, max_longvarchar_size=8190
                disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=1
                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=0
                extra_systable_prefixes='', conn_settings=''
conn=94137712, query=' '
conn=94137712, query='set DateStyle to 'ISO''
conn=94137712, query='set geqo to 'OFF''
conn=94137712, query='set ksqo to 'ON''
conn=94137712, query='BEGIN'
conn=94137712, query='declare SQL_CUR059D0D60 cursor for select oid from
pg_type where typname='lo''
conn=94137712, query='fetch 100 in SQL_CUR059D0D60'
    [ fetched 0 rows ]
conn=94137712, query='close SQL_CUR059D0D60'
conn=94137712, query='END'
conn=94137712, query='BEGIN'
conn=94137712, query='declare SQL_CUR059D0D60 cursor for select
version()'
conn=94137712, query='fetch 100 in SQL_CUR059D0D60'
    [ fetched 1 rows ]
    [ PostgreSQL version string = 'PostgreSQL 7.1.2 on
i686-pc-linux-gnu, compiled by GCC 2.96' ]
    [ PostgreSQL version number = '7.1' ]
conn=94137712, query='close SQL_CUR059D0D60'
conn=94137712, query='END'
conn=94137712,

PGAPI_DriverConnect(out)='DSN=mhc_biblio;DATABASE=mhc_biblio;SERVER=MHCGEO01A;PORT=5432;UID=paallen;PWD=pjallen;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;UNKNOWNSIZES=0;MAXVARCHARSIZE=256;MAXLONGVARCHARSIZE=8190;DEBUG=1;COMMLOG=1;OPTIMIZER=1;KSQO=1;USEDECLAREFETCH=1;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=0;BOOLSASCHAR=0;PARSE=1;CANCELASFREESTMT=1;EXTRASYSTABLEPREFIXES='

CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=1209',
errnum=205, errmsg='Unknown connect option (Get)'
            ------------------------------------------------------------

            henv=94179184, conn=94137712, status=1, num_stmts=16
            sock=94179136, stmts=94179056, lobj_type=-999
            ---------------- Socket Info -------------------------------

            socket=536, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=94148632, buffer_out=94152736
            buffer_filled_in=9, buffer_filled_out=0, buffer_read_in=9
conn=94137712, query='BEGIN'
conn=94137712, query='declare SQL_CUR059D05C0 cursor for select relname,
usename, relkind from pg_class, pg_user where relkind in ('r', 'v') and
relname !~ '^pg_' and usesysid = relowner order by relname'
conn=94137712, query='fetch 100 in SQL_CUR059D05C0'
    [ fetched 24 rows ]
conn=94137712, query='close SQL_CUR059D05C0'
conn=94137712, query='END'
conn=94137712, query='BEGIN'
conn=94137712, query='declare SQL_CUR059D0D60 cursor for SELECT * FROM
.biblio'
ERROR from backend during send_query: 'ERROR:  parser: parse error at or
near "."'
conn=94137712, query='ABORT'
STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while
executing the query'

------------------------------------------------------------
                 hdbc=94137712, stmt=94178656, result=94178416
                 manual_result=0, prepare=0, internal=0
                 bindings=0, bindings_allocated=0
                 parameters=0, parameters_allocated=0
                 statement_type=0, statement='SELECT * FROM .biblio'
                 stmt_with_params='declare SQL_CUR059D0D60 cursor for
SELECT * FROM .biblio'
                 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_CUR059D0D60'
                 ----------------QResult Info
-------------------------------
                 fields=94178384, manual_tuples=0, backend_tuples=0,
tupleField=0, conn=0
                 fetch_count=0, fcount=0, num_fields=0, cursor='(NULL)'
                 message='(NULL)', command='(NULL)', notice='(NULL)'
                 status=7, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:
parser: parse error at or near "."'
            ------------------------------------------------------------

            henv=94179184, conn=94137712, status=1, num_stmts=16
            sock=94179136, stmts=94179056, lobj_type=-999
            ---------------- Socket Info -------------------------------

            socket=536, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=94148632, buffer_out=94152736
            buffer_filled_in=11, buffer_filled_out=0, buffer_read_in=11
conn=94137712, query='BEGIN'
conn=94137712, query='declare SQL_CUR059D0D60 cursor for SELECT * FROM
.biblio'
ERROR from backend during send_query: 'ERROR:  parser: parse error at or
near "."'
conn=94137712, query='ABORT'
STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while
executing the query'

------------------------------------------------------------
                 hdbc=94137712, stmt=94178656, result=94178416
                 manual_result=0, prepare=0, internal=0
                 bindings=0, bindings_allocated=0
                 parameters=0, parameters_allocated=0
                 statement_type=0, statement='SELECT * FROM .biblio'
                 stmt_with_params='declare SQL_CUR059D0D60 cursor for
SELECT * FROM .biblio'
                 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_CUR059D0D60'
                 ----------------QResult Info
-------------------------------
                 fields=94178384, manual_tuples=0, backend_tuples=0,
tupleField=0, conn=0
                 fetch_count=0, fcount=0, num_fields=0, cursor='(NULL)'
                 message='(NULL)', command='(NULL)', notice='(NULL)'
                 status=7, inTuples=0
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:
parser: parse error at or near "."'
            ------------------------------------------------------------

            henv=94179184, conn=94137712, status=1, num_stmts=16
            sock=94179136, stmts=94179056, lobj_type=-999
            ---------------- Socket Info -------------------------------

            socket=536, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=94148632, buffer_out=94152736
            buffer_filled_in=11, buffer_filled_out=0, buffer_read_in=11
conn=94137712, PGAPI_Disconnect


MY MYLOG.log file (partial, it is 69 pages long)

CC_connect: entering...
CC_connect(): DSN = 'mhc_biblio', server = 'MHCGEO01A', port = '5432',
database = 'mhc_biblio', username = 'paallen', password='pjallen'
connecting to the server socket...
connection to the server socket succeeded.
sizeof startup packet = 292
sent the authentication block.
sent the authentication block successfully.
gonna do authentication
read 5, global_socket_buffersize=4096
auth got 'R'
areq = 0
read 10, global_socket_buffersize=4096
auth got 'K'
auth got 'Z'
sending an empty query...
send_query(): conn=94137712, query=' '
send_query: done sending query
read 3, global_socket_buffersize=4096
send_query: got id = 'I'
in QR_Constructor
exit QR_Constructor
send_query: got id = 'Z'
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
empty query seems to be OK.
CC_send_settings: entering...
PGAPI_AllocStmt: entering...
**** PGAPI_AllocStmt: hdbc = 94137712, stmt = 94178656
CC_add_statement: self=94137712, stmt=94178656
PGAPI_ExecDirect: entering...
**** PGAPI_ExecDirect: hstmt=94178656, statement='set DateStyle to
'ISO''
PGAPI_ExecDirect: calling PGAPI_Execute...
PGAPI_Execute: entering...
PGAPI_Execute: clear errors...
PGAPI_Execute: copying statement params: trans_status=1, len=22,
stmt='set DateStyle to 'ISO''
   stmt_with_params = 'set DateStyle to 'ISO''
      it's NOT a select statement: stmt=94178656
send_query(): conn=94137712, query='set DateStyle to 'ISO''
send_query: done sending query
read 15, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - SET VARIABLE
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'SET VARIABLE'
send_query: returning res = 94178416
send_query: got id = 'Z'
PGAPI_ExecDirect: returned 0 from PGAPI_Execute
CC_send_settings: result 0, status 1 from set DateStyle
PGAPI_ExecDirect: entering...
**** PGAPI_ExecDirect: hstmt=94178656, statement='set geqo to 'OFF''
PGAPI_ExecDirect: calling PGAPI_Execute...
PGAPI_Execute: entering...
PGAPI_Execute: clear errors...
PGAPI_Execute: recycling statement (should have been done by app)...
recycle statement: self= 94178656
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
SC_free_params:  ENTER, self=94178656
PGAPI_Execute: copying statement params: trans_status=1, len=17,
stmt='set geqo to 'OFF''
   stmt_with_params = 'set geqo to 'OFF''
      it's NOT a select statement: stmt=94178656
send_query(): conn=94137712, query='set geqo to 'OFF''
send_query: done sending query
read 15, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - SET VARIABLE
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'SET VARIABLE'
send_query: returning res = 94178416
send_query: got id = 'Z'
PGAPI_ExecDirect: returned 0 from PGAPI_Execute
CC_send_settings: result 0, status 1 from set geqo
PGAPI_ExecDirect: entering...
**** PGAPI_ExecDirect: hstmt=94178656, statement='set ksqo to 'ON''
PGAPI_ExecDirect: calling PGAPI_Execute...
PGAPI_Execute: entering...
PGAPI_Execute: clear errors...
PGAPI_Execute: recycling statement (should have been done by app)...
recycle statement: self= 94178656
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
SC_free_params:  ENTER, self=94178656
PGAPI_Execute: copying statement params: trans_status=1, len=16,
stmt='set ksqo to 'ON''
   stmt_with_params = 'set ksqo to 'ON''
      it's NOT a select statement: stmt=94178656
send_query(): conn=94137712, query='set ksqo to 'ON''
send_query: done sending query
read 15, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - SET VARIABLE
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'SET VARIABLE'
send_query: returning res = 94178416
send_query: got id = 'Z'
PGAPI_ExecDirect: returned 0 from PGAPI_Execute
CC_send_settings: result 0, status 1 from set ksqo
PGAPI_FreeStmt: entering...hstmt=94178656, fOption=1
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
SC_Destructor: self=94178656, self->result=0, self->hdbc=94137712
SC_free_params:  ENTER, self=94178656
SC_Destructor: EXIT
CC_lookup_lo: entering...
PGAPI_AllocStmt: entering...
**** PGAPI_AllocStmt: hdbc = 94137712, stmt = 94178656
CC_add_statement: self=94137712, stmt=94178656
PGAPI_ExecDirect: entering...
**** PGAPI_ExecDirect: hstmt=94178656, statement='select oid from
pg_type where typname='lo''
PGAPI_ExecDirect: calling PGAPI_Execute...
PGAPI_Execute: entering...
PGAPI_Execute: clear errors...
PGAPI_Execute: copying statement params: trans_status=1, len=42,
stmt='select oid from pg_type where typname='lo''
   stmt_with_params = 'declare SQL_CUR059D0D60 cursor for select oid
from pg_type where typname='lo''
   about to begin a transaction on statement = 94178656
send_query(): conn=94137712, query='BEGIN'
send_query: done sending query
read 8, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - BEGIN
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'BEGIN'
send_query: returning res = 94176880
send_query: got id = 'Z'
SC_exec: begin ok = 1, status = 1
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
       Sending SELECT statement on stmt=94178656,
cursor_name='SQL_CUR059D0D60'
send_query(): conn=94137712, query='declare SQL_CUR059D0D60 cursor for
select oid from pg_type where typname='lo''
send_query: done sending query
read 9, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - SELECT
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'SELECT'
send_query: returning res = 94176880
send_query: got id = 'Z'
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
send_query(): conn=94137712, query='fetch 100 in SQL_CUR059D0D60'
send_query: done sending query
read 42, global_socket_buffersize=4096
send_query: got id = 'P'
send_query: got id = 'T'
in QR_Constructor
exit QR_Constructor
send_query: 'T' no result_in: res = 94176880
QR_fetch_tuples: cursor = 'SQL_CUR059D0D60', self->cursor=0
num_fields = 1
READING ATTTYPMOD
CI_read_fields: fieldname='oid', adtid=26, adtsize=4, atttypmod=-1
QR_fetch_tuples: past CI_read_fields: num_fields = 1
MALLOC: tuple_size = 100, size = 800
next_tuple: inTuples = true, falling through: fcount = 101, fetch_count
= 101
end of tuple list -- setting inUse to false: this = 94176880
_next_tuple: 'C': DONE (fcount == 0)
send_query: got id = 'Z'
     done sending the query:
extend_bindings: entering ... stmt=94178656, bindings_allocated=0,
num_columns=1
exit extend_bindings
PGAPI_ExecDirect: returned 0 from PGAPI_Execute
PGAPI_Fetch: stmt = 94178656, stmt->result= 94176880
manual_result = 0, use_declarefetch = 1
next_tuple: fcount < CACHE_SIZE: fcount = 0, fetch_count = 0
**** SC_fetch: end_tuples
PGAPI_FreeStmt: entering...hstmt=94178656, fOption=1
QResult: in DESTRUCTOR
QResult: closing cursor: 'close SQL_CUR059D0D60'
send_query(): conn=94137712, query='close SQL_CUR059D0D60'
send_query: done sending query
read 8, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - CLOSE
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'CLOSE'
send_query: returning res = 94175856
send_query: got id = 'Z'
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
CC_cursor_count: self=94137712, num_stmts=16
CC_cursor_count: returning 0
QResult: END transaction on conn=94137712
send_query(): conn=94137712, query='END'
send_query: done sending query
read 9, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - COMMIT
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'COMMIT'
send_query: returning res = 94175856
send_query: got id = 'Z'
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
SC_Destructor: self=94178656, self->result=0, self->hdbc=94137712
SC_free_params:  ENTER, self=94178656
SC_Destructor: EXIT
CC_lookup_pg_version: entering...
PGAPI_AllocStmt: entering...
**** PGAPI_AllocStmt: hdbc = 94137712, stmt = 94178656
CC_add_statement: self=94137712, stmt=94178656
PGAPI_ExecDirect: entering...
**** PGAPI_ExecDirect: hstmt=94178656, statement='select version()'
PGAPI_ExecDirect: calling PGAPI_Execute...
PGAPI_Execute: entering...
PGAPI_Execute: clear errors...
PGAPI_Execute: copying statement params: trans_status=1, len=16,
stmt='select version()'
   stmt_with_params = 'declare SQL_CUR059D0D60 cursor for select
version()'
   about to begin a transaction on statement = 94178656
send_query(): conn=94137712, query='BEGIN'
send_query: done sending query
read 8, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - BEGIN
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'BEGIN'
send_query: returning res = 94178416
send_query: got id = 'Z'
SC_exec: begin ok = 1, status = 1
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
       Sending SELECT statement on stmt=94178656,
cursor_name='SQL_CUR059D0D60'
send_query(): conn=94137712, query='declare SQL_CUR059D0D60 cursor for
select version()'
send_query: done sending query
read 9, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - SELECT
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'SELECT'
send_query: returning res = 94178416
send_query: got id = 'Z'
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
send_query(): conn=94137712, query='fetch 100 in SQL_CUR059D0D60'
send_query: done sending query
read 111, global_socket_buffersize=4096
send_query: got id = 'P'
send_query: got id = 'T'
in QR_Constructor
exit QR_Constructor
send_query: 'T' no result_in: res = 94178416
QR_fetch_tuples: cursor = 'SQL_CUR059D0D60', self->cursor=0
num_fields = 1
READING ATTTYPMOD
CI_read_fields: fieldname='version', adtid=25, adtsize=-1, atttypmod=-1
QR_fetch_tuples: past CI_read_fields: num_fields = 1
MALLOC: tuple_size = 100, size = 800
next_tuple: inTuples = true, falling through: fcount = 101, fetch_count
= 101
qresult: len=59, buffer='PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled
by GCC 2.96'
end of tuple list -- setting inUse to false: this = 94178416
_next_tuple: 'C' fetch_max && fcount = 1
send_query: got id = 'Z'
     done sending the query:
extend_bindings: entering ... stmt=94178656, bindings_allocated=0,
num_columns=1
exit extend_bindings
PGAPI_ExecDirect: returned 0 from PGAPI_Execute
PGAPI_Fetch: stmt = 94178656, stmt->result= 94178416
manual_result = 0, use_declarefetch = 1
next_tuple: fetch_count < fcount: returning tuple 0, fcount = 1
fetch: cols=1, lf=0, self = 94178656, self->bindings = 94175904,
buffer[] = 0
PGAPI_GetData: enter, stmt=94178656
  socket: value = 'PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by
GCC 2.96'
**** PGAPI_GetData: icol = 0, fCType = 1, field_type = 25, value =
'PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.96'
copy_and_convert: field_type = 25, fctype = 1, value = 'PostgreSQL 7.1.2
on i686-pc-linux-gnu, compiled by GCC 2.96', cbValueMax=128
DEFAULT: len = 59, ptr = 'PostgreSQL 7.1.2 on i686-pc-linux-gnu,
compiled by GCC 2.96'
    SQL_C_CHAR, default: len = 59, cbValueMax = 128, rgbValueBindRow =
'PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.96'
Got the PostgreSQL version string: 'PostgreSQL 7.1.2 on
i686-pc-linux-gnu, compiled by GCC 2.96'
Extracted PostgreSQL version number: '7.1'
PGAPI_FreeStmt: entering...hstmt=94178656, fOption=1
QResult: in DESTRUCTOR
QResult: closing cursor: 'close SQL_CUR059D0D60'
send_query(): conn=94137712, query='close SQL_CUR059D0D60'
send_query: done sending query
read 8, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - CLOSE
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'CLOSE'
send_query: returning res = 94175808
send_query: got id = 'Z'
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
CC_cursor_count: self=94137712, num_stmts=16
CC_cursor_count: returning 0
QResult: END transaction on conn=94137712
send_query(): conn=94137712, query='END'
send_query: done sending query
read 9, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - COMMIT
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'COMMIT'
send_query: returning res = 94175808
send_query: got id = 'Z'
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
QResult: free memory in, fcount=1
row = 0, num_fields = 1
free [lf=0] 94175968
QResult: free memory out
QResult: exit DESTRUCTOR
SC_Destructor: self=94178656, self->result=0, self->hdbc=94137712
SC_free_params:  ENTER, self=94178656
SC_Destructor: EXIT
CC_connect: returning...
szConnStrOut =

'DSN=mhc_biblio;DATABASE=mhc_biblio;SERVER=MHCGEO01A;PORT=5432;UID=paallen;PWD=pjallen;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;UNKNOWNSIZES=0;MAXVARCHARSIZE=256;MAXLONGVARCHARSIZE=8190;DEBUG=1;COMMLOG=1;OPTIMIZER=1;KSQO=1;USEDECLAREFETCH=1;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=0;BOOLSASCHAR=0;PARSE=1;CANCELASFREESTMT=1;EXTRASYSTABLEPREFIXES='
len=432,1024
PGAPI_DRiverConnect: returning 0
[SQLError]**** PGAPI_Error: henv=0, hdbc=94137712, hstmt=0 <512>
calling CC_get_error
enter CC_get_error
enter CC_create_errormsg
msg = ''
exit CC_create_errormsg
exit CC_get_error
CC_Get_error returned nothing.
[SQLGetFunctions]PGAPI_GetFunctions: entering...0
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=23
PGAPI_GetInfo: p='<NULL>', len=2, value=1, cbMax=2
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=24
PGAPI_GetInfo: p='<NULL>', len=2, value=1, cbMax=2
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=79
PGAPI_GetInfo: p='<NULL>', len=4, value=3, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=83
PGAPI_GetInfo: p='<NULL>', len=4, value=0, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=78
PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=81
PGAPI_GetInfo: p='<NULL>', len=4, value=15, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=72
PGAPI_GetInfo: p='<NULL>', len=4, value=2, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=82
PGAPI_GetInfo: p='<NULL>', len=4, value=0, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=44
PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=43
PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=79
PGAPI_GetInfo: p='<NULL>', len=4, value=3, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=8
PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=80
PGAPI_GetInfo: p='<NULL>', len=4, value=0, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=78
PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=79
PGAPI_GetInfo: p='<NULL>', len=4, value=3, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=8
PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=80
PGAPI_GetInfo: p='<NULL>', len=4, value=0, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=78
PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=79
PGAPI_GetInfo: p='<NULL>', len=4, value=3, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=8
PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=80
PGAPI_GetInfo: p='<NULL>', len=4, value=0, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=78
PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=8
PGAPI_GetInfo: p='<NULL>', len=4, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=80
PGAPI_GetInfo: p='<NULL>', len=4, value=0, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=111
PGAPI_GetInfo: p='N', len=0, value=0, cbMax=512
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=16
PGAPI_GetInfo: p='', len=0, value=0, cbMax=0
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=23
PGAPI_GetInfo: p='<NULL>', len=2, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=24
PGAPI_GetInfo: p='<NULL>', len=2, value=1, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=46
PGAPI_GetInfo: p='<NULL>', len=2, value=2, cbMax=4
[SQLSetConnectionOption]PGAPI_SetConnectOption: entering fOption = 1
vParam = 0
SetStmtOption(): SQL_MAX_ROWS, vParam = 0
[SQLSetConnectionOption]PGAPI_SetConnectOption: entering fOption = 0
vParam = 0
SetStmtOption: SQL_QUERY_TIMEOUT, vParam = 0
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=6
PGAPI_GetInfo: p='PSQLODBC.DLL', len=0, value=0, cbMax=52
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=6
PGAPI_GetInfo: p='PSQLODBC.DLL', len=0, value=0, cbMax=52
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=6
PGAPI_GetInfo: p='PSQLODBC.DLL', len=0, value=0, cbMax=52
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=79
PGAPI_GetInfo: p='<NULL>', len=4, value=3, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=1
PGAPI_GetInfo: p='<NULL>', len=2, value=0, cbMax=4
[SQLGetConnectOption]PGAPI_GetConnectOption: entering...
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=26
PGAPI_GetInfo: p='<NULL>', len=4, value=2, cbMax=4
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=91
PGAPI_GetInfo: p='<NULL>', len=4, value=0, cbMax=4
[SQLDisconnect]PGAPI_Disconnect: entering...
CC_connect: entering...
CC_connect(): DSN = 'mhc_biblio', server = 'MHCGEO01A', port = '5432',
database = 'mhc_biblio', username = 'paallen', password='pjallen'
connecting to the server socket...
connection to the server socket succeeded.
sizeof startup packet = 292
sent the authentication block.
sent the authentication block successfully.
gonna do authentication
read 5, global_socket_buffersize=4096
auth got 'R'
areq = 0
read 10, global_socket_buffersize=4096
auth got 'K'
auth got 'Z'
sending an empty query...
send_query(): conn=94137712, query=' '
send_query: done sending query
read 3, global_socket_buffersize=4096
send_query: got id = 'I'
in QR_Constructor
exit QR_Constructor
send_query: got id = 'Z'
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
empty query seems to be OK.
CC_send_settings: entering...
PGAPI_AllocStmt: entering...
**** PGAPI_AllocStmt: hdbc = 94137712, stmt = 94178656
CC_add_statement: self=94137712, stmt=94178656
PGAPI_ExecDirect: entering...
**** PGAPI_ExecDirect: hstmt=94178656, statement='set DateStyle to
'ISO''
PGAPI_ExecDirect: calling PGAPI_Execute...
PGAPI_Execute: entering...
PGAPI_Execute: clear errors...
PGAPI_Execute: copying statement params: trans_status=1, len=22,
stmt='set DateStyle to 'ISO''
   stmt_with_params = 'set DateStyle to 'ISO''
      it's NOT a select statement: stmt=94178656
send_query(): conn=94137712, query='set DateStyle to 'ISO''
send_query: done sending query
read 15, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - SET VARIABLE
in QR_Constructor
exit QR_Constructor
send_query: setting cmdbuffer = 'SET VARIABLE'
send_query: returning res = 94178416
send_query: got id = 'Z'
PGAPI_ExecDirect: returned 0 from PGAPI_Execute
CC_send_settings: result 0, status 1 from set DateStyle
PGAPI_ExecDirect: entering...
**** PGAPI_ExecDirect: hstmt=94178656, statement='set geqo to 'OFF''
PGAPI_ExecDirect: calling PGAPI_Execute...
PGAPI_Execute: entering...
PGAPI_Execute: clear errors...
PGAPI_Execute: recycling statement (should have been done by app)...
recycle statement: self= 94178656
QResult: in DESTRUCTOR
QResult: free memory in, fcount=0
QResult: free memory out
QResult: exit DESTRUCTOR
SC_free_params:  ENTER, self=94178656
PGAPI_Execute: copying statement params: trans_status=1, len=17,
stmt='set geqo to 'OFF''
   stmt_with_params = 'set geqo to 'OFF''
      it's NOT a select statement: stmt=94178656
send_query(): conn=94137712, query='set geqo to 'OFF''
send_query: done sending query
read 15, global_socket_buffersize=4096
send_query: got id = 'C'
send_query: ok - 'C' - SET VARIABLE
in QR_Constructor




Re: Problems with OLE DB connection to my Postgesql database with

From
Hiroshi Inoue
Date:
Please try the driver at
        http://w2422.nsk.ne.jp/~inoue/.

regards,
Hiroshi Inoue

"Phillip J. Allen" wrote:
>
> Hi all,
>
> I am trying to get the new ArcView 8.1 to open up my data in a
> PostgreSQL linux server.  I am using PostgreSQL 7.1.2 and
> ODBC-PostgreSQL 7.01.00.09.  Now I have set up my 鉄ystem DNS\xC7properly
> and all works fine with connecting MS Access97 to the PG database.
>
> Now the new ArcView 8.1 does not use ODBC directly but 徹LE DB\xC7  From
> within the ArcCatalog when I setup my connection I select 徹LE DB
> Provider for ODBC Providers\xC7and select the 鉄ystem DNS\xC7that I know
> works properly.  With this 堵ood\xC7DNS connection, after connecting to
> the database when I look at the available tables in my database all the
> tables names appears with a \xC5\xC7Before them (.tablename).  Then when I
> try to browse the table I get an error.  My limited knowledge of sql
> logs can see that it is try to pass the query 鉄elect * from
> .tablename;\xC7