TSQLScript

│ English (en) │ français (fr) │ 日本語 (ja) │

TSQLScript lets you run a batch of SQL statements/multiple SQL statements in one run. It is useful if you want to set up a new database or update an existing database schema.

TSQLScript is available for both FPC and Lazarus and runs on any database that SQLdb supports. It can be found on the SQLdb tab of the Component Palette.

Example

Suppose you have a set of SQL statements like the following DDL dump of a Firebird database script in the FlameRobin tool (in Database Properties/DDL):

Notice that unlike TSQLQuery, TSQLScript requires a semicolon (;) after each block of commands.

This example assumes you have existing SQL connection and transaction objects set up.

uses
...sqldb, TIBConnection,...
const
  DBSchemaFile='dbreporter.sql';
var
  FBScript:TSQLScript;
  ScriptText:TStringList;
  TranWasStarted: boolean;
begin
  TranWasStarted:=FTran.Active; //Ftran is the transaction, defined somewhere in our class
  if not TranWasStarted then FTran.StartTransaction;
  FBScript:=TSQLScript.Create(nil);
  ScriptText:=TStringList.Create;
  try
    if not fileexists(DBSchemaFile) then 
      raise Exception.CreateFmt('dbreporter: could not load database schema file %s',[DBSchemaFile]);
    ScriptText.LoadFromFile(DBSchemaFile);
    FBScript.DataBase:=(FConn as TIBConnection);
    FBScript.Transaction:=FTran;
    FBScript.Script:=ScriptText;
    // Now everything is loaded in, run all commands at once:
    FBScript.Execute;
    //... and then commit to make them stick and show them to the SQL that comes
    // after the commit
    FTran.Commit;
  finally
    FBScript.Free;
    ScriptText.Free;
  end;
  // Make sure we leave the transaction state as we found it, handy for switchnig
  // between explicit start/commit transaction and commitretaining:
  if TranWasStarted then FTran.StartTransaction;

See also \examples\database\tsqlscript in your Lazarus directory for an example program demonstrating TSQLScript.

Warning: (At least) FPC 2.6.4 and earlier: TSQLScript will not correctly parse all Firebird DDL, so you may need to test your script in advance. TSQLScript has been improved in FPC trunk (2.7.1). An alternative could be to call Firebird's isql executable using e.g. TProcess

  Note: Setting the CommentsInSQL property to false may help improve TSQLScript's reliability if- e.g. if you have comments inside stored procedure declarations.