Thread: Access 2000 & postgres ODBC-driver: "ODBC error"

Access 2000 & postgres ODBC-driver: "ODBC error"

From
Michael Ruder
Date:
Hi,

I use Postgres 7.0.3, psqlodbc 6.50.0000 with Access 2000.

Everything works fine but I get an "ODBC error" when I use a Visual Basic
construct like "MyTable.AddNew" (where MyTable is a Recordsetobject
referring to the table). This all works fine both my MyODBC and MySQL and
native Access tables, so it is definitely an issue with psqlodbc. The
logfile of psqlodbc shows this:

CONN ERROR: func=SQLGetConnectOption, desc='fOption=30002', errnum=205, errmsg='Unknown connect option (Get)'
------------------------------------------------------------          henv=143982724, conn=142936188, status=0,
num_stmts=16          sock=143982740, stmts=143982780, lobj_type=-999           ---------------- Socket Info
-------------------------------          socket=-1, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=142942516,buffer_out=142946616           buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
 
CONN ERROR: func=SQLSetConnectOption, desc='fOption=30002, vParam=139526100', errnum=205, errmsg='Unknown connect
option(Set)'           ------------------------------------------------------------           henv=143982724,
conn=142936188,status=0, num_stmts=16           sock=143982740, stmts=143982780, lobj_type=-999
----------------Socket Info -------------------------------           socket=-1, reverse=0, errornumber=0,
errormsg='(NULL)'          buffer_in=142942516, buffer_out=142946616           buffer_filled_in=0, buffer_filled_out=0,
buffer_read_in=0
conn=142936188, SQLDriverConnect(
in)='DSN=Adressdatenbank;UID=ADR_Master;PWD=;DATABASE=Adressen;SERVER=database.big-dipper.universe;PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=1;SHOWSYSTEMTABLES=0;CONNSETTINGS=;',
fDriverCompletion=0
DSN info:
DSN='Adressdatenbank',server='database.big-dipper.universe',port='5432',dbase='Adressen',user='ADR_Master',passwd='ADR_Master'
       onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'         conn_settings=''
translation_dll='',translation_option=''
Global Options: Version='06.50.0000', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254,
max_longvarchar_size=8190              disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=1
text_as_longvarchar=0,unknowns_as_longvarchar=1, bools_as_char=0               extra_systable_prefixes='dd_;',
conn_settings=''
conn=142936188, query=' '
conn=142936188, query='set DateStyle to 'ISO''
conn=142936188, query='set geqo to 'OFF''
conn=142936188, query='set ksqo to 'ON''
conn=142936188, query='BEGIN'
conn=142936188, query='declare SQL_CUR0885413C cursor for select oid from pg_type where typname='lo''
conn=142936188, query='fetch 100 in SQL_CUR0885413C'   [ fetched 0 rows ]
conn=142936188, query='close SQL_CUR0885413C'
conn=142936188, query='END'
conn=142936188,
SQLDriverConnect(out)='DSN=Adressdatenbank;DATABASE=Adressen;SERVER=database.big-dipper.universe;PORT=5432;UID=ADR_Master;PWD=ADR_Master;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=1;SHOWSYSTEMTABLES=0;CONNSETTINGS='
conn=142936188, query='BEGIN'
conn=142936188, query='declare SQL_CUR0885413C cursor for SELECT Config, nValue FROM MSysConf'
ERROR from backend during send_query: 'ERROR:  Relation 'msysconf' does not exist'
conn=142936188, query='ABORT'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'
------------------------------------------------------------               hdbc=142936188, stmt=142950716, 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='declare SQL_CUR0885413C cursor for 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_CUR0885413C'               ----------------QResult Info -------------------------------
 
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:  Relation 'msysconf' does not exist'
------------------------------------------------------------          henv=143982724, conn=142936188, status=1,
num_stmts=16          sock=143982740, stmts=143982780, lobj_type=-999           ---------------- Socket Info
-------------------------------          socket=332, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=142942516,buffer_out=142946616           buffer_filled_in=3, buffer_filled_out=0, buffer_read_in=2
 
conn=142936188, query='BEGIN'
conn=142936188, query='declare SQL_CUR0885413C cursor for SELECT "Personen"."PersonenNummer" FROM "Personen" '
conn=142936188, query='fetch 100 in SQL_CUR0885413C'   [ fetched 2 rows ]
conn=142936188, query='ROLLBACK'
CONN ERROR: func=SQLGetConnectOption, desc='fOption=30002', errnum=205, errmsg='Unknown connect option (Get)'
------------------------------------------------------------          henv=143982724, conn=143016456, status=0,
num_stmts=16          sock=143986564, stmts=143986604, lobj_type=-999           ---------------- Socket Info
-------------------------------          socket=-1, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=143022784,buffer_out=143026884           buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
 
CONN ERROR: func=SQLSetConnectOption, desc='fOption=30002, vParam=138480932', errnum=205, errmsg='Unknown connect
option(Set)'           ------------------------------------------------------------           henv=143982724,
conn=143016456,status=0, num_stmts=16           sock=143986564, stmts=143986604, lobj_type=-999
----------------Socket Info -------------------------------           socket=-1, reverse=0, errornumber=0,
errormsg='(NULL)'          buffer_in=143022784, buffer_out=143026884           buffer_filled_in=0, buffer_filled_out=0,
buffer_read_in=0
conn=143016456, SQLDriverConnect(
in)='DSN=Adressdatenbank;UID=ADR_Master;PWD=ADR_Master;DATABASE=Adressen;SERVER=database.big-dipper.universe;PORT=5432;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=1;SHOWSYSTEMTABLES=0;CONNSETTINGS=;',
fDriverCompletion=0
DSN info:
DSN='Adressdatenbank',server='database.big-dipper.universe',port='5432',dbase='Adressen',user='ADR_Master',passwd='ADR_Master'
       onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'         conn_settings=''
translation_dll='',translation_option=''
Global Options: Version='06.50.0000', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254,
max_longvarchar_size=8190              disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=1
text_as_longvarchar=0,unknowns_as_longvarchar=1, bools_as_char=0               extra_systable_prefixes='dd_;',
conn_settings=''
conn=143016456, query=' '
conn=143016456, query='set DateStyle to 'ISO''
conn=143016456, query='set geqo to 'OFF''
conn=143016456, query='set ksqo to 'ON''
conn=143016456, query='BEGIN'
conn=143016456, query='declare SQL_CUR08867AC8 cursor for select oid from pg_type where typname='lo''
conn=143016456, query='fetch 100 in SQL_CUR08867AC8'   [ fetched 0 rows ]
conn=143016456, query='close SQL_CUR08867AC8'
conn=143016456, query='END'
conn=143016456,
SQLDriverConnect(out)='DSN=Adressdatenbank;DATABASE=Adressen;SERVER=database.big-dipper.universe;PORT=5432;UID=ADR_Master;PWD=ADR_Master;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=1;SHOWSYSTEMTABLES=0;CONNSETTINGS='
conn=143016456, query='BEGIN'
conn=143016456, query='declare SQL_CUR08867AC8 cursor for SELECT "Adressen"."AdressNummer" FROM "Adressen" '
conn=143016456, query='fetch 100 in SQL_CUR08867AC8'   [ fetched 0 rows ]
conn=143016456, query='close SQL_CUR08867AC8'
conn=143016456, query='END'
conn=143016456, query='BEGIN'
conn=143016456, query='declare SQL_CUR08867AC8 cursor for SELECT "Telefonnummern"."TelefonNummer" FROM "Telefonnummern"
'
conn=143016456, query='fetch 100 in SQL_CUR08867AC8'   [ fetched 0 rows ]
conn=143016456, query='close SQL_CUR08867AC8'
conn=143016456, query='END'
conn=143016456, query='BEGIN'
conn=143016456, query='declare SQL_CUR08867AC8 cursor for SELECT "Emailadressen"."EmailNummer" FROM "Emailadressen" '
conn=143016456, query='fetch 100 in SQL_CUR08867AC8'   [ fetched 0 rows ]
conn=143016456, query='close SQL_CUR08867AC8'
conn=143016456, query='END'
conn=143016456, SQLDisconnect
conn=142936188, SQLDisconnect

There are several errors: the GetConnectOption-Error which I already saw
in other messages in this list but never saw an answer to it and then the
problem with MSysConf which I don't understand at all.

I was looking through mailing lists for several hours (!) already and I am
really sorry if this is an issue which has been discussed already, but I
couldn't find anything on this topic and I would be VERY thankful if
someone can give me a hint!

Thanks,
-- 
.      -Michael
 /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\< Hans Michael Ruder, mailto:ruder@gmx.de,
http://www.ruder.home.pages.de/> \/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/     PGP
(DH/DSS)fingerprint: E36AA1E3E9083C104E71 8374375B2F4272C7E44E
 



Re: Access 2000 & postgres ODBC-driver: "ODBC error"

From
"Byron Nikolaidis"
Date:
The Msysconf is an optional configuration file -- It is *not* an error if it
does not exist!  The MS Jet engine looks for it; if it's present it takes
some optional configuration parameters from it.  This is mentioned in the
FAQ by the way.

The connect option is most likely the error, but even that may not be the
real cause of the failure.

Turn on tracing in the odbc admin panel and check the odbc trace log after
you run it.  Most likely, the error will be near the end.

Byron

----- Original Message -----
From: Michael Ruder <ruder@gmx.de>
To: <pgsql-interfaces@postgresql.org>
Sent: Wednesday, December 27, 2000 8:28 AM
Subject: [INTERFACES] Access 2000 & postgres ODBC-driver: "ODBC error"


> Hi,
>
> I use Postgres 7.0.3, psqlodbc 6.50.0000 with Access 2000.
>
> Everything works fine but I get an "ODBC error" when I use a Visual Basic
> construct like "MyTable.AddNew" (where MyTable is a Recordsetobject
> referring to the table). This all works fine both my MyODBC and MySQL and
> native Access tables, so it is definitely an issue with psqlodbc. The
> logfile of psqlodbc shows this:
>
> CONN ERROR: func=SQLGetConnectOption, desc='fOption=30002', errnum=205,
errmsg='Unknown connect option (Get)'
>             ------------------------------------------------------------
>             henv=143982724, conn=142936188, status=0, num_stmts=16
>             sock=143982740, stmts=143982780, lobj_type=-999
>             ---------------- Socket Info -------------------------------
>             socket=-1, reverse=0, errornumber=0, errormsg='(NULL)'
>             buffer_in=142942516, buffer_out=142946616
>             buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
> CONN ERROR: func=SQLSetConnectOption, desc='fOption=30002,
vParam=139526100', errnum=205, errmsg='Unknown connect option (Set)'
>             ------------------------------------------------------------
>             henv=143982724, conn=142936188, status=0, num_stmts=16
>             sock=143982740, stmts=143982780, lobj_type=-999
>             ---------------- Socket Info -------------------------------
>             socket=-1, reverse=0, errornumber=0, errormsg='(NULL)'
>             buffer_in=142942516, buffer_out=142946616
>             buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
> conn=142936188,
QLDriverConnect( in)='DSN=Adressdatenbank;UID=ADR_Master;PWD=;DATABASE=Adres
sen;SERVER=database.big-dipper.universe;PORT=5432;READONLY=0;PROTOCOL=6.4;FA
KEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=1;SHOWSYSTEMTABLES=0;CONNSETTINGS
=;', fDriverCompletion=0
> DSN info:
DSN='Adressdatenbank',server='database.big-dipper.universe',port='5432',dbas
e='Adressen',user='ADR_Master',passwd='ADR_Master'
>
onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
>           conn_settings=''
>           translation_dll='',translation_option=''
> Global Options: Version='06.50.0000', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
>                 disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=1
>                 text_as_longvarchar=0, unknowns_as_longvarchar=1,
bools_as_char=0
>                 extra_systable_prefixes='dd_;', conn_settings=''
> conn=142936188, query=' '
> conn=142936188, query='set DateStyle to 'ISO''
> conn=142936188, query='set geqo to 'OFF''
> conn=142936188, query='set ksqo to 'ON''
> conn=142936188, query='BEGIN'
> conn=142936188, query='declare SQL_CUR0885413C cursor for select oid from
pg_type where typname='lo''
> conn=142936188, query='fetch 100 in SQL_CUR0885413C'
>     [ fetched 0 rows ]
> conn=142936188, query='close SQL_CUR0885413C'
> conn=142936188, query='END'
> conn=142936188,
SQLDriverConnect(out)='DSN=Adressdatenbank;DATABASE=Adressen;SERVER=database
.big-dipper.universe;PORT=5432;UID=ADR_Master;PWD=ADR_Master;READONLY=0;PROT
OCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=1;SHOWSYSTEMTABLES=0;C
ONNSETTINGS='
> conn=142936188, query='BEGIN'
> conn=142936188, query='declare SQL_CUR0885413C cursor for SELECT Config,
nValue FROM MSysConf'
> ERROR from backend during send_query: 'ERROR:  Relation 'msysconf' does
not exist'
> conn=142936188, query='ABORT'
> STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while
executing the query'
>                  ---------------------------------------------------------
---
>                  hdbc=142936188, stmt=142950716, 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='declare SQL_CUR0885413C cursor for
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_CUR0885413C'
>                  ----------------QResult
Info -------------------------------
> CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR:  Relation
'msysconf' does not exist'
>             ------------------------------------------------------------
>             henv=143982724, conn=142936188, status=1, num_stmts=16
>             sock=143982740, stmts=143982780, lobj_type=-999
>             ---------------- Socket Info -------------------------------
>             socket=332, reverse=0, errornumber=0, errormsg='(NULL)'
>             buffer_in=142942516, buffer_out=142946616
>             buffer_filled_in=3, buffer_filled_out=0, buffer_read_in=2
> conn=142936188, query='BEGIN'
> conn=142936188, query='declare SQL_CUR0885413C cursor for SELECT
"Personen"."PersonenNummer" FROM "Personen" '
> conn=142936188, query='fetch 100 in SQL_CUR0885413C'
>     [ fetched 2 rows ]
> conn=142936188, query='ROLLBACK'
> CONN ERROR: func=SQLGetConnectOption, desc='fOption=30002', errnum=205,
errmsg='Unknown connect option (Get)'
>             ------------------------------------------------------------
>             henv=143982724, conn=143016456, status=0, num_stmts=16
>             sock=143986564, stmts=143986604, lobj_type=-999
>             ---------------- Socket Info -------------------------------
>             socket=-1, reverse=0, errornumber=0, errormsg='(NULL)'
>             buffer_in=143022784, buffer_out=143026884
>             buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
> CONN ERROR: func=SQLSetConnectOption, desc='fOption=30002,
vParam=138480932', errnum=205, errmsg='Unknown connect option (Set)'
>             ------------------------------------------------------------
>             henv=143982724, conn=143016456, status=0, num_stmts=16
>             sock=143986564, stmts=143986604, lobj_type=-999
>             ---------------- Socket Info -------------------------------
>             socket=-1, reverse=0, errornumber=0, errormsg='(NULL)'
>             buffer_in=143022784, buffer_out=143026884
>             buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
> conn=143016456,
QLDriverConnect( in)='DSN=Adressdatenbank;UID=ADR_Master;PWD=ADR_Master;DATA
BASE=Adressen;SERVER=database.big-dipper.universe;PORT=5432;READONLY=0;PROTO
COL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=1;SHOWSYSTEMTABLES=0;CO
NNSETTINGS=;', fDriverCompletion=0
> DSN info:
DSN='Adressdatenbank',server='database.big-dipper.universe',port='5432',dbas
e='Adressen',user='ADR_Master',passwd='ADR_Master'
>
onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
>           conn_settings=''
>           translation_dll='',translation_option=''
> Global Options: Version='06.50.0000', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
>                 disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=1
>                 text_as_longvarchar=0, unknowns_as_longvarchar=1,
bools_as_char=0
>                 extra_systable_prefixes='dd_;', conn_settings=''
> conn=143016456, query=' '
> conn=143016456, query='set DateStyle to 'ISO''
> conn=143016456, query='set geqo to 'OFF''
> conn=143016456, query='set ksqo to 'ON''
> conn=143016456, query='BEGIN'
> conn=143016456, query='declare SQL_CUR08867AC8 cursor for select oid from
pg_type where typname='lo''
> conn=143016456, query='fetch 100 in SQL_CUR08867AC8'
>     [ fetched 0 rows ]
> conn=143016456, query='close SQL_CUR08867AC8'
> conn=143016456, query='END'
> conn=143016456,
SQLDriverConnect(out)='DSN=Adressdatenbank;DATABASE=Adressen;SERVER=database
.big-dipper.universe;PORT=5432;UID=ADR_Master;PWD=ADR_Master;READONLY=0;PROT
OCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=1;SHOWSYSTEMTABLES=0;C
ONNSETTINGS='
> conn=143016456, query='BEGIN'
> conn=143016456, query='declare SQL_CUR08867AC8 cursor for SELECT
"Adressen"."AdressNummer" FROM "Adressen" '
> conn=143016456, query='fetch 100 in SQL_CUR08867AC8'
>     [ fetched 0 rows ]
> conn=143016456, query='close SQL_CUR08867AC8'
> conn=143016456, query='END'
> conn=143016456, query='BEGIN'
> conn=143016456, query='declare SQL_CUR08867AC8 cursor for SELECT
"Telefonnummern"."TelefonNummer" FROM "Telefonnummern" '
> conn=143016456, query='fetch 100 in SQL_CUR08867AC8'
>     [ fetched 0 rows ]
> conn=143016456, query='close SQL_CUR08867AC8'
> conn=143016456, query='END'
> conn=143016456, query='BEGIN'
> conn=143016456, query='declare SQL_CUR08867AC8 cursor for SELECT
"Emailadressen"."EmailNummer" FROM "Emailadressen" '
> conn=143016456, query='fetch 100 in SQL_CUR08867AC8'
>     [ fetched 0 rows ]
> conn=143016456, query='close SQL_CUR08867AC8'
> conn=143016456, query='END'
> conn=143016456, SQLDisconnect
> conn=142936188, SQLDisconnect
>
> There are several errors: the GetConnectOption-Error which I already saw
> in other messages in this list but never saw an answer to it and then the
> problem with MSysConf which I don't understand at all.
>
> I was looking through mailing lists for several hours (!) already and I am
> really sorry if this is an issue which has been discussed already, but I
> couldn't find anything on this topic and I would be VERY thankful if
> someone can give me a hint!
>
> Thanks,
> --
> .      -Michael
>
>
/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\
>  < Hans Michael Ruder, mailto:ruder@gmx.de,
http://www.ruder.home.pages.de/ >
>
\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/
>       PGP (DH/DSS) fingerprint: E36AA1E3E9083C104E71 8374375B2F4272C7E44E
>