Delphi to InterBase in 15 M...

Delphi to InterBase in 15 Minutes

file:///C:/Users/TECBMFRP/AppData/Roaming/Mozilla/Firefox/Pro...

IBPhoenix Development

General Information Home Who Are We? Contact Us What is Firebird? IPL License Events

Blogs Paul Beach Philippe Makowski Dimitri Kuzmenko

Other Languages www.ibphoenix.cz www.ibphoenix.fr

Services Service & Support Training & Consulting

Products Developer CD Deployment CD IBReplicator dbFile IBAnalyst IBFirstAid IBBackupSurgeon IBUndelete FBScanner Firebird Book Firebird Goodies Firebird Appliances

Shop Online IBPhoenix Shop

Development Firebird Development Vulcan Development General Documentation Function and Design Other Links

Community Firebird Conference 2006 Firebird Conference 2005 Get Involved Member Websites Projects News News Archive Groups, Lists, and Forums

1 de 42

06/04/2010 18:22



Powered by Firebird Users of Firebird Publications History Web Hosting

Downloads Main Downloads InterBase® Contributed

Research Research Center Articles Examples FAQs

Search Search KnowledgeBase Search Web Site Search Mers List

Partners Partners This Site Uses:

Delphi to InterBase in 15 Minutes Contents 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

2 de 42

06/04/2010 18:22



Status of the guide Chapter 8 - Guide updates and limitation of liability The text below references several demo files. These can be downloaded by clicking on these three links: DelphiToInterbasePAS.zip DelphiToInterbaseSQL.zip IBDemos.zip

Chapter 1 : The 15 minute steps 1.1 Setting up InterBase - 10 minutes Step Step Step Step Step

1.1 1.2 1.3 1.4 1.5

-

Create the database Create the tables Create the indexes Populate the tables 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 linked 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: 1. Open IBConsole (Problems? Go to Using IBConsole) 2. Click "Database, Create Database" 3. Enter the Database path name and database name

3 de 42

06/04/2010 18:22



(Networks? See Database locations) 4. Enter user name and password (Special considerations? Go to creating a database in IBConsole) 5. 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: 1. Open "Tables.SQL" in any text editor (eg. Notepad, Delphi) 2. Change the file location within the "Connect" statement to the location to which you saved the Database 3. Change the "user" and "password" to the username and password used to create the database 4. Change the Table layout to the layout you want. (Want help with Datatypes? See InterBase Datatypes) 5. Save the file (Latest changes not used by InterBase? See Creating tables - Tips ) 6. In the program IBConsole, click "Query, Load script" 7. Find the SQL file you saved, and click "Open". 8. Comment out the "Connect ... " statement (How? See Comments paragraph in Chapter on SQL) 9. Click "Query, Execute" 10. 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.

4 de 42

06/04/2010 18:22



11. If successful - you will be told (well done!). (Failure? See Handling SQL script errors)

2.3 Create the indexes Background Indexes allow InterBase to locate data (dramatically) more quickly. An index has two key components to it - the field(s) that you will want to search on and whether the field(s) are unique (e.g. a Reference number will probably need to be unique, but you may well need to accommodate several people sharing a birth date or a last name). One particular type of index that is usually needed is an index on the Field(s) which uniquely identify a record within a table (e.g. 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 1. Open your "table.SQL" file. 2. 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) 3. If you have already created your table, see Creating indexes - Tips Creating other Indexes 1. Open the "Indexes.SQL" file 2. 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

5 de 42

06/04/2010 18:22



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: 1. Open "TestData.SQL" in any text editor (eg. Notepad, Delphi) 2. Change the file location within the "Connect" statement to the location to which you saved the Database 3. Change the "user" and "password" to the username and password used to create the database 4. Amend the test data. (Why this format? See InterBase SQL reference guide - "Insert" commands) 5. Save the file (Latest changes not used by InterBase? See Handling SQL script errors ) 6. In the IBConsole program, click "Query, Load script". 7. Find the SQL file you saved, and click "Open". 8. Comment out the "Connect.." statement (How? See Comments paragraph in Chapter on SQL ). Click "Query, Execute" 9. 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. 10. 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

6 de 42

06/04/2010 18:22



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: 1. procedures that update the Database's Metadata (What's that? See Create the database). 2. 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: 1. accept input parameters (if there are any), provide formatted output parameters giving single item output (such as the total of relevant sales invoices), 2. provide formatted output parameters (if there are any) giving a result set (such as every invoice detail that corresponds with an invoice reference), or 3. 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

7 de 42

06/04/2010 18:22



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" linked 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 1. Connected - leave as false. Set to true when ready to see if the component is set properly. 2. Database name - Fill in the file location and name (File can't be found? See Database locations) 3. Default transaction - See TIBTransaction component below 4. Login prompt - If you want users to login, leave as false. If not, set to true AND see step c. 5. Params - set if step c. is not enough Double click on the component for the Property editor 1. Connection - leave as "local" if InterBase is running on your machine. Otherwise, set to "remote" and see InterBase Network issues 2. 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. 3. Password - Enter the Password used to create the Database (but note points relating to Login prompt) 4. 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. 5. 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) 8 de 42

06/04/2010 18:22



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: 1. All defaults can be accepted 2. In the TIBDatabase component, set the Default Transaction to the name of the TIBTransaction just created.

9 de 42

06/04/2010 18:22



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: 1. Pop a TIBTable component onto the form (from the InterBase tab) 2. Set the Database property to the TIBDatabase component's name 3. Set the Table property to the Table you want to display 4. Pop a TDataSource component onto the form (from the Data Access tab) 5. Set the Dataset property to the TIBTable component's name 6. Pop a data aware control, such as a TDBGrid (from the Data Access tab) 7. Set the Datasource property to the TDatasource component's name 8. Pop a TDBNavigator on the form (from the Data Access tab) 9. Set the Datasource property to the TDatasource component's name 10. Set the TIBTable's "Active" property to True 11. Save and run the program

Chapter 4 - Practical issues 4.1 4.2 4.3 4.4 4.5 4.6 4.7 4.8 4.9

Creating tables - Tips Creating indexes - Tips Opening and closing a Database from Delphi - Tips Database locations Network issues IB Console - Running SQL files Creating a SQL script file Handling SQL script errors 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". 1. Every comma and semi-colon is significant. Watch for inadvertent deletions. (More detail? See Creating a SQL script) 2. If you are unsure whether you want a Primary Key, see (2.3) Create the indexes.

10 de 42

06/04/2010 18:22



3. Don't use referential integrity facilities at this stage 4. Keep the file structure simple. It is easy to make it more complex later. 5. 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. 6. 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". 1. 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 2. 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).

11 de 42

06/04/2010 18:22



In Windows, file locations follow the familiar pattern "c:generalInterBasefilename.txt". For a networked drive, (mapped, say to f: which is on the machine name "Machine2"), the pattern for the location is "f:generalintertbasefilename.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:generalInterBasefilename.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 WinNTSystem32driversetc 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

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

#Comments

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: 1. Open the Services file 2. 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.

12 de 42

06/04/2010 18:22



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 1. 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. 2. 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). 3. 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" 4. 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.

13 de 42

06/04/2010 18:22



5. 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). 6. To disconnect from a database, click "Database, Disconnect". 7. 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. 8. 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

14 de 42

06/04/2010 18:22



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. The zip file contains 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. 1. 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) 2. 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:generaldataIBDemos.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) 3. 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

15 de 42

06/04/2010 18:22



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. 4. The SQL script file is usually "closed" with the "COMMIT" statement, to commit to the database changes that occurred. 5. 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. 1. Create, import and execute a script - see Creating a SQL script 2. Error messages for sections that have not previously been

16 de 42

06/04/2010 18:22



reported - see Creating a SQL script, final paragraph 3. Unable to connect to the Database - see Creating a SQL script, CONNECT paragraph 4. Errors related to end of statements - see Creating a SQL script, TERMINATIONS paragraph 5. 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. 6. 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. 1. 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. 2. 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.

17 de 42

06/04/2010 18:22



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" 3. 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. Component

Function

TIBDatabase

The Database component

TIBTransaction

The Transaction component

TIBTable

The Table component

TIBQuery

The Query component

TIBSQL

The "unidirectional" Query component

Updating read-only dataset Explanation

Background to updating Read-Only datasets

TIBUpdateSQL

The simple Read-Only updating component

TIBDataSet

The self-contained Read-Only updating component

Stored procedures TIBStoredProc

The Stored Procedure procedure

5.1 TIBDatabase/TIBTransaction/TIBTable These components are explained in Chapter 3, Setting Up Delphi.

18 de 42

06/04/2010 18:22



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: 1. Fill in the Database name property 2. Fill in in the SQL property (eg. Select * from sales where sales_value > 50000 ) 3. 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 1. hooking it up to a Data Aware control (like TDBGrid), 2. scrolling backwards and forwards (using, say, a TDBNavigator, or using the IBQuery1.Next and IBQuery1.Previous command to move backwards and forwards programmatically) 3. 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'; 4. 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

19 de 42

06/04/2010 18:22



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 handling 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

20 de 42

06/04/2010 18:22



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. 1. 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. 2. 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. 3. 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

21 de 42

06/04/2010 18:22



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. 4. 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. 5. 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: 1. DataSet1.Params[0].AsInteger=24 2. 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. 1. 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'

22 de 42

06/04/2010 18:22



2. 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: 1. Fill in the Database property 2. 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) 3. 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') 4. Format any output parameters you want formatted, through the Params property 5. 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

23 de 42

06/04/2010 18:22



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: 1. 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). 2. Directly into InterBase through IBConsole that comes with InterBase. The SQL commands fall into two main categories: 1. the Metadata statements that control the structure of the database and tables and 2. 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. in the zip file , 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,

24 de 42

06/04/2010 18:22



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:generalInterBasedataIBDemo.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

25 de 42

06/04/2010 18:22



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

26 de 42

06/04/2010 18:22



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

27 de 42

06/04/2010 18:22



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. 1. 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 ). 2. 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; ). 3. 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"). 4. 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, '' || last || ' AS surname from employees; ).

28 de 42

06/04/2010 18:22



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". 1. 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 ) ). 2. 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) 3. 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; ) 4. 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); ). 5. 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

29 de 42

06/04/2010 18:22



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. 6. 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. 7. 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. 8. 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 9. 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

30 de 42

06/04/2010 18:22



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 10. 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 11. The "Where" statement applies equally to the Update, Delete and Insert statements. 12. 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. 1. You can Order by any field or fields within the original table (eg. of sql : SELECT * FROM employees WHERE department='IT' ORDER BY last; ) 2. You can Order by more than one field at a time (eg. of sql: SELECT * FROM employees ORDER BY last, first; ). 3. 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; ) 4. 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

31 de 42

06/04/2010 18:22



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. 1. 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; ). 2. 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; ) 3. 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

32 de 42

06/04/2010 18:22



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. 4. 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. 5. 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; ) 6. 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

33 de 42

06/04/2010 18:22



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). 7. 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. 1. 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.) 2. 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

34 de 42

06/04/2010 18:22



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: 1. Create a SQL script file, which you will be able to use to "import" the view into InterBase (Example? See the downloadable file">http://www.softwaredesign.co.uk /download/DelphiToInterbaseSQL.zip>file Tables.SQL, and look out for the "Create View" statement 2. 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.) 3. 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". 4. 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. 5. 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

35 de 42

06/04/2010 18:22



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. 1. 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.) 2. 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; ). 3. 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; .) 4. You can use any normal SQL statements within the statements, such as ORDER BY, and WHERE clauses. 5. 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

36 de 42

06/04/2010 18:22



include: 1. 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'; ) 2. 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. 3. 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). 4. 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').

37 de 42

06/04/2010 18:22



Chapter 7 - Stored Procedures This Chapter is written on the basis that the InterBase SQL file from this zip file (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. 1. 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. 2. 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.) 3. 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. 1. You must create a SQL script file. (How? See Creating a

38 de 42

06/04/2010 18:22



SQL script) 2. 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: 1. 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 2. 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 3. 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 4. 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. 5. 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

39 de 42

06/04/2010 18:22



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 1. 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) 2. The second part (which is mandatory if there is a 'FOR

40 de 42

06/04/2010 18:22



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 41 de 42

06/04/2010 18:22



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 [email protected]. This guide comes in HTML format and is available free from IBPhoenix. 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 by for any errors or omissions in the guide.

42 de 42

06/04/2010 18:22

Delphi to InterBase in 15 M...

Recommend Documents