Thread: ODBC isolationi level SQL_TXN_SERIALIZABLE -> "08001 Already connected."

Hello everyone,

I just ran into following issue with a big surprise that it does not work in
ODBC.

What I am doing is:
- set the isolation level to: SQL_TXN_SERIALIZABLE
- then connect to postgres database via ODBC function SQLDriverConnect()

Example code:

    ret = SQLSetConnectAttr(hDbc, SQL_ATTR_TXN_ISOLATION,
(SQLPOINTER)SQL_TXN_SERIALIZABLE, SQL_IS_INTEGER);

    ret = SQLDriverConnect(hConnect,
                                  0,
                                 (SQLCHAR*)connString,
                                  ustrlen((unsigned char *)connString),
                                  NULL,
                                  0,
                                  NULL,
                                  SQL_DRIVER_NOPROMPT);

Then SQLDriverConnect() returns -1 with error code: "08001 Already
connected."

With other isolation level like SQL_TXN_READ_COMMITTED, it works fine.

Any idea to solve this problem? Or is this a Postgres ODBC bug? I couldn't
find anything here or on Google.







--
View this message in context:
http://postgresql.nabble.com/ODBC-isolationi-level-SQL-TXN-SERIALIZABLE-08001-Already-connected-tp5923778.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

Hello,

Looking at the code, That seems to be a psqlODBC's bug.  psqlODBC tries to execute "SET SESSION ... ISOLATION LEVEL
..."upon the call to SQLSetSetConnectAttr(), which fails because the connection is not established yet.  The driver
shouldrun the SET query after establishing the connection.  FYI, even though SQLSetConnectAttr() for other isolation
levelsreturns SQL_SUCCESS, it doesn't set the isolation level correctly. 

The workaround is to call SQLSetConnectAttr() after SQLDriverConnect().  Anyway, I'll consider fixing the bug.

Regards
Takayuki Tsunakawa


> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Hong06
> Sent: Friday, September 30, 2016 10:41 PM
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] ODBC isolationi level SQL_TXN_SERIALIZABLE -> "08001
> Already connected."
>
> Hello everyone,
>
> I just ran into following issue with a big surprise that it does not work
> in ODBC.
>
> What I am doing is:
> - set the isolation level to: SQL_TXN_SERIALIZABLE
> - then connect to postgres database via ODBC function SQLDriverConnect()
>
> Example code:
>
>     ret = SQLSetConnectAttr(hDbc, SQL_ATTR_TXN_ISOLATION,
> (SQLPOINTER)SQL_TXN_SERIALIZABLE, SQL_IS_INTEGER);
>
>     ret = SQLDriverConnect(hConnect,
>                                   0,
>                                  (SQLCHAR*)connString,
>                                   ustrlen((unsigned char *)connString),
>                                   NULL,
>                                   0,
>                                   NULL,
>                                   SQL_DRIVER_NOPROMPT);
>
> Then SQLDriverConnect() returns -1 with error code: "08001 Already
> connected."
>
> With other isolation level like SQL_TXN_READ_COMMITTED, it works fine.
>
> Any idea to solve this problem? Or is this a Postgres ODBC bug? I couldn't
> find anything here or on Google.

Thanks so much Takayuki.

Yes the isolation level should be set after the connection is established,
then it works.

Actually I had before another similar issue that was about
SQL_AUTOCOMMIT_OFF, which can only be set after the connection is
established.

Millions thanks.

With best regards,

Hong



--
View this message in context:
http://postgresql.nabble.com/ODBC-isolationi-level-SQL-TXN-SERIALIZABLE-08001-Already-connected-tp5923778p5924112.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.

Hello, Inoue-san
cc: Hong

I confirmed the attached patch fixes this bug.  Could you review and commit it?

I corrected relevant SQLSTATE values, referring to the following page for ODBC v2.

SQLSTATE Mappings
https://msdn.microsoft.com/en-us/library/ms712451(v=vs.85).aspx

Regards
Takayuki Tsunakawa