ExecSqlEx Params

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Freezer_86
Posts: 9
Joined: Thu 14 Feb 2013 09:11

ExecSqlEx Params

Post by Freezer_86 » Wed 07 Dec 2016 10:05

After upgrading project from version from ODAC version 6 to 9 i findout that was changed logic of working ExecSqlEx with parameters.

Earlier you can call:

Code: Select all

  ExecSQLEx( ' begin :result := GetSysFilter(:p_ClassID, :p_UserID); end;',
             [ 'p_ClassID', p_ClassID,
               'p_UserID', p_UserID
             ]
           );
Odac link all params value by name and all works fine. Now - "Array of parameter values arranged in the same order as they appear in SQL statement."(from documentation). So using of this function lose sense, it's same ExecSQL but with ignoring each param[i mod 2 = 0] (from user side).

I check your sources and find out that it's can be easily fixed by simply changing 2 lines of code, from:

Code: Select all

      varByte, varWord, varLongWord, varShortInt, varSmallint, varInteger:
        FCommand.Params[i].AsInteger := Params[i * 2 + 1];
      else
        FCommand.Params[i].Value := Params[i * 2 + 1];
to:

Code: Select all

      varByte, varWord, varLongWord, varShortInt, varSmallint, varInteger:
        FCommand.ParamByName(PName).AsInteger := Params[i * 2 + 1];
      else
        FCommand.ParamByName(PName).Value := Params[i * 2 + 1];
But using of manually changed sources it's not a good way of working(for future updates), so question: Can you do this(or you got better solution) changes in your sources?

P.S. Changing of my sources it's very hard task, because it's using as base for uses scripts with thousands lines of code.

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: ExecSqlEx Params

Post by MaximG » Wed 07 Dec 2016 13:58

Thank you for the information. We fixed the TСustomDAConnection.ExecSQLEx method implementation. The fix will be included in the next build of our products. If you are an owner of Professional Edition with source code license, make the following changes in the DBAccess.pas file :

Code: Select all

  …
  for i := 0 to (High(Params) + 1) div 2 - 1  do begin
    if VarIsStr(Params[i * 2]) then
      PName := Params[i * 2]
    else
      ParamsError;

    Param := FCommand.ParamByName(PName);

    if FCommand.IsInOutParamSupported then
      Param.ParamType := ptInputOutput
    else
      Param.ParamType := ptUnknown;

    case VarType(Params[i * 2 + 1]) of
      varByte, varWord, varLongWord, varShortInt, varSmallint, varInteger:
        Param.AsInteger := Params[i * 2 + 1];
      else
        Param.Value := Params[i * 2 + 1];
    end;
  end;
  .

Post Reply