Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
Reiner, You can certainly do this with IBX for Lazarus (http://www.mwasoftware.co.uk). You will also find there some guidelines on using the Firebird embedded library under both Windows and Linux that are more general than just IBX. If you want to stick with SQLDB then there is a global variable UseEmbeddedFirebird that you need to set to force use of the embedded library. On 03/08/11 06:39, Reinier Olislagers wrote: When using the Firebird embedded database, it's nice to be able to create the database using only FreePascal/Lazarus. Is there a way to do this, e.g. using the SQLScript component, using something like this: const DatabaseFile = 'DATABASE1.FDB'; var CreateScript: TSQLScript; ScriptText: TStringList; begin //Connection to Firebird database IBConnection1.HostName := ''; //must be empty for embedded Firebird; must be filled for client/server Firebird IBConnection1.DatabaseName := DatabaseFile; //Filename of Firebird database IBConnection1.Username := 'SYSDBA'; IBConnection1.Password := 'masterkey'; //default password for SYSDBA IBConnection1.Charset := 'UTF8'; //Send and receive string data in UTF8 encoding IBConnection1.Dialect := 3; //Nobody uses 1 or 2 anymore. if (FileExists(DatabaseFile)=false) then begin CreateScript := TSQLScript.Create(nil); ScriptText:=TStringList.Create; try CreateScript.OnException:=@ExceptionHandler; IBConnection1.DatabaseName:=''; CreateScript.DataBase:=IBConnection1; CreateScript.Transaction:=SQLTransaction1; IBConnection1.Open; SQLTransaction1.StartTransaction; ScriptText.Text:='CREATE DATABASE ''database1.fdb'' page_size 16384 user ''SYSDBA'' password ''masterkey'' default character set UTF8;'; CreateScript.Script:=ScriptText; CreateScript.ExecuteScript; SQLTransaction1.Commit; SQLTransaction1.StartTransaction; ScriptText.Text:='CREATE TABLE Table1 (id VARCHAR(255), name VARCHAR(255));'; CreateScript.Script:=ScriptText; CreateScript.ExecuteScript; SQLTransaction1.Commit; IBConnection1.Close; finally ScriptText.Free; CreateScript.Free; end; end; ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
On 3-8-2011 9:48, Tony Whyman wrote:
Reiner,
You can certainly do this with IBX for Lazarus
(http://www.mwasoftware.co.uk). You will also find there some guidelines
on using the Firebird embedded library under both Windows and Linux that
are more general than just IBX.
If you want to stick with SQLDB then there is a global variable
UseEmbeddedFirebird that you need to set to force use of the embedded
library.
Thanks for the suggestion, Tony. I did download and install IBX, but
then I found bug report
http://bugs.freepascal.org/view.php?id=13340
with sample code for regular sqldb.
Adapted this code*:
(*Note: if you don't specify UseEmbeddedFirebird, you can rename
fbembed.dll to fbclient.dll and get the same results - I'd wish somebody
would implement the provided fix in issue
http://bugs.freepascal.org/view.php?id=17664)
//requires ibase60dyn in uses:
UseEmbeddedFirebird:=true;
IBConnection1.HostName := '';
IBConnection1.DatabaseName := DatabaseFile; //
IBConnection1.Username := 'SYSDBA';
IBConnection1.Password := 'masterkey';
IBConnection1.Charset := 'UTF8';
IBConnection1.Dialect := 3;
DBParams:=TStringList.Create;
try
DBParams.Add('PAGE_SIZE=16384');
IBConnection1.Params := DBParams;
finally
DBParams.Free;
end;
if (FileExists(DatabaseFile)=false) then
begin
SQLTransaction1.Active:=false;
IBConnection1.Transaction:=SQLTransaction1;
IBConnection1.CreateDB; //Create the database file.
IBConnection1.Open;
SQLTransaction1.StartTransaction;
IBConnection1.ExecuteDirect('CREATE TABLE Table1 (id VARCHAR(255),
name VARCHAR(255));');
SQLTransaction1.Commit;
IBConnection1.Close;
end;
//Finally switch on connection
IBConnection1.Connected := True;
IBConnection1.KeepConnection := True;
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
On Wed, 3 Aug 2011, Reinier Olislagers wrote: When using the Firebird embedded database, it's nice to be able to create the database using only FreePascal/Lazarus. Why do you think this is not possible ? The CreateDB method of TIBConnection does exactly that. I've been using it for years. Michael. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
Actually, my preference for creating a database in a deployed
application is to first create it on my local system using isql with
input from a script and then to save it using gbak in a portable
format. The TIBRestoreService is then used to create the database from
the backup archive when your program fails to detect a local database copy.
The advantages of this approach are that:
- you only ever have one file to distribute
- backup/restore is very robust and quick
- you can readily add Blob data to your archive and distribute it as
part of the archive
- the initial database is consistent across all deployments
Scripts tend to be more useful when issuing patches to existing
databases. In this case, simply calling isql from your program and
pointing it at a patch script can be much easier than embedding SQL
execution in your program.
On 03/08/11 10:18, Reinier Olislagers wrote:
On 3-8-2011 9:48, Tony Whyman wrote:
Reiner,
You can certainly do this with IBX for Lazarus
(http://www.mwasoftware.co.uk). You will also find there some guidelines
on using the Firebird embedded library under both Windows and Linux that
are more general than just IBX.
If you want to stick with SQLDB then there is a global variable
UseEmbeddedFirebird that you need to set to force use of the embedded
library.
Thanks for the suggestion, Tony. I did download and install IBX, but
then I found bug report
http://bugs.freepascal.org/view.php?id=13340
with sample code for regular sqldb.
Adapted this code*:
(*Note: if you don't specify UseEmbeddedFirebird, you can rename
fbembed.dll to fbclient.dll and get the same results - I'd wish somebody
would implement the provided fix in issue
http://bugs.freepascal.org/view.php?id=17664)
//requires ibase60dyn in uses:
UseEmbeddedFirebird:=true;
IBConnection1.HostName := '';
IBConnection1.DatabaseName := DatabaseFile; //
IBConnection1.Username := 'SYSDBA';
IBConnection1.Password := 'masterkey';
IBConnection1.Charset := 'UTF8';
IBConnection1.Dialect := 3;
DBParams:=TStringList.Create;
try
DBParams.Add('PAGE_SIZE=16384');
IBConnection1.Params := DBParams;
finally
DBParams.Free;
end;
if (FileExists(DatabaseFile)=false) then
begin
SQLTransaction1.Active:=false;
IBConnection1.Transaction:=SQLTransaction1;
IBConnection1.CreateDB; //Create the database file.
IBConnection1.Open;
SQLTransaction1.StartTransaction;
IBConnection1.ExecuteDirect('CREATE TABLE Table1 (id VARCHAR(255),
name VARCHAR(255));');
SQLTransaction1.Commit;
IBConnection1.Close;
end;
//Finally switch on connection
IBConnection1.Connected := True;
IBConnection1.KeepConnection := True;
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
On 3-8-2011 11:50, Michael Van Canneyt wrote: On Wed, 3 Aug 2011, Reinier Olislagers wrote: When using the Firebird embedded database, it's nice to be able to create the database using only FreePascal/Lazarus. Why do you think this is not possible ? The CreateDB method of TIBConnection does exactly that. I've been using it for years. Michael. Thanks Michael, I had just found that out (see my reply to Tony Whyman). I've added the information to the Lazarus Firebird tutorial on the wiki. Regards, Reinier ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
On Wed, 3 Aug 2011, Tony Whyman wrote: Actually, my preference for creating a database in a deployed application is to first create it on my local system using isql with input from a script and then to save it using gbak in a portable format. The TIBRestoreService is then used to create the database from the backup archive when your program fails to detect a local database copy. The advantages of this approach are that: - you only ever have one file to distribute - backup/restore is very robust and quick - you can readily add Blob data to your archive and distribute it as part of the archive - the initial database is consistent across all deployments Scripts tend to be more useful when issuing patches to existing databases. In this case, simply calling isql from your program and pointing it at a patch script can be much easier than embedding SQL execution in your program. Why ? You need SQL execution anyway. If you want to use isql, you must detect where it is installed and whatnot; You must write code to capture output or check for errors. In SQL execution you don't need all that - you already have it. I've used creation and update of database with TSQLScript for years, and we've thousands of installed databases at client sites. You need the SQL execution technology anyway, so IMHO there is little point in using 2 different technologies. It means extra coding, and therefor extra room for errors. As for backup/restore being robust: this is not 100% correct. It is extremely easy to create a corrupt backup in firebird. Just add a required field to any table with data, create the backup. This backup cannot be restored. Michael. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
On 3-8-2011 12:05, Tony Whyman wrote: Actually, my preference for creating a database in a deployed application is to first create it on my local system using isql with input from a script and then to save it using gbak in a portable format. The TIBRestoreService is then used to create the database from the backup archive when your program fails to detect a local database copy. The advantages of this approach are that: - you only ever have one file to distribute - backup/restore is very robust and quick - you can readily add Blob data to your archive and distribute it as part of the archive - the initial database is consistent across all deployments Scripts tend to be more useful when issuing patches to existing databases. In this case, simply calling isql from your program and pointing it at a patch script can be much easier than embedding SQL execution in your program. I understand your approach - and I suspect you have a lot more experience with Firebird deployments than I. As for the scripts, yes, I agree that's a good idea for patching, but it would be nice if you could use SQLDB's SQLScript for that (haven't tried it yet). This cuts down on the number of external files you have to distribute. I needed to create a db in code because I was writing a sample Lazarus application to show how to use SQLDB with embedded Firebird: http://lazarus.freepascal.org/index.php/topic,10811.msg74279.html#msg74279 Having people download and run isql just to set up the database seemed inelegant, error prone and time consuming. Of course, I could have used IBX components, but then people would have to download install those Thanks for your insights, Reinier ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
Michael, I will concede that backup/restore is vulnerable to rubbish in/rubbish out errors. But then if you don't test your backup before distribution On 03/08/11 11:21, michael.vancann...@wisa.be wrote: As for backup/restore being robust: this is not 100% correct. It is extremely easy to create a corrupt backup in firebird. Just add a required field to any table with data, create the backup. This backup cannot be restored. Michael. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
On Wed, 3 Aug 2011, Tony Whyman wrote: Michael, I will concede that backup/restore is vulnerable to rubbish in/rubbish out errors. But then if you don't test your backup before distribution Yes, of course. We have checklists and procedures for that when we do updates :-) I just wanted to say that firebird backups are not guaranteed to be reliable. It can bite you in the leg quite unexpectedly. Michael. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
I understand now what you are doing and would agree that for a simple demo, you really do want to create the DB in code. I am thinking more about distributing and supporting a fully supported application. Interesting that I seemed to get so much push-back when I suggested isql - I did so as a simple way to distribute a patch (and with an underlying assumption that you would install a copy in your application directory in order to avoid searching for it). In practice, I don't use isql to patch a deployed database but have my own set of units to execute an SQL Script using the TIBSQL component. I didn't suggest this because this is more than just executing a script, but also includes version control tables in the database itself and a configuration file distributed with the scripts to tell the updating application which scripts have to be applied to get to a specific patch level and so on. Or to put it another way, I didn't want to get into a lengthy discussion on how to maintain a database in the field. Perhaps I should have just avoided the topic :( On 03/08/11 11:23, Reinier Olislagers wrote: I needed to create a db in code because I was writing a sample Lazarus application to show how to use SQLDB with embedded Firebird: http://lazarus.freepascal.org/index.php/topic,10811.msg74279.html#msg74279 Having people download and run isql just to set up the database seemed inelegant, error prone and time consuming. Of course, I could have used IBX components, but then people would have to download install those Thanks for your insights, Reinier ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
On Wed, 3 Aug 2011, Tony Whyman wrote: I understand now what you are doing and would agree that for a simple demo, you really do want to create the DB in code. I am thinking more about distributing and supporting a fully supported application. Interesting that I seemed to get so much push-back when I suggested isql - I did so as a simple way to distribute a patch (and with an underlying assumption that you would install a copy in your application directory in order to avoid searching for it). In practice, I don't use isql to patch a deployed database but have my own set of units to execute an SQL Script using the TIBSQL component. I didn't suggest this because this is more than just executing a script, but also includes version control tables in the database itself and a configuration file distributed with the scripts to tell the updating application which scripts have to be applied to get to a specific patch level and so on. Or to put it another way, I didn't want to get into a lengthy discussion on how to maintain a database in the field. Perhaps I should have just avoided the topic :( Funny to see that you actually do (A) and then recommend doing (B). When in fact, I do (A) almost exactly as you do, and so would expect you to recommend (A) as well ? Practice learns that the so-called 'simple solutions' (B) over time turn out to be highly insufficient, and you end up doing (A) anyway. So better recommend (A) from the start :-) It was not my intention to scare you off, my apologies if it came over as such :-/ Michael. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
On 3-8-2011 12:38, Tony Whyman wrote: I understand now what you are doing and would agree that for a simple demo, you really do want to create the DB in code. I am thinking more about distributing and supporting a fully supported application. Interesting that I seemed to get so much push-back when I suggested isql - I did so as a simple way to distribute a patch (and with an underlying assumption that you would install a copy in your application directory in order to avoid searching for it). In practice, I don't use isql to patch a deployed database but have my own set of units to execute an SQL Script using the TIBSQL component. I didn't suggest this because this is more than just executing a script, but also includes version control tables in the database itself and a configuration file distributed with the scripts to tell the updating application which scripts have to be applied to get to a specific patch level and so on. Or to put it another way, I didn't want to get into a lengthy discussion on how to maintain a database in the field. Perhaps I should have just avoided the topic :( Agreed regarding putting isql in the app directory if you're using it; that way you get to control what you're executing... I understand what you're doing too - even about version tables and such. Just never had to do it in Firebird FPC yet ;) As for lengthy discussions, it seems this list seems to have a few - but I'm not in an argumentative mood ;) Regards, Reinier ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
[fpc-pascal] Is there a way to create a Firebird embedded database programmatically?
When using the Firebird embedded database, it's nice to be able to create the database using only FreePascal/Lazarus. Is there a way to do this, e.g. using the SQLScript component, using something like this: const DatabaseFile = 'DATABASE1.FDB'; var CreateScript: TSQLScript; ScriptText: TStringList; begin //Connection to Firebird database IBConnection1.HostName := ''; //must be empty for embedded Firebird; must be filled for client/server Firebird IBConnection1.DatabaseName := DatabaseFile; //Filename of Firebird database IBConnection1.Username := 'SYSDBA'; IBConnection1.Password := 'masterkey'; //default password for SYSDBA IBConnection1.Charset := 'UTF8'; //Send and receive string data in UTF8 encoding IBConnection1.Dialect := 3; //Nobody uses 1 or 2 anymore. if (FileExists(DatabaseFile)=false) then begin CreateScript := TSQLScript.Create(nil); ScriptText:=TStringList.Create; try CreateScript.OnException:=@ExceptionHandler; IBConnection1.DatabaseName:=''; CreateScript.DataBase:=IBConnection1; CreateScript.Transaction:=SQLTransaction1; IBConnection1.Open; SQLTransaction1.StartTransaction; ScriptText.Text:='CREATE DATABASE ''database1.fdb'' page_size 16384 user ''SYSDBA'' password ''masterkey'' default character set UTF8;'; CreateScript.Script:=ScriptText; CreateScript.ExecuteScript; SQLTransaction1.Commit; SQLTransaction1.StartTransaction; ScriptText.Text:='CREATE TABLE Table1 (id VARCHAR(255), name VARCHAR(255));'; CreateScript.Script:=ScriptText; CreateScript.ExecuteScript; SQLTransaction1.Commit; IBConnection1.Close; finally ScriptText.Free; CreateScript.Free; end; end; ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
