How to create a database in postgre witihin lazarus?

[El Muerte]

Hi all,

My problem is that i wanna to create a new database in postgre within a program built in lazarus.

I tried the SQL command CREATE DATABASE test and it didnt work.

I using SQLdb, and i tried to run the sqlk command so:

Database1.SQLQuery1.Close;
Database1.SQLQuery1.Clear;
Database1.SQLQuery1.Add('CREATE DATABASE test;');
Database1.SQLQuery1.ExecSQL;
Database1.PQTransaction1.CommitRetainning;

When I run this command, que compiled program says that a database cant be created in a transaction.

What do i have to do?

[johnf]

I think you can't use sqlquery to create tables, databases, etc...  Only select,Update, and delete are available in the sqlquery.

I think what you want is to use the connection.  See bcreatetable.pp in the fpc source db examples.

John

[Anonymous]

Ok, didnt work...

I've tryed so:

 try
   sql:= 'CREATE DATABASE teste;';
   PQConnection1.ExecuteDirect(sql);
   PQConnection1.ExecuteDirect('Commit Work;');

 except
   on E : EDatabaseError do
     Memo1.Append('DB ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
   on E : Exception do
     Memo1.Append('ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
 end;  

And the error message...

DB ERROR:CREATE DATABASE teste;
EDatabaseError
PQConnection1 : Execution of query failed (PostgreSQL: ERROR:  CREATE DATABASE cannot run inside a transaction block
)


Heeeellp!!!

[johnf]

My response is old but for the benefit of others that may read this here is the code to create a database using SQLDB:
program bCreateTable;

{$mode objfpc}{$H+}

uses
  Classes,
  sqldb,
  SqldbExampleUnit;

begin
  ReadIniFile;
 
  CreateFConnection;
  CreateFTransaction;

  Fconnection.Transaction := Ftransaction;
 
  Fconnection.ExecuteDirect('create table FPDEV (       ' +
                            '  id INT NOT NULL,           ' +
                            '  Name VARCHAR(50),          ' +
                            '  Email CHAR(50),            ' +
                            '  Birthdate Date,            ' +
                            '  PRIMARY KEY (id)           ' +
                            ')                            ');

  FTransaction.Commit;
  Ftransaction.Free;
  Fconnection.Free;
end.

John

[johnf]

I just now realized that my code creates 'tables' and not 'databases'.  I'm not sure how to create databases.
John

[Anonymous]

I got!!!!

It seems that when I run the software, postgre creates a transaction and in that situation postgre can't create a database.
All we have to do is end the transaction, create the database and, done!

try
// the SQL code
sql:= 'CREATE DATABASE teste;';
// that is the point: end the transaction, the postgre command for this it's 'Commit work'
PQConnection1.ExecuteDirect('Commit Work;');
// Now, with execute direct, a run de SQL command directly in postgre
PQConnection1.ExecuteDirect(sql);

//Done!

except
on E : EDatabaseError do
Memo1.Append('DB ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
on E : Exception do
Memo1.Append('ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
end;

[]'s
El Muerte