SQLdb with PostgreSQL without transaction
lazarus / 16-Databases

----------------------------------------------------------------------
PierceNg · 2018-12-18 11:22

Hi all,

I am writing a console program to do some PostgreSQL admin stuff. The
program is basically the example program at
https://www.freepascal.org/docs-html/fcl/sqldb/usingsqldb.html, with
appropriate changes. The program executes DROP DATABASE, and here's
the error:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
TPQConnection : Execution of query failed  (PostgreSQL: ERROR:  DROP
DATABASE cannot run inside a transaction block Severity: ERRORSQL
State: 25001Primary Error: DROP DATABASE cannot run inside a
transaction block)Exception at 000000000047FBED:
EPQDatabaseError:TPQConnection : Execution of query failed
 (PostgreSQL: ERROR:  DROP DATABASE cannot run inside a transaction
block 

I tried setting the transaction's active property to false - same
error. I tried not using a transaction, didn't work either:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Exception
at 0000000000492570: EDatabaseError:Transaction not set 
So, is there a way to use SQLdb without transactions, or should I try
the low-level 'postgres' unit instead?

Pierce

----------------------------------------------------------------------
Zvoni · 2018-12-18 11:33

show the code where you call the SQL-Statement

----------------------------------------------------------------------
PierceNg · 2018-12-18 13:37

Here's the program:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i {$mode
objfpc}{$H+} program pg_dropdatabase; uses   Crt, SysUtils, sqldb,
pqconnection; var  db: TSQLConnection;  txn: TSQLTransaction;  query:
TSQLQuery; begin   db := TPQConnection.create(nil);  try   
db.hostName := 'localhost';    db.userName := 'postgres';   
db.databaseName := 'postgres';    txn := TSQLTransaction.create(db); 
 txn.database := db;    db.connected := true;    query :=
TSQLQuery.create(db);    query.database := db;    query.transaction :=
txn;    // testdb exists...    query.SQL.text := 'drop database
testdb;';    try      query.execSQL;    except      on e: Exception do
        writeln(e.message);    end;  finally    db.free;  end;end. 
Here's a transcript. I'm running PostgreSQL in Docker. The e10a... bit
is the Docker container id.

Code: [Select]% sudo docker exec -it e10a31437f22 bash
root@e10a31437f22:/# su - postgres
postgres@e10a31437f22:/$ psql
psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access
privileges  
-----------+----------+----------+------------+------------+----------
-------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
=c/postgres          +
           |          |          |            |            |
postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
=c/postgres          +
           |          |          |            |            |
postgres=CTc/postgres
(3 rows)

postgres=# create database testdb;
CREATE DATABASE
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access
privileges  
-----------+----------+----------+------------+------------+----------
-------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
=c/postgres          +
           |          |          |            |            |
postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
=c/postgres          +
           |          |          |            |            |
postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
(4 rows)

postgres=# \q
postgres@e10a31437f22:/$ ./dropdatabase
TPQConnection : Execution of query failed  (PostgreSQL: ERROR:  DROP
DATABASE cannot run inside a transaction block

Severity: ERROR
SQL State: 25001
Primary Error: DROP DATABASE cannot run inside a transaction block)

----------------------------------------------------------------------
Zvoni · 2018-12-18 15:01

Hmm, the Connection-Object has an ExecuteDirect-Method, and a DropDB-
Method.......
https://www.freepascal.org/docs-html/fcl/sqldb/tsqlconnection.html

----------------------------------------------------------------------
PierceNg · 2018-12-18 15:52

Thanks. I'm trying to automate the database set up of a web
application that uses PostgreSQL, so basically wrapping the following
steps into a program:

- drop database if exists ...
- drop role ...
- create role ...
- create database ...
- some other database stuff

The DropDB method may work. But ExecuteDirect's documentation states
that it will use the "default" transaction. Anyhow, I will try it and
if it fails then try the postgres unit next.

----------------------------------------------------------------------
szaman · 2023-06-28 14:28

Probably the solution is to set in the mentioned code:

txn.Options := [stoUseImplicit];

and call ExecuteDirect.