pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedTransactionless ODBC
4+ messages / 3 participants
[nested] [flat]
* Transactionless ODBC
@ 2000-06-05 18:44 Alex Stewart <astewart@freedomintelligence.com>
0 siblings, 1 reply; 4+ messages in thread
From: Alex Stewart @ 2000-06-05 18:44 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Transactionless ODBC
@ 2000-06-06 13:43 Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
parent: Alex Stewart <astewart@freedomintelligence.com>
0 siblings, 1 reply; 4+ messages in thread
From: Kovacs Zoltan Sandor @ 2000-06-06 13:43 UTC (permalink / raw)
To: Alex Stewart <astewart@freedomintelligence.com>; +Cc: pgsql-interfaces
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Transactionless ODBC
@ 2000-06-06 17:55 Alex Stewart <astewart@freedomintelligence.com>
parent: Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
0 siblings, 0 replies; 4+ messages in thread
From: Alex Stewart @ 2000-06-06 17:55 UTC (permalink / raw)
To: tip@pc10.radnoti-szeged.sulinet.hu; +Cc: pgsql-interfaces
> > 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Transactionless ODBC
@ 2000-06-07 08:27 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Dave Page @ 2000-06-07 08:27 UTC (permalink / raw)
To: 'Alex Stewart' <astewart@freedomintelligence.com>; +Cc: pgsql-interfaces
> -----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/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2000-06-07 08:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-05 18:44 Transactionless ODBC Alex Stewart <astewart@freedomintelligence.com>
2000-06-06 13:43 ` Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
2000-06-06 17:55 ` Alex Stewart <astewart@freedomintelligence.com>
2000-06-07 08:27 RE: Transactionless ODBC Dave Page <dpage@vale-housing.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox