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.
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.