Create DB
lazarus / 16-Databases

----------------------------------------------------------------------
comdora · 2025-04-21 12:55

Hi,
I am trying to create dynamically database with next code

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var 
MetaConn: TPQConnection;  CheckTran: TSQLTransaction;  Qry:
TSQLQuery;   CreateConn: TPQConnection;begin  Result := False;   //
Konekcija za proveru postojanja baze  MetaConn :=
TPQConnection.Create(nil);  MetaConn.HostName := GlobalDBConfig.Host; 
MetaConn.UserName := GlobalDBConfig.User;  MetaConn.Password :=
GlobalDBConfig.Password;  MetaConn.DatabaseName := 'postgres';  
CheckTran := TSQLTransaction.Create(MetaConn);  MetaConn.Transaction
:= CheckTran;   try    MetaConn.Connected := True;     Qry :=
TSQLQuery.Create(nil);    Qry.DataBase := MetaConn;    Qry.Transaction
:= CheckTran;     Qry.SQL.Text := 'SELECT 1 FROM pg_database WHERE
datname = ' + QuotedStr(GlobalDBConfig.Database);    Qry.Open;     if
Qry.IsEmpty then    begin      Qry.Close;      CheckTran.Commit;     
// new connection WITHOUT transaction for DB creating      CreateConn
:= TPQConnection.Create(nil);      try        CreateConn.HostName :=
GlobalDBConfig.Host;        CreateConn.UserName :=
GlobalDBConfig.User;        CreateConn.Password :=
GlobalDBConfig.Password;        CreateConn.DatabaseName :=
'postgres';         CreateConn.Connected := True;        
CreateConn.ExecuteDirect(          'CREATE DATABASE "' +
GlobalDBConfig.Database + '" WITH OWNER = ' +         
QuotedStr(GlobalDBConfig.User) + ' ENCODING = ''UTF8'''        );    
   Result := True;      finally        CreateConn.Free;      end;   
end    else      Result := True;   finally    Qry.Free;   
CheckTran.Free;    MetaConn.Free;  end; 
But, on executing

CreateConn.ExecuteDirect(
          'CREATE DATABASE "' + GlobalDBConfig.Database + '" WITH
OWNER = ' +
          QuotedStr(GlobalDBConfig.User) + ' ENCODING = ''UTF8'''
        );

exists problem Transaction not Set.

On execution
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var 
MetaConn: TPQConnection;  CheckTran: TSQLTransaction;  Qry:
TSQLQuery; begin  Result := False;   // Konekcija za proveru
postojanja baze  MetaConn := TPQConnection.Create(nil); 
MetaConn.HostName := GlobalDBConfig.Host;  MetaConn.UserName :=
GlobalDBConfig.User;  MetaConn.Password := GlobalDBConfig.Password; 
MetaConn.DatabaseName := 'postgres';   CheckTran :=
TSQLTransaction.Create(MetaConn);  MetaConn.Transaction :=
CheckTran;   try    MetaConn.Connected := True;     Qry :=
TSQLQuery.Create(nil);    Qry.DataBase := MetaConn;    Qry.Transaction
:= CheckTran;     Qry.SQL.Text := 'SELECT 1 FROM pg_database WHERE
datname = ' + QuotedStr(GlobalDBConfig.Database);    Qry.Open;     if
Qry.IsEmpty then    begin      Qry.Close;      CheckTran.Commit;     
  MetaConn.ExecuteDirect(          'CREATE DATABASE "' +
GlobalDBConfig.Database + '" WITH OWNER = ' +         
QuotedStr(GlobalDBConfig.User) + ' ENCODING = ''UTF8'''        );    
   Result := True;     end    else      Result := True;   finally   
Qry.Free;    CheckTran.Free;    MetaConn.Free;  end; an error appears
"Create database cannot run inside transaction block"

Any suggestion?

Thanks in advance
Bojan

----------------------------------------------------------------------
Handoko · 2025-04-21 14:23

First of all, if you want to post codes please use code tag. If you
don't know what a code tag is or how to use it, see the screenshot
below.

Now, back to your question. I am not good in explaining things. So I
will show you an example demo that you can try and study. The demo
uses SQLite, but with a few changes it should work on your case. Hope
it helps.

https://forum.lazarus.freepascal.org/index.php/topic,65185.msg496461.h
tml#msg496461

----------------------------------------------------------------------
comdora · 2025-04-22 05:37

Hi, thanks.
I have solved problem using

CreateDB from TPQConnection;

Bojan