[DUG] Can dbExpress's TSQLQuery use ? as parameters?

[Robo]

Yes we currently set parameter values by index.

We ended up writing a method to locates the question marks in an SQL
statement and replaces them with parameters, then use TSQLQuery in the
way it was intended to.

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;

On Tue, Feb 14, 2012 at 6:08 PM, Todd Martin <todd.martin.nz at gmail.com> wrote:
>
> Are you currently setting the parameter values by index then?
>
> Using regular expressions, search
>
> \'(\w)+\s\=\s\?
>
> and replace  with
>
> '$1 = :$1
>
> so that
>
> StringField =?,
>
> becomes
>
> StringField = :StringField,
>
> It should take no time at all.
>
> Todd.
>
>
>
> 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?
>
> Robo
>
>
>
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at listserver.123.net.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at listserver.123.net.nz with Subject: unsubscribe
>
>
>
> --
> Passion is no substitute for reason
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at listserver.123.net.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at listserver.123.net.nz with Subject: unsubscribe