Parameterized queries with dbExpress (DBX) Framework

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

Related posts:

Using dbExpress Framework on Windows and Mac with C++Builder Android apps connected to Delphi DataSnap Server Connecting to PostgreSQL through dbExpress ODBC driver DataSnap 2010 authentication throught TCP/IP Transport