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)  

Tue, 16 May 2000 09:17:21 -0000
Dave Page <dpage@vale-housing.co.uk>
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.)

> 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


Wed, 17 May 2000 19:18:23 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: ODBC & v7.0(Rel) Errors with Users and Databases