[solved] How to create Postgres database at runtime?
lazarus / 16-Databases

----------------------------------------------------------------------
fpcoder · 2014-11-09 05:38

I try to create the database:

Code: [Select]Conn.ExecuteDirect('CREATE DATABASE mydb')
An error says: "can't run inside a transaction block".
If I set Conn.Transaction to None I get another error: Transaction not
set.

This works with ZEOS, but I want to use sqlDB:
Code: [Select]DM.ZConn.Connect;
DM.ZConn.ExecuteDirect('CREATE DATABASE mydb');

----------------------------------------------------------------------
marcov · 2014-11-09 13:52

Try to use TSQLScript instead of query.

----------------------------------------------------------------------
goodname · 2014-11-09 19:43

Have not tried it but should work.

TPQconnection.DatabaseName:='DBname';
TPQconnection.CreateDB;

----------------------------------------------------------------------
fpcoder · 2014-11-09 21:49

CreateDB worked. Thanks Goodname.

@Marcov: For some reason TSQLScript didn't create the database. No
error message came up.
Inside the SQLScript.Script I just put:
Code: [Select]CREATE DATABASE db1
Then from code:
Code: [Select]Transaction.StartTransaction;
SQLScript.Execute;
Transaction.Commit;

I also fired up the TSQLScript demo in the Lazarus examples and tried
"CREATE DATABASE db1" but it also didnt work. Maybe CreateDB is the
only way? Because the Transaction?

----------------------------------------------------------------------
goodname · 2014-11-10 14:32

Both CreateDB and DropDB run outside of a transaction. All other
queries are explicitly put inside a transaction. MSEgui for SQL Server
has a tao_fake behaviour that allows queries to run using implicit
transactions (the server starts and stops a transaction for each
query). This behaviour can be ported to PostgreSQL connection code but
don't think anyone is currently working on it.

----------------------------------------------------------------------
BigChimp · 2014-11-10 14:56

@Goodname: please look at the fpc-devel list for info on that.

----------------------------------------------------------------------
fpcoder · 2014-11-10 22:15

I see, then CreateDB is probably the only way to go right now.

Quote from: goodname on November 10, 2014, 03:32:11 pmBoth CreateDB
and DropDB run outside of a transaction. All other queries are
explicitly put inside a transaction.