Lazarus
Programming => Databases => Topic started by: El Muerte 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?
-
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
-
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!!!
-
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
-
I just now realized that my code creates 'tables' and not 'databases'. I'm not sure how to create databases.
John
-
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