Parameterized queries with dbExpress (DBX) Framework

Let's share the knowledge with your friends

In order to answer a question from two Brazilian developers about how to use parameterized queries using dbExpress Framework I decided to write this post.

Let start understanding the small difference between parameterized queries using DBX Framework and VCL. While VCL uses “:PARAMETER” to define query parameters, DBX Framework represent parameter as “?”, and it doesn’t include name.

We have been using VCL for a long time and are accustomed to read and write our code based on DataSet parameters. When you start using the DBX Framework you expect it to work in a very similar way, but the framework has some differences. The Delphi language allow us to extend the framework and it would became more VCL friendly.

In case you use only what you have in the DBX Framework, your code will look like this:

program DBX4Example;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  DBXDynalink,
  DBXCommon,
  DBXInterbase;

var

  aDBXConn: TDBXConnection;
  aDBXTrans : TDBXTransaction;
  aCmnd: TDBXCommand;
  aReader: TDBXReader;

begin

    aDBXConn := TDBXConnectionFactory.GetConnectionFactory.GetConnection('employee ib','sysdba','masterkey');

    if aDBXConn <> nil then
    begin

      aCmnd := aDBXConn.CreateCommand;

      // Start transaction
      aDBXTrans:= aDBXConn.BeginTransaction(TDBXIsolations.ReadCommitted);

      // Prepare and execute the SQL Statement
      aCmnd.Text := 'SELECT * FROM Country  Where Country = ?';
      aCmnd.Prepare;
      aCmnd.Parameters.Parameter[0].Value.SetAnsiString('USA');
      aReader := aCmnd.ExecuteQuery;

      while aReader.Next do
      begin
        Writeln( aReader.Value['Country'].GetAnsiString );
      end;

      // Commit transaction
      aDBXConn.CommitFreeAndNil(aDBXTrans);

      Readln;
      aReader.Free;
      aCmnd.Free;
      aDbxConn.Free;
    end;
end.

Note that in the SQL we use question mark to define the parameter, prepare the query and follow the long line to define the parameter value at position zero.

We can make this code more VCL friendly using Class Helpers, one of the new features implemented in Delphi 2006. It will allow us to extend the class without inheriting from it.

So we’ll extend the class TDBXParameterList, which will allow us to name the parameters and set their values by name.

The follow code extend the class through class helpers, look this is not inherited. This extension define two new methods:
SetParametersName: this method will receive a list of names, which will be used to name the parameters, you must pass a number of names equal to the number of parameters;
ByName: method that allows us to access parameters by name, the DBX Framework has the method GetOrdinal, it allow us to access parameters by name, but byName will be more VCL friendly;

unit DBXParameterListHelper;

interface

uses
  DBXCommon, SysUtils;

type
  TDBXParameterListHelper = class helper for TDBXParameterList

  public
    procedure SetParamatersName(aNames: Array of String);
    function ByName( Name : String ) : TDBXWritableValue;
  end;

implementation

{ TDBXParameterListHelper }

function TDBXParameterListHelper.ByName(Name: String): TDBXWritableValue;
begin
  Result := Self.Parameter[Self.GetOrdinal(Name)].Value;
end;

procedure TDBXParameterListHelper.SetParamatersName(aNames: array of String);
var
  i: Integer;
begin

  if Length(aNames) <> Self.Count then
     raise Exception.Create('aNames should have the same number of parameters');

  for i := 0 to Self.Count - 1 do
  begin
    Self[i].Name := aNames[i];
  end;
end;

end.

Using the extension this part of the code:

      // Prepare and execute the SQL Statement
      aCmnd.Text := 'SELECT * FROM Country  Where Country = ?';
      aCmnd.Prepare;
      aCmnd.Parameters.Parameter[0].Value.SetAnsiString('USA');

Will be replaced by this one.

      // Prepare and execute the SQL Statement
      aCmnd.Text := 'SELECT * FROM Country  Where Country = ?';
      aCmnd.Prepare;
      aCmnd.Parameters.SetParamatersName(['COUNTRYNAME']);
      aCmnd.Parameters.ByName('COUNTRYNAME').SetAnsiString('USA');

In case your query has multiple parameteres, like this:

      // Prepare and execute the SQL Statement
      aCmnd.Text := 'SELECT * FROM Country  Where Country = ? and Currency = ?';
      aCmnd.Prepare;
      aCmnd.Parameters.SetParamatersName(['COUNTRYNAME', 'CURRENCY']);
      aCmnd.Parameters.ByName('COUNTRYNAME').SetAnsiString('USA');
      aCmnd.Parameters.ByName('CURRENCY').SetAnsiString('Dolar');

In closing, we’ve learned how to work with query parameters in DBX Framework and how to use class helper to extend our classes.

Source code download, available here.

COUNTRYNAME

Let's share the knowledge with your friends
7 replies
  1. Ajasja Ljubetič
    Ajasja Ljubetič says:

    I think the code following the line “Will be replaced by this one.” has an error

    Cmnd.Parameters.ByName(‘NAME’).SetAnsiString(‘USA’);
    is probably meant to be
    Cmnd.Parameters.ByName(‘COUNTRYNAME’).SetAnsiString(‘USA’);

    Best regards,
    Ajasja

    Reply
  2. Dmitry Arefiev
    Dmitry Arefiev says:

    It looks like EMBD created an issue and you showed how to solve it 🙂

    The class helpers also may be useful in resolving of the classic issue, when the developers are using the literals instead of parameters. The issue is due to, probably at first, that parameters usage requires more coding. The following my article shows how to “catch both hares” – to code less and still use the parameters. Take it as ironical 🙂

    http://www.da-soft.com/blogs/the-parameterized-sql-queries-for-the-lazy.html

    Reply

Trackbacks & Pingbacks

  1. […] more from the original source: Parameterized queries with dbExpress (DBX) Framework | Andreano … No […]

  2. […] This post was mentioned on Twitter by Embarcadero Tech, Andreano Lanusse and Mário Guedes, Embarcadero Asia. Embarcadero Asia said: Parameterized queries with dbExpress (DBX) Framework http://bit.ly/bStkyj […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.