Create DB

[comdora]

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

Code: Pascal  [Select][+][-]

1. var
2.   MetaConn: TPQConnection;
3.   CheckTran: TSQLTransaction;
4.   Qry: TSQLQuery;
5.  
6.   CreateConn: TPQConnection;
7. begin
8.   Result := False;
9.  
10.   // Konekcija za proveru postojanja baze
11.   MetaConn := TPQConnection.Create(nil);
12.   MetaConn.HostName := GlobalDBConfig.Host;
13.   MetaConn.UserName := GlobalDBConfig.User;
14.   MetaConn.Password := GlobalDBConfig.Password;
15.   MetaConn.DatabaseName := 'postgres';
16.  
17.   CheckTran := TSQLTransaction.Create(MetaConn);
18.   MetaConn.Transaction := CheckTran;
19.  
20.   try
21.     MetaConn.Connected := True;
22.  
23.     Qry := TSQLQuery.Create(nil);
24.     Qry.DataBase := MetaConn;
25.     Qry.Transaction := CheckTran;
26.  
27.     Qry.SQL.Text := 'SELECT 1 FROM pg_database WHERE datname = ' + QuotedStr(GlobalDBConfig.Database);
28.     Qry.Open;
29.  
30.     if Qry.IsEmpty then
31.     begin
32.       Qry.Close;
33.       CheckTran.Commit;
34.  
35.       // new connection WITHOUT transaction for DB creating
36.       CreateConn := TPQConnection.Create(nil);
37.       try
38.         CreateConn.HostName := GlobalDBConfig.Host;
39.         CreateConn.UserName := GlobalDBConfig.User;
40.         CreateConn.Password := GlobalDBConfig.Password;
41.         CreateConn.DatabaseName := 'postgres';
42.  
43.         CreateConn.Connected := True;
44.  
45.         CreateConn.ExecuteDirect(
46.           'CREATE DATABASE "' + GlobalDBConfig.Database + '" WITH OWNER = ' +
47.           QuotedStr(GlobalDBConfig.User) + ' ENCODING = ''UTF8'''
48.         );
49.  
50.         Result := True;
51.       finally
52.         CreateConn.Free;
53.       end;
54.     end
55.     else
56.       Result := True;
57.  
58.   finally
59.     Qry.Free;
60.     CheckTran.Free;
61.     MetaConn.Free;
62.   end;
63.  

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][+][-]

1. var
2.   MetaConn: TPQConnection;
3.   CheckTran: TSQLTransaction;
4.   Qry: TSQLQuery;
5.  
6. begin
7.   Result := False;
8.  
9.   // Konekcija za proveru postojanja baze
10.   MetaConn := TPQConnection.Create(nil);
11.   MetaConn.HostName := GlobalDBConfig.Host;
12.   MetaConn.UserName := GlobalDBConfig.User;
13.   MetaConn.Password := GlobalDBConfig.Password;
14.   MetaConn.DatabaseName := 'postgres';
15.  
16.   CheckTran := TSQLTransaction.Create(MetaConn);
17.   MetaConn.Transaction := CheckTran;
18.  
19.   try
20.     MetaConn.Connected := True;
21.  
22.     Qry := TSQLQuery.Create(nil);
23.     Qry.DataBase := MetaConn;
24.     Qry.Transaction := CheckTran;
25.  
26.     Qry.SQL.Text := 'SELECT 1 FROM pg_database WHERE datname = ' + QuotedStr(GlobalDBConfig.Database);
27.     Qry.Open;
28.  
29.     if Qry.IsEmpty then
30.     begin
31.       Qry.Close;
32.       CheckTran.Commit;
33.  
34.         MetaConn.ExecuteDirect(
35.           'CREATE DATABASE "' + GlobalDBConfig.Database + '" WITH OWNER = ' +
36.           QuotedStr(GlobalDBConfig.User) + ' ENCODING = ''UTF8'''
37.         );
38.  
39.         Result := True;
40.  
41.     end
42.     else
43.       Result := True;
44.  
45.   finally
46.     Qry.Free;
47.     CheckTran.Free;
48.     MetaConn.Free;
49.   end;
50.  

an error appears "Create database cannot run inside transaction block"

Any suggestion?

Thanks in advance
Bojan

[Handoko]

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.html#msg496461

[comdora]

Hi, thanks.
I have solved problem using

CreateDB from TPQConnection;

Bojan