Recent

Author Topic: How to create a database in postgre witihin lazarus?  (Read 7656 times)

El Muerte

  • Guest
How to create a database in postgre witihin lazarus?
« on: May 22, 2006, 04:21:57 pm »
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

  • New Member
  • *
  • Posts: 18
RE: How to create a database in postgre witihin lazarus?
« Reply #1 on: May 24, 2006, 08:11:05 am »
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

  • Guest
RE: How to create a database in postgre witihin lazarus?
« Reply #2 on: June 23, 2006, 05:24:25 pm »
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

  • New Member
  • *
  • Posts: 18
RE: How to create a database in postgre witihin lazarus?
« Reply #3 on: June 29, 2006, 05:03:42 pm »
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

  • New Member
  • *
  • Posts: 18
RE: How to create a database in postgre witihin lazarus?
« Reply #4 on: June 29, 2006, 05:05:38 pm »
I just now realized that my code creates 'tables' and not 'databases'.  I'm not sure how to create databases.
John

Anonymous

  • Guest
RE: How to create a database in postgre witihin lazarus?
« Reply #5 on: June 29, 2006, 09:40:22 pm »
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