Delphi to Interbase in 15 minutes
Contents
End Chapter - Status of this document
Delphi to Interbase in 15 minutes
Chapter 1 - The 15 minute
steps
Chapter 2 - 10 minutes to
setting up Interbase
Chapter 3 - 5 minutes to
setting up Delphi
Reducing the learning curve
Chapter 4 - Some
practical issues
Chapter 5 - Delphi Interbase components
Chapter 6 - SQL
Chapter 7 -
Stored procedures
Status of the guide
Chapter 8 - Guide
updates and limitation of liability
Chapter 1 : The 15 minute steps
1.1 Setting up Interbase - 10 minutes
Step 1.1 - Create the database
Step 1.2 - Create the tables
Step 1.3 - Create the indexes
Step 1.4 - Populate the tables
Step 1.5 - Create stored procedures
1.2 Setting up Delphi - 5 minutes
Step 2.1 - Pop up a TIBDatabase component and fill in details
Step 2.2 - Pop up a TIBTransaction component and
fill in details
Step 2.3 - Pop up a TIBTable component, a Datasource and a
DBGrid and DBNavigator
Chapter 2 : 10 Minutes to setting up Interbase
This Chapter is written on the basis that the
Interbase SQL files attached to this guide (Tables.SQL and Indexes.SQL) are
open within a simple text editor (such as Notepad or Delphi). If you read the
guide in conjunction with the SQL files, the points below should be simple to
follow.
2.1. Create the database
Background
First, you need to create the database. This
is the file structure within which Interbase will subsequently store both the
table structure, indexes etc. (called the Metadata), and the data itself
(called the Data).
Do it!
To create the database:
- Open IBConsole (Problems? Go to
Using IBConsole)
- Click "Database, Create
Database"
- Enter the Database path name and database
name (Networks? See Database
locations)
- Enter user name and password (Special
considerations? Go to creating a database in
IBConsole)
- Click the "Create" button
2.2 Create the tables
Background
Next, create the table structures. This can be
achieved in several ways. The easiest way is to take a simple text file, fill
in the table structure, and "import" the structure to Interbase using
IBConsole, as explained below. There is a separate section covering "Importing database changes using a SQL file" which will
be of interest when you need to carry out the Importing process for the second
time.
A simple text file has been provided with this guide (Tables.SQL) with
illustrative file layouts. Change the layout(s) to the layout(s) you require,
and import the structure into Interbase. You can change it easily later. Those
who don't feel comfortable with what they are doing might want to see
Creating tables - Tips, prior to
"doing it".
Do it!
To create the tables:
- Open "Tables.SQL" in any text
editor (eg. Notepad, Delphi)
- Change the file location within the
"Connect" statement to the location to which you saved the
Database
- Change the "user" and
"password" to the username and password used to create the
database
- Change the Table layout to the layout you
want. (Want help with Datatypes? See Interbase
Datatypes)
- Save the file (Latest changes not used by
Interbase? See Creating tables - Tips
)
- In the program IBConsole, click "Query,
Load script"
- Find the SQL file you saved, and click
"Open".
- Comment out the "Connect ... "
statement (How? See Comments paragraph in Chapter on
SQL)
- Click "Query, Execute"
- If you want the confirmation and/or the error
messages to appear in the IBConsole window, click the "No" button. If
you prefer them to be saved to disc in a simple text file, click the
"Yes" button and give the program a file name and location to which
the results will be written.
- If successful - you will be told (well
done!). (Failure? See Handling SQL script
errors)
2.3 Create the indexes
Background
Indexes allow Interbase to sort data
(dramatically) more quickly. An index has two key components to it - the
field(s) that you will want to be sorted on (eg. Sort by Last name and, where
there are more than one last name, sub-sort by first name) and whether the
field(s) are unique (eg. a Reference number will probably need to be unique,
but you may well need to accommodate several people sharing a birthdate, even
though you need to get a list of people sorted by their age).
One particular type of index that is
usually needed is an index that sorts on the Field(s) which identifies uniquely
a record within a table (eg. the unique reference number given to each record,
or a Social Security ID, or a post code and House number/name combination
within an Address table). This is called the Primary key. Those who don't feel
comfortable with what they are doing might want to see
Creating indexes - Tips, prior to
"doing it".
Do it!
Creating the Primary Key
- Open your "table.SQL"
file.
- Add a line at the bottom of the
definition, immediately before the final ")", and add the phrase
(with the comma in front)
, PRIMARY KEY (field)
where field is the name of the field(s) you want as the
primary key, eg:
, PRIMARY KEY (REF) or another
example: , PRIMARY KEY (LASTNAME, FIRSTNAME, POSTCODE)
- If you have already created your table,
see Creating indexes -
Tips
Creating other
Indexes
- Open the "Indexes.SQL"
file
- Add a new index with the syntax (don't forget the semi-colon at the end):
CREATE INDEX NAME ON
ANIMALS(NAME);
where "Animals" is the name of
the table, and "Name" is the field on which to index (sort)
2.4 Populate the tables
Background
When the database has been created, and the
structure set up, you may want to fill up the database with test data. This can
be achieved through the "old-fashioned" technique of manually
entering data into the database (especially if you have already set up the
Delphi side which allows data entry).
A more robust technique is to create a series of SQL commands that insert data
to the table within a simple text file, and import the SQL file to Interbase.
The advantages of this approach include the ability (a) to copy, paste and
update lines to achieve a methodical selection of all types of data more
easily, (b) to re-enter the data whenever you choose to clear all the data from
the database and start again and (c) to reuse relevant test data within new
database applications in future.
Do it!
To populate the database using this
method:
- Open "TestData.SQL" in any text
editor (eg. Notepad, Delphi)
- Change the file location within the
"Connect" statement to the location to which you saved the
Database
- Change the "user" and
"password" to the username and password used to create the
database
- Amend the test data. (Why this format? See
Interbase SQL reference guide - "Insert" commands)
- Save the file (Latest changes not used by
Interbase? See Handling SQL script errors
)
- In the IBConsole program, click "Query,
Load script".
- Find the SQL file you saved, and click
"Open".
- Comment out the "Connect.."
statement (How? See Comments paragraph in Chapter on
SQL ). Click "Query, Execute"
- If you want the confirmation and/or the error
messages to appear in IBConsole, click the "No" button. If you prefer
them to be saved to disc in a simple text file, click the "Yes"
button and give the program a file name and location to which the results will
be written.
- If successful - you will be told (well
done!). (Failure? See Handling SQL script
errors)
2.5 Create stored procedures
Why and when?
Stored procedures are arguably some of the
most powerful features of Interbase.
Almost (but not quite) everything that can be
achieved through Stored Procedures can also be achieved by sending SQL commands
from Delphi to Interbase. But the programming time can increase well more than
tenfold, and the running time can increase by vast factors in certain
circumstances. To start using Interbase, stored procedures are not necessary.
To program Database applications more quickly and to run faster, it is well
worth learning how to use Stored Procedures - a learning curve that should be
shallow for most programmers with the help of the Stored Procedures section in
this guide. Some background to Stored Procedures is set out below:
Background
Stored procedures fall into two main
categories:
- procedures that update the Database's
Metadata (What's that? See Create the
database).
- procedures that handle the Database's data,
such selecting specified items (eg. people older than 12) or modifying data
(such as inserting a new address, editing an existing address, updating a wage
rate by a calculated percentage or deleting a group of employees from a table
where the record matches a record in another archive table)
The stored procedure is similar in concept to
a Delphi procedure or function. It is programmed directly into Interbase
to:
- accept input parameters (if there are any),
provide formatted output parameters giving single item output (such as the
total of relevant sales invoices),
- provide formatted output parameters (if there
are any) giving a result set (such as every invoice detail that corresponds
with an invoice reference), or
- simply modify the database information using
conventional programming techniques (such as updating records if the department
value is greater than one value but less than another)
The stored procedure can be "called"
either from within a Delphi program (How? See the
Delphi components section), from within
another Interbase Stored Procedure (using the Interbase SQL command
Execute Procedure ) or from any other program
capable of "calling" the stored procedure (such as the IBConsole
program that comes with Interbase).
The syntax used to "call" the stored
procedures depends on what the stored procedure does. An example of the syntax
would be SELECT * FROM
SP_NEW_RECRUITS('4/1/1999','12/1/1999') to select all new recruits who
joined a company within two dates, and EXECUTE PROCEDURE
SP_ARCHIVE_RECORDS('Sales and Marketing') to archive records from the
Sales and Marketing table.
The use of Stored Procedures is so powerful,
there is a section even in this "Introductory" guide to Interbase,
with some examples of Stored Procedures we have used within our own programs to
illustrate the power and syntax of the facility - see the
Chapter on Stored Procedures. The
full technical specification of using Stored Procedures can be found in the
Interbase manuals, in several places depending on what you are looking to
achieve).
Chapter 3 : 5 minutes to setting up Delphi
This Chapter is written on the basis that the
Demo Delphi project "DelphiDemo.dpr" attached to this guide is
running within Delphi. If you read the guide in conjunction with the program,
the points below should be simple to follow.
3.1 Database
component (TIBDatabase on Interbase tab)
Drop a TIBDatabase component onto a Form (or
DataModule if you use them). The configuration is in two places:
Object inspector
- Connected - leave as false. Set to true when
ready to see if the component is set properly.
- Database name - Fill in the file location and
name (File can't be found? See Database
locations)
- Default transaction - See
TIBTransaction component below
- Login prompt - If you want users to login,
leave as false. If not, set to true AND see step c.
- Params - set if step c. is not enough
Double click on the component for the Property
editor
- Connection - leave as "local" if
Interbase is running on your machine. Otherwise, set to "remote" and
see Interbase Network issues
- User Name - Enter the UserName used to create
the Database (but note points relating to Login prompt). Leave blank if you do
want the User to type in their own User name.
- Password - Enter the Password used to create
the Database (but note points relating to Login prompt)
- Login Prompt - Set to Ticked if you want
users to enter their username and password. Set to Blank if you want Interbase
to use the UserName and Password you have entered in the TIBDatabase Component
(see above). Note - Interbase gives you the capability to allocate different
users with different rights. If you want to make use of this facility, you will
need to leave the Login prompt ticked.
- There is a trap for the unwary when opening
and closing a database using the component. To avoid the trap, see
Opening and Closing a database using Delphi
- Tips
3.2 Transaction component (TIBTransaction on
Interbase tab)
Background
Interbase allows you to collect a group of
modifications and commands together and process either all of them or none of
them. The commands are, of course, processed sequentially (such as Check stock
line exists, then if it does, update the stock table, then issue a sales
invoice, then update the sales table).
The problem that Interbase solves is where
there is a break after some transactions have been processed, but before others
within the collection have not. An example is where stock is updated but, for
some reason, the sales invoice should not be raised. Without solution, the
stock level would no longer "match" the sales levels. With the
solution, Interbase collects all commands (also called transactions) together,
processed the batch and if anywhere along the line there is a failure before
all the transactions are completed, all transactions that have already been
processed are rolled back).
Interbase solves the problem by allowing the
Delphi programmer to "mark" within the Delphi program where a
collection of commands starts (with the Delphi command TIBTransaction.StartTransaction) and where it ends (with
the Delphi command TIBTransaction.Commit or
TIBTransaction.Rollback. When each transaction is
"processed", it is simply held in a temporary location with the
command being carried out only on a temporary basis, pending the Commit
command. When the programmer issues the command TIBTransaction.Commit in the Delphi program, all
transactions within the group are finally processed. When the programmer issues
the command TIBTransaction.Rollback in the Delphi
program, the commands are removed from the temporary location and the data
restored to the state prior to the first command where the StartTransaction
command was issued.
There is one conceptually strange aspect to
the Transaction component. Interbase will not operate other than in the context
of a Transaction. However, once the Transaction component has been hooked up
(see below), there is no requirement that you activate the Start and End of the
Transaction in your program. If the Transaction is in place, you are able
simply to issue a database command (say, to update a record in a table), and
the Transaction assumes you mean for it to start just before the command is
processed, and for it to end just after the command has been processed, unless
you override the default behavior with the StartTransaction and Commit/Rollback
instructions. The Transaction will be committed for you when the Database is
closed (or when the program is closed if that comes first).
Do it!
Drop a TIBTransaction component onto the Form
(or DataModule if you use them). To configure the component:
- All defaults can be accepted
- In the TIBDatabase component, set the Default
Transaction to the name of the TIBTransaction just created.
3.3 Displaying the Interbase data in the Delphi
program
Background
Once the TIBDatabase and TIBTransaction have
been set up, everything is programmed as any other database application.
Do it!
To display the Interbase Database:
- Pop a TIBTable component onto the form (from
the Interbase tab)
- Set the Database property to the TIBDatabase
component's name
- Set the Table property to the Table you want
to display
- Pop a TDataSource component onto the form
(from the Data Access tab)
- Set the Dataset property to the TIBTable
component's name
- Pop a data aware control, such as a TDBGrid
(from the Data Access tab)
- Set the Datasource property to the
TDatasource component's name
- Pop a TDBNavigator on the form (from the Data
Access tab)
- Set the Datasource property to the
TDatasource component's name
- Set the TIBTable's "Active"
property to True
- Save and run the program
Chapter 4 - Practical issues
4.1 Creating
tables - Tips
4.2 Creating
indexes - Tips
4.3 Opening
and closing a Database from Delphi - Tips
4.4 Database
locations
4.5 Network
issues
4.6 IB Console - Running SQL
files
4.7 Creating a SQL script
file
4.8 Handling SQL
script errors
4.9 Interbase
Datatypes
4.1 Creating tables - Tips
This section expands on
Creating tables from section 2.2. It is
designed for those who don't feel comfortable creating tables without some
further guidance. For a detailed specification on creating tables, see the
Interbase guide "Data Definition Guide".
- Every comma and semi-colon is significant.
Watch for inadvertent deletions. (More detail? See Creating a SQL script)
- If you are unsure whether you want a Primary
Key, see (2.3) Create the indexes.
- Don't use referential integrity facilities at
this stage
- Keep the file structure simple. It is easy to
make it more complex later.
- Don't fill in large amounts of test or real
data to a column/field that may subsequently change, since it is a fiddly
process to change the definition of a column/field (eg. a REF field from an
INTEGER to a VARCHAR(10), or an AMOUNT field without a default value to an
AMOUNT field with a default value ) which already has data in it.
- When you have amended an "SQL"
file, save it before importing it. Otherwise, Interbase will import the version
before you made any amendments - or, more accurately, the version that was last
saved.
4.2 Creating indexes - Tips
This section expands on
Creating indexes from section 2.3. It is
designed for those who don't feel comfortable creating indexes without some
further guidance. For a detailed specification on creating indexes, see the
Interbase guide "Data Definition Guide".
- If you want to create a Primary key, and you
have already created the table, the format of the SQL command to achieve the
Primary key is:
ALTER TABLE ITEMS ADD PRIMARY KEY
(REF);
although you can not create a second primary key if one already exists
- For details of the Create Index rules, see
"Create Index" in the Interbase SQL reference manual.
4.3 Opening and closing a Database
from Delphi - Tips
One tip - in the BDE, the Database is closed
for you when you exit the program. This is not so with the Interbase Express
components. The solution is to have a IBDatabase1.close statement in the Form.OnClose event
(or similar). If you forget to close the Database explicitly, it stays
"open". If you try to open a database that is already open, using the
TIBDatabase component, you get an error message. The solution is always to
check with the database is open before opening it (eg. of Delphi :
if not (IBTransaction1.connected) then
IBTransaction1.open; ) and closed before closing it (eg. of Delphi :
if (IBTransaction1.connected) then
IBTransaction1.close; - Note that when you close a database, the
Transaction and any other component "connected" to that Database is
also disconnected for you, whether you intended it or not.)
4.4 Database locations
The Interbase components do not work with the
BDE, so no Aliases. Instead, you need to use the absolute location when filling
in the location of the database (and its file name).
In Windows, file locations follow the familiar
pattern "c:\general\interbase\filename.txt". For a networked drive,
(mapped, say to f: which is on the machine name "Machine2"), the
pattern for the location is
"f:\general\intertbase\filename.txt"
Interbase is well geared up for dealing with
both internal and external networks. It has to be a little more sophisticated.
In some circumstances, this notation works fine. In others (particularly where
you are involved with internet Web applications and/or the TCP/IP protocol
within your internal network), you have to precede the file location name with
the machine name, eg. "Machine2:c:\general\interbase\filename.txt".
Note the absence of the "\" character between the machine name and
the machine's local file location.
When using TCP/IP, you will need to
"tell" Interbase where the machine (eg. Machine2) can be found. This
is done very easily by editing the file called "hosts" (note not
hosts.sam, which is something quite different). This file can be in more than
one place, and is easily found by a Windows search of the Windows or WinNT
directories (including sub-directories) for the file "hosts" (in our
configuration, Hosts is found in the WinNT\System32\drivers\etc directory).
When found, open the file with a simple text editor (such as Notepad or Delphi)
and add at the end of the file the line which "identifies" the IP
address with its machine name.
An example of the entries might be (note that
the # sign means a comment follows):
#IP address |
#Machine name |
#Comments |
127.0.0.1 |
localhost |
|
192.1.1.1 |
Machine1 |
#This is a local machine with address 192.1.1.1 |
192.1.1.2 |
Machine2 |
#Local machine, referred to as Machine2 by programs |
99.876.78.9 |
MyISPMachine |
#External machine with address 99.888.77.66 |
Another possible file you may need to edit is
the Services file. You can find it the same way as you found the hosts file.
Interbase needs to have the line below added in. It should have been added in
automatically during installation. But on occasions, you may have to add it
"manually. If so:
- Open the Services file
- Add the line below
gds_db 3050/tcp
4.5 Interbase - Network issues
Interbase can work with three network
protocols, TCP/IP, NamedPipe and SPX. Interbase must be configured to use the
protocols used by your network.
If you have the choice, use TCP/IP, since
there are situations where the others will not run. For a detailed explanation
of the performance issues where you have the choice of protocols to use, check
out the Interbase manuals.
If you are using TCP/IP, be sure to check out
the section on Database locations.
4.6 Using the IBConsole
IBConsole is a program written to allow easy
access to Interbase. It enables you to create a Database, to set up and amend
the Metadata (what's that? See Create the
Database), and to insert, update, delete and query data in the
database.
This guide explains just a few of the issues
relating to using IBConsole, to encourage its use for "no fuss"
access to the database.
Open the program (for Windows users ..
"Start, Programs, Interbase 6, IBConsole"), then
- To create a new database, click
"Database, Create Database", and fill in the blanks. For assistance,
the database name must include the path to the directory in which you want to
store the database. The Username and Password are important in the respect that
it is the default name and password combination that give you full access to
the database in future. Do not use generic names/passwords (such as
"SYSDBA" and "masterkey") if you want to control people
accessing the data who you may have concern about access. Conversely, try to
avoid setting up a database whose name and password you can not remember - you
will not be able to connect to it from the time you have forgotten. If you have
to use a "Remote server", and you have to make a selection about
which network protocol to use, try the TCP/IP protocol if your network has
access to TCP/IP and if don't have a clue about the others.
- If you have
already created a database, click "Database, Connect", and fill in
the blanks. Once you have connected to a Database, you then have full access
(subject to the rights to which you may have been constrained by someone else
with control over the Database you are reviewing).
- Much of the time, you will want to run SQL
commands. For this you use the ISQL window, which you get access to by clicking
"Tools, Interactive SQL"
- The top window allows you to enter SQL
commands (Examples? See the Chapter on SQL). This
is particularly useful for checking whether a SQL statement does what you
expect, before entering it into a Stored Procedure. To "execute" the
command, type it is and click the Lightening hot key (or click "Query,
Execute). To rerun (or pull up to amend) a previous query, click the back arrow
with the symbols "<--?", make any changes you want, and execute
the command again.
- The bottom
window contains the results of the query. If you want to print the output, you
can click "Query, Save output", give a file name and open the text
file in any simple text editor (such as Notepad or Delphi).
Another problem with the bottom window is that records with many fields get
wrapped around in a less than satisfactory way. The solution is to get the
record displayed as a list (one field per line until the record is finished,
then leave a line, then the next record line by line), rather than in columnar
format. To achieve this, click "Session, Basic Session" then check
the "Display in List Format" option and re-execute the query,
- To disconnect from a database, click
"Database, Disconnect".
- When you have "executed" an action
in the top window, you generally have the option to Commit or Rollback (via
"Transaction, Commit" or "Transaction, Rollback"). Rolling
back reverses any change to the database since the start of, if later, the
previous commit or rollback. Committing does two things. Firstly, it makes
permanent any changes you have made to the database (metadata and/or data).
Secondly, it "refreshes" your dataset. If you are working on a
database file, and the data is changed outside of the IBConsole ( EITHER by
someone else OR you where you change the data through, say, a test Delphi
program or through another instance of IBConsole connected to the same
database), the changes are not reflected in your session of IBConsole which is
working on a cached version of the data. When you Commit or Rollback, the
cached version is "thrown away" and you will access the current
version of the data from the main database itself. The Commit command will
clear the output window at the bottom, so you will have to re-run your SQL
query, for which you can use the "<--?" hot key.
- The
final - and crucial - guide in this section explains how to "import"
SQL to the database. You always have the option to type the SQL commands
directly in the top window. But there are several commands that are better to
enter in groups (such as the setting up of a table, whose structure is far
easier to formulate as a group, than entering each field one by one). In order
to import the SQL, set up a simple text file in any text editor (such as
Notepad or Delphi), and save it with the extension "SQL" (not
mandatory, but it saves one step further along the line). See the section on
Creating a SQL script file for what to include in
the file.
When you are ready, save the SQL file. Within the ISQL window, click
"Query, Load script". Find the file, and click "Open". You
then need to comment out the "Connect.." or "Create
database.." statements. Comment out means enclose the statements within
the comment symbols (eg. of sql : /* Connect database
'IBDemos.gdb' */ ). The statement has to be in the script to allow you
to run the SQL script from outside IBConsole, but IBConsole will not allow you
to run a script without having first connected to an existing database. The
solution is simple - connect to the database first. (How? See
parapraph on connecting). Then, to execute the
script, click "Query, Execute".
The results of the Execution will either be "Your request was
successful" (in which case, well done), or it will give you an error
message. Every error we have ever encountered at this stage has been Syntax
errors. The error messages are close to the most cryptic and unhelpful you may
ever encounter. For this reason, we would recommend you take very careful note
of the syntax rules in Creating a SQL script file,
and try to avoid making mistakes in the first place - which is surprisingly
possible.
4.7 Creating a SQL script
A SQL script is a simple text file that
contains a series of SQL commands that are run together as a series. There are
many times this is preferable to entering SQL directly to a database line by
line through IBConsole (What? see Using IBConsole). Using a
SQL script file is very easy, but as with all program code, the Syntax has to
be exact, and the rules appear inconsistent and run contrary to the instinct of
a Delphi programmer.
The easiest way to get the script (and the
Syntax) right is to crib from an existing file with the correct syntax.
Attached to this file are several files with the extension ".SQL".
You should open one of them when looking through this part of the guide to make
it easy to understand what is going on.
- To put a comment
within the SQL script file, use the symbols /* to
start the comment, and */ to end it. Carriage
returns within a comment do not "cease" the commenting out. (This is
more useful that you would imagine - see the last paragraph in this section for
one circumstance where the commenting symbols are invaluable)
- The "Connect" statement is
essential. This both connects to the Database you want to update, and enters
the Username and Password (eg. of sql : CONNECT
"machine1:c:\general\data\IBDemos.gdb" USER "SYSDBA"
PASSWORD "masterkey"; - Note that the machine name is required
without "\" if connecting to a database on another machine, and note
the position of the semi-colon at the very end)
- There are two kinds of SQL you
will enter. One kind is an entirely self contained statement (such as
SET GENERATOR ORDER_GEN TO 138; - there is no
break anywhere in the statement which is relevant to Interbase ), and the other
is a series of statement that can not be interpreted without identifying there
is a break within the statement (such as CREATE TABLE
ORDERSDETAIL (REF INTEGER NOT NULL, ORDERREF INTEGER NOT NULL, STOCKREF INTEGER
NOT NULL); - without the comma, Interbase would not have know whether
ORDERREF was the start of a new field or an error in typing). The problem this
paragraph deals with involves identifying a break which represents the end of
the statement, distinguishing it from a break within the statement. The problem
arises only because the standard Delphi symbol used for a break is the
semi-colon (';'). This break symbol is used within a stored procedure to
identify that the end of a SQL statement has arrived and the next SQL statement
is coming. And it is also used within a SQL script to identify that the next
SQL script statement is coming.
The solution provided by Interbase is to allow you to define your own
"break" symbol for the SQL script file. When you set this symbol (we
use '^', others use '!!', and you can use whatever you feel comfortable with),
you continue to use the ';' symbol to identify the break of a sql statement
within a stored procedure, but to identify the break of the SQL script
statement (such as to denote the end of the Stored Procedure itself), you then
use your self-defined symbol.
To set Interbase to start to recognise the break symbol, use the instruction :
SET TERM ^ ; - Note the semi-colon is still
required to denote the end of this SQL script statement. To switch this symbol
off, use the instruction SET TERM ; ^ - Note the
self-defined break symbol is still required to terminate this statement, but
all subsequent statements will break with the conventional ';' symbol. Within
the SET TERM pair of instructions, any SQL script statement termination uses
the defined symbol (eg. of sql : SET GENERATOR ORDER_GEN
TO 138^ ). BUT, where you are creating a stored procedure with distinct
SQL statements within the Procedure, each statement must continue to use the
conventional ';' symbol, or the Stored Procedure will not operate properly when
it is at a later date.
This solution, which is not really so cumbersome once you have the Syntax
right, is confused by the way that a BEGIN .. END pair is terminated in an
Interbase stored procedure. Where the BEGIN .. END is within the definition of
a Stored Procedure, you do not terminate the END statement, other than at the
very end of the Stored Procedure where you use the self-defined SQL script
termination symbol to denote the end of the Stored Procedure. But within the
Stored Procedure's definition, you may use a begin .. end set within an 'IF'
type clause, in which case the End IS terminated with the ';' symbol. For more
details, see the Chapter on Stored
Procedures.
- The SQL script file is usually
"closed" with the "COMMIT"
statement, to commit to the database changes that occurred.
- If you ran a script that failed, you
will find that the part that "succeeded" prior to the failure will
have been committed to the database. Therefore, the next time you run the
script, you may find a new error message saying that you are trying to create
something that already exists. The solution is to comment out any statements
that have been "passed" on importing.
4.8 Handling SQL script errors
You import a series of SQL statements into
Interbase, typically to set up Metadata and to insert test data to the Database
(How? See Creating a SQL script). If and when you
import a script with Syntax errors, IBConsole provides you with inadequate
error messages. Throughout this guide are tips to help you avoid error. This
section provides you with the links for information on avoiding and handling
errors on importing.
- Create, import and execute a script - see
Creating a SQL script
- Error messages for sections that have not
previously been reported - see Creating a SQL script, final
paragraph
- Unable to connect to the Database - see
Creating a SQL script, CONNECT
paragraph
- Errors related to end of statements - see
Creating a SQL script,
TERMINATIONS paragraph
- Corrected errors failing to be picked up by
Interbase - If you amend a text file to correct an error, it is not saved to
disk until you save it. Therefore, remember to save the file WHENEVER you make
any changes. If you have corrected an error, check the output to confirm which
version of the file Interbase has just tried to import. If is the pre-saved
version, simply save the corrections in your text editor and re-run the SQL
script.
- One final point - Interbase identifies where
there is an error by reference to the line count. For this purpose, Interbase
ignores lines without any data. So the 32nd line, is not necessarily the 32nd
line in your file. One solution is to put in a know error towards the top, run
the script and note the line number of the known error. Keep moving the error
down and re-running the script until you are able to identify the problem
script.
4.9 Interbase Datatypes
There are only a few datatypes within
Interbase, and they are largely instinctive to a Delphi programmer. The
Interbase manuals give the technical specification of different datatype (eg.
integer). There are a couple of datatypes listed below for which you may find
that you may a small amount of guidance saves you a large amount of
time.
- Strings - The Interbase equivalent of Strings
is VARCHAR(25) or CHAR(22) . The number in brackets is the number of
characters you want the string to have. A CHAR definition reserves the number
of characters you have specified in the database, irrespective of the number of
characters in the string (eg. of sql : Name
CHAR(50); will allow you to enter any name up to 50 characters, and
Interbase will store 50 characters on disk, even if the name has less
characters (such as 'Smith'). A VARCHAR definition puts an upper limit on the
number of characters you are entitled to save, but will only use the number of
characters in the saved name (5 in the above example). If you try to put a
string with more characters than the defined field length, the string gets
truncated and you lose the truncated characters.
- In earlier versions of Interbase, Floats had
a different concept of decimal point numbers to Delphi. A number was stored in
Interbase with all its decimals, up to the maximum Interbase can store. If you
stored a number, say 4.22, this was stored within Interbase as 4.2200000012651
(or something like that). If you stored the result of 4/3, it was store
1.333333333315561 (or something like that). The Datatypes available to you
affected ONLY the formatting for display purposes. They ido not affect the
storage of the data.
The two main types of decimal definitions were NUMERIC(15,2) and DECIMAL(22,5),
where the first number was the length of the number and the second number was
the number of decimal points. The difference between the two above numbers are
subtle and explained in the Interbase manual - Data definition.
To avoid rounding errors when dealing with currencies, you had to round any
number you wanted to store to 2 decimal places prior to saving. This prevented
the result of storing '4/3', and adding it to another stored '4/3' from giving
you a result that does not match the displayed sum of '1.33' + '1.33'.
Under the new versions, the method of storage and implementation of the number
system has changed. For this reasons, users migrating from earlier versions of
Interbase to version 6 should pay particular attention to the Interbase Manual
- Getting started, Section "Migration issues"
- Dates - Separate datatypes exist for Date,
Time and the datatype TimeStamp, which is the equivalent of a TDateTime. If you
want Interbase to use a date field, say in an output report, you need to use
the "Cast" methods within Interbase. (How? See SQL:Sundry)
Chapter 5 - Delphi Interbase components
The Delphi Interbase components are described
very well in the Interbase manual - "Developers Guide". This section
is designed to help understand some of the concepts that are not necessarily
apparent to a programmer who is new to the components.
5.1 TIBDatabase/TIBTransaction/TIBTable
These components are explained in
Chapter 3, Setting Up Delphi.
5.2 TIBQuery - The Query component
This component is designed to be the main
Query component that is used. The "Query" component allows you to
send through any SQL command to Interbase. It's power can be seen when dealing
with a result set of multiple records. This component is one that brings the
full power of Interbase's reporting facilities to Delphi, by allowing you to
use SQL commands from Delphi. For a guide to the power of SQL commands, see the
Chapter on SQL
Note that the TIBQuery result set is
read-only. For those who want to display a result set within a data-aware
component (say a TDBGrid or TDBEdit) for users to have immediate ability to
update, you will need to implement the TIBDataset
component to give your users this facility. - BUT REMEMBER that you have to set
the TIBQuery's "CachedUpdates" property to "true" before it
will work.
There are growing number of programmers,
however, who perfer to "collect" data from a database, fill in each
record into an Object they create, and get the Object to feed unmodified data
to the user and modified data to the Database- this gives the Programmer
additional control and faster upgrades and maintenance later in the programming
cycle in exchange for more complex programming in the earlier stages of the
programming cycle. The TIBQuery is perfect for this type of use.
To send off a SQL query to Interbase:
- Fill in the Database name property
- Fill in in the SQL property (eg.
Select * from sales where sales_value > 50000
)
- Run the SQL query from the Delphi program,
using the command TIBQuery.ExecSQL).
The result set can be used in just about any
way, such as
- hooking it up to a Data Aware control (like
TDBGrid),
- scrolling backwards and forwards (using, say,
a TDBNavigator, or using the IBQuery1.Next and
IBQuery1.Previous command to move backwards and
forwards programmatically)
- using and filling in parameters eg:
In the SQL property, use the statement
Select Firstname, TelephoneNumber from
Customers where Lastname=:LastnameParam
In the program at runtime, use the statement
IBQuery1.Params.ParamByName('LastnameParam').AsString
:='Smith';
- querying data from more than one table at a
time, such as with the statement
Select Person.Firstname, Person.Lastname,
Address.County from Person, Address where
Person.AddressRef=Address.Ref.
It's downside is that it takes up more
overhead. We have yet to find a situation where it has significantly affected
our program, but if this is significant to your application/hardware
configuration, check out the TIBSQL component for an
alternative in restricted circumstances.
5.3 TIBSQL - The "unidirectional" query
component
This component is designed to be quick and
basic, or to use "minimal overhead" in Delphi speak.
- It is used by filling in any SQL command in
the SQL property, and executing it using the command TIBQuery.ExecQuery .
- It's upside is that is really is a very quick
and simple way to pass through any SQL command to Interbase. So for queries
that, count the number of records in a selected group where the result is a
single number, the SQL command is very effective. An example might be
- Select count(ref) from
employees where DepartmentName='IT'
- It's downside is its handing of results. If
the query returns many records, the IBQuery can only handle moving forward
through the result set. This means that you can comfortably transfer all
records to, say, a separate object for each record. But you can not go forward
two records, then go back, say, one record. This makes scrolling impractical
for most uses. Further, the IBSQL component does not hook up to data aware
components. So if you want to display the result set through Data Aware
components, the TIBQuery is not for you.
Background to updating Read-Only
datasets
The two components, TIBUpdateSQL and TDataSet
are examples of the few "indirect" components that are used in
Interbase Experss. (Eh? Read on..).
- There are times where you will want to pull
together a query which is read-only (such as a TIBQuery or TIBSQL) , into a
data-aware component that your Program user can update "directly"
through a Data-Aware control.
- The Read-only components are read-only
because too often there may be an ambiguity to Interbase about how to handle
the update. An example is where a query extracts data from more than one table,
which is put into a single TDBGrid for the user to update, such as matching an
employee's name from the Employee table, with his/her address from the Address
table, where the first and last name are concatenated into a single
"name" field in the TDBGrid. In the "simple" situation of a
Table fed directly into a TDBGrid, the user can change details and post them,
and the changes are updated immediately. In the possible "ambiguity"
situation, Interbase will not take responsibility for ensuring that the correct
fields have been updated.
- The TIBUpdate component is Delphi's solution
to allow you to specify precisely what data modification should take place in
the database, whenever the TDBGrid (or other data aware component), which may
contain data from more than one table (such as in a SQL
join statement), is "posted".
- The value of understanding that the component
is indirect is to help you use this very powerful tool. The
"indirect" part comes because a user instructs the program to
"post" a queried datatset, and the Delphi program redirects the Post
from the initial "read-only" component, to another component that is
never directly called. Note that you need a separated "indirect"
component for each table you want to update. If the "post" needs to
modify data from a single table, then you should use the TIBUpdateSQL
component. If the "post" needs to modify data in more than one table,
you need to use the TIBDataSet components - a separate one for each table to be
updated.
5.4 TIBUpdateSQL - The simple Read-Only updating
component
Be sure to read the
Background section before trying to
understand this section. The TIBUpdateSQL only works where one table alone
needs to be updated. Where you want to update more than one table, use the
TIBDataSet components.
- The TIBUpdateSQL requires you to specify four
different SQL commands, one which effects each of Edit, Insert, Delete or
Refresh. (What SQL works? See the Chapter on SQL.)
Remember, this is the Indirect component that is called automatically by
another component that is linked to this one, and is not called explicitly in
your program.
- Once you have set up the TIBUpdateSQL
component, go back to the "originating" component, such as a TIBQuery
component. In the originating component, select the relevant TIBUpdateSQL in
the UpdateObject property. Also set the "CachedUpdates" property to
True.
One word of caution : Caching is Delphi's equivalent to the Interbase
Transactions handling (What's that? See TIBTransaction in Setting up Delphi). Setting the
Caching property to "true" means that updates will be stored
temporarily, and will not be made permanent until you use the Delphi command
QueryName.ApplyUpdates, or QueryName.CancelUpdates. The caching is entirely
independent of Interbase's Transaction handling. Conceptually, Delphi does not
pass through changes to Interbase until the ApplyUpdates is called, at which point Interbase treats
it as any other data entry, which is not fed through permanently to the actual
database until the Interbase command Commit or
Rollback is called. Of course, if you are not
using Transaction handling in Interbase, then the changes fed through to
Interbase are fed through to the actual database immediately the
ApplyUpdates command is called in Delphi, in the
same way as any other data update. See the Interbase manual - Developers Guide
for more details on handling cached data.
- The selection of which of the four SQL
commands to operate is made by the TDBNavigator action. This is, of course,
what happens when using a "conventional" Navigator and Data-Aware
component, but you are normally shielded from this process where there is no
ambiguity. The "onUpdateRecord" event handler will be passed a
parameter "UpdateKind" which allows you to know which request you are
dealing with.
- You will probably be glad of the
"OnUpdateRecord" event handler in the "originating"
component, such as a TIBQuery. If, for example, you want to deal with NULL
fields that Interbase is set up to reject, to fill in parameter values or to
carry out any other validation, here is a good place to write your code.
- If you have not specified the
"UpdateObject" property in the originating component, you can apply
the appropriate SQL command, in the OnUpdateRecord, use the "apply"
command (eg. of Delphi: IBUpdateSQL1.Apply(UpdateKind);). This allows you to use
more than one IBUpdateSQL's if you need to. If you do use this facility with a
TIBUpdateSQL, note that TIBUpdateSQL is not derived from TDataSet, so the
DataSet parameter of the originating component's "OnUpdateRecord"
event handler will not be of any help to you.
Note - If your SQL has parameters, you can set
the value of the parameter at run time with commands like:
- DataSet1.Params[0].AsInteger=24
-
DataSet1.Params.ByName[Department].AsString=Sales
5.5 TIBDataset - The self-contained Read-Only
updating component
Be sure to read the
Background section before trying to
understand this section. The TIBDataset works where more than one table needs
to be updated. Where you want to update only one table, you can use the
easierTIBUpdateSQL component if you prefer.
The TIBDataset component almost identical to
the TIBUpdateSQL component (described above). The key differences are that the
TIBDataset component is derived from the TDataset. This means the result set
can be hooked up directly to a Data-Aware control set, which means you do not
use an "origniating" component. For the "hook up", there is
an additional SQL line "SelectSQL". This is the SQL you enter for
your query, and is the query that is executed when the component's property
"Active" is set to true, and is the source of information for the
data-aware controls.
5.6 TIBStoredProc - The Updating component
The TIBStoredProc component is designed to
allow you to run a Stored Procedure within Interbase.
There are two types of stored procedure.
- One type returns a result set of multiple
records. For this type of Stored Procedure, use the TIBQuery, using the Stored
Procedure name in place of the table, eg:
Select ref, firstname, lastname, telephone
from StoredProcedure_GetCustomers where county='London'
- The other type returns either nothing, or
single items, or a combination of single items (such as totals) and also a
multiple result set. This is where a TIBStoredProc's power is unparalleled and
is required.
To use a Stored Procedure component, you must
first write the Stored Procedure and update Interbase with it (More
information? See the Stored Procedures
Chapter. Then, pop a TStoredProc component on the form as set the
following:
- Fill in the Database property
- Select the desired Stored Procedure in the
StoredProcName property (if the Procedure is not yet in Interbase, it's name
will not appear in the list of options)
- Fill in any input parameter details (either
at design time through the Params property or at run time using code like
StoredProc1.ParamByName{'Department'}.AsString
:='IT')
- Format any output parameters you want
formatted, through the Params property
- Run the stored procedure using
StoredProc1.ExecProc
Chapter 6 - SQL
SQL |
Outline |
Background |
Background to SQL Metadata |
Concepts |
Introduction to concepts behind SQL |
6.2 Metadata controls |
|
Database |
Setting Database, Connections and Commits |
Tables |
The Query component |
Generators |
The "unidirectional" Query component |
Triggers |
The Updating component |
Indexes |
The SQL component |
6.3 Data controls |
|
Insert |
Inserting new records to the database |
Update |
Updating records already in the database |
Delete |
Deleted records already in the database |
Select |
Pull out a group of records in the database |
6.4 Great tips |
|
The "Where" statement |
Limiting a SQL command to affect only restricted data |
Order By |
Sorting the result set into a required order |
Multiple tables |
Joining more than one table together |
Multiple databases |
Joining tables from more than one database together |
Views |
Creating and using views to extent joining capabilities |
Statistics |
Getting statistics from data within the database |
Sundry others |
A collection of other SQL that may be useful
(Cast, Upper, User Defined Functions, Date handling) |
6.1 Background
SQL is a (very simple) programming language.
It is the standard "language" to access any "grown up"
database, such as Interbase, Oracle and Microsoft SQL Server. The SQL
statements can be fed into a database in either of two ways:
- From a Delphi (or any other) program - Delphi
is set up to take the strain out of working out how to send through the
statements. (More information? See the Chapter on
Delphi Interbase components).
- Directly into Interbase through IBConsole
that comes with Interbase.
The SQL commands fall into two main
categories:
- the Metadata statements that control the
structure of the database and tables and
- the Data statements that control the data
within the database.
The good news is that the entire language is
made up of between 20 and 50 statements - so it is not too taxing to learn. The
problem with SQL, therefore, is not its rules, but how to apply them to make
use of the vast power of Interbase. To be of maximum help:
- this guide will provide only a cursory
introduction to SQL statements. The majority of the guide will deal with
concepts, practical requirements and solutions to help you get the most from
Interbase in the minimum time. It deals primarily with the data manipulation
aspects. More extensive details of SQL can be found in the Interbase SQL
guide.
- attached to this guide, you should find the
file Practical.SQL which you can open in any simple text editor, such as
Notepad or Delphi. The file provides a gaggle of SQL statements which carry out
different functions we have had to generate in applications, and which should
help you deal with a number of practical problems that can be solved by
Interbase easily, once you have the answer how to do it.
6.2 Metadata SQL - some concepts
The Metadata SQL commands in Interbase control
the structure of the database, and not its data contents. You are unlikely to
need these statements when first using Interbase, so this guide is either for
the brave, foolhardy or for those who want to understand the concepts for a
future time. The commands are well explained in manuals. There are a few tips
below to help deal with a number of practical issues:
6.2.1 The Database itself
The commands allow you to "create"
and to "drop" a database (eg. of sql: create
database 'employee.gdb' and drop database.
- Watch out - "drop" means delete the
entire database, and there are precious few (ie. no) controls to prevent
accidental use of this statement.
- Distinguish these commands from the commands
that allow you to "connect" and "disconnect" from the
database.
- These commands only work for an existing
database.
- Connecting (eg. of sql:Connect 'machine1:c:\general\interbase\data\IBDemo.gdb' user
'SYSDBA' password 'masterkey' )is the equivalent of saying "Use the
database I am about to give you for all the commands I give you until I
disconnect".
- Disconnecting (eg. of sql: Disconnect 'IBDemos.gdb') is the termination.
- One pair of commands that is clealy in the
"confusing" series are the "Commit" and
"Rollback" statements. When you send through to Interbase a series of
commands, by default Interbase treats them as being temporary pending a call to
Commit (eg. of sql: Commit) or Rollback (eg. of
sql: Rollback). If you Commit explicitly (or if
you call it indirectly either by using from within Delphi a TTransaction
component to StartTransaction and/or CommitTransaction, or by using a sql
statement followed by a command to close the database), the transaction will be
cast in stone. Alternatively, if you call the Rollback statement directly or
indirectly, the transaction (or all transactions since the last commit or
rollback command) will be reversed and eliminated from the database.
6.2.2 Table control
You can create or alter a table (eg. of sql :
Create table WebCounter (ref integer, visit_date date,
visitors_ip_address string); - remember the semi-colon) once you have
connected to the database.
- You can drop (ie. delete, along with all its
data - again, no warnings, so be very careful ) the table (eg of sql:
Drop table WebCounter ) or, alter the table (eg of
sql : Alter table WebCounter add column Page_visited
varchar(60) default 'HomePage'; or Alter table
WebCounter drop Page_visited; ). This is invaluable if you want to add a
column to a table you have already set up, or drop it.
- If you want to change a column (eg. a column
with a reference as an integer to a reference as an integer), you first need to
create a new "temporary" column, transfer all the data from the
"old" to the "temporary" column, drop the "old"
column, add the "new" column you want to create, copy the data from
the "temporary" column to the "new" column, and then drop
the "temporary" column. Easy, isn't it!
When modifying a table, you may find that you
are not permitted to "drop" a column.
- If a column is used in a stored procedure, or
a calculated field, the column is labeled as "having a dependency".
- You will have to delete all the
"dependencies" before you can delete a column - then re-enter the
dependencies you have just created. This is a good reason to get the table
structure right before you create large numbers of dependencies (if you
can).
There are some other Metadata statements that
are useful.
6.2.3 Generators
A Generator set (eg. of sql : CREATE GENERATOR VISIT_GEN; or SET GENERATOR VISIT_GEN TO 1; or DROP GENERATOR VISIT_GEN; ) is an Interbase mechanism to
generate a number count.
- You can set it to, say, 1. Each time you then
call the Generator, Interbase tells you what the current number is and then
increases if for you automatically.
- To call the Generator, you will probably want
to set up a simple stored procedure that you can call either from Interbase or
from a Delphi program (eg. of sql : CREATE procedure
NEW_VISIT RETURNS (NEW_REF integer) AS BEGIN NEW_REF=gen_id(visit_gen,1);
END^ - note the strange syntax of semi-colon and ^ symbol. See the
explanation of importing SQL scripts into Interbase for an explanation).
- You can then get the next number in a
sequence by calling the stored procedure New_visit, and the new reference will
be returned in the output parameter "new_ref".
6.2.4 Triggers
A Trigger set (eg. of sql :
Create trigger set_emp_no for employee before insert as
begin new.ref=gen_id(employee_gen,1); end^ ) instructs Interbase to
carry out the statements in the Trigger statement automatically (ie. without be
called explicitly) every time data in the database is updated. The example
statement will set the "ref" field in a record immediately before it
is inserted to the table "employee", with the next value in the
generator "employee_gen" .
- The Trigger set allow you to set up automatic
procedures either before or after any of an insert, update or delete to a
table. Vastly useful if you want to force a series of updates on the basis,
say, of a delete. Hugely frustrating if you also want use the new reference
number just created within the Delphi program that set the Trigger in motion.
For this reason, using Triggers to create unique reference numbers may be less
preferable than calling a stored procedure to return the new generator number,
and including the new reference number with the other information of the record
that is to be inserted to the table.
- A warning about Triggers - the
"rollbacks" do not work quite as you might expect. Generators, for
example, will not be set back to their earlier value, even within a rolledback
Transaction series.
6.2.5 Indexes
An index set (create and drop) is used to
create indexes to speed up searching and ordering by the indexed column. (eg.
of sql: Create unique ascending index SurnameX on person
(lastname, firstname, password); or Drop index
SurnameX; )
- If an index is unique, you can not enter a
second record with the same details as the first. Useful if you want to
restrict entries to just one, say, password.
- Breaching this rule will be fed back to a
program as an error message which you will have to trap in Delphi if you want
to provide a graceful message to the program user. In certain situations,
therefore, you may prefer to check for the existence of a particular record
from within Delphi before passing the record through to Interbase to
update.
6.3 Data manipulation - SQL
6.3.1 Insert
You insert a record to an Interbase table (or
tables) with the Insert command (eg. of sql: INSERT INTO
Nominal_Code (ref,nl_Code, nl_Name, BalanceSheet_Category,Report_Category)
VALUES (1, 100, 'Shares', 'b', 'fa'); ).
6.3.2 Update
You update a record that already exists within
an Interbase table (or tables) with the Update command (eg. of sql:
UPDATE employees SET new.salary=old.salary*1.1 ).
With the update statement, you would usually want to control the situations
where you update a record, such as update salary only where the employee has
been awarded a "satisfactory" rating or more. For control over
whether you carry out one Update command depending on whether a given
circumstance is met, see the explanation of the Where
statement.
With other update statements, you want even
more control using an IF statement, such as updating a stock record if a stock
item has been ordered from a customer and can be fulfilled, or inserting a
stock re-order item for a supplier in another table if the customer order can
not be fulfilled. To use the more precise control of an IF statement where you
want to select from more than one action in different circumstances, you would
need the facility of grouping a number of SQL statements together using a
Stored Procedure.
6.3.3 Delete
You delete a record that already exists within
an Interbase table (or tables) with the Delete command (eg. of sql:
DELETE from employees ). WARNING! This command
deletes all records in the table employees, and there are no warnings. With the
delete statement, you would usually want to control the situations where you
delete a record, such as delete a record from the employees table only where
the employee reference is the given number. For control over whether you carry
out one Delete depending on whether a given circumstance is met, see the
explanation of the Where statement.
With other delete statements, you want even
more control using an IF statement, such as deleting a fulfilled sales order if
the order has been copied to the archive file, or doing the archive if not and
then deleting the record. To use the more precise control of an IF statement
where you want to select from more than one action in different circumstances,
you would need the facility of grouping a number of SQL statements together
using a Stored Procedure.
6.3.4 Select
The Select statements are very powerful, and
there are a few more things you may want to achieve, ranging from the simple to
the very advanced.
- You pull out a record set from within an
Interbase table (or tables) with the Select command (eg. of sql:
SELECT ref, first, last FROM employees ).
- You specify the fields you want to see (such
as ref, first and last in the example). If you want all fields, you can use the
'*' character (eg. of sql: SELECT * FROM
employees; ).
- On occasions, you want to pull out a field,
but report it in a more user friendly way. You can achieve this using the
"AS" word (eg. of sql: SELECT first, last AS
surname FROM employees; - this will report the result set with all first
names headed as "first" and all last names headed as
"surname").
- You may also want to join fields together
into one field, such as report a name as the merging of "first" and a
space and "last". You can achieve this using the '||' characters (eg.
of sql: SELECT ref, first || ' ' || last AS FullName,
salary FROM employees; ). We have found this facility particularly
useful when creating a result set that will be displayed within an Internet
application, where you want a field in the result set to be a link to, say, a
document or image. You can achieve this effect by pulling out a column, say,
surname, which merges the html with the database fields (eg. of sql:
SELECT first, '<a
href="http://webserver/application.exe/employeelink?Ref=' || ref || '>'
|| last || '</a> AS surname from employees; ).
With the select statement, you would usually
want to pull out only certain records, such as select all records from the
employees table where they are in a given department. For control over the
content of selected records depending on whether a given circumstance is met,
see the explanation of the Where statement.
6.4 Great SQL tips
6.4.1 Where
The data manipulation sql set (insert, update,
delete and select) all work in conjunction with the "where" clause.
The "where" clause allows you to limit the sql command to doing just
what you tell it to do. An example is selecting out all records from employees,
but only if the employee is in the IT department, or updating all salaries, but
only where the employee has been evaluated as "satisfactory or
better".
- The "where" clause within the SQL
command allows you to compare any field of any table with a given value. The
value can be a number, a string, a date or another field. The comparison can
be=, <, > or various others (eg. of sql : select
ref, first, last, salary, department where department='IT' and salary >
125000 or update employees set
new.salary=old.salary + 55000 where department='IT' and old.salary < 95000
) ).
- If you are comparing strings, you often want
to use a Wildcard character ("*" in windows). The Interbase
equivalent is '%' (eg. of sql: select ref, first, last,
salary, department where last like 'Smi%'; to pull out anyone with the
Surname starting with Smi and followed by anything, such as Smith, Smithe,
Smile ect.) You can also use the '%' before a letter series (eg. of sql:
SELECT ref, first, last, salary, department FROM
employees WHERE department like '%IT%'; - this would pull out any
department with IT anywhere in the string). Note that you have to use the LIKE
word. Using the "=" symbol would not work, because Interbase would be
looking for the character string that was exactly equal to '%IT%' (ie. the '%'
would be treated as a character instead of a wildcard)
- You can use the AND word to limit the result
set to those that meet both (or all) of the AND statements (eg. of sql:
SELECT * FROM employees WHERE department='IT' and salary
<=90000 and salary >=20000; )
- You can also use the OR word to expand the
result set to those that meeting any of the OR statements (eg. of sql:
SELECT * FROM employees WHERE (department='IT') OR
(salary <=90000 AND salary >=20000); ).
- Note where you mix both the OR word and the
AND word that you should use brackets to be clear about what you intend. Each
statement within the bracket is evaluated before any statement outside a
bracket. If the statement in the example above has been without brackets (SELECT * FROM employees WHERE department='IT' OR salary
<=90000 AND salary >=20000;), Interbase would not have know
whether the statement meant pull out everyone within the IT department and also
pull out anyone with a salary between 20K and 90K (as intended in the example)
or SELECT * FROM employees WHERE (department='IT' OR
salary <=90000) AND (salary >=20000);, which is everyone who is
either in the IT department or who has a salary less than 90K, but who also
must have a salary > 20K. The latter example would preclude people in the IT
department who earn less than 20K, which is clearly not intended.
- You may want to compare with one value does
not equal another. There are several alternatives, but the one method that we
have found infallible is to use the NOT word in front of the statement to
negate (eg. of sql: SELECT * FROM employees WHERE not
(department like 'IT%' ); ). If you try another method (eg. WHERE
department <> 'IT') within a larger SQL command, and items do not get
reported as you would expect, remember the slightly uncomfortable solution
above.
- There are (many) times where you would want
to use parameters for the comparison values. This would allow you the facility
to set up a SQL statement which you could amend at run time (eg. which does not
work: SELECT * FROM employees WHERE salary > :salary
; ). You would want to be able to "invoke" the select command
substituting the :salary parameter with, say, the amount input by a user. To
achieve this result, can use two alternatives. Either set up a
Stored Procedure, which does take
parameters, or generate the SQL within Delphi at run time using one of the
Interbase components.
- In all the above examples, we have compared a
field with a fixed value. You can also compare the value with another field
within the database. You may want to select all sales where the customer's
county is different from the county of the department who serves that customer
(eg. of sql: SELECT Customers.Ref FROM Customers,
Departments WHERE Customers.Department_ref=Departments.Ref AND not
(Customers.Postcode=Departments.County); ) . This example joins two
table together, for which you may want to see SQL
: Multiple Tables
- A very powerful extension to comparing a
field with another field is the ability to compare a field with a result set
from another sql command (what?!?). Say you wanted to select all records from
employees in the IT department where the salary was less than the maximum
salary being paid to a Secretary. The WHERE clause can compare the Salary of
the employee with the result set of a separate select statement that pulled out
the maximum salary of an employee where the department was 'Secretary' (eg. of
sql: SELECT ref, first, last FROM employees WHERE
department='IT' AND salary < ( SELECT max(salary) FROM employees WHERE
department='Secretary') ;). In this example, the bracketed SELECT
statement returns a single result. (How? See Getting
Statistics from the Database). A quick warning - if the sub-select
statement is complicated or, itself, uses another sub-set, this can be a hugely
time consuming process. The solution, if you find the time delay is
unacceptable, is to use Stored
Procedures
- Another very powerful extension to comparing
a field within another field, is the ability to compare a field if it is within
a result set of another SELECT statement, using the IN word. Say you wanted to
pick out all accounts that were within the profit and loss account, and the
profit and loss accounts were recorded in a separate table. The WHERE clause
would compare the Account_ref of the accounts with the list of Account_refs
from the master table that were profit and loss items (eg. of sql:
SELECT sum(account_value) FROM accounts WHERE account_ref
IN (select account_ref from Accounts_Master where Account_type='Profit and
loss'); ). A quick warning - if the sub-select statement provides a long
result set, this can be a hugely time consuming process. The solution, if you
find the time delay is unacceptable, is to use
Stored Procedures
- The "Where" statement applies
equally to the Update, Delete and Insert statements.
- There are a number of other comparators that
can be fun, such as "Starting with", "Containing" and
others. These are explained in more detail in the Interbase SQL manuals.
6.4.2 Order by
You sort a result set into a desired order
with the Order by statement.
- You can Order by any field or fields within
the original table (eg. of sql : SELECT * FROM employees
WHERE department='IT' ORDER BY last; )
- You can Order by more than one field at a
time (eg. of sql: SELECT * FROM employees ORDER BY last,
first; ).
- You can select restricted fields, and order
by a field that is not displayed, if you ever find this to be of benefit (eg.
of sql: SELECT ref, first FROM employees ORDER BY
last; )
- The default sort order is to sort in
ascending order, but you can define it as descending (eg. of sql:
SELECT * FROM employees ORDER BY last desc, first
asc; - where desc is short for descending and asc is short for
ascending)
If you have defined an index which puts a
result set in the same order as the Group By statement, Interbase will
automatically detect this and will use the index to speed up the search and
sort. You can not use the name of the index within the Group By
statement(!)
6.4.3 Multiple tables
You will often want to select data which
involves more than one table at a time. You may have data in two tables which
you want to combine in one report. An example is where you keep employee
details in one table, and address of everyone on another table, and you want to
report the names and address of all employees.
- To use multiple tables, you can refer to them
in the SQL statement. The tables must be "joined" in a logical way.
Within an Employees table, for example, if the address is not stored directly
in the Employees table, then there must be a reference in the Employees table
to the record in the Address table, to be able to match the records. The Select
statement must link the two tables with the "WHERE" statement. (eg.
of sql: SELECT employees.first, employees.last,
address.address1, address.address2, address.county, address.postcode FROM
address, employees WHERE employees.address_ref=address.ref; ).
- You can create shorthand for the full name of
the table to save typing, by "defining" the shorthand immediately
following the table's name (eg. of sql : SELECT e.first,
e.last, add.address1, add.address2, add.county, add.postcode FROM address add,
employees e WHERE employees.address_ref=address.ref; )
- One word of warning - when you
"join" tables (as above), Interbase dumbly does what you instruct. If
there is a unique reference within the address table, then there will only ever
be one record from the address table matched with any one employee record
(since the employee record can not have more than one Address_ref in its
Address_ref field). But, if you ask interbase to join two tables where there
can be many matches for each record, you will get a huge number of records
returned.
If, for example, you had "joined" the tables with the statement
"select * from address, employees WHERE employee_ref <
address_ref", then Interbase would have returned a result set whose fields
were every field from both the address and the employee table. The number of
items in the result set would have been huge. Interbase would start with the
address table. It would then look at the first employee record and see if the
condition were matched. If so, the combined record would be added to the result
set. Then, with the same address record, it would then look at the next
employee record. Again, if the condition were satisfied, the combined record
would be added to the record set. And so on until the end of the employee
table. At this point, there may be several hundred records. But Interbase would
only have completed the first record of the Address table. It would then carry
out the same process with the second address record, by which time another few
hundred records may have been added to the result set. Then Interbase would
have gone on to the third address record, and then the fourth and so on, until
the end. If there were several hundred records in both the Employees and the
Address files, it is easily conceivable the result set would have exceeded half
a million.
The symptom of an excessive join is Interbase taking several minutes to several
hours to process the query and perhaps, before it is able to finish, running
out of memory. The solution is to check the SQL statement by trying to
reperform the computer logic, to ensure that the join only ever matches one
record in one table with one record in the other table, unless you specifically
want there to be a many-to-one relationship.
- The "join" above is known as an
"inner join" because it returns only records that are in BOTH tables.
This means that a record in the employee table that has a matching record in
the address table will be reported, but a record in the employee table without
a matching record (such as the employee's address has not yet been entered to
the system), is not reported.
There are many times where you want to report all records in one table, and the
only the matching information from the other table. An example would be
reporting all work in progress and, if an invoice has been raised, including
the invoice information, but still reporting the work in progress even if the
invoice has not been raised.
The join that achieves this is an "outer join". Inconsistently, the
outer join requires you to use an additional word "on" to define the
linking reference fields, in place of the "where" clause in the
examples to date (eg. of sql: SELECT wip.ref,
wip.job_name, wip.value, inv.amount FROM work_in_progress wip LEFT OUTER JOIN
invoices inv ON inv.wip_ref=wip.ref WHERE wip.completed_flag='False'; -
note using joins does not prevent the use of the "WHERE" clause, but
it does require the ON clause ). The word LEFT or RIGHT is used to tell
Interbase which of the two tables is the base for which all records will be
reported even without a match. In the example, the work_in_progress table is on
the LEFT of the statement, and this is the table that you want to be reported
in full, even where there is no match.
- The OUTER JOIN statement is explained above.
The prior examples of inner joins use the WHERE clause to achieve the match.
You can also achieve the same result using the INNER JOIN statement (eg. of
sql: SELECT e.first, e.last, add.address1, add.address2,
add.county, add.postcode FROM address add, employees e ON employees.address_ref
INNER JOIN address.ref; )
- There are often occasions where you will want
to "chain" more than two tables together. You can chain joins
conceptually joining two tables together, then joining the result set with the
third table (and so on if you have more than three tables to join). This is
useful where you are reporting information from more than two tables in one
report, such as reporting details from our ebooks site which reports the name
of a reader (from the address table), the date of the purchase (from the order
table) and the name of the book bought (from the book table) (eg. of sql :
SELECT p.ref,p.first, p.last, o.orderdate, d.bookref,
b.booktitle FROM address p INNER JOIN orders o ON o.personref=p.ref LEFT OUTER
join order_details d ON d.orderref=o.ref LEFT OUTER JOIN books b ON
b.ref=d.bookref order by last, orderdate; - if you want to use a further
WHERE clause, it comes after the final ON statement, irrespective of which file
the restrictions apply to).
- There are often occasions where even this
level of combination is inadequate for what you want to achieve. Although
conceptually the second join works on the result set of the first join, there
is very little manipulation you can carry out prior to the second join. This
can be too limiting. An example of the sql constraints are where may need to
get the sales totals of each salesperson on which to calculate commission, you
need to report the result in descending order of sales totals. There is no sql
command you can use to Order on the results of a calculated amount. You will
come across several other circumstances where you need to carry out a function
(such as selection, grouping, sorting) on the results of another group.
The solution is to use Views or Stored Procedures, which you are then able to
chain together to your hearts content.
6.4.4 Multiple databases
Joining data between two tables all held
within the one database is useful. You may find the need to join data held in
two or more databases.
All rules that apply to joining tables from a
single database apply equally to joining tables from two separate databases.
The additional issues that are explained below relate to the need to
"login" to both databases which may have different usernames and
passwords, and the syntax SQL requires to understand to look in the two
databases.
- The connection issue is simple. For each
database you want to connect to, you must enter the username and password. One
method to achieve this within a single sql command is to achieve the
connections within the Delphi program. Create (or drop onto a form) two
separate Delphi Interbase TIBDatabase components, and fill in the details and,
if appropriate, the username and passwords. (How? See TIBDatabase components.)
- The SQL syntax requires the databases to be
within quotation marks, preceded by a colon (eg. of sql: SELECT own.NAME, own.OWNER FROM ":invDB:OWNER" own,
":crownDB:COMPANY" co WHERE co.REF=inv.COMP_REF - where invDB
and crownDB are the TIBDatabase names allocated in the Delphi program.) To
complete this example, fill in a TIBQuery component, filling in the SQL in the
SQL property of the TIBQuery component, and the query is ready to be called by
your Delphi program.
6.4.5 Views
Views are sql statements that are
"frozen" into a procedure within Interbase. Typically, but not
always, a View will return a result set. Once set up, the view can then be
called and used as if it is a Table. There are some restrictions (see below),
but the value comes from the ability to carry out a reasonably complex SQL
statement within a view, and then to use the View as if it were a table,
against which you can perform a separate SQL statement.
Views are a quirk of Interbase, so they have
to be set up within Interbase. You can set them up as follows:
- Create a SQL script file, which you will be
able to use to "import" the view into Interbase (Example? See the
attached file Tables.SQL, and look out for the "Create View"
statement
- Create the View, which attaches a basic SQL
statement to the instruction to Interbase to file the statement within its
Views Metadata (eg. of sql: CREATE VIEW Full_Name (iRef
integer,iDepartment varchar(30)) AS SELECT ref, first || ' ' || last as
EntireName, AddressRef FROM employee WHERE ref >=:iRef and
department=:iDepartment;- where the View is called "Full_Name"
and two input parameters have been defined as "iRef", which is an
integer and iDepartment which is a string. The output is the result set
generated by the "Select" statement.)
- Once a View has been created, it can be used
from any SQL statement (from Interbase or from a Delphi program as described in
earlier sections) as if it is a Table (eg. of sql: SELECT
* FROM Full_Name(27, 'IT') ORDER BY ref desc; - note that the View has
an input parameter, so when calling it, you have to include the parameter after
"Full_Name" in brackets. If there had been no input parameters, there
would have been no brackets after "Full_Name".
- You can create a view which uses the result
set of another view (and so on indefinitely). As with any SQL command, if the
first view has parameters, the second view must provide the parameter call
syntax (eg. of sql : CREATE VIEW Addressed_Full_Name
(iRef integer,iDepartment varchar(30)) AS SELECT * FROM Full_Name(:iRef,
:iDepartment), Address WHERE Address.ref=Full_Name.AddressRef ORDER BY
Full_Name.EntireName; - this is called as any other view. When Interbase
hits the "table" Full_Name within the SQL statement, it knows this is
a view, the second view is put on hold and the first view is run before
processing of the second view resumes.
- Views have a couple of limitations - if the
limitations prevent you from doing something you need to do, you should almost
certainly be using Stored Procedures. (How? See the Chapter on
Stored Procedures). One limitation
relates to using Views to update, insert or delete data. Views can be used for
this purpose, but generally only if there is no ambiguity in the SQL commands
to Interbase. If you use a view simply with a SQL command to Update a record,
using a where statement, the update should work fine. However, if your view has
changed the information from the form of a basic table, or if it is no longer
clear which table the information came from, then you are prohibited from using
a View for this purpose. For more details, see the Interbase manuals. Another
limitation is the inability to define an output parameter.
6.4.6 Statistics
Reporting statistics from data within the
database is easy. Used in conjunction with Views and/or Stored Procedures, they
provide you with the ability to provide almost any report that may be
requested.
- The basic statistics come from a Select
statement, using words like "Sum", "Count" and
"Avg" (eg. of sql: SELECT count(ref), avg(VAT),
sum(Sales_Value) FROM sales; - this provides the total number of
transactions, the average of the VAT and the sum of the Sales_Value.)
- The above example provides one total only
for the entire database. If you wanted to group a number of like items together
and provide the sum for each of the groups, you can achieve this by using the
GROUP BY statement. For example, you may have a table of Sales, and you want to
know how much are the sales by product line (eg. of sql: SELECT product_line, sum(sales_value) FROM sales GROUP BY
product_line; ).
- You can also get sub-totals of sub_totals
using the same technique. If you wanted to group the total sales value of each
product lines sold each sales person, you can do so (eg. of sql :
SELECT sales_person, product_line, sum(sales_value) FROM
sales GROUP BY sales_person, product_line; .)
- You can use any normal SQL statements within
the statements, such as ORDER BY, and WHERE clauses.
- If you wanted to use the results of the
Statistics for a further action, such as sorting the results by descending
value of the sum of the sales_value, you will fund you are not permitted to do
so within the one sql statement. The simple solution is to create a View with
the statistics, then use the View within a second SQL statement to carry out
the action that you wanted. (How? See Views)
6.4.7 Sundry
There are several other functions that may
help you. They include:
- If you want to change one datatype to
another, use the Cast function. For example, you may want to convert a
reference defined as an integer to a string to consolidate within HTML (eg. of
sql : SELECT cast(ref as varchar(20)) FROM sales WHERE
Payment_flag='False'; )
- String comparisons are case sensitive.
Programmers and Users are not always consistent when obtaining or entering data
(eg. Names) that may be used within case sensitive comparisons (eg. passwords).
Interbase has a function UPPER, which converts a field into its Upper case
equivalent. This allows comparison of the upper case of one field with the
upper case of the other, and removes the case sensitivity problems, if this is
what you want to do (eg. of sql : SELECT ref, name FROM
employee WHERE upper(first)=upper(:InputParameter); ). Curiously, there
is no LOWER equivalent in Interbase.
- Interbase has a very limited number of
functions (such as CAST and UPPER). There are many occasions you would want
these functions extended (such as stripping out blanks, selecting out the left
three characters of a string, using a MID function, formatting Currency strings
and SIN functions). There are a large number of functions that perform these
tasks, and which are available free. Check out the Interbase manuals for
External Functions which will give you a pointer to where to get the suite of
External Functions (also called User Defined Functions), and how to instruct
Interbase where to find the code for them. When you have "imported"
an External Function, you can use it in the same way as UPPER and CAST above,
using parameters where required by the Function (eg. of sql :
SELECT F_FixedDecimalPoint(PRICE,2) FROM Sales; -
where FixedDecimalPoint is the imported user defined function, Price is a field
in the Sales database, and "2" is the number of decimal places to
which the number should be rounded).
- Date handling in Interbase is primitive. You
may have to use the US format for date entry (ie. mm/dd/yy, instead of the UK
configuration of dd/mm/yy), when entering the date as a string. Interbase
stores the Date as DateTime. This causes huge problems when trying to pull out
transactions on, say, a particular day, since the date stored (which includes
the time tagged to the end), will not necessarily equal the date you have input
for comparison. There are several solutions to date problems. One is to specify
time consistently when you enter a date (messy), another is to use a User
Defined Function to strip out the time element of a date field (for which you
need the UDF and you need to import it), another is always to use dates within
a range (such as 'DateField >=1/1/2000 00:00 and DateField <='1/1/2000
23:59', in place of 'DateField=1/1/2000').
Chapter 7 - Stored Procedures
This Chapter is written on the basis that the
Interbase SQL file attached to this guide (StoredProcedures.SQL ) are open
within a simple text editor (such as Notepad or Delphi). If you read the guide
in conjunction with the SQL files, the points below should be simple to
follow.
7.1 What you can do with Stored Procedures
Stored Procedures are functions that are
written and stored in Interbase, that carry out a series of SQL commands. They
are fast, powerful and can be used with both input and output
parameters.
- You can define a Stored Procedure to return
both a Result set (eg. a list of sales items), and also some output parameters
(eg. the total of the sales items output). Used in conjunction with the
TIBStoredProcedure component, you have access to both the Result set and also
to the output parameters.
- If a Stored Procedure returns just a result
set, you can call it from either Delphi or from Interbase, as you would any SQL
statement, using the Stored Procedure as if it were a table. If there are input
parameters, you put the parameters in brackets immediately after the Stored
Procedure name (eg. of sql : SELECT * FROM
sp_Sales_Report('1/1/2000','1/31/2000','IT');- where the Procedure is
called "sp_Sales_Report" and three input parameters have been defined
(a start date, and end date and a Department name). The output is the result
set generated by the "Select" statement.)
- Alternatively, you may use a
TIBStoredProcedure component from Delphi, or the "Execute Procedure"
command Interbase. If there is not a result set, but a single output parameter
(or parameters), or if the Stored Procedure does not return any results (eg. on
Inserting or updating a record), you must use the TIBStoredProcedure component
from Delphi, or the "Execute Procedure" command from
Interbase.
7.2 The Syntax
If you get the Stored Procedures syntax wrong,
there may be two consequences. Firstly, the Stored Procedure may be imported
successfully into Interbase, but fail to operate properly. The second is that
the Procedure may not be imported successfully. There are few programs that
will cause you so much frustration as you may find trying to work your way
through the error messages returned when a Stored Procedure fails to import
successfully. To save yourself a vast amount of time and frustration, you may
want to read the following Syntax concepts carefully.
- You must create a SQL script file. (How? See
Creating a SQL script)
- Creating a procedure uses the "Create
Procedure" command. If you have already successfully created a procedure
and you want to amend part of it, change the command to "Alter
Procedure", then use exactly the same code as if you had just deleted the
procedure and you were now creating it from scratch.
Once the script is in place, the Stored
procedure has a number of distinct parts:
- Input parameters, if there are any, come
within brackets immediately after the name of the procedure. Each Input
parameter must have a data type, and each parameter is separated by a comma.
The last parameter is not followed by a comma, but by the closed bracket sign.
(eg. of sql : CREATE PROCEDURE sp_Salespersons_summary
(Salesperson_ref integer, Start_date date, End_date date) - Note: no
semi colon goes here
- Output parameters, if there are any, start
with the RETURNS word, and have the same rules as the Input parameters. Note
that the Output parameter must not have the same name as the Input parameter.
If there is any possible conflict, use any resolution technique you are
comfortable with, such as preceding the input parameters with the letter
"i" (eg. of sql : CREATE PROCEDURE
sp_Salespersons_summary (iSalesperson_ref integer, iStart_date date, iEnd_date
date) RETURNS (oSales_value decimal(10,2) , oName varchar(50) )- Note:
no semi colon anywhere in sight
- Next you must place the word "AS",
irrespective of whether you have defined input and/or output parameters. (eg.
of sql : CREATE PROCEDURE sp_Archive_Sales AS
...)- Note: no semi colon anywhere in sight. If there are input and/or
output parameters, they go between the word PROCEDURE and AS
- The next part is the place where you can
define a variable for use within the stored procedure. Define each variable
with its datatype. Again, be careful to avoid name conflicts. Each variable is
defined independently of another (eg of sql: CREATE
PROCEDURE sp_Archive_sales AS declare variable vRef integer; declare variable
vSalesTotal numeric(15,2); declare variable vCustomerRef integer; - Note
that there is a semi-colon after each declared variable.
- Then comes the part where the data set is
Selected, on which the Stored Procedure does its work. It comes within a BEGIN
.. END statement. Since there are other Begin..End pairs coming, this guide
refers to this BEGIN .. END pairing as the Procedure's Workframe. The
Workframe's "END" word would be followed by a semi-colon to denote
cessation of the overall Stored Procedure, but for the fact that there are
semi-colons within the Stored Procedure which are used to denote the end of a
statement. So there is a separate symbol used to show that the Stored Procedure
has terminated, which you define in your SQL script file. The example files use
the symbol '^', but another symbol commonly used to denote the end of a Stored
Procedure is '!!' (eg. of sql: CREATE PROCEDURE
sp_Archive_Sales AS BEGIN END^ - This stored procedure does nothing, but
is valid.)
The concept of the WorkFrame (as defined in
the preceding paragraph) is really very powerful. Once you have defined the
input, output and variable parameters, you can then fill in any manner of SQL
statements you desire. The procedure's WorkFrame are typically separated into
two distinct parts
- The first part (which is optional) selects
out a set of records that the Stored Procedure can then work on, one record at
a time. One of the key problems with basic SQL statements it that they issue
commands which work at table level, and they are not capable of working at
record level. With conventional SQL, therefore, you are able to carry out or
not an action on a record depending on the "Where" conditions, but
you are unable to carry out a different action depending on the status of the
record itself. The first part, therefore, defines the data which the second
part will work on, one by one.
The Syntax should be familiar by now, but with some modifications. To instruct
Interbase that the Procedure will work on the data one at a time, the word
"FOR" precedes the Select statement. The Select statement is then
identical to any other Select statement, with the ability to use input
parameters, by preceding the name with a colon to indicate it is a parameter (
eg. of sql: FOR SELECT Ref, Name FROM employees WHERE
ref=:iRef - where :iRef is an input parameter).
But to allow the Procedure to work on each record individually, you have to put
the data selected for that record ("ref" and "name" in the
above example), into a variable that you can refer to later in the Procedure.
The variable can be either an output parameter or a declared variable. If you
put the data into an output variable, it will be included within the record
written as part of the result set, when the Procedure has finished processing
the record. If you do not put the data into an output variable, you will have
to do so later on in the Procedure, or "null" data will be written.
To put the result of each record into a variable, use the INTO work (eg. of sql
CREATE PROCEDURE sp_Salespersons_summary (iRef integer)
RETURNS (oRef integer, oName varchar(50) ) FOR SELECT Ref, Name FROM employees
WHERE ref=:iRef into :oRef, :oName - Note, no semi-colon anywhere in
sight in this section so far, although there would have been if an internal
variable had been declared)
- The second part (which is mandatory if there
is a 'FOR SELECT' statement in the first part) does something with the data
selected above, one record at a time.
The section starts with the word DO, and has its own "begin .. end"
pair. Note that this pair does not have a semi-colon to denote its termination.
Within the "begin .. end" pair, you can then put in as many or as few
sql statements as you desire. There are also a few simple programming
statements you can use (such as vSalesTotal=0; ).
Each programming statement is terminated with a semi-colon.
Parameters and variables are accessed with a colon in front. Watch out for one
pitfall. Sometimes a variable can not be accessed if you precede it with a
colon, whereas at other times, it can not be accessed without the colon in
front(!), such as vSalesTotal=:vSalesTotal + 10;
). We have found that trial and error has always worked, once you are aware of
the potential problem.
Each statement can be a simple programming statement (such as
:outputTotal=:outputTotal +
:inputTransactionAmount;), or a SQL statement (such as
UPDATE assets SET num=num + :jNum, cost=cost + :jCost,
val=val + :jVal, latestDate=:latestDate where ref=:jAssetRef; - where
assets is the name of a table, num, cost, vale and latestDate are fields in the
table assets and :jNum, :jCost, :jVal, :latestDate and :jAssetRef are all
variables into which the Select statement placed its data.)
When the Stored Procedure has finished processing the record from the select
statement and is ready to move onto the next, the instruction telling the
Stored Procedure to add the output data to the record set that will eventually
be available to the Delphi (or Interbase) program, is "SUSPEND;". Note the semi-colon. SQL statements can
continue to operate subsequent to the SUSPEND; statement, but it would seem
sensible for the SUSPEND; statement to be the last statement. Note, too, that
the SUSPEND; statement is required, even if there has been no processing of SQL
statements, or the data selected in the SELECT statement will not be written to
the result set (eg. of sql : CREATE PROCEDURE
sp_Salespersons_summary (iRef integer) RETURNS (oRef integer, oName varchar(50)
) BEGIN FOR SELECT Ref, Name FROM employees WHERE
ref=:iRef into :oRef, :oName do begin suspend;
end END^ )
The Procedures.SQL file is intended to provide
several examples of use of Stored Procedures in a practical environment. The
Procedures can almost all be written better, but they are designed to
illustrate concepts and use of Stored Procedure.
Chapter 8 - Guide updates and limitation of
liability
This guide has been inspired by the move of
Interbase towards Open Source. Interbase appears to have a strategy to make
Interbase to Linux what DB was to DOS and the Paradox format/BDE was to Delphi.
It is already a very powerful, efficient and compact program. The trend towards
Open Source hold out the promise of even more power and ease of use for
Developers, and continuing adaptation to developments in IT.
8.1The purpose of the guide
Interbase is available on the Linux. Delphi is
being ported (as at 1 July 2000) to the Linux platform.
The combination of Delphi and Interbase is a
powerful one. The combination may well be the first mature offering for mass
development within the Linux platform. The purpose of this book is to reduce
the learning curve for anyone wanting to have access to this powerful and free
resource.
8.2 Updates and correction of errors
If any readers find errors, or if there are
any areas of Interbase or Delphi's interaction with Interbase that are still
difficult to fathom from the manuals that exist, for which this guide may be
useful, please email your comments to gnc@softwaredesign.co.uk. This guide
comes in HTML format and is available free from Interbase. Based on comments
fed through, the guide will be updated regularly and the latest updated version
will be available from www.ebooks.uk.net for a nominal sum, under the title
"Delphi to Interbase in 15 minutes" (the ebooks site itself is driven
by Delphi and Interbase).
8.3 Limitation of liability
This guide is provided within the spirit of
the Open Source initiative. No representations are made that any of the
material above is accurate or complete. No liability can be accepted by the
authors or by the Interbase Corporate for any errors or omissions in the
guide.
8.4 The UK Borland User Group
The initiative to support the Interbase Open
Source for Delphi programmers comes through the UK Borland User Group. The
group provides support for users of Borland Products, including technical
support and regular technical updates, information and training. For anyone
wanting more details of the group, check out the Web site at
www.richplum.co.uk , tel 01980 630032
(or from outside the UK : +44 1980 630032)
Chapter 9 - Status of this guide
This guide is in draft - dated 17/7/00. It is
free, and will be free. It is part of the IT self-help concept that if we all
help each other, we will all be better off. Download the SQL files by clicking
on the links below.
This guide is being written in conjunction
with Interbase Corporation, and the UK-BUG (Borland User Group).
This draft is aimed at programmers. Please let
us have any feedback you have including:
- Did you like the layout?
- Were you up and running within 15
minutes?
- Tell us any technical errors
- Tell us any omissions that you think would
help
- Any other comments? - All gratefully
received
Send comments by EMail to Nigel Cohen at
gnc@softwaredesign.co.uk . If
vitally important that you speak directly, call (44) 1628 660665.
SQL file downloads (zipped) can be accessed
from the www.softwaredesign.co.uk web site by clicking on the links below. The
first two files below can be found additionally on the Delphi Magazine disk
accompanying the Delphi Magazine December 2000 issue:
- SQL files - DelphiToInterbaseSQL.zip
(4K)
- Delphi program files -
DelphiToInterbasePAS.zip (9K)
- Database file
(IBDemos.gdb) - IBDemos.zip (756K)