News
Photos
Articles
Components
Applications
Kleinkunst

Delphi - Returning data with stored procedures

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 ?

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

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. This 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

Delphi 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. It 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 powerful 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.