# TSQLScript

English (en) français (fr)
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]);
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.