SQLdb with PostgreSQL without transaction

[PierceNg]

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][+][-]

1. TPQConnection : Execution of query failed  (PostgreSQL: ERROR:  DROP DATABASE cannot run inside a transaction block
2.  
3. Severity: ERROR
4. SQL State: 25001
5. Primary Error: DROP DATABASE cannot run inside a transaction block)
6. Exception at 000000000047FBED: EPQDatabaseError:
7. TPQConnection : Execution of query failed  (PostgreSQL: ERROR:  DROP DATABASE cannot run inside a transaction block
8.  

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][+][-]

1. Exception at 0000000000492570: EDatabaseError:
2. Transaction not set
3.  

So, is there a way to use SQLdb without transactions, or should I try the low-level 'postgres' unit instead?

Pierce
 

[Zvoni]

show the code where you call the SQL-Statement

[PierceNg]

Here's the program:

Code: Pascal  [Select][+][-]

1. {$mode objfpc}{$H+}
2.  
3. program pg_dropdatabase;
4.  
5. uses
6.   Crt, SysUtils, sqldb, pqconnection;
7.  
8. var
9.   db: TSQLConnection;
10.   txn: TSQLTransaction;
11.   query: TSQLQuery;
12.  
13. begin
14.   db := TPQConnection.create(nil);
15.   try
16.     db.hostName := 'localhost';
17.     db.userName := 'postgres';
18.     db.databaseName := 'postgres';
19.     txn := TSQLTransaction.create(db);
20.     txn.database := db;
21.     db.connected := true;
22.     query := TSQLQuery.create(db);
23.     query.database := db;
24.     query.transaction := txn;
25.     // testdb exists...
26.     query.SQL.text := 'drop database testdb;';
27.     try
28.       query.execSQL;
29.     except
30.       on e: Exception do
31.         writeln(e.message);
32.     end;
33.   finally
34.     db.free;
35.   end;
36. end.
37.  

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]

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

[PierceNg]

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]

Probably the solution is to set in the mentioned code:

txn.Options := [stoUseImplicit];

and call ExecuteDirect.