Recent

Author Topic: [solved] How to create Postgres database at runtime?  (Read 2771 times)

kapibara

  • Hero Member
  • *****
  • Posts: 511
[solved] How to create Postgres database at runtime?
« on: November 09, 2014, 06:38:42 am »
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');
« Last Edit: November 10, 2014, 11:15:38 pm by kapibara »
Lazarus trunk / fpc 3.0.4 / Debian Stretch 64-bit

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 7350
Re: How to create Postgres database at runtime?
« Reply #1 on: November 09, 2014, 02:52:55 pm »
Try to use TSQLScript instead of query.

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: How to create Postgres database at runtime?
« Reply #2 on: November 09, 2014, 08:43:57 pm »
Have not tried it but should work.

TPQconnection.DatabaseName:='DBname';
TPQconnection.CreateDB;
« Last Edit: November 09, 2014, 08:45:54 pm by goodname »

kapibara

  • Hero Member
  • *****
  • Posts: 511
Re: How to create Postgres database at runtime?
« Reply #3 on: November 09, 2014, 10:49:45 pm »
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?
Lazarus trunk / fpc 3.0.4 / Debian Stretch 64-bit

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: How to create Postgres database at runtime?
« Reply #4 on: November 10, 2014, 03:32:11 pm »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: How to create Postgres database at runtime?
« Reply #5 on: November 10, 2014, 03:56:43 pm »
@Goodname: please look at the fpc-devel list for info on that.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

kapibara

  • Hero Member
  • *****
  • Posts: 511
Re: How to create Postgres database at runtime?
« Reply #6 on: November 10, 2014, 11:15:06 pm »
I see, then CreateDB is probably the only way to go right now.

Both CreateDB and DropDB run outside of a transaction. All other queries are explicitly put inside a transaction.
Lazarus trunk / fpc 3.0.4 / Debian Stretch 64-bit