Delphi - Stored procedures returning data (MSSQL, Firebird, Oracle)
Date |
Author
|
Level
|
2004-12-26
|
Stefan Cruysberghs
|
![]() ![]() |
In this article I will show the Delphi features to return data from a database to the client by using stored procedures. The examples will use ADO (ActiveX Data Objects), DBX (dbExpress) and IBX (InterBase Express) components and databases like Microsoft SQL Server, Oracle and Firebird/Interbase.
- Why stored procedures ?
- Delphi and databases versions
- Database engines
- The table ANIMALS
- The stored procedure examples
- ADO and Microsoft SQL Server (or MSDE)
- Interbase Express (IBX) and Firebird/Interbase
- dbExpress (DBX) and Firebird/Interbase
- dbExpress (DBX) and Microsoft SQL Server
- Oracle
- DB2
- MySQL
Why stored procedures ?
Stored procedures are saved and executed on the database, they are already compiled and that is why they are fast. Stored procedures can decrease network traffic and you can use them to move all business logic to the server.
So when queries have shortcomings, stored procedures can be a good alternative.
You can create stored procedures to execute UPDATE, DELETE, INSERT statements, ... but they can also be used to return some data; one value or a whole dataset/recordset.
Delphi and databases versions
All examples were tested with Borland Delphi 7.0 and Delphi 2005 (VCL).
The database versions I used :
- MS SQL Server 2000
- Firebird 1.5
- Oracle 8.1.7
Database engines
Delphi (VCL) supports several database engines. In Delphi 7 and previous versions you could use the BDE (Borland Database Engine) but this engine is not supported anymore. New engines are ADO, dbExpress and IBX.
ADO (ActiveX Data Objexts)
- ADO, the Microsoft, technology, works very well especially with Microsoft products like SQL Server or Access.
- Via ODBC or specific third-party drivers you can access all kind of databases.
- The new ADO.NET technology - which differ from ADO - is very well supported by all database suppliers.
DBX (dbExpress)
- dbExpress is a Borland technology which can be used in Delphi, Kylix, C# Builder and C++Builder.
- The Borland products include some dbExpress drivers for Interbase, Oracle, DB2, SQL Server and MySQL.
- Not all drivers are working perfect but there are several good third-party drivers available. CoreLab is one of these companies who develop good drivers for dbExpress. http://www.crlab.com/products.html
IBX (InterBase Express)
- The Interbase Express technology can only be used for Interbase and Firebird (=open-source version of Interbase) databases.
- The advantage is that it has several specific Interbase components like TIBEvents, TIBScript, TIBSQLMonitor, ... and it IBX is open-source. http://sourceforge.net/projects/ibx
Connection strings
The table ANIMALS
The table which is used in all examples is the ANIMALS table which is provided by the DBDEMOS (BDE, Paradox) database from Borland. I have imported this table in all other databases.
The stored procedure examples
In this article I will use 5 stored procedures :
CalcSum
- Simple function with 2 integer input-parameters.
- The return-value is the sum of the 2 integers.
GetAnimalWeight
- The input-parameter is the name of the animal.
- The return-value is the weight. If the animal can not be found, -1 will be returned.
GetBigAnimals
- Will return all animals which have as size larger then 10.
GetAnimalsAttribute
- Will return name and size or name and weight of all animals.
- The input-parameter Kind can have value 1 (size) or value 2 (weight).
GetBigAndSmallAnimals
- This procedure will return 2 recordsets
- All animals with size larger then 10
- All animals with size smaller then 3
ADO and Microsoft SQL Server (or MSDE)
Returning a value
SQL Server has an extensive T-SQL (transact-SQL) language which can be used to develop UDF's, stored procedures and triggers. SQL Server makes it possible to create a UDF (User Defined Function) as well as a stored procedure to return a value to the client. In both cases you have to use the RETURN command.
CREATE FUNCTION CalcSum(@intValue1 INTEGER, @intValue2 INTEGER) RETURNS INTEGER AS BEGIN RETURN @intValue1 + @intValue2 END
CREATE PROCEDURE CalcSum2(@intValue1 INTEGER, @intValue2 INTEGER) AS BEGIN RETURN @intValue1 + @intValue2 END
Passing the result of a SELECT statement to a variable can be done in this way
SELECT @variabele = Field
Be aware that you first have to declare the variable with the DECLARE command.
CREATE FUNCTION GetAnimalWeight(@strName VARCHAR(30)) RETURNS INTEGER AS BEGIN DECLARE @intWeight INTEGER IF NOT EXISTS (SELECT 1 FROM animals a WHERE a.Name = @strName) RETURN -1 ELSE SELECT @intWeight = a.Weight FROM animals a WHERE a.Name = @strName RETURN @intWeight END
CREATE PROCEDURE GetAnimalWeight2(@strName VARCHAR(30)) AS BEGIN DECLARE @intWeight INTEGER IF NOT EXISTS (SELECT 1 FROM DBDEMOS.dbo.animals a WHERE a.Name = @strName) RETURN -1 ELSE SELECT @intWeight = a.Weight FROM animals a WHERE a.Name = @strName RETURN @intWeight END
With ADO it is quite easy to execute UDF's or stored procedures. Of course you first have to create a link to the database with the TADOConnection component. There are 2 ADO components which can be used : TADOCommand and TADOStoredProc.
The TADOCommand component can also be used to access tables or to execute queries. To execute a stored procedure or UDF you have to set the CommandType property to cmdStoredProc. The CommandText property should be set to the name of the stored procedure/UDF. To make sure that all input parameters are known, you first have to call the Refresh method. Of course the first 3 lines can be skipped when all properties are set in design-time. By calling the Execute method the stored procedure/UDF can be executed. The result is saved in the return-value parameter @RETURN_VALUE.
ADOCommand1.CommandType := cmdStoredProc; ADOCommand1.CommandText := 'CalcSum2'; ADOCommand1.Parameters.Refresh; ADOCommand1.Parameters.ParamByName('@intValue1').Value := 10; ADOCommand1.Parameters.ParamByName('@intValue2').Value := 28; ADOCommand1.Execute; ShowMessage(ADOCommand1.Parameters.ParamValues['@RETURN_VALUE']);
Using the TADOStoredProc component is even more easy. Just fill the property ProcedureName and call the ExecProc method.
ADOStoredProc1.ProcedureName := 'GetAnimalWeight'; ADOStoredProc1.Parameters.Refresh; ADOStoredProc1.Parameters.ParamByName('@strName').Value := 'Boa'; ADOStoredProc1.ExecProc; ShowMessage(ADOStoredProc1.Parameters.ParamValues['@RETURN_VALUE']);
Returning dataset/recordset
A stored procedure can also be used to return more values or even a whole dataset/recordset. In SQL Server this can be done very easily. Just use a SELECT statement in the stored procedure and the result will be this dataset/recordset.
CREATE PROCEDURE GetBigAnimals AS BEGIN SELECT * FROM animals a WHERE a.Size > 10 END
CREATE PROCEDURE GetAnimalsAttribute(@intKind INTEGER) AS BEGIN IF @intKind = 1 SELECT a.Name, a.Size FROM animals a ELSE SELECT a.Name, a.Weight FROM animals a END
In Delphi you need 3 components to return a dataset; a TADOStoredProc, a TProvider and a TClientDataset.
You first have to set all properties of the TADOStoredProc component. Afterwards you only have to call the methods of the TClientDataset. Always call Close first, then FetchParams, set the parameter values and finally call the Open method to retrieve the data.ADOStoredProc2.ProcedureName := 'GetAnimalsAttribute'; ADOStoredProc2.Parameters.Clear; with ADOStoredProc2.Parameters.AddParameter do begin Name := '@intKind'; DataType := ftInteger; end; ClientDataSetADO2.Close; ClientDataSetAD02.FetchParams; ClientDataSetADO2.Params.ParamByName('@intKind').Value := 1; ClientDataSetADO2.Open;
Returning multiple recordsets
With some databases it is possible to return multiple datasets. In SQL Server you only have to implement several SELECT statements in the stored procedure.
CREATE PROCEDURE GetBigAndSmallAnimals AS BEGIN SELECT * FROM animals a WHERE a.Size > 10 SELECT * FROM animals a WHERE a.Size < 3 END
On the Delphi side you still have to use a TADOStoredProc component, but now you have to call the Open method. The property RecordSet contains the data of the first recordset. This can be assigned to the RecordSet property of a TADODataset. The next recordsets can be accessed by the NextRecordSet method.
var intRecordsAffected : Integer; begin ADOStoredProc1.ProcedureName := 'GetBigAndSmallAnimals'; ADOStoredProc1.Open; ADODataSet1.Recordset := ADOStoredProc1.RecordSet; ADODataSet2.Recordset := ADOStoredProc1.NextRecordset(intRecordsAffected); end;
Interbase Express (IBX) and Firebird/Interbase
Returning a value
Also Interbase/Firebird is acquainted with UDF's, but you should develop them externally in C or C+. Stored procedures on the other hand can be implemented on the database.
In a Interbase/Firebird stored procedure you always have to use the SUSPEND command to return a result. With INTO you can copy the field values of the SELECT statement to the return-parameters. If a query has no result, the INTO parameter is NULL.
CREATE PROCEDURE CALCSUM ( INTVALUE1 INTEGER, INTVALUE2 INTEGER) RETURNS ( INTSUM INTEGER) AS BEGIN INTSUM = INTVALUE1 + INTVALUE2; SUSPEND; END
CREATE PROCEDURE GETANIMALWEIGHT ( STRNAME VARCHAR (30)) RETURNS ( INTWEIGHT INTEGER) AS BEGIN SELECT Weight FROM animals WHERE Name = :STRNAME INTO INTWEIGHT; IF (INTWEIGHT IS Null) THEN INTWEIGHT = -1; SUSPEND; END
When using IBX components, you first have to add a TIBDatase andTIBTransaction component. All other IBX components have to be linked to this database and transaction component. Using a TIBStoredProc component is quite easy.
IBStoredProc1.StoredProcName := 'CALCSUM'; IBStoredProc1.ParamByName('intValue1').AsInteger := 3; IBStoredProc1.ParamByName('intValue2').AsInteger := 12; IBStoredProc1.ExecProc; ShowMessage(IBStoredProc1.ParamByName('intSum').AsString);
Returning a dataset/recordset
To return a whole dataset/recordset in Interbase/Firebird, you have to program some little more. First declare all return-parameters and in a FOR-DO iteration you have to call the SUSPEND command.
CREATE PROCEDURE GETBIGANIMALS RETURNS ( NAME VARCHAR (30), SIZEX INTEGER, WEIGHT INTEGER, AREA VARCHAR (30)) AS BEGIN FOR SELECT Name, SizeX, Weight, Area FROM animals WHERE SizeX > 10 INTO :Name, :SizeX, :Weight, :Area DO SUSPEND; END
![ArticleDelphi%20-%20FireBird_GetAnimalsAttribute.jpg](https://i-blog.csdnimg.cn/blog_migrate/d4c7eab695a3450f06d964ae8d98178b.jpeg)
CREATE PROCEDURE GETANIMALSATTRIBUTE ( INTKIND INTEGER) RETURNS ( NAME VARCHAR (30), ATTRIBUTE INTEGER) AS BEGIN IF (INTKIND = 1) THEN FOR SELECT Name, SizeX FROM animals INTO :Name, :Attribute DO SUSPEND; ELSE FOR SELECT Name, Weight FROM animals INTO :Name, :Attribute DO SUSPEND; END
Returning a whole dataset can not be done with a TIBStoredProc component. Therefore you have to use a TIBQuery component. In the FROM clause of the SELECT statement you have to fill the name of the stored procedure with all necessary parameters. Afterwards you can open the dataset by using a TProvider and a TClientDataset.
![ArticleDelphi%20-%20IBX3.jpg](https://i-blog.csdnimg.cn/blog_migrate/32e2b719e707a8fdbd95befd905ed92b.jpeg)
IBQuery1.SQL.Text := 'SELECT * FROM GETANIMALSATTRIBUTE(:intKind)'; ClientDataSetIBX1.Close; ClientDataSetIBX1.FetchParams; ClientDataSetIBX1.Params.ParamByName('intKind').AsInteger := 1; ClientDataSetIBX1.Open;
This method of working is different from other database engines but it has the advantage that you can limit the number of fields or change the order of fields in the SELECT clause. Adding calculated fields or extra WHERE conditions seems to be not possible.
dbExpress (DBX) en Firebird/Interbase
Using dbExpress (DBX) components with Interbase is not so much different then InterBase Express (IBX), so you should be familiar with it. Always start first with a TSQLConnection. Stored procedures can be executed with a TSQLStoredProc component.
SQLStoredProc1.StoredProcName := 'CALCSUM'; SQLStoredProc1.ParamByName('intValue1').AsInteger := 6; SQLStoredProc1.ParamByName('intValue2').AsInteger := 3; SQLStoredProc1.ExecProc; ShowMessage(SQLStoredProc1.ParamByName('intSum').AsString);
dbExpress posses an extra component which can be used to execute a stored procedure; the TSQLDataset component. Tis component can be compared with the TADOCommand component.
SQLDataSet1.CommandType := ctStoredProc; SQLDataSet1.CommandText := 'CALCSUM'; SQLDataSet1.ParamByName('intValue1').AsInteger := 10; SQLDataSet1.ParamByName('intValue2').AsInteger := 7; SQLDataSet1.ExecSQL; ShowMessage(SQLDataSet1.ParamByName('intSum').AsString);
When returning a dataset, you should use a TSQLQuery component with a provider and clientdataset.
SQLQuery1.SQL.Text := 'SELECT * FROM GETANIMALSATTRIBUTE(:intKind)'; ClientDataSetSQL1.Close; ClientDataSetSQL1.FetchParams; ClientDataSetSQL1.Params.ParamByName('intKind').AsInteger := 1; ClientDataSetSQL1.Open;
dbExpress (DBX) and Microsoft SQL Server
D
elphi 7 & CoreLab driver
In my Delphi 7 the MS SQL Server driver of dbExpress does not work well. So I installed a trial version of the CoreLab driver which does work perfectly. Iit seems that you only can execute stored procedures and no UDF's with the TSQLStoredProc component.
SQLStoredProcSQL1.StoredProcName := 'CALCSUM2'; SQLStoredProcSQL1.ParamByName('intValue1').AsInteger := 3; SQLStoredProcSQL1.ParamByName('intValue2').AsInteger := 8; SQLStoredProcSQL1.ExecProc; ShowMessage(SQLStoredProcSQL1.ParamByName('RETURN_VALUE').AsString);
SQLStoredProcSQLServ2.StoredProcName := 'GetAnimalsAttribute'; ClientDataSetSQL1.Close; ClientDataSetSQL1.FetchParams; ClientDataSetSQL1.Params.ParamByName('intKind').AsInteger := 1; ClientDataSetSQL1.Open;
Delphi 2005
In Delphi 2005 all problems with the standard Borland driver seems to be solved. Executing "CalcSum" (function) and "CalcSum2" (stored procedure) is no problem. When using the Borland driver you have to use the @-character in the parameter name. When using the CoreLab driver this is not necessary.
SQLStoredProcSQL1.StoredProcName := 'CALCSUM2'; SQLStoredProcSQL1.ParamByName('@intValue1').AsInteger := 3; SQLStoredProcSQL1.ParamByName('@intValue2').AsInteger := 8; SQLStoredProcSQL1.ExecProc; ShowMessage(SQLStoredProcSQLServ1.ParamByName('@RETURN_VALUE').AsString);
SQLStoredProcSQLServ2.StoredProcName := 'GetAnimalsAttribute'; ClientDataSetSQL1.Close; ClientDataSetSQL1.FetchParams; ClientDataSetSQL1.Params.ParamByName('@intKind').AsInteger := 1; ClientDataSetSQL1.Open;
Oracle
Oracle is equipped with a powerful PL/SQL language which can be used in triggers, stored procedures, functions, ... In Oracle functions and procedures are implemented as in most programming languages. So a function can only return one value. A stored procedures can be used to return a cursor. In Delphi this cursor can be used like a dataset.
create or replace function Calcsum(intValue1 pls_integer, intValue2 pls_integer) return pls_integer is begin return(intValue1 + intValue2); end Calcsum;
Implementing exceptions is quite easy in Oracle. The no_data_found exeption can be intercepted when a SELECT statement returns no result.
create or replace function GetAnimalWeight(strName VARCHAR) return pls_integer is intWeight pls_integer; begin SELECT a.Weight INTO intWeight FROM animals a WHERE a.Name = strName; return(intWeight); exception when no_data_found then return(-1); end GetAnimalWeight;
Functions and procedures can be bundled in a package. In the specification functions and procedures should be declared. The implementation should be done in the body.
create or replace package DBDEMOS is type TReturnCursor is ref cursor; function Calcsum(intValue1 pls_integer, intValue2 pls_integer) return pls_integer; function GetAnimalWeight(strName varchar) return pls_integer; procedure GetBigAnimals(Result out TReturnCursor); procedure GetAnimalsAttribute(intKind pls_integer, Result out TReturnCursor); end DBDEMOS;
create or replace package body DBDEMOS is /*----------------------------------------------------------------------------*/ function Calcsum(intValue1 pls_integer, intValue2 pls_integer) return pls_integer is begin return(intValue1 + intValue2); end Calcsum; /*----------------------------------------------------------------------------*/ function GetAnimalWeight(strName varchar) return pls_integer is intWeight Integer; begin SELECT a.Weight INTO intWeight FROM animals a WHERE a.Name = strName; return(intWeight); exception when no_data_found then return(-1); end GetAnimalWeight; /*----------------------------------------------------------------------------*/ procedure GetBigAnimals(Result out TReturnCursor) is begin open Result for 'SELECT * FROM animals WHERE SizeX > 10'; end; /*----------------------------------------------------------------------------*/ procedure GetAnimalsAttribute(intKind pls_integer, Result out TReturnCursor) is begin if intKind = 1 then open Result for 'SELECT Name, SizeX FROM animals'; else open Result for 'SELECT Name, Weight FROM animals'; end if; end; end DBDEMOS;
DB2
- I have no experience with IBM's DB2 database. DB2 is a powerfull database which has a lot of features to develop stored procedures, triggers, ...
- Besides Borland and IBM do have a agreement so Delphi should have good DB2 drivers.
- For DB2/400 on the iSeries no standard driver is provided. There are some third-party drivers and with the ADO or ODBC driver from IBM Client Express you can access a DB2/400 database.
MySQL
- MySQL has support for stored procedures since the new version 5.0.
- At this moment most webservers have version 4.0 installed.
- I have no experience with version 5.0 yet.