[SOLVED] FBConnection.CreateDatabase won't work with Firebird 4
lazarus / 16-Databases

----------------------------------------------------------------------
iso4free · 2023-11-08 12:31

I have next code for programmatically creating database with Firebird:
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 procedure
TDBSettingsForm.bbCreateClick(Sender: TObject);begin  with DataModule1
do begin    FirebirdConnection1.Connected:=False;   
FirebirdConnection1.DatabaseName:=DBFileNameEdit.Text;    if
ckbLocal.Checked then FirebirdConnection1.HostName:='127.0.0.1'     
 else FirebirdConnection1.HostName:=edHost.Text;   
FirebirdConnection1.Port:=spedPort.Value;   
FirebirdConnection1.CharSet:='UTF8';   
FirebirdConnection1.UserName:=UsernameEdit.Text;   
FirebirdConnection1.Password:=PasswordEdit.Text;    try   
 FirebirdConnection1.CreateDB;    except      on E:Exception do begin 
     ShowMessage('Database creation error: '+E.Message);       Exit; 
   end;    end;    FirebirdConnection1.Open;   
SQLScript1.ExecuteScript;    SQLTransaction1.Commit;   
bbTestClick(Sender);  end;end;
This code works well with Firebird v.3 on Linux server.

But when i try create new database on Windows 10 with Firebird 4 i got
the next error:
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
FirebirdConnection1 : CreateDB :  -Your user name and password are not
defined. Ask your database administrator to set up a Firebird
login.Username and password are correct and i can connect to existing
database on this server.
But if i try create new database with Flamerobin fron Linux to Windows
with Firebird 4 all works. Firebird 4 has restriction on database
acces only with path 'c:\databases'. Database name for creation is
'c:\databases\newdatabase.fbd'.
When i try to connect to database created with Flamerobin from my app
all works, but i can`t create new database programmatically. Maybe I
need to change some server options?
What wrong with Firebird 4 or with my code?

----------------------------------------------------------------------
rvk · 2023-11-08 19:14

Quote from: iso4free on November 08, 2023, 01:31:46
pmQuoteFirebirdConnection1 : CreateDB :
 -Your user name and password are not defined. Ask your database
administrator to set up a Firebird login.Username and password are
correct and i can connect to existing database on this server.This is
the same message that you get if you enter a wrong username or
password.
(it says "not defined" but it meant "wrong username or password")

With Firebird 4 you need to specify the SYSDBA password on
installation.
Are you absolutely sure you gave the same password.
(password is still only 8 characters, everything after the 8th
character is ignored)

Do you have one or multiple instances of Firebird installed?
(it could be that always port 3050 is used, I didn't get an error when
using a random other port. could be a bug.)

----------------------------------------------------------------------
iso4free · 2023-11-09 09:11

Quote from: rvk on November 08, 2023, 08:14:08 pmWith Firebird 4 you
need to specify the SYSDBA password on installation.
Are you absolutely sure you gave the same password.
(password is still only 8 characters, everything after the 8th
character is ignored)
Username and password was set default (SYSDBA/masterkey). I can
connect to test database created by Flamerobin from the same PC where
my app is running but i can't create new database (see attachment - my
form to setup connection params).

Quote from: rvk on November 08, 2023, 08:14:08 pmDo you have one or
multiple instances of Firebird installed?
(it could be that always port 3050 is used, I didn't get an error when
using a random other port. could be a bug.)

For Firebird 4 port changed to 3054, server runs as one instance. I
have another PC with Firebird 3 installed and with it all works fine.

I can connect to Firebird4 server database 'c:\databases\test.fdb' but
if i change database to 'c:\databases\newdatabase.fdb' or any other
name in this path and try create it nothing happens.
Debug just stops on the line CreateDatabase:
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     try   
 FirebirdConnection1.CreateDB; //<--debugger stops here without any
message    except      on E:Exception do begin     
 ShowMessage('Database creation error: '+E.Message);       Exit;     
end;    end;      Maybe for Firebird 4 i need to set some specific
parameters for new database creation? With Firebird 3 a new database
is created and initialized by my script to create the initial
structure from my app well.

----------------------------------------------------------------------
rvk · 2023-11-09 12:49

Quote from: iso4free on November 09, 2023, 10:11:53 amI can connect to
Firebird4 server database 'c:\databases\test.fdb' but if i change
database to 'c:\databases\newdatabase.fdb' or any other name in this
path and try create it nothing happens.
Debug just stops on the line CreateDatabase:
...
Maybe for Firebird 4 i need to set some specific parameters for new
database creation? With Firebird 3 a new database is created and
initialized by my script to create the initial structure from my app
well.
TIBConnection.CreateDB worked fine for me for Firebird 4 in a Windows
sandbox.
(Default FB4 install)

You said Firebird 4 has restriction on database acces only with path
'c:\databases'.
Why and how did you set this?

----------------------------------------------------------------------
rvk · 2023-11-09 18:42

Quote from: iso4free on November 09, 2023, 10:11:53 amFor Firebird 4
port changed to 3054, server runs as one instance. I have another PC
with Firebird 3 installed and with it all works fine.
AI... did you remember I said something about the port.
For me, changing the port to a random port, say 1234, didn't do
anything for CreateDB (it just worked on port 3050).

It turns out there is a bug in TIBConnection.
The TIBConnection.Port doesn't work for TIBConnection.CreateDB.

See the following code from TIBConnection.ConnectFB:
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  
HN:=HostName;  if HN <> '' then     begin    if Port<>0 then     
HN:=HN+'/'+IntToStr(Port);    ADatabaseName := HN+':'+DatabaseName   
end  else     ADatabaseName := DatabaseName;
And now the code from TIBConnection.CreateDB:
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   CreateSQL
:= 'CREATE DATABASE ';  if HostName <> '' then    CreateSQL :=
CreateSQL + ''''+ HostName+':'+DatabaseName + ''''  else    CreateSQL
:= CreateSQL + '''' + DatabaseName + '''';
So TIBConnection.Port isn't used in the TIBConnection.CreateDB !!!

You could test this by setting the TIBConnection.Hostname to
192.168.0.9/3054 for example and see if that works ??
After that you need to reset the Hostname back to 192.168.0.9 because
for ConnectFB the Port property is used again.

----------------------------------------------------------------------
iso4free · 2023-11-09 19:30

Quote from: rvk on November 09, 2023, 07:42:38 pm
You could test this by setting the TIBConnection.Hostname to
192.168.0.9/3054 for example and see if that works ??
After that you need to reset the Hostname back to 192.168.0.9 because
for ConnectFB the Port property is used again.

Thank You very much!!! It helps!!!
I modify my code to this:
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 procedure
TDBSettingsForm.bbCreateClick(Sender: TObject);begin  with DataModule1
do begin    FirebirdConnection1.Connected:=False;   
FirebirdConnection1.DatabaseName:=DBFileNameEdit.Text;    if
ckbLocal.Checked then
FirebirdConnection1.HostName:='127.0.0.1/'+spedPort.Text       else
FirebirdConnection1.HostName:=edHost.Text+'/'+spedPort.Text;   
FirebirdConnection1.Port:=spedPort.Value;   
FirebirdConnection1.CharSet:='UTF8';   
FirebirdConnection1.UserName:=UsernameEdit.Text;   
FirebirdConnection1.Password:=PasswordEdit.Text;     try   
 FirebirdConnection1.CreateDB;    except      on E:Exception do begin 
     ShowMessage('Database creation error:  '+E.Message);     
 Close;       Exit;      end;    end;   
 FirebirdConnection1.DatabaseName:=DBFileNameEdit.Text;     if
ckbLocal.Checked then FirebirdConnection1.HostName:='127.0.0.1'      
else FirebirdConnection1.HostName:=edHost.Text;   
 FirebirdConnection1.Port:=spedPort.Value;   
 FirebirdConnection1.CharSet:='UTF8';   
 FirebirdConnection1.UserName:=UsernameEdit.Text;   
 FirebirdConnection1.Password:=PasswordEdit.Text;   
FirebirdConnection1.Open;    SQLScript1.ExecuteScript;   
SQLTransaction1.Commit;    bbTestClick(Sender);  end;end; 
And now new database was created!!!
Thank You again!