ODBC & v7.0(Rel) Errors with Users and Databases
Hi,
I originally posted this problem to the interfaces list but have not had any
responses. I would like to resolve this as pgAdmin cannot manage users or
databases whilst this problem exists:
I have a problem with the use of CREATE/ALTER/DROP USER/DATABASE via ODBC
which was not there in v6.x.x. Any code that executes any of the SQL listed
results in an error along the lines of:
ERROR: DROP DATABASE: May not be called in a transaction block
The ODBC log (and knowledge that it isn't pgAdmin or M$ ADO) shows that the
ODBC driver is automatically wrapping the query in a transaction.
conn=47987408, query='BEGIN'
conn=47987408, query='DROP DATABASE "matt"'
ERROR from backend during send_query: 'ERROR: DROP DATABASE: May not be
called in a transaction block'
conn=47987408, query='COMMIT'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while
executing the query'
------------------------------------------------------------
hdbc=47987408, stmt=49221232, result=0
manual_result=0, prepare=0, internal=0
bindings=0, bindings_allocated=0
parameters=0, parameters_allocated=0
statement_type=6, statement='DROP DATABASE "matt"'
stmt_with_params='DROP DATABASE "matt"'
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_CUR02EF0E70'
----------------QResult Info
-------------------------------
CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: DROP
DATABASE: May not be called in a transaction block'
------------------------------------------------------------
henv=47987392, conn=47987408, status=1, num_stmts=16
sock=47980304, stmts=47980352, lobj_type=27904
---------------- Socket Info -------------------------------
socket=488, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=47993744, buffer_out=47997848
buffer_filled_in=3, buffer_filled_out=0, buffer_read_in=2
conn=47987408, SQLDisconnect
Any thoughts/suggestions would be welcomed!!
Regards,
Dave.
--
"If you stand still, sooner or later something will eat you."
- James Burke
http://www.vale-housing.co.uk/ (Work)
http://www.pgadmin.freeserve.co.uk/ (Home of pgAdmin)
Dave Page writes:
ERROR: DROP DATABASE: May not be called in a transaction block
This command can't be rolled back so you aren't allowed to try. This was
thought as an improvement. In general, a database isn't a database object
so one shouldn't be transacting around with them. (Same goes for users.)
The ODBC log (and knowledge that it isn't pgAdmin or M$ ADO) shows that the
ODBC driver is automatically wrapping the query in a transaction.
I don't know anything about ODBC but it certainly should provide a means
to execute a command without that wrapping block. Is this a special
function or do you just execute some exec("DROP DATABASE") style call?
--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden
-----Original Message-----
From: Peter Eisentraut [mailto:peter_e@gmx.net]
Sent: 17 May 2000 17:18
To: Dave Page
Cc: 'pgsql-hackers@postgresql.org'
Subject: Re: [HACKERS] ODBC & v7.0(Rel) Errors with Users and
DatabasesDave Page writes:
ERROR: DROP DATABASE: May not be called in a transaction block
This command can't be rolled back so you aren't allowed to
try. This was
thought as an improvement. In general, a database isn't a
database object
so one shouldn't be transacting around with them. (Same goes
for users.)
This makes perfect sense of course.
The ODBC log (and knowledge that it isn't pgAdmin or M$
ADO) shows that the
ODBC driver is automatically wrapping the query in a transaction.
I don't know anything about ODBC but it certainly should
provide a means
to execute a command without that wrapping block. Is this a special
function or do you just execute some exec("DROP DATABASE") style call?
Yes, I just issue the DROP DATABASE sql exactly as I would issue and INSERT,
DELETE or UPDATE query. From my fumblings around in the source for the ODBC
driver I have found what I believe to be the offending code in statement.c
at line 748 in the version shipped with 7.0, however I know nothing about
how the driver works and my C is far from good so (having very little spare
time also) I'm reluctant to try to fix it myself:
/* 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))) {
mylog(" about to begin a transaction on statement = %u\n",
self);
res = CC_send_query(conn, "BEGIN", NULL);
Again, any assistance would be greatfully received!
Regards,
Dave.
--
"If you stand still, sooner or later something will eat you."
- James Burke
http://www.vale-housing.co.uk/ (Work)
http://www.pgadmin.freeserve.co.uk/ (Home of pgAdmin)
Import Notes
Resolved by subject fallback
Again, any assistance would be greatfully received!
I'm extremely busy this week, but if the problem still exists next
week I'll plan on taking a look at it. Sorry that the time just isn't
there at the moment :(
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California