Nieuws
Foto's
Artikelen
Componenten
Applicaties
Kleinkunst

Delphi - Data teruggeven met stored procedures

In dit artikel overloop ik de mogelijkheden van Delphi om via stored procedures data terug te geven naar de client. De voorbeelden maken gebruik van zowel ADO (ActiveX Data Objects), DBX (dbExpress) als IBX (InterBase Express) componenten en databases zoals Microsoft SQL Server, Oracle en Firebird/Interbase.

Waarom stored procedures ?

Stored procedures worden bewaard en uitgevoerd op de database en ze zijn zeer snel omdat ze reeds gecompileerd zijn. Bovendien kan door het gebruik van stored procedures het netwerkverkeer verminderen en alle business logica naar de server verplaatsen.

Van zodra gewone queries tekortschieten is het dus interessant om stored procedures te ontwikkelen.

Je kan stored procedures maken voor het uitvoeren van UPDATE, DELETE, INSERT statements, … maar ook gewoon om data terug te geven, zowel 1 bepaalde waarde als een volledig dataset/recordset.

 

Delphi en databases versies

Alle voorbeelden werden getest met Borland Delphi 7.0 en Delphi 2005 (VCL).

De versies van de 3 gebruikte databases zijn :

  • MS SQL Server 2000
  • Firebird 1.5
  • Oracle 8.1.7

 

Database engines

In Delphi (VCL) zijn er verschillende database engines beschikbaar. In Delphi 7 en voorgaande versies was er de BDE (Borland Database Engine) maar deze engine wordt al enkele jaren niet meer verder ontwikkeld. Nieuwere engines zijn ADO, dbExpress en IBX.

ADO (ActiveX Data Objexts)

  • ADO, de Microsoft technologie, werkt vooral zeer goed met Microsoft producten zoals SQL Server of Access.
  • Via ODBC of specifieke third-party drivers kan je echter alle databases aanspreken.
  • De ADO.NET technologie - die toch wel serieus verschilt van ADO - krijgt tegenwoordig zeer goede ondersteuning van alle grote database fabrikanten.

DBX (dbExpress)

  • dbExpress is een Borland technologie die zowel in Delphi, Kylix, C# Builder als C++Builder beschikbaar is.
  • Borland levert standaard een reeks dbExpress drivers mee voor Interbase, Oracle, DB2, SQL Server en MySQL.
    Deze meegeleverde drivers werken echter niet voor elke database perfect en soms lopen de versies wat achter.
  • Gelukkige zijn er verschillende goede third-party drivers op de markt. CoreLab is een van die bedrijven die goede drivers voor verschillende databases ontwerpen. Met de gratis trial versies kan je alvast alles uittesten. http://www.crlab.com/products.html

IBX (InterBase Express)

  • De Interbase Express technologie kan je enkel gebruiken voor Interbase en FireBird (=open-source versie van Interbase) databases.
  • Het voordeel is wel dat er allerlei specifieke Interbase componenten worden meegeleverd zoals TIBEvents, TIBScript, TIBSQLMonitor, ... en bovendien is IBX open-source. http://sourceforge.net/projects/ibx

Connection strings

 

De basistabel ANIMALS

De tabel die we gaan gebruiken is de eenvoudige ANIMALS tabel die met de Borland voorbeeld database DBDEMOS (BDE, Paradox) wordt meegeleverd. Deze heb ik geïmporteerd in alle andere databases. Alle stored procedures worden zo eenvoudig mogelijk uitgewerkt zodat de basisprincipes het meest duidelijk worden.

 

De stored procedure voorbeelden

Voor dit artikel heb ik 5 verschillende maar eenvoudige stored procedures ontwikkeld die in elke database terugkomen :

CalcSum

  • Eenvoudige functie met 2 integer input-parameters.
  • De return-waarde is de som van de 2 getallen.

GetAnimalWeight

  • De input-parameter is de naam van het dier (Name).
  • De return-waarde is het gewicht (Weight). Als het dier echter niet gevonden is dan wordt de waarde -1 teruggegeven.

GetBigAnimals

  • Geeft alle dieren waarbij de grootte (Size) groter is dan 10.
  • Het resultaat is een query met een WHERE voorwaarde.

GetAnimalsAttribute

  • Geeft de naam en de grootte of de naam en de gewicht van alle dieren.
  • De input-parameter Kind kan de waarde 1 (grootte/Size) of 2 (gewicht/Weight) hebben.
  • Het resultaat zal dus een van de 2 queries zijn.

GetBigAndSmallAnimals

  • Geeft 2 recordsets terug
    • Met alle dieren waarbij de grootte (Size) groter is dan 10
    • Met alle dieren waarbij de grootte (Size) kleiner is dan 3


ADO en Microsoft SQL Server (of MSDE)

Een waarde teruggeven

SQL Server beschikt over een vrij uitgebreide T-SQL (transact-SQL) taal waarmee je UDF’s, stored procedures en triggers kan ontwikkelen. In SQL Server kan je zowel een UDF (User Defined Function) als een stored procedure maken die een waarde teruggeven. In beide gevallen moet je het RETURN commando gebruiken om een waarde terug te geven. Het voordeel van een UDF is dat je deze ook kan gebruiken in SELECT statements. Een functie maken zonder input parameters kan echter niet, dat is dan wel weer mogelijk met een stored procedure. Bij een functie ben je ook verplicht om het datatype van het resultaat op te geven.

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

Je kan het resultaat van een SELECT statement doorgeven aan een variabele op volgende manier

SELECT @variabele = Veld

Hiervoor dien je deze variabele wel eerst te declareren met het DECLARE commando.

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

Deze UDF’s of stored procedures van SQL Server kan je via ADO vrij eenvoudig aanspreken. Natuurlijk moet je eerst een TADOConnection component gebruiken en een link naar je database maken. Er zijn 2 ADO componenten die je kan gebruiken; TADOCommand en TADOStoredProc.

Met een TADOCommand component kan je ook tabellen raadplegen of queries uitvoeren. Om een stored procedure of UDF uit te voeren zet je de CommandType property op cmdStoredProc. Bij CommandText vul je de naam van de UDF of stored procedure in. Om er voor te zorgen dat de input parameters bekend worden moet je eerst de Refresh method oproepen. Deze 3 regels hoef je wel niet te gebruiken als je in design-time reeds alles hebt toegekend. Hierna kan je de parameters invullen en met de Execute method voer je de UDF of stored procedure uit. Het resultaat wordt in de return-value parameter @RETURN_VALUE bewaard.

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']);

Met een TADOStoredProc component kan het nog iets eenvoudiger, je dient enkel de property ProcedureName op te vullen. Met de ExecProc method voer je de stored procedure uit.

ADOStoredProc1.ProcedureName := 'GetAnimalWeight';
ADOStoredProc1.Parameters.Refresh;

ADOStoredProc1.Parameters.ParamByName('@strName').Value := 'Boa';
ADOStoredProc1.ExecProc;
ShowMessage(ADOStoredProc1.Parameters.ParamValues['@RETURN_VALUE']);

Dataset/recordset teruggeven

Met een stored procedure kan je ook meerdere waardes of zelfs volledige datasets/recordsets teruggeven. In SQL Server kan dit vrij eenvoudig. Het SELECT statement dat je gebruikt in de stored procedure zal automatisch het resultaat van de procedure zijn.

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

Wil je in Delphi een stored procedure gebruiken die een dataset teruggeeft, dan kan dit op ongeveer dezelfde manier als met een query component. Je hebt namelijk een TADOStoredProc, een TProvider en een TClientDataset nodig. Als je de data wil koppelen aan een data-aware component heb je natuurlijk ook nog een TDataSource nodig.

Ook hier moet je eerst de property ProcedureName opvullen. De Refresh method van de Parameters gebruiken, kan nu echter niet. Je moet dus eerst de parameters aanmaken. Dit kan je natuurlijk reeds in design-time doen.

Daarna maak je enkel gebruik van het TClientDataset component. Altijd eerst sluiten met de Close method, dan met FetchParams de parameters ophalen, de waardes aan de parameters toekennen en tenslotte met de Open method de data ophalen.

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;

Meerdere recordsets teruggeven

In sommige databases kan je zelfs nog een stapje verder gaan. Met SQL Server kan je zeer eenvoudig meerdere recordsets teruggeven door gewoon meerdere SELECT statements in je stored procedure te implementeren.

CREATE PROCEDURE GetBigAndSmallAnimals
AS
BEGIN
  SELECT * 
  FROM animals a
  WHERE a.Size > 10

  SELECT *
  FROM animals a
  WHERE a.Size < 3
END

Aan de Delphi kant moet je nog steeds een TADOStoredProc component gebruiken, maar nu wel met de Open method. In de property RecordSet zit de data van het eerste recordset. Deze kan je toekennen aan de RecordSet property van een TADODataset. De andere recordsets opvragen kan met de NextRecordSet van het stored procedure component. 

var
  intRecordsAffected : Integer;
begin
  ADOStoredProc1.ProcedureName := 'GetBigAndSmallAnimals';
  ADOStoredProc1.Open;

  ADODataSet1.Recordset := ADOStoredProc1.RecordSet;
  ADODataSet2.Recordset := ADOStoredProc1.NextRecordset(intRecordsAffected);
end;

 

Interbase Express (IBX) en Firebird/Interbase

Een waarde teruggeven

Ook Interbase/Firebird kent het principe van UDF’s (User Defined Functions) maar deze moet je echter in C of C++ programmeren en daarna importeren. Stored procedures daarentegen kan je wel rechtstreeks op de database implementeren.

In een Interbase/Firebird stored procedure moet je altijd het SUSPEND commando gebruiken om een resultaat terug te geven. Met de INTO kan je de velden van je SELECT in de return-parameters plaatsen. Als de query geen resultaat geeft is deze INTO parameter gelijk aan 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

Bij IBX moet je altijd een TIBDatase als TIBTransaction component gebruiken en deze moet je daarna aan alle queries en stored procedures koppelen. Met het TIBStoredProc component kan je vrij eenvoudig de procedure naam opgeven, de parameters instellen en het resultaat gebruiken.

IBStoredProc1.StoredProcName := 'CALCSUM';
IBStoredProc1.ParamByName('intValue1').AsInteger := 3;
IBStoredProc1.ParamByName('intValue2').AsInteger := 12;
IBStoredProc1.ExecProc;
ShowMessage(IBStoredProc1.ParamByName('intSum').AsString);

Dataset/recordset teruggeven

Om een volledig dataset/recordset terug te geven in Interbase/Firebird moet je iets meer programmeren. Je moet namelijk alle return-parameters declareren en met een FOR-DO lus moet je bij elk record van het dataset het SUSPEND commando oproepen. 

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

Wil je met de IBX componenten een volledig dataset teruggeven, dan kan je hiervoor geen TIBStoredProc component gebruiken. Met een gewoon TIBQuery component kan dit wel. Bij de FROM in het SELECT statement vul je dan de naam van je stored procedure met de nodige parameters in. Hierna kan je via een TProvider en TClientDataset de parameters opvullen en het dataset openen.

IBQuery1.SQL.Text := 'SELECT * FROM GETANIMALSATTRIBUTE(:intKind)';

ClientDataSetIBX1.Close;
ClientDataSetIBX1.FetchParams;
ClientDataSetIBX1.Params.ParamByName('intKind').AsInteger := 1;
ClientDataSetIBX1.Open;

Deze werkwijze wijkt dus wat af van de gangbare norm. Wel kan je op deze manier eventueel het aantal velden beperken of de volgorde veranderen door de SELECT clause aan te passen. Berekende velden of extra WHERE voorwaardes toevoegen lijkt niet mogelijk.

 

dbExpress (DBX) en Firebird/Interbase

De werking van de dbExpress (DBX) componenten bij Interbase verschilt bijna niet met de InterBase Express (IBX) componenten. Ook hier moet je altijd beginnen met een TSQLConnection. Stored procedures uitvoeren kan met het TSQLStoredProc component. 

SQLStoredProc1.StoredProcName := 'CALCSUM';
SQLStoredProc1.ParamByName('intValue1').AsInteger := 6;
SQLStoredProc1.ParamByName('intValue2').AsInteger := 3;
SQLStoredProc1.ExecProc;
ShowMessage(SQLStoredProc1.ParamByName('intSum').AsString);

Bij dbExpress is er nog een extra component waarmee je een stored procedure kan uitvoeren; het TSQLDataset component is vergelijkbaar met het 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);

Ook voor het teruggeven van een dataset moet je een TSQLQuery component aan de provider en clientdataset koppelen.

SQLQuery1.SQL.Text := 'SELECT * FROM GETANIMALSATTRIBUTE(:intKind)';

ClientDataSetSQL1.Close;
ClientDataSetSQL1.FetchParams;
ClientDataSetSQL1.Params.ParamByName('intKind').AsInteger := 1;
ClientDataSetSQL1.Open;

 

dbExpress (DBX) en Microsoft SQL Server

Delphi 7 & CoreLab driver

In mijn Delphi 7 werkt de MS SQL Server driver niet goed met dbExpress. Ik heb dan ook een trial versie van de driver van CoreLab geïnstalleerd. Hiermee werken dbExpress en MS SQL Server wel goed samen. Wel is enkel "CalcSum2" beschikbaar, dus met het TSQLStoredProc component kan je enkel stored procedures en geen UDF’s uitvoeren.

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 lijken de problemen met de standaard Borland driver opgelost. De meegeleverde MS SQL Server driver werkt hier wel probleemloos. Ook zijn zowel "CalcSum" als "CalcSum2" beschikbaar, dus je kan zowel UDF’s als stored procedures uitvoeren. Met de Borland driver moet je ook het @-teken gebruiken voor de parameternaam. Ook bij ADO was dit nodig. Bij de CoreLab driver voor dbExpress was dit niet het geval.

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 beschikt over een zeer krachtige PL/SQL taal die je kan gebruiken in triggers, stored procedures, functies, … In Oracle zijn de functie en procedure geïmplementeerd zoals in de meeste programmeertalen. Met een functie kan je namelijk 1 resultaat teruggeven. Met een procedure kan je een cursor teruggeven als output-parameter. Deze cursor wordt in Delphi dan een dataset.

create or replace function Calcsum(intValue1 pls_integer, intValue2 pls_integer) return pls_integer  
is
begin
  return(intValue1 + intValue2);
end Calcsum;

Oracle kent ook het principe van exceptions. Met de no_data_found exceptie kan je eenvoudig opvangen als een SELECT statement geen resultaat geeft.

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;

Functies en procedures kan je in Oracle ook verzamelen in een package. In de specification declareer je de functies en procedures en in de body kan je ze verder uitwerken. 

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

  • Met IBM’s DB2 database heb ik eigenlijk geen ervaring. DB2 is een krachtige database en heeft zeer uitgebreide mogelijkheden voor het ontwikkelen van stored procedures, triggers, …
  • Bovendien heeft Borland een samenwerkingsakkoord met IBM waardoor er met Delphi altijd goede drivers voor DB2 zouden moeten meegeleverd worden.
  • Voor DB2/400 op iSeries is er wel geen standaard driver beschikbaar. Er zijn enkele third-party drivers op de markt en via de ADO en de ODBC driver van IBM Client Express kan je ook de DB2/400 database aanspreken.

 

MySQL

  • MySQL heeft pas sinds versie 5.0 ondersteuning voor stored procedures en functies.
  • Op de meeste webservers wordt echter nog altijd versie 4.0 gebruikt.
  • Met versie 5.0 heb ik ook nog geen ervaring.