SQLScript and UpdateSQLScript 

components


allows to execute a set of SQL commands from Delphi and C++ Builder application

Table of contents

What is this
 

These are two components for Delphi and C++ Builder.

You know that SQL script is a set of SQL sentences. Now it is possible to execute such SQL scripts by using SQLScript component. For example, for database installation or moving data between databases.

UpdateSQLScript component works like well known UpdateSQL (see Delphi documentation) object but its InsertSQL, ModifySQL, DeleteSQL properties can contain SQL scripts - more than one SQL statement. An  instance of the component may be assigned to UpdateObject property of Query object.
 

Introduction
 

When creating database, often you need to run a set of SQL / DML statement to create tables, view, triggers, stored procedures or modify database data. For example, with Interbase you can use Interbase isql tool. But usually it is not convenient. Now you can create your own tool or incorporate such possibility to your application by using SQLScript component.

You can use SQLScript component by two different ways:

What about the UpdateSQLScript component ?! When developing complex database system there are cases when to you use SELECT from more then one table or corresponding VIEW. Therefore you need to modify more than one table when such data changed by user. UpdateSQLScript component solves this problem. With UpdateSQLScript you need only to write InsertSQL, ModifySQL, DeleteSQL as you did it with standard UpdateSQL component. But now those properties can contain SQL script from more than one statement.

Available versions, downloading
 

Available compiled versions for:

Source code is compatible with Delphi 3 or above, C++ Builder 3 or above.

Latest version of the component is always available from the components download page.
 

How to install
 
1. Unzip archive sqlscr.zip with subdirectories.
2. Directory CB3\ is for CBuilder 3 users.
Directory CB4\ is for CBuilder 4 users.
Directory CB5\ is for CBuilder 5 users.
Directory D3\ is for Delphi 3 users.
Directory D4\ is for Delphi 4 users.
Directory D5\ is for Delphi 5 users.
Directory D6\ is for Delphi 6 users.

Install sqlscript_reg.pas from Menu > Component > Install Component.
By default SQLScript and UpdateSQLScript will be installed to "Apelseen" components page.

 

Hint for CBuilder 3: It may happen when installing component you will need to add to your DCLUSR35.cpp one line manually: 

USEPACKAGE("vcldb35.bpi");

Then make it: Menu > Project > Make. And add to design pakages list: Menu > Component > Install Packages and then Design Packages > User's components > Checkbox on.
 
3. Just drop a component to the form at Designer when need it.

Examples are located in the corresponding directories. Also see examples below.
 

Usage
SQL script is a list of SQL statements divided by the terminator. By default it is semicolon - ";". SQL statement can include comments which are beginning from /* and ending by */.


SQLScript component

Base class of SQLScript is TComponent.

The first way to use the SQLScript component is to call Execute methods and implement OnSQLStatement event handler. You have to call Execute only one time for one SQL script. For each statement in the script you will receive an event.
 
SQLScript.Execute() -> CREATE TABLE ...
; 		-> OnSQLStatement
INSERT INTO ...
; 		-> OnSQLStatement
SET TERM ###
; 		-> OnDirective
CREATE STORED PROC ...
###

->
 

OnSQLStatement

This is an example of use of  OnSQLStatement and OnDirective events.
 
==== at other method =====================
query := TQuery.Create(Self);
// session and database initialization for 'query'
// ... 

// execution 
SQLScript1.Execute();

query.Free;
==== at other method =====================
 

// OnSQLStatement
procedure TForm1.SQLScript1SQLStatement(Sender: TObject; 
                                        SQL_Statement: TStrings; 
                                      var StopExecution: Boolean);
begin

  query.Close();
  query.SQl.Assign(SQL_Statement);

  try
   query.Open();
   query.Close();
  except
   // ...
  end;

end;
 

// OnDirevtive
procedure TForm1.SQLScript1Directive(Sender: TObject; 
                                     Directive, Argument: String; 
                                     var StopExecution: Boolean);
begin

  if Directive = 'SET TERM' then begin
    SQLScript1.Terminator := Argument;
  end;

end;
 

 

The second way - just to use the internal SQL parser. You will receive SQL statements one by one in loop like this. Note that it works on the low level. You will receive equally as statements so directives from the source script. 
 

while SQLScript1.Available() do begin

  statement :=  SQLScript1.NextStatement();

  // your code
  // ... 

end;


 


Properties
 
Name Type Default value Description
SQL TStrings   SQL script to work. It is a list of SQL statements divided by some Terminator.
Terminator String ; SQL instructions delimiter in the script. 

It can be changed anytime, for example,  in OnDirective event when execution is in progress
Directives TStrings   List of possible directives in the script. 

When SQLScript is in progress and such directive is found it calls OnDirective event handler. For all other instructions it calls OnSQLStatement. This is flexible to control script execution flow. For example, it is possible to define directives SET TERM, COMMIT WORK and others. Including Interbase isql tool directives for full script compatibility.
CommentsInSQL Boolean True Leave True if need to leave comments in the parsed pieces of source SQL script given as SQL property. False otherwise. 
Line Integer   Read only property. It is used to get current work line of the script. 

For example, to perform progress bar. Line is  >= 0. And it always less then SQL.Count.


Methods
 
Name Description
Create constructor Create(AOwner : TComponent);

This is a constructor.
DeleteComments class function  DeleteComments(SQL_Text : AnsiString) : AnsiString;

It purges comments from one SQL instruction given as SQL_Text. SQL comment's beginning from /* and ending by */. Function returns a clear SQL script, without any comments.

For example, if CommentsInSQL = True then you always receive SQL with comments which were in the source SQL script. You can use this function to remove them. if CommentsInSQL = False then you always receive SQL without comments.
Available function  Available : Boolean;

It checks that SQL script given as SQL property contains minimum one else SQL instruction or directive. It means that next call to NextStatement will give a nonempty string.
NextStatement function  NextStatement : AnsiString;

It gives next instruction from the SQL script. It doesn't call OnSQLStatement or OnDirectives event handlers. It just parses script. Use Execute method to high level execute with events generation.
Execute procedure Execute;

It runs the execution process. It doesn't exit until whole script is parsed. Or until StopExecution event inout parameter is set to True. It calls OnSQLStatement or OnDirectives for each SQL statement in the script. If the next statement begins from one of the directives given as Directives property it calls OnDirectives. Otherwise it calls OnSQLStatement.


Events
 
Name Description
OnSQLStatement procedure(Sender : TObject; SQL_Statement : TStrings; var StopExecution : Boolean) of object;

It occurs when Execute method  found a SQL statement but not a directive from Directives.

Use StopExecution parameter when need to interrupt the execution process.
OnDirective procedure(Sender : TObject; Directive, Argument : AnsiString; var StopExecution : Boolean) of object;

It occurs when Execute method found one of the directives from Directives.
Argument parameter is a right part of instruction in script. For example, if Directives contains SET TERM line then for instruction

SET TERM ### 

in the source script it will give 

Directive = SET TERM
Argument = ###

Use StopExecution parameter when need to interrupt the execution process.



UpdateSQLScript component

Take a look at Delphi documentation for TUpdateSQL class. Most of properties, methods correspond to it. Base class of UpdateSQLScript component is TDataSetUpdateObject class (or TSQLUpdateObject in Delphi 6).


Properties
 
Name Type Default value Description
SQL TStrings   type TUpdateKind = (ukModify, ukInsert, ukDelete)
property SQL[UpdateKind: TUpdateKind]: TStrings;

Returns a specified SQL statement used when applying cached updates.

Returns the SQL statement in the ModifySQL, InsertSQL, or DeleteSQL property, depending on the setting of UpdateKind. UpdateKind can be any of the following:

  • ukModify Return the SQL statement used to update records in the dataset
  • ukInsert Return the SQL statement used to insert new records into the dataset
  • ukDelete Return the SQL statement used to delete records in the dataset.
DeleteSQL TStrings   property DeleteSQL: TStrings;

Specifies the SQL DELETE statements to use when applying a cached deletion of a record. Statements have to be divided by string given as Terminator property.

Statements can be parameterized queries. For example:

delete from COUNTRY
where
  Name = :OLD_Name
;
delete from REGION
where
  Name = :OLD_Name
;

As the example illustrates, DeleteSQL supports an extension to normal parameter binding. To retrieve the value of a field as it exists prior to application of cached updates, the field name with ‘OLD_’. This is especially useful when doing field comparisons in the WHERE clause of the statement.
InsertSQL TStrings   property InsertSQL: TStrings;

Specifies the SQL INSERT statements to use when applying a cached deletion of a record. Statements have to be divided by string given as Terminator property.

Statements can be parameterized queries. For example:

insert into COUNTRY
(Name, Capital, Continent)
value (:Name, :Capital, :Continent)
where :OLD_Name = 'Rangoon'
;
insert into REGION
(Name, Capital, Continent)
value (:Name, :Capital, :Continent)
where :OLD_Name = 'Rangoon'
;

As the example illustrates, InsertSQL supports an extension to normal parameter binding. To retrieve the value of a field as it exists prior to application of cached updates, the field name with ‘OLD_’. This is especially useful when doing field comparisons in the WHERE clause of the statement.
ModifySQL TStrings   Specifies the SQL UPDATE statements to use when applying a cached deletion of a record. Statements have to be divided by string given as Terminator property.

Statements can be parameterized queries. For example:

update COUNTRY
set Name = :Name, Capital = :Capital, Continent = :Continent
where Name = :OLD_Name
;

update REGION
set Name = :Name, Capital = :Capital, Continent = :Continent
where Name = :OLD_Name
;

As the example illustrates, ModifySQL supports an extension to normal parameter binding. To retrieve the value of a field as it exists prior to application of cached updates, the field name with ‘OLD_’. This is especially useful when doing field comparisons in the WHERE clause of the statement.
DataSet TDataSet   Readonly property. Identifies the dataset to which a TUpdateSQL component belongs. 

At design time, setting the dataset object’s UpdateObject property automatically sets the DataSet property of the specified TUpdateSQL object. An application should only need to set this property if it creates a new update component at run time.
Terminator String ; SQL instructions delimiter in the scripts.


Methods
 
Name Description
Create constructor Create(AOwner : TComponent);

This is a constructor.
Apply procedure Apply(UpdateKind: TUpdateKind);

Sets the parameters for the specified SQL statement type, and executes the resulting statement. Call Apply to set parameters for the SQL and execute it to update a record. UpdateKind indicates which SQL to bind and execute. Apply is primarily intended for manually executing update statements from an OnUpdateRecord event handler.

Advantages of the professional version

At this release the professional version has no additional features.

How to buy and download the source code

For commercial purposes and if you are interested in the sources you have to buy the professional version.
Software will be available immediately after your registration from the secure web site.
  

SQLScript and UpdateSQLScript components
(professional version, source code, compatible with D3 and above, CB3 and above)
Personal license,
for one developer		 30 $ Buy it
Company license,
for any number of developers within one company		 70 $ Buy it
Contact information

Send your questions and comments to support@apelseen.com

Apelseen software website is http://www.apelseen.com
 

Useful links

Version 1.5
Copyright (c) 1999, Apelseen software. All Rights Reserved