Thread: ODBC - MSysConf

ODBC - MSysConf

From
Karsten Kaus
Date:
ok, in general it seems to be possible to connect Access97 ( not Access
2.0! ?right?  ) to postgres.
It works on ONE of my Computers in the office (486). On that computer
I cannot start the 32Bit-ODBC in the systemconfiguration, but I can
create DSNs within Access97, and I get connected to any postgres-
database I wish (tried only 2).
On my Pentium in the office and on ALL machines at home I get the
error 4307, that there is a Table called MSysConf on my server but
it does not have the expected format.
I searched for this table using grep and pg_dumpall as well as psql \dS
and could not find anything. So I created one as described in the
MSAccess-Help, but this didn't help. Still the fomat was invalid.
I do not want to use this table anyway ( do I? ), so what to do?

Thanks for help,
Karsten Kaus

Re: [INTERFACES] ODBC - MSysConf

From
David Hartwig
Date:

Karsten Kaus wrote:

> ok, in general it seems to be possible to connect Access97 ( not Access
> 2.0! ?right?  ) to postgres.

I recall someone doing this successfully.  The key here is that it needs to
be running on Win95, Win98, or NT.

>
> It works on ONE of my Computers in the office (486). On that computer
> I cannot start the 32Bit-ODBC in the systemconfiguration, but I can
> create DSNs within Access97, and I get connected to any postgres-
> database I wish (tried only 2).
> On my Pentium in the office and on ALL machines at home I get the
> error 4307, that there is a Table called MSysConf on my server but
> it does not have the expected format.

You do not need this table on the server.  The driver will report it as an
error in the log file, but it should not be a problem to the application.

Activate the CommLog in the driver settings and send a copy of the errant
session.  Its located in C:\psqlodbc*.log.

>
> I searched for this table using grep and pg_dumpall as well as psql \dS
> and could not find anything. So I created one as described in the
> MSAccess-Help, but this didn't help. Still the fomat was invalid.
> I do not want to use this table anyway ( do I? ), so what to do?
>

I would drop the table unless you know what you are doing with MSysConf. At
least until you get on solid footing.   It's improper configuration may
cause other problems.



Re: [INTERFACES] ODBC - MSysConf

From
Karsten Kaus
Date:
David Hartwig wrote:
>
> Karsten Kaus wrote:
>
> > ok, in general it seems to be possible to connect Access97 ( not Access
> > 2.0! ?right?  ) to postgres.
>
> I recall someone doing this successfully.  The key here is that it needs to
> be running on Win95, Win98, or NT.
>
as far as I understood, the PostgreSQL driver is a 32-Bit program, so it
does
not show up when I'm using Access 2.0 to link tables, pity, but I can
live with that,
even it will cost money again.
> >
> > It works on ONE of my Computers in the office (486). On that computer
> > I cannot start the 32Bit-ODBC in the systemconfiguration, but I can
...
> > error 4307, that there is a Table called MSysConf on my server but
> > it does not have the expected format.
>
> You do not need this table on the server.  The driver will report it as an
> error in the log file, but it should not be a problem to the application.
>
I read that before, but the case is slightly different. The table does
not exist.
I created the table at home, not here in the office. I just veryfied
dropping the table and got the message it woldn't exist.
Now access should notice that the table is missing, but it states the
table would
be there having a wrong format.

> Activate the CommLog in the driver settings and send a copy of the errant
> session.  Its located in C:\psqlodbc*.log.
I did that in the driversetup. but it didn't log to C:\psqlodbc*.log
(I've seen and read them before, but now they are not created any more).
The log is in c:\sql.log. This file is also mentioned in the driver
setup.
So I think it's ok. The info contained looks a little different than in
the
psql*-files, but as fas as I see - the same info is contained:
--------------Here comes c:\sql.log ---------------------

MSACCESS        fff27265:fff26281    ENTER SQLAllocConnect
        HENV                0x00b30204
        HDBC *              0x0062c910

MSACCESS        fff27265:fff26281    EXIT  SQLAllocConnect  with return
code 0 (SQL_SUCCESS)
        HENV                0x00b30204
        HDBC *              0x0062c910 ( 0x00b31668)

MSACCESS        fff27265:fff26281    ENTER SQLSetConnectOption
        HDBC                0x00b31668
        UWORD                      103
        UDWORD                    20

MSACCESS        fff27265:fff26281    EXIT  SQLSetConnectOption  with return
code 0 (SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                      103
        UDWORD                    20

MSACCESS        fff27265:fff26281    ENTER SQLDriverConnectW
        HDBC                0x00b31668
        HWND                0x000002b8
        WCHAR *             0x00b30560 [      -3] ""
        SWORD                       -3
        WCHAR *             0x00b31734
        SWORD                      510
        SWORD *             0x0062c966
        UWORD                        1 <SQL_DRIVER_COMPLETE>

MSACCESS        fff27265:fff26281    EXIT  SQLDriverConnectW  with return
code 0 (SQL_SUCCESS)
        HDBC                0x00b31668
        HWND                0x000002b8
        WCHAR *             0x00b30560 [      -3] ""
        SWORD                       -3
        WCHAR *             0x00b31734 [     362]

"DSN=PostgreSQL;DATABASE=faxlog;SERVER=192.168.0.3;PORT=5432;UID=kk;PWD=<censored>;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS="
        SWORD                      510
        SWORD *             0x0062c966 (362)
        UWORD                        1 <SQL_DRIVER_COMPLETE>

MSACCESS        fff27265:fff26281    ENTER SQLGetInfo
        HDBC                0x00b31668
        UWORD                        9 <SQL_ODBC_API_CONFORMANCE>
        PTR                      51478
        SWORD                        2
        SWORD *             0x0062c90e

MSACCESS        fff27265:fff26281    ENTER SQLGetInfoW
        HDBC                0x00b31668
        UWORD                        9 <SQL_ODBC_API_CONFORMANCE>
        PTR                      51478
        SWORD                        2
        SWORD *             0x0062c90e

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfoW  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                        9 <SQL_ODBC_API_CONFORMANCE>
        PTR                      51478
        SWORD                        2
        SWORD *             0x0062c90e (2)

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfo  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                        9 <SQL_ODBC_API_CONFORMANCE>
        PTR                      51478
        SWORD                        2
        SWORD *             0x0062c90e (2)

MSACCESS        fff27265:fff26281    ENTER SQLGetInfo
        HDBC                0x00b31668
        UWORD                        6 <SQL_DRIVER_NAME>
        PTR                 0x0062c8ac
        SWORD                      100
        SWORD *             0x0062c916

MSACCESS        fff27265:fff26281    ENTER SQLGetInfoW
        HDBC                0x00b31668
        UWORD                        6 <SQL_DRIVER_NAME>
        PTR                 0x00b31838
        SWORD                      200
        SWORD *             0x0062c916

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfoW  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                        6 <SQL_DRIVER_NAME>
        PTR                 0x00b31838 [      24] "PSQLODBC.DLL"
        SWORD                      200
        SWORD *             0x0062c916 (24)

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfo  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                        6 <SQL_DRIVER_NAME>
        PTR                 0x0062c8ac [      12] "PSQLODBC.DLL"
        SWORD                      100
        SWORD *             0x0062c916 (12)

MSACCESS        fff27265:fff26281    ENTER SQLGetInfo
        HDBC                0x00b31668
        UWORD                       46 <SQL_TXN_CAPABLE>
        PTR                      51328
        SWORD                        2
        SWORD *             0x0062c742

MSACCESS        fff27265:fff26281    ENTER SQLGetInfoW
        HDBC                0x00b31668
        UWORD                       46 <SQL_TXN_CAPABLE>
        PTR                      51328
        SWORD                        2
        SWORD *             0x0062c742

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfoW  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                       46 <SQL_TXN_CAPABLE>
        PTR                      51328
        SWORD                        2
        SWORD *             0x0062c742 (2)

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfo  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                       46 <SQL_TXN_CAPABLE>
        PTR                      51328
        SWORD                        2
        SWORD *             0x0062c742 (2)

MSACCESS        fff27265:fff26281    ENTER SQLGetInfo
        HDBC                0x00b31668
        UWORD                       23 <SQL_CURSOR_COMMIT_BEHAVIOR>
        PTR                      27128
        SWORD                        2
        SWORD *             0x0062c742

MSACCESS        fff27265:fff26281    ENTER SQLGetInfoW
        HDBC                0x00b31668
        UWORD                       23 <SQL_CURSOR_COMMIT_BEHAVIOR>
        PTR                      27128
        SWORD                        2
        SWORD *             0x0062c742

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfoW  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                       23 <SQL_CURSOR_COMMIT_BEHAVIOR>
        PTR                      27128
        SWORD                        2
        SWORD *             0x0062c742 (2)

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfo  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                       23 <SQL_CURSOR_COMMIT_BEHAVIOR>
        PTR                      27128
        SWORD                        2
        SWORD *             0x0062c742 (2)

MSACCESS        fff27265:fff26281    ENTER SQLGetInfo
        HDBC                0x00b31668
        UWORD                       24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
        PTR                      27130
        SWORD                        2
        SWORD *             0x0062c742

MSACCESS        fff27265:fff26281    ENTER SQLGetInfoW
        HDBC                0x00b31668
        UWORD                       24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
        PTR                      27130
        SWORD                        2
        SWORD *             0x0062c742

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfoW  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                       24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
        PTR                      27130
        SWORD                        2
        SWORD *             0x0062c742 (2)

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfo  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                       24 <SQL_CURSOR_ROLLBACK_BEHAVIOR>
        PTR                      27130
        SWORD                        2
        SWORD *             0x0062c742 (2)

MSACCESS        fff27265:fff26281    ENTER SQLGetInfo
        HDBC                0x00b31668
        UWORD                        1 <SQL_ACTIVE_STATEMENTS>
        PTR                      51332
        SWORD                        2
        SWORD *             0x0062c87e

MSACCESS        fff27265:fff26281    ENTER SQLGetInfoW
        HDBC                0x00b31668
        UWORD                        1 <SQL_ACTIVE_STATEMENTS>
        PTR                      51332
        SWORD                        2
        SWORD *             0x0062c87e

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfoW  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                        1 <SQL_ACTIVE_STATEMENTS>
        PTR                      51332
        SWORD                        2
        SWORD *             0x0062c87e (2)

MSACCESS        fff27265:fff26281    EXIT  SQLGetInfo  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                        1 <SQL_ACTIVE_STATEMENTS>
        PTR                      51332
        SWORD                        2
        SWORD *             0x0062c87e (2)

MSACCESS        fff27265:fff26281    ENTER SQLSetConnectOption
        HDBC                0x00b31668
        UWORD                      101
        UDWORD                     0

MSACCESS        fff27265:fff26281    EXIT  SQLSetConnectOption  with return
code 0 (SQL_SUCCESS)
        HDBC                0x00b31668
        UWORD                      101
        UDWORD                     0

MSACCESS        fff27265:fff26281    ENTER SQLAllocStmt
        HDBC                0x00b31668
        HSTMT *             0x0062c744

MSACCESS        fff27265:fff26281    EXIT  SQLAllocStmt  with return code 0
(SQL_SUCCESS)
        HDBC                0x00b31668
        HSTMT *             0x0062c744 ( 0x00b31870)

MSACCESS        fff27265:fff26281    ENTER SQLGetStmtOption
        HSTMT               0x00b31870
        UWORD                        0
        PTR                0x0062c6e8

MSACCESS        fff27265:fff26281    EXIT  SQLGetStmtOption  with return
code 0 (SQL_SUCCESS)
        HSTMT               0x00b31870
        UWORD                        0
        PTR                0x0062c6e8

MSACCESS        fff27265:fff26281    ENTER SQLSetStmtOption
        HSTMT               0x00b31870
        UWORD                        0
        UDWORD                    60

MSACCESS        fff27265:fff26281    EXIT  SQLSetStmtOption  with return
code 0 (SQL_SUCCESS)
        HSTMT               0x00b31870
        UWORD                        0
        UDWORD                    60

MSACCESS        fff27265:fff26281    ENTER SQLExecDirect
        HSTMT               0x00b31870
        UCHAR *             0x040ce9a0 [      -3] "SELECT Config, nValue FROM
MSysConf"
        SDWORD                    -3

MSACCESS        fff27265:fff26281    EXIT  SQLExecDirect  with return code
0 (SQL_SUCCESS)
        HSTMT               0x00b31870
        UCHAR *             0x040ce9a0 [      -3] "SELECT Config, nValue FROM
MSysConf"
        SDWORD                    -3

MSACCESS        fff27265:fff26281    ENTER SQLFetch
        HSTMT               0x00b31870

MSACCESS        fff27265:fff26281    EXIT  SQLFetch  with return code -1
(SQL_ERROR)
        HSTMT               0x00b31870

MSACCESS        fff27265:fff26281    ENTER SQLError
        HENV                0x00b30204
        HDBC                0x00b31668
        HSTMT               0x00b31870
        UCHAR *             0x0062c6ac (NYI)
         SDWORD *            0x0062c6c8
        UCHAR *             0x0089a9cc
        SWORD                     8192
        SWORD *             0x0062c6de

MSACCESS        fff27265:fff26281    ENTER SQLErrorW
        HENV                0x00b30204
        HDBC                0x00b31668
        HSTMT               0x00b31870
        WCHAR *             0x0062c25c (NYI)
         SDWORD *            0x0062c6c8
        WCHAR *             0x0062c268
        SWORD                     1024
        SWORD *             0x0062c6de

MSACCESS        fff27265:fff26281    EXIT  SQLErrorW  with return code 0
(SQL_SUCCESS)
        HENV                0x00b30204
        HDBC                0x00b31668
        HSTMT               0x00b31870
        WCHAR *             0x0062c25c (NYI)
         SDWORD *            0x0062c6c8 (3)
        WCHAR *             0x0062c268 [      74] "Bindings were not allocated
properly."
        SWORD                     1024
        SWORD *             0x0062c6de (74)

MSACCESS        fff27265:fff26281    EXIT  SQLError  with return code 0
(SQL_SUCCESS)
        HENV                0x00b30204
        HDBC                0x00b31668
        HSTMT               0x00b31870
        UCHAR *             0x0062c6ac (NYI)
         SDWORD *            0x0062c6c8 (3)
        UCHAR *             0x0089a9cc [      37] "Bindings were not allocated
properly."
        SWORD                     8192
        SWORD *             0x0062c6de (37)

MSACCESS        fff27265:fff26281    ENTER SQLError
        HENV                0x00b30204
        HDBC                0x00b31668
        HSTMT               0x00b31870
        UCHAR *             0x0062c6ac (NYI)
         SDWORD *            0x0062c6c8
        UCHAR *             0x0089a9fa
        SWORD                     8146
        SWORD *             0x0062c6de

MSACCESS        fff27265:fff26281    ENTER SQLErrorW
        HENV                0x00b30204
        HDBC                0x00b31668
        HSTMT               0x00b31870
        WCHAR *             0x0062c25c (NYI)
         SDWORD *            0x0062c6c8
        WCHAR *             0x0062c268
        SWORD                     1024
        SWORD *             0x0062c6de

MSACCESS        fff27265:fff26281    EXIT  SQLErrorW  with return code 100
(SQL_NO_DATA_FOUND)
        HENV                0x00b30204
        HDBC                0x00b31668
        HSTMT               0x00b31870
        WCHAR *             0x0062c25c (NYI)
         SDWORD *            0x0062c6c8
        WCHAR *             0x0062c268
        SWORD                     1024
        SWORD *             0x0062c6de

MSACCESS        fff27265:fff26281    EXIT  SQLError  with return code 100
(SQL_NO_DATA_FOUND)
        HENV                0x00b30204
        HDBC                0x00b31668
        HSTMT               0x00b31870
        UCHAR *             0x0062c6ac (NYI)
         SDWORD *            0x0062c6c8
        UCHAR *             0x0089a9fa
        SWORD                     8146
        SWORD *             0x0062c6de

MSACCESS        fff27265:fff26281    ENTER SQLFreeStmt
        HSTMT               0x00b31870
        UWORD                        0 <SQL_CLOSE>

MSACCESS        fff27265:fff26281    EXIT  SQLFreeStmt  with return code 0
(SQL_SUCCESS)
        HSTMT               0x00b31870
        UWORD                        0 <SQL_CLOSE>

MSACCESS        fff27265:fff26281    ENTER SQLFreeStmt
        HSTMT               0x00b31870
        UWORD                        1 <SQL_DROP>

MSACCESS        fff27265:fff26281    EXIT  SQLFreeStmt  with return code 0
(SQL_SUCCESS)
        HSTMT               0x00b31870
        UWORD                        1 <SQL_DROP>

MSACCESS        fff27265:fff26281    ENTER SQLDisconnect
        HDBC                0x00b31668

MSACCESS        fff27265:fff26281    EXIT  SQLDisconnect  with return code
0 (SQL_SUCCESS)
        HDBC                0x00b31668

MSACCESS        fff27265:fff26281    ENTER SQLFreeConnect
        HDBC                0x00b31668

MSACCESS        fff27265:fff26281    EXIT  SQLFreeConnect  with return code
0 (SQL_SUCCESS)
        HDBC                0x00b31668

MSACCESS        fff27265:fff26281    ENTER SQLFreeEnv
        HENV                0x00b30204

MSACCESS        fff27265:fff26281    EXIT  SQLFreeEnv  with return code 0
(SQL_SUCCESS)
        HENV                0x00b30204
--------------------eof-----------------------------------

I verified again using pg_dumpall, that there is no table MSysConf.
And - even better: postmasters log says in that moment:
ERROR: msysconf: Table does not exist.
So I think I can be shure of that.

has anyone seen that before?

KK

Re: [INTERFACES] ODBC - MSysConf

From
David Hartwig
Date:
What version of the PostgreSQL driver are you running?

What version of Access?

What version of the Driver Manager?   The driver install will upgrade to the DM to 3.0 but it is optional.

I probably should have asked this before we got this far.

Karsten Kaus wrote:

>
> MSACCESS        fff27265:fff26281       ENTER SQLExecDirect
>                 HSTMT               0x00b31870
>                 UCHAR *             0x040ce9a0 [      -3] "SELECT Config, nValue FROM
> MSysConf"
>                 SDWORD                    -3
>

Everything above this point looks ok.

>
> MSACCESS        fff27265:fff26281       EXIT  SQLExecDirect  with return code
> 0 (SQL_SUCCESS)
>                 HSTMT               0x00b31870
>                 UCHAR *             0x040ce9a0 [      -3] "SELECT Config, nValue FROM
> MSysConf"
>                 SDWORD                    -3
>

Here is where it gets strange.   The above statement should have a return code of -1 if the table does not exist.   And
theerror message should say so.   (I just this verified this on my 
system.)   The next few statements tries to cope with the result set and it is not happy with what it is getting.


>
> MSACCESS        fff27265:fff26281       ENTER SQLFetch
>                 HSTMT               0x00b31870
>
> MSACCESS        fff27265:fff26281       EXIT  SQLFetch  with return code -1
> (SQL_ERROR)
>                 HSTMT               0x00b31870
>
> MSACCESS        fff27265:fff26281       ENTER SQLError
>                 HENV                0x00b30204
>                 HDBC                0x00b31668
>                 HSTMT               0x00b31870
>                 UCHAR *             0x0062c6ac (NYI)
>                 SDWORD *            0x0062c6c8
>                 UCHAR *             0x0089a9cc
>                 SWORD                     8192
>                 SWORD *             0x0062c6de
>
> MSACCESS        fff27265:fff26281       ENTER SQLErrorW
>                 HENV                0x00b30204
>                 HDBC                0x00b31668
>                 HSTMT               0x00b31870
>                 WCHAR *             0x0062c25c (NYI)
>                 SDWORD *            0x0062c6c8
>                 WCHAR *             0x0062c268
>                 SWORD                     1024
>                 SWORD *             0x0062c6de
>
> MSACCESS        fff27265:fff26281       EXIT  SQLErrorW  with return code 0
> (SQL_SUCCESS)
>                 HENV                0x00b30204
>                 HDBC                0x00b31668
>                 HSTMT               0x00b31870
>                 WCHAR *             0x0062c25c (NYI)
>                 SDWORD *            0x0062c6c8 (3)
>                 WCHAR *             0x0062c268 [      74] "Bindings were not allocated
> properly."
>                 SWORD                     1024
>                 SWORD *             0x0062c6de (74)
>

...

>
> I verified again using pg_dumpall, that there is no table MSysConf.
> And - even better: postmasters log says in that moment:
> ERROR: msysconf: Table does not exist.
> So I think I can be shure of that.
>
> has anyone seen that before?

Not I.


Re: [INTERFACES] ODBC - MSysConf

From
Karsten Kaus
Date:
David Hartwig wrote:

I still do not know, whats going on, but I get it running somehow:
I must not need the controlpanel to set up the DSN. If I stay within
Access when
linking the tables and configure the DSN from the panel Access opens up,
then
everything works (as far as I checked). I even get write-access - wow.
Access seems to make a copy of the driver-settings. I set up a DSN with
read-only access (wich shows up in the contolpanel after linking the
tables) and
try to change to read/write in the controlpanel - it does not work.
I have to link all the 70 tables new with a freshly created DSN, within
Access.
Hope I get this distributed to all the stations without too much
handwork.
For this first try, I'm only creating a faxlog for 6 of our users. But
in 2 weeks it's
going to be harder.

( By the way: Most of the records I will use are bigger than 2k. Can
someone give me a
keyword how to handle that?)

>
> What version of the PostgreSQL driver are you running?
I thought it was the latest: PostgreSQL 6.40.0004
>
> What version of Access?
Access 97 - Access 2.0 does not recognize the PostgreSQL Driver.
>
> What version of the Driver Manager?   The driver install will upgrade to the DM to 3.0 but it is optional.
I should be already above that.
>
> I probably should have asked this before we got this far.
>
> Karsten Kaus wrote:
>
...
>
> >
> > MSACCESS        fff27265:fff26281       EXIT  SQLExecDirect  with return code
> > 0 (SQL_SUCCESS)
> >                 HSTMT               0x00b31870
> >                 UCHAR *             0x040ce9a0 [      -3] "SELECT Config, nValue FROM
> > MSysConf"
> >                 SDWORD                    -3
> >
>
> Here is where it gets strange.   The above statement should have a return code of -1 if the table does not exist.
Andthe error message should say so.   (I just this verified this on my 
> system.)   The next few statements tries to cope with the result set and it is not happy with what it is getting.

Thanks a lot so far, but still I'm not too happy with that.

KK