TSQLScript: what changed
Revision 0 to 1. Removed lines have a minus sign; added lines have a plus sign.
Title: TSQLScript
| Old | New | Line | |
|---|---|---|---|
| 1 | + | **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. | |
| 2 | + | ||
| 3 | + | 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]]. | |
| 4 | + | ||
| 5 | + | ## Example | |
| 6 | + | ||
| 7 | + | 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): | |
| 8 | + | ||
| 9 | + |  | |
| 10 | + | ||
| 11 | + | Notice that unlike [[TSQLQuery]], TSQLScript requires a semicolon (**;**) after each block of commands. | |
| 12 | + | ||
| 13 | + | This example assumes you have existing SQL connection and transaction objects set up. | |
| 14 | + | ||
| 15 | + | ``` pascal | |
| 16 | + | uses | |
| 17 | + | ...sqldb, TIBConnection,... | |
| 18 | + | const | |
| 19 | + | DBSchemaFile='dbreporter.sql'; | |
| 20 | + | var | |
| 21 | + | FBScript:TSQLScript; | |
| 22 | + | ScriptText:TStringList; | |
| 23 | + | TranWasStarted: boolean; | |
| 24 | + | begin | |
| 25 | + | TranWasStarted:=FTran.Active; //Ftran is the transaction, defined somewhere in our class | |
| 26 | + | if not TranWasStarted then FTran.StartTransaction; | |
| 27 | + | FBScript:=TSQLScript.Create(nil); | |
| 28 | + | ScriptText:=TStringList.Create; | |
| 29 | + | try | |
| 30 | + | if not fileexists(DBSchemaFile) then | |
| 31 | + | raise Exception.CreateFmt('dbreporter: could not load database schema file %s',[DBSchemaFile]); | |
| 32 | + | ScriptText.LoadFromFile(DBSchemaFile); | |
| 33 | + | FBScript.DataBase:=(FConn as TIBConnection); | |
| 34 | + | FBScript.Transaction:=FTran; | |
| 35 | + | FBScript.Script:=ScriptText; | |
| 36 | + | // Now everything is loaded in, run all commands at once: | |
| 37 | + | FBScript.Execute; | |
| 38 | + | //... and then commit to make them stick and show them to the SQL that comes | |
| 39 | + | // after the commit | |
| 40 | + | FTran.Commit; | |
| 41 | + | finally | |
| 42 | + | FBScript.Free; | |
| 43 | + | ScriptText.Free; | |
| 44 | + | end; | |
| 45 | + | // Make sure we leave the transaction state as we found it, handy for switchnig | |
| 46 | + | // between explicit start/commit transaction and commitretaining: | |
| 47 | + | if TranWasStarted then FTran.StartTransaction; | |
| 48 | + | ``` | |
| 49 | + | ||
| 50 | + | See also \examples\database\tsqlscript in your Lazarus directory for an example program demonstrating TSQLScript. | |
| 51 | + | ||
| 52 | + | > **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]] | |
| 53 | + | ||
| 54 | + | > **Note:** Setting the CommentsInSQL property to false may help improve TSQLScript's reliability if- e.g. if you have comments inside stored procedure declarations. | |
| 55 | + | ||
| 56 | + | --- | |
| 57 | + | *From the Free Pascal wiki, page "TSQLScript", as it stood on 2020-03-02 after a change by Trev, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).* |