Обсуждение: Transactionless ODBC

Hello,

Is there a way to execute a SQL query without using a transaction under
ODBC?  I'm trying to create a user and I get the following output:

OpenLink ODBC Demonstration program
This program shows an interactive SQL processor


Enter ODBC connect string (? shows list): DSN=Testing

SQL>create user roger
Error creating the table;
ERROR:  CREATE USER: may not be called in a transaction block, SQLSTATE=S0001
ERROR:  CREATE USER: may not be called in a transaction block, SQLSTATE=S1000

My ~/.odbc.ini is:

[ODBC Data Sources]
Testing = Debugging Database

[Testing]
Driver = /usr/local/lib/libpsqlodbc.so
Debug = 1
CommLog = 1
ReadOnly = 0
Servername = localhost
Username = freedom
Password = "joint"
Port = 5432
Database = testcdbc

[ODBC]
InstallDir = /var/lib/pgsql

Problem code seems to be in interfaces/odbc/statement.c:748.  There's an
if statement that I think I want to be false, but I don't know how.

I don't want to have to call an shell script to create my users, but that
seems like the way to go, now.

Alex Stewart

> SQL>create user roger
> Error creating the table;
> ERROR:  CREATE USER: may not be called in a transaction block, SQLSTATE=S0001
A workaround for this problem is: try inserting rows into pg_shadow
(and also into pg_group if it is important for you).

We had similar problems with transactions on Windows. Please check if "Use
DeclareFetch" option is off (configuring the ODBC driver): if it is on,
most statement implies a transaction BEGIN. (I don't know that you
have the opportunity to configure the ODBC driver on Unix, on Windows you
can.)

Regards, Zoltan

> > SQL>create user roger
> > Error creating the table;
> > ERROR:  CREATE USER: may not be called in a transaction block, SQLSTATE=S0001
> A workaround for this problem is: try inserting rows into pg_shadow
> (and also into pg_group if it is important for you).

Thanks for the workaround.  However, only the postgres super-user can change
pg_shadow, so I would need to connect as him.  I might be able to make do,
but I would like to know if there is a cleaner solution.
> We had similar problems with transactions on Windows. Please check if "Use
> DeclareFetch" option is off (configuring the ODBC driver): if it is on,
> most statement implies a transaction BEGIN. (I don't know that you
> have the opportunity to configure the ODBC driver on Unix, on Windows you
> can.)

I've set globals.use_declarefetch to 0 via /etc/odbcinst.ini with the
following two lines:
[PostgreSQL]
UseDeclareFetch = 0

That seems to set use_declarefetch to the right value, but I'm still in a
transaction because STMT_UPDATE(self) is true (i.e.
stmt->statement_type > STMT_TYPE_SELECT i.e. the statement type is one of:
INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, or REVOKE).  Is this
the intended behaviour of STMT_TYPE_SELECT?  i.e. I thought cursors (the
reason for doing everything in a transaction as stated in the comment) were
only for getting the results back from a SELECT statement.  So why do we
use transactions when we don't need cursors?  Mind you, I've only recently
come in contact with database programming, so the answer could be very
obvious.

The troubling code is (if I have already posted it, I apologize)

/*      Begin a transaction if one is not already in progress */
/*      The reason is because we can't use declare/fetch cursors without       starting a transaction first.
*/
if ( ! self->internal && ! CC_is_in_trans(conn) && (globals.use_declarefetch || STMT_UPDATE(self))) {

Alex Stewart

> -----Original Message-----
> From: Alex Stewart [mailto:astewart@freedomintelligence.com]
> Sent: 06 June 2000 17:55
> To: tip@pc10.radnoti-szeged.sulinet.hu
> Cc: pgsql-interfaces@postgresql.org
> Subject: Re: [INTERFACES] Transactionless ODBC
> 
<SNIP>
>
> The troubling code is (if I have already posted it, I apologize)
> 
> /*      Begin a transaction if one is not already in progress */
> /*      The reason is because we can't use declare/fetch 
> cursors without
>         starting a transaction first.
> */
> if ( ! self->internal && ! CC_is_in_trans(conn) && 
> (globals.use_declarefetch || STMT_UPDATE(self))) {
> 
> Alex Stewart

I raised this question on the Hackers list last month (after getting no
responses here) and got a message back from Thomas Lockhart saying he
planned to take a look. I believe he's on holiday at the moment but
hopefully he can help when he gets back....

Regards,  
Dave.  
-- 
Disclaimer: the above is the author's personal opinion and is not the
opinion or policy of his employer or of the little green men that have been
following him all day.
http://www.vale-housing.co.uk/ - http://www.pgadmin.freeserve.co.uk/