This code uses the Mozilla Public License 1.1
Use the SQL Executer component to run a large SQL script file which contains "GO" statements using ADO.
NOTE: This has been branched into Version two:
https://github.com/djjd47130/sql-executer/tree/SQL-Exec-V2
Main Unit: SQLExec.pas
Main Component:
TSQLExec
This component can be installed into the Delphi IDE by adding it to a package and registering it.
How To Use:
- Assign
TADOConnection
to theConnection
property - Load SQL script to the
SQL
property (for exampleSQL.LoadFromFile(Filename)
) - Execute SQL script by calling
Execute
function
Features:
- Split a large SQL script file into individual blocks to be executed
- Change keyword
GO
to a different custom keyword (as supported by MS tools) - Use transaction mode to be able to rollback changes on script errors
- Iterate through each parsed SQL script block
- Monitor current position to be able to implement a progress bar
- Caching mechanism to only parse when it needs to (via Invalidation)
- Custom exception handlers to catch specific exceptions and related data
- Return multiple recordsets and translate to datasets
- Return error messages (or otherwise output messages)
Sample Usage:
procedure TForm1.Button1Click(Sender: TObject);
begin
SQLExec1.SQL.LoadFromFile('C:\MyScriptFile.sql');
SQLExec1.Connection:= ADOConnection1;
SQLExec1.Execute;
end;
#Sample Application ###SQL Script Executer Package also includes fully featured application to demonstrate the usage of this component.
Application Features:
- Execute single script on multiple databases at once
- Error reporting with exact script which failed
- Connect to multiple servers at once
- Full syntax highlighting with editing capabilities
- Supports Open With and Recent SQL files (Jump List)
- Edit SQL files with syntax highlighting
Requirements
- Requires SynEdit library (Not Included)
- Built and tested on Delphi XE7, none others
- VCL Style: "Iceberg Classico" (pre-shipped with Delphi XE7)
- Artwork from personal collection: https://github.com/djjd47130/jd-artwork/tree/master/Office%20Applications%20v1
#Background
This project originally started about a year ago when I was tasked with enhancing how our customers' SQL Server Database gets updated. We have a script file over 38,000 lines of SQL text, and over 2,000 "GO" statements. ADO does not support "GO", it's specific to MS tools. But it does have an advantage - anywhere a GO statement appears, it makes sure the previous code finishes executing before moving onto the next segment.
So, I wrote this utility to parse out the SQL Script and split it at each "GO" statement, and run each segment one by one. This was then used in our server update application, but I cannot share that application here. Therefore, I have just written this sample application in the past few days to demonstrate its usage. This sample application then quickly took off and I'm using it already for production.