Recent

Author Topic: SQLdb with PostgreSQL without transaction  (Read 2492 times)

PierceNg

  • New Member
  • *
  • Posts: 37
SQLdb with PostgreSQL without transaction
« on: December 18, 2018, 12:22:15 pm »
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

  • Sr. Member
  • ****
  • Posts: 423
Re: SQLdb with PostgreSQL without transaction
« Reply #1 on: December 18, 2018, 12:33:35 pm »
show the code where you call the SQL-Statement
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

PierceNg

  • New Member
  • *
  • Posts: 37
Re: SQLdb with PostgreSQL without transaction
« Reply #2 on: December 18, 2018, 02:37:35 pm »
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

  • Sr. Member
  • ****
  • Posts: 423
Re: SQLdb with PostgreSQL without transaction
« Reply #3 on: December 18, 2018, 04:01:22 pm »
Hmm, the Connection-Object has an ExecuteDirect-Method, and a DropDB-Method.......
https://www.freepascal.org/docs-html/fcl/sqldb/tsqlconnection.html
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

PierceNg

  • New Member
  • *
  • Posts: 37
Re: SQLdb with PostgreSQL without transaction
« Reply #4 on: December 18, 2018, 04:52:01 pm »
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.


 

TinyPortal © 2005-2018