Thread: 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='DROPDATABASE "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 
> Databases
> 
> 
> 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.)

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)  

> 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