Difference between revisions of "TSQLScript"

From Free Pascal wiki
Jump to navigationJump to search
(screenshot with ddl for clarity)
(Disable comments for less bugs :))
Line 51: Line 51:
  
 
{{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}}
 
{{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.}}
  
 
{{LCL Components Footer |TSQTransaction|TSQLConnector}}
 
{{LCL Components Footer |TSQTransaction|TSQLConnector}}

Revision as of 14:32, 5 August 2014

Overview

TSQLScript lets you run a batch of SQL statements/multiple SQL statements in one run. It is useful if you want o 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.

sqldbcomponents.png

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):

FlameRobinDatabasePropertiesDDL.png

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

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

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

Return To: LCL Components  — Previous: TSQTransaction Next: TSQLConnector


LCL Components
Component Tab Components
Standard TMainMenu • TPopupMenu • TButton • TLabel • TEdit • TMemo • TToggleBox • TCheckBox • TRadioButton • TListBox • TComboBox • TScrollBar • TGroupBox • TRadioGroup • TCheckGroup • TPanel • TFrame • TActionList
Additional TBitBtn • TSpeedButton • TStaticText • TImage • TShape • TBevel • TPaintBox • TNotebook • TLabeledEdit • TSplitter • TTrayIcon • TControlBar • TFlowPanel • TMaskEdit • TCheckListBox • TScrollBox • TApplicationProperties • TStringGrid • TDrawGrid • TPairSplitter • TColorBox • TColorListBox • TValueListEditor
Common Controls TTrackBar • TProgressBar • TTreeView • TListView • TStatusBar • TToolBar • TCoolBar • TUpDown • TPageControl • TTabControl • THeaderControl • TImageList • TPopupNotifier • TDateTimePicker
Dialogs TOpenDialog • TSaveDialog • TSelectDirectoryDialog • TColorDialog • TFontDialog • TFindDialog • TReplaceDialog • TTaskDialog • TOpenPictureDialog • TSavePictureDialog • TCalendarDialog • TCalculatorDialog • TPrinterSetupDialog • TPrintDialog • TPageSetupDialog
Data Controls TDBNavigator • TDBText • TDBEdit • TDBMemo • TDBImage • TDBListBox • TDBLookupListBox • TDBComboBox • TDBLookupComboBox • TDBCheckBox • TDBRadioGroup • TDBCalendar • TDBGroupBox • TDBGrid • TDBDateTimePicker
Data Access TDataSource • TCSVDataSet • TSdfDataSet • TBufDataset • TFixedFormatDataSet • TDbf • TMemDataset
System TTimer • TIdleTimer • TLazComponentQueue • THTMLHelpDatabase • THTMLBrowserHelpViewer • TAsyncProcess • TProcessUTF8 • TProcess • TSimpleIPCClient • TSimpleIPCServer • TXMLConfig • TEventLog • TServiceManager • TCHMHelpDatabase • TLHelpConnector
Misc TColorButton • TSpinEdit • TFloatSpinEdit • TArrow • TCalendar • TEditButton • TFileNameEdit • TDirectoryEdit • TDateEdit • TTimeEdit • TCalcEdit • TFileListBox • TFilterComboBox • TComboBoxEx • TCheckComboBox • TButtonPanel • TShellTreeView • TShellListView • TXMLPropStorage • TINIPropStorage • TJSONPropStorage • TIDEDialogLayoutStorage • TMRUManager • TStrHolder
LazControls TCheckBoxThemed • TDividerBevel • TExtendedNotebook • TListFilterEdit • TListViewFilterEdit • TLvlGraphControl • TShortPathEdit • TSpinEditEx • TFloatSpinEditEx • TTreeFilterEdit • TExtendedTabControl •
RTTI TTIEdit • TTIComboBox • TTIButton • TTICheckBox • TTILabel • TTIGroupBox • TTIRadioGroup • TTICheckGroup • TTICheckListBox • TTIListBox • TTIMemo • TTICalendar • TTIImage • TTIFloatSpinEdit • TTISpinEdit • TTITrackBar • TTIProgressBar • TTIMaskEdit • TTIColorButton • TMultiPropertyLink • TTIPropertyGrid • TTIGrid
SQLdb TSQLQuery • TSQLTransaction • TSQLScript • TSQLConnector • TMSSQLConnection • TSybaseConnection • TPQConnection • TPQTEventMonitor • TOracleConnection • TODBCConnection • TMySQL40Connection • TMySQL41Connection • TMySQL50Connection • TMySQL51Connection • TMySQL55Connection • TMySQL56Connection • TMySQL57Connection • TSQLite3Connection • TIBConnection • TFBAdmin • TFBEventMonitor • TSQLDBLibraryLoader
Pascal Script TPSScript • TPSScriptDebugger • TPSDllPlugin • TPSImport_Classes • TPSImport_DateUtils • TPSImport_ComObj • TPSImport_DB • TPSImport_Forms • TPSImport_Controls • TPSImport_StdCtrls • TPSCustomPlugin
SynEdit TSynEdit • TSynCompletion • TSynAutoComplete • TSynMacroRecorder • TSynExporterHTML • TSynPluginSyncroEdit • TSynPasSyn • TSynFreePascalSyn • TSynCppSyn • TSynJavaSyn • TSynPerlSyn • TSynHTMLSyn • TSynXMLSyn • TSynLFMSyn • TSynDiffSyn • TSynUNIXShellScriptSyn • TSynCssSyn • TSynPHPSyn • TSynTeXSyn • TSynSQLSyn • TSynPythonSyn • TSynVBSyn • TSynAnySyn • TSynMultiSyn • TSynBatSyn • TSynIniSyn • TSynPoSyn
Chart TChart • TListChartSource • TRandomChartSource • TUserDefinedChartSource • TCalculatedChartSource • TDbChartSource • TChartToolset • TChartAxisTransformations • TChartStyles • TChartLegendPanel • TChartNavScrollBar • TChartNavPanel • TIntervalChartSource • TDateTimeIntervalChartSource • TChartListBox • TChartExtentLink • TChartImageList
IPro TIpFileDataProvider • TIpHtmlDataProvider • TIpHttpDataProvider • TIpHtmlPanel
Virtual Controls TVirtualDrawTree • TVirtualStringTree • TVTHeaderPopupMenu