How to create a database in postgre witihin lazarus?
lazarus / 16-Databases

----------------------------------------------------------------------
El Muerte · 2006-05-22 14:21

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 · 2006-05-24 06:11

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 · 2006-06-23 15:24

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 · 2006-06-29 15:03

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 · 2006-06-29 15:05

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

----------------------------------------------------------------------
Anonymous · 2006-06-29 19:40

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