Same parameter more than once in a query

[MaksymK]

I'm moving development to another server and decided to update all the dependencies. I was using Zeos version 7.2.1-rc and now I've checked out the latest trunk from SVN. I'm getting an errors for queries that contain the same parameter more than once. RDBMS: MariaDB 10.5.8.

Example.
Query:

Code: Select all

SELECT :pString as STRING1,
       :pString as STRING2,
       :pInteger as INTEGER1,
       :pInteger as INTEGER2

Parameter types:
- pString - ftString
- pInteger - ftInteger
Exception message: 'STRING' is not a valid integer value
Exception location (https://sourceforge.net/p/zeoslib/code- ... .pas#l1612):

Code: Select all

      stShort, stSmall, stInteger: BindSInteger(Index, stBoolean, RawToInt(Buf));

I believe that the problem is caused by the loop inside the

Code: Select all

TZAbstractRODataset.InternalPrepare

(https://sourceforge.net/p/zeoslib/code- ... .pas#l4143). It loops through the TDataSet.Params (2 items - pString, pInteger):

Code: Select all

      for i := 0 to Params.Count -1 do
        if (Params[I].ParamType <> ptUnknown) then begin
          FHasOutParams := FHasOutParams or (Ord(Params[I].ParamType) >= Ord(ptOutput));
          Statement.RegisterParameter(i, ConvertDatasetToDbcType(Params[I].DataType),
            DatasetTypeToProcColDbc[Params[i].ParamType], Params[i].Name,
            Max(Params[i].Precision, Params[i].Size), Params[i].NumericScale);
        end;

, but the

Code: Select all

TZAbstractRODataset.SetStatementParams

(https://sourceforge.net/p/zeoslib/code- ... .pas#l2332) loops (in some cases) through the

Code: Select all

FSQL.Statements[0].ParamNamesArray

(4 items - pString, pString, pInteger, pInteger):

Code: Select all

  if (not ParamCheck) and (not Assigned(ParamNames)) and (FParams.Count > 0) then begin
    for I := 0 to Params.Count -1 do begin
      Param := Params[i];
      if not Assigned(Param) or (Param.ParamType in [ptOutput, ptResult]) then
        Continue;
      SetStatementParam(I{$IFNDEF GENERIC_INDEX}+1{$ENDIF}, Statement, Param);
    end;
  end else begin
    TempParam := {$IFNDEF DISABLE_ZPARAM}TZParam{$ELSE}TParam{$ENDIF}.Create(nil);
    try
      for I := Low(ParamNames) to High(ParamNames) do begin
        ...
        SetStatementParam(I{$IFNDEF GENERIC_INDEX}+1{$ENDIF}, Statement, Param);
      end;
    finally
      TempParam.Free;
    end;

I could get rid of the exception reorganizing

Code: Select all

TZAbstractRODataset.InternalPrepare

in the same fashion, but I'm not sure if it covers all the cases:

Code: Select all

procedure TZAbstractRODataset.RegisterStatementParameter(ParameterIndex: Integer; Param: {$IFNDEF DISABLE_ZPARAM}TZParam{$ELSE}TParam{$ENDIF});
begin
  if (Assigned(Param)) and (Param.ParamType <> ptUnknown) then begin
    FHasOutParams := FHasOutParams or (Ord(Param.ParamType) >= Ord(ptOutput));
    Statement.RegisterParameter(ParameterIndex, ConvertDatasetToDbcType(Param.DataType),
      DatasetTypeToProcColDbc[Param.ParamType], Param.Name,
      Max(Param.Precision, Param.Size), Param.NumericScale);
  end;
end;

{**
  Performs the internal preparation of the query.
}
procedure TZAbstractRODataset.InternalPrepare;
var
  I: Integer;
  ParamNames: TStringDynArray;
begin
  CheckSQLQuery;
  CheckInactive;  //AVZ - Need to check this
  CheckConnected;

  Connection.ShowSQLHourGlass;
  try
    if (FSQL.StatementCount > 0) and((Statement = nil) or (Statement.GetConnection.IsClosed)) then begin
      Statement := CreateStatement(FSQL.Statements[0].SQL, Properties);
      ParamNames := FSQL.Statements[0].ParamNamesArray;
      FHasOutParams := False;
      if (not ParamCheck) and (not Assigned(ParamNames)) and (Params.Count > 0) then begin
        for I := 0 to Params.Count -1 do begin
          RegisterStatementParameter(I, Params[I]);
        end;
      end else begin
        for I := Low(ParamNames) to High(ParamNames) do begin
          RegisterStatementParameter(I, Params.ParamByName(ParamNames[I]));
        end;
      end;
    end else if (Assigned(Statement)) then
      Statement.ClearParameters;
  finally
    Connection.HideSQLHourGlass;
  end;
end;

P.S. I've attached a sample project to reproduce the issue.

[marsupilami]

Hello,

I created a ticket omn the bug tracker for this issue: https://sourceforge.net/p/zeoslib/tickets/467/.

Best regards,

Jan

[EgonHugeist]

Hello MaksymK & welcome to the forum.

Thanks for reporting and the nice test. I was able to resolve your testcase. See https://sourceforge.net/p/zeoslib/tickets/467.

So please update from Trunk and report your findings.