Can dbExpress's TSQLQuery use ? as parameters?

Question

We've porting code to Delphi XE2, and need to change our data access components from third party ODBCExpress which is no longer in business, to dbExpress's TSQLQuery.

We have parametrized SQL query such as:

sSQL :=
  'UPDATE ZTestData SET '+
  ' StringField =?, '+
  ' IntField = ?, '+
  ' DecimalField = ?, '+
  ' BooleanField = ?, '+
  ' DateTimeField = ?, '+
  ' TextField = ? '+
  ' WHERE UniqueID = 3';

if we use the following code:

var
  qry:TSQLQuery;
begin
  qry.Close;
  qry.SQL.Text := sSQL;
  ShowMessage(IntToStr(qry.Params.Count));
end;

It returns 0, so we're unable to get the bindings working, but if we change sSQL to:

sSQL :=
  'UPDATE ZTestData SET '+
  ' StringField =:Param1, '+
  ' IntField = :Param2, '+
  ' DecimalField = ?, '+
  ' BooleanField = ?, '+
  ' DateTimeField = ?, '+
  ' TextField = ? '+
  ' WHERE UniqueID = 3';

It returns 2.

It's going to be a big hassle to change all the SQL queries to the new parameter syntax. Is there anyway for the TSQLQuery to recognize the ? syntax?

I see that DBXCommon.TDBXCommand uses the ? syntax:

http://www.andreanolanusse.com/en/parameterized-queries-with-dbexpress-dbx-framework/

But it would mean throwing away our code that uses TSQLQuery. What's the quickest/easiest way to resolve this? What's the difference between TSQLQuery and TDBXCommand anyway, in terms of what's relevant to me?

score 8 · Answer 1 · answered Feb 14 '12 at 05:51

I think the fastest approach is to use class helpers that will implement this functionality something like:

type
  TMyParamsHelper = class Helper for TSQLQuery
  public
    function SetupParams(AParamList: array of Variant): Boolean; overload;
    function SetupParams(ASQL: string; AParamList: array of Variant): Boolean; overload;
  end;

// implementation

function TMyParamsHelper.SetupParams(AParamList: array of Variant): Boolean;
var
  Index: Integer;
begin
  // here you can process the SQL as text and replace each ?
  // with :paramINDEX
  // first occurence of ? will be :param0, second will be :param1, etc.
  // implement your replace algorithm before the "for loop"
  for Index := Low(AParamList) to High(AParamList) do
    ParamByName(Format(':param%d', [Index])).AsVaraint := AParamList[ Index ];
  // of course you need to do it in a try...except block and return TRUE|FALSE
end;

function TMyParamsHelper.SetupParams(ASQL: string; AParamList: array of Variant): Boolean;
begin
  SQL.Text := ASQL;
  Result := SetupParams( AParamList );
end;

So now all you have to do is call:

...
ASQLQueryVariable.SetupParams([2012, 'Hello World', 2.14, 'It WORKS!']);
// or
ASQLQueryVariable.SetupParams(
  'UPDATE MyTable SET Year = ?, Title = ?, Cents = ?, Comment = ? WHERE <CLAUSE HERE>',
  [2012, 'Hello World', 0.02, 'It WORKS!']
);
...

Note: I'm writing this off the top of my head, could have typos and might not be the best approach...

Let me know how this works out for you, I always wanted the "?" in stead of ParamByName but was too lazy to implement it...

See my answer. DB.TParams.ParseSQL is a method that converts parameters to question marks, if that's the route you want to go. I much prefer ParamByName myself. — Robo, Feb 16 '12 at 22:00

score 5 · Answer 2 · answered Feb 14 '12 at 06:16

Non-trivial approach:

subsclass TMyQuery from TSQLQuery;
in constructor of TMyQuery set TStringList(SQL).OnChange to your own QueryChanged method. See TSQLQuery.QueryChanged in SqlExpr.pas unit for details, what it is doing.
there you will need to replace SetParamsFromSQL call with your own, which will parse SQL text and create parameter object for each '?' occurence.

More simple approach:

create procedure, wich will get a SQL string and Params collection;
the procedure will parse SQL text and create parameter object for each '?' occurence;
set TSQLQuery.ParamCheck to False and call the proc after setting the SQL property.

Finally consider to use 3d party solutions, like AnyDAC. It supports ODBC and '?' parameter markers.

score 0 · Accepted Answer · answered Feb 16 '12 at 21:49

I ended up writing a method to convert question marks in the query to :param1 style parameters. Interestingly, Delphi has a DB.TParams.ParseSQL method that converts parameters to question marks. This method is basically a reverse of that.

function THstmt.AddParamsToSQL(const SQL: String): String;
var
  LiteralChar: Char;
  CurPos, StartPos, BeginPos: PChar;
  ParamCount:Integer;
begin
  //Locates the question marks in an SQL statement
  //and replaces them with parameters.
  //i.e. the reverse of DB.TParams.ParseSQL

  //This method is base on DB.TParams.ParseSQL

  //For example, given the SQL string
  //SELECT * FROM EMPLOYEES WHERE (ID = ?) AND (NAME = ?)

  //ParseSQL returns the string
  //SELECT * FROM EMPLOYEES WHERE (ID = :1) AND (NAME = :2)

  Result := '';

  ParamCount := 0;
  StartPos := PChar(SQL);
  BeginPos := StartPos;
  CurPos := StartPos;
  while True do
  begin
    // Fast forward
    while True do
    begin
      case CurPos^ of
        #0, '?', '''', '"', '`':
          Break;
      end;
      Inc(CurPos);
    end;

    case CurPos^ of
      #0: // string end
        Break;
      '''', '"', '`': // literal
      begin
        LiteralChar := CurPos^;
        Inc(CurPos);
        // skip literal, escaped literal chars must not be handled because they
        // end the string and start a new string immediately.
        while (CurPos^ <> #0) and (CurPos^ <> LiteralChar) do
          Inc(CurPos);
        if CurPos^ = #0 then
          Break;
        Inc(CurPos);
      end;
      '?': //parameter
      begin
        Inc(CurPos);
        Inc(ParamCount);
        Result := Result + Copy(SQL, StartPos - BeginPos + 1, CurPos - StartPos - 1) + ':' + IntToStr(ParamCount);
        StartPos := CurPos;
      end;
    end;
  end;
  Result := Result + Copy(SQL, StartPos - BeginPos + 1, CurPos - StartPos);
end;

Can dbExpress's TSQLQuery use ? as parameters?

3 Answers3