Zeoslib Portal

This is the official forum of ZeosLib providing support to all ZeosLib users.
https://zeoslib.sourceforge.io/

FreeTDS, MS SQL, Stored procedures problem

https://zeoslib.sourceforge.io/viewtopic.php?t=33483

Page 1 of 1

FreeTDS, MS SQL, Stored procedures problem

Posted: 23.10.2015, 10:18
by dstaniak
I'm intensively using Zeos in my everyday work with MS SQL Server. To this day I have used the ntwdblib.dll, but recently I started to port my software to Linux. Therefore I'm forced to use FreeTDS. Suddenly after switching to FreeTDS my stored procedures stopped working with errors about missing parameters. For two days I've been examining the problem and I have found the source of it.

There are two error sources in ZDbcDbLibStatement.pas , both in TZDBLibCallableStatement.ExecutePrepared.

First of all one cannot use single variable (like DatInteger and other Dat...) to pass input parameters to FreeTDS RPC because it stores the value's addresses instead of actual values. This means that if a stored procedure has 2 integer parameters, at the calling time both will have the value of the last passed parameter because of using single DatInteger variable for all parameters. That applies to all passed parameters and is an obvious mistake.
I've fixed it by declaring arrays for all parameter types used.

The second error is when passing input parameters that are converted to strings (stString, stUnicodeString, stDate, stTime, stTimeStamp, stAsciiStream, stUnicodeStream, stBinaryStream, stBytes).

How stated in FreeTDS source file rpc.c in dbcrpcparam function:

Code: Select all

	
* Well, maxlen should be used only for output parameter however it seems
* that ms implementation wrongly require this 0 for NULL variable
* input parameters, so fix it
So, the maxlen parameter should not be passed for input parameters, but, currently, it is.
My fix here was to replace MaxInt to -1 for all input parameters FPlainDriver.dbRpcParam calls.

Below is the complete fixed TZDBLibCallableStatement.ExecutePrepared:

Code: Select all

function TZDBLibCallableStatement.ExecutePrepared: Boolean;
var
  S: RawByteString;
  I, ParamIndex, DatLen: Integer;
  RetParam: Byte;

  DatBoolean: Boolean;
  DatByte: Byte;
  DatShort: SmallInt;
  DatInteger: Integer;
  DatFloat: Single;
  DatDouble: Double;
  DatString: RawByteString;
  DatMoney: Currency;
  DatDBDATETIME: DBDATETIME;
  DatBytes: TByteDynArray;
  Temp: TZVariant;
  ParamType: TZSQLType;
  TempBlob: IZBlob;

  ABooleans: Array of Boolean;
  ABooleansCount, ABooleansNo: Integer;
  ABytes: Array of Byte;
  ABytesCount, ABytesNo: Integer;
  AShorts: Array of SmallInt;
  AShortsCount, AShortsNo: Integer;
  AIntegers: Array of Integer;
  AIntegersCount, AIntegersNo: Integer;
  AFloats: Array of Single;
  AFloatsCount, AFloatsNo: Integer;
  ADoubles: Array of Double;
  ADoublesCount, ADoublesNo: Integer;
  AStrings: Array of RawByteString;
  AStringsCount, AStringsNo: Integer;

  OthersCount : Integer;

begin
  S := {$IFDEF WITH_UNITANSISTRINGS}AnsiStrings.{$ENDIF}Trim(ASql);
  if FPLainDriver.dbRPCInit(FHandle, Pointer(S), 0) <> DBSUCCEED then
    FDBLibConnection.CheckDBLibError(lcOther, 'EXECUTEPREPARED:dbRPCInit');

  ABooleansCount := 0;
  ABytesCount := 0;
  AShortsCount := 0;
  AIntegersCount := 0;
  AFloatsCount := 0;
  ADoublesCount := 0;
  AStringsCount := 0;
  OthersCount := 0;

  for I := 1 to InParamCount - 1 do begin
    if DefVarManager.IsNull(InParamValues[I]) and (InParamTypes[I] <> stUnknown) then continue;

    ParamType := InParamTypes[I];
    if ParamType = stUnknown then
      ParamType := OutParamTypes[I];

    case ParamType of
      stBoolean:              Inc(ABooleansCount);
      stByte:                 Inc(ABytesCount);
      stShort:                Inc(AShortsCount);
      stInteger, stLong:      Inc(AIntegersCount);
      stFloat:                Inc(AFloatsCount);
      stDouble, stBigDecimal: Inc(ADoublesCount);
      stString:               Inc(AStringsCount);
      stUnicodeString:        Inc(AStringsCount);
      stDate:                 Inc(AStringsCount);
      stTime:                 Inc(AStringsCount);
      stTimeStamp:            Inc(AStringsCount);
      stAsciiStream, stUnicodeStream, stBinaryStream:
                              Inc(AStringsCount);
      stBytes:                Inc(AStringsCount);
    else
      Inc(OthersCount);
    end;
  end;

  SetLength(ABooleans,ABooleansCount);
  SetLength(ABytes,ABytesCount);
  SetLength(AShorts,AShortsCount);
  SetLength(AIntegers,AIntegersCount);
  SetLength(AFloats,AFloatsCount);
  SetLength(ADoubles,ADoublesCount);
  SetLength(AStrings,AStringsCount);

  ABooleansNo := 0;
  ABytesNo := 0;
  AShortsNo := 0;
  AIntegersNo := 0;
  AFloatsNo := 0;
  ADoublesNo := 0;
  AStringsNo := 0;

  for I := 1 to InParamCount - 1 do//The 0 parameter is the return value
  begin
    RetParam := 0;
    if OutParamTypes[I] <> stUnknown then
      RetParam := DBRPCRETURN;

    ParamType := InParamTypes[I];
    if ParamType = stUnknown then
      ParamType := OutParamTypes[I];

    if DefVarManager.IsNull(InParamValues[I]) and (InParamTypes[I] <> stUnknown) then
    begin
      if FDBLibConnection.FreeTDS then
        FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
        ConvertSqlTypeToFreeTDSType(InParamTypes[I]), -1, 0, nil)
      else
        FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
        ConvertSqlTypeToDBLibType(InParamTypes[I]), -1, 0, nil)
    end
    else
    begin
      case ParamType of
        stBoolean:
          begin
            ABooleans[ABooleansNo] := SoftVarManager.GetAsBoolean(InParamValues[I]);
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLINT1], -1, -1, @ABooleans[ABooleansNo]);
            Inc(ABooleansNo);
          end;
        stByte:
          begin
            DatBytes[ABytesNo] := Byte(SoftVarManager.GetAsInteger(InParamValues[I]));
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLINT1], -1, -1, @ABytes[ABytesNo]);
            Inc(ABytesNo);
          end;
        stShort:
          begin
            AShorts[AShortsNo] := SmallInt(SoftVarManager.GetAsInteger(InParamValues[I]));
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLINT2], -1, -1, @AShorts[AShortsNo]);
            Inc(AShortsNo);
          end;
        stInteger, stLong:
          begin
            AIntegers[AIntegersNo] := Integer(SoftVarManager.GetAsInteger(InParamValues[I]));
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLINT4], -1, -1, @AIntegers[AIntegersNo]);
            Inc(AIntegersNo);
          end;
        stFloat:
          begin
            AFloats[AFloatsNo] := SoftVarManager.GetAsFloat(InParamValues[I]);
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLFLT4], -1, -1, @AFloats[AFloatsNo]);
            Inc(AFloatsNo);
          end;
        stDouble, stBigDecimal:
          begin
            ADoubles[ADoublesNo] := SoftVarManager.GetAsFloat(InParamValues[I]);
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLFLT8], -1, -1, @ADoubles[ADoublesNo]);
            Inc(ADoublesNo);
          end;
        stString:
          begin
            AStrings[AStringsNo] := ZPlainString(SoftVarManager.GetAsString(InParamValues[I]));
            if AStrings[AStringsNo] = '' then
              DatLen := 1
            else
              DatLen := Length(AStrings[AStringsNo]);
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLCHAR], -1, DatLen, PAnsiChar(AStrings[AStringsNo]));
            Inc(AStringsNo);
          end;
        stUnicodeString:
          begin
            AStrings[AStringsNo] := UTF8Encode(SoftVarManager.GetAsUnicodeString(InParamValues[I]));
            if AStrings[AStringsNo] = '' then
              DatLen := 1
            else
              DatLen := Length(AStrings[AStringsNo]);
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLCHAR], -1, DatLen, PAnsiChar(AStrings[AStringsNo]));
            Inc(AStringsNo);
          end;
        stDate:
          begin
            AStrings[AStringsNo] := AnsiString(FormatDateTime('yyyymmdd',
              SoftVarManager.GetAsDateTime(InParamValues[I])));
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLCHAR], -1, Length(AStrings[AStringsNo]), PAnsiChar(AStrings[AStringsNo]));
            Inc(AStringsNo);
          end;
        stTime:
          begin
            AStrings[AStringsNo] := AnsiString(FormatDateTime('hh":"mm":"ss":"zzz',
              SoftVarManager.GetAsDateTime(InParamValues[I])));
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLCHAR], -1, Length(AStrings[AStringsNo]), PAnsiChar(AStrings[AStringsNo]));
            Inc(AStringsNo);
          end;
        stTimeStamp:
          begin
            AStrings[AStringsNo] := AnsiString(FormatDateTime('yyyymmdd hh":"mm":"ss":"zzz',
              SoftVarManager.GetAsDateTime(InParamValues[I])));
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLCHAR], -1, Length(AStrings[AStringsNo]), PAnsiChar(AStrings[AStringsNo]));
            Inc(AStringsNo);
          end;
        stAsciiStream, stUnicodeStream, stBinaryStream:
          begin
            TempBlob := SoftVarManager.GetAsInterface(InParamValues[I]) as IZBlob;
            AStrings[AStringsNo] := TempBlob.GetString;
            if AStrings[AStringsNo] = '' then
              DatLen := 1
            else
              DatLen := Length(AStrings[AStringsNo]);
            if ParamType = stBinaryStream then
              FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
                FPlainDriver.GetVariables.datatypes[Z_SQLBINARY], -1, Length(AStrings[AStringsNo]), PAnsiChar(AStrings[AStringsNo]))
            else
              FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
                FPlainDriver.GetVariables.datatypes[Z_SQLTEXT], FPlainDriver.GetVariables.dboptions[Z_TEXTSIZE], DatLen, PAnsiChar(AStrings[AStringsNo]));
            Inc(AStringsNo);
          end;
        stBytes:
          begin
            AStrings[AStringsNo] := AnsiString(SoftVarManager.GetAsString(InParamValues[I]));
            FPlainDriver.dbRpcParam(FHandle, nil, RetParam,
              FPlainDriver.GetVariables.datatypes[Z_SQLBINARY], -1, Length(AStrings[AStringsNo]), PAnsiChar(AStrings[AStringsNo]));
          end;
      else
        FPlainDriver.dbRpcParam(FHandle, nil, 0, FPlainDriver.GetVariables.datatypes[Z_SQLCHAR], 0, 0, nil);
    end;
  end;
  end;

  if FPLainDriver.dbRpcExec(FHandle) <> DBSUCCEED then
    FDBLibConnection.CheckDBLibError(lcOther, 'EXECUTEPREPARED:dbRPCExec');
  FetchResults;
  Result := GetMoreResults;

  if FPLainDriver.dbHasRetStat(FHandle) then
    DefVarManager.SetAsInteger(Temp, FPlainDriver.dbRetStatus(FHandle))
  else
    Temp := NullVariant;
  OutParamValues[0] := Temp; //set function RETURN_VALUE

  ParamIndex := 1;
  for I := 1 to OutParamCount - 1 do
  begin
    if OutParamTypes[I] = stUnknown then
      Continue;
    if FPlainDriver.dbRetData(FHandle, ParamIndex) = nil then
      Temp := NullVariant
    else
    begin
      if FDBLibConnection.FreeTDS then
        case FPLainDriver.dbRetType(FHandle, ParamIndex) of
          TDSSQLCHAR, TDSSQLBINARY:
            begin
              DatLen := FPLainDriver.dbRetLen(FHandle, ParamIndex);
              SetLength(DatBytes, DatLen);
              Move(PAnsiChar(FPLainDriver.dbRetData(FHandle, ParamIndex))^,
                DatBytes[0], Length(DatBytes));
              DefVarManager.SetAsString(Temp, String(BytesToStr(DatBytes)));
            end;
          TDSSQLINT1:
            DefVarManager.SetAsInteger(Temp,
              PByte(FPlainDriver.dbRetData(FHandle, ParamIndex))^);
          TDSSQLINT2:
            DefVarManager.SetAsInteger(Temp,
              PSmallInt(FPLainDriver.dbRetData(FHandle, ParamIndex))^);
          TDSSQLINT4:
            DefVarManager.SetAsInteger(Temp,
              PInteger(FPLainDriver.dbRetData(FHandle, ParamIndex))^);
          TDSSQLFLT4:
            DefVarManager.SetAsFloat(Temp,
              PSingle(FPLainDriver.dbRetData(FHandle, ParamIndex))^);
          TDSSQLFLT8:
            DefVarManager.SetAsFloat(Temp,
              PDouble(FPLainDriver.dbRetData(FHandle, ParamIndex))^);
          TDSSQLMONEY4:
            begin
              FPlainDriver.dbConvert(FHandle, TDSSQLMONEY4,
                FPlainDriver.dbRetData(FHandle, ParamIndex), 4, TDSSQLMONEY,
                @DatMoney, 8);
              DefVarManager.SetAsFloat(Temp, DatMoney);
            end;
          TDSSQLMONEY:
            DefVarManager.SetAsFloat(Temp,
              PCurrency(FPLainDriver.dbRetData(FHandle, ParamIndex))^);
          TDSSQLDECIMAL:
            begin
              FPLainDriver.dbConvert(FHandle, TDSSQLDECIMAL,
                FPLainDriver.dbRetData(FHandle, ParamIndex),
                FPLainDriver.dbRetLen(FHandle, ParamIndex),
                TDSSQLFLT8, @DatDouble, 8);
              DefVarManager.SetAsFloat(Temp, DatDouble);
            end;
          TDSSQLNUMERIC:
            begin
              FPLainDriver.dbConvert(FHandle, TDSSQLNUMERIC,
                FPLainDriver.dbRetData(FHandle, ParamIndex),
                FPLainDriver.dbRetLen(FHandle, ParamIndex),
                TDSSQLFLT8, @DatDouble, 8);
              DefVarManager.SetAsFloat(Temp, DatDouble);
            end;
          TDSSQLDATETIM4:
            begin
              FPLainDriver.dbConvert(FHandle, TDSSQLDATETIM4,
                FPLainDriver.dbRetData(FHandle, ParamIndex), 4,
                TDSSQLDATETIME, @DatDBDATETIME, 8);
              DefVarManager.SetAsDateTime(Temp,
                DatDBDATETIME.dtdays + 2 + (DatDBDATETIME.dttime / 25920000));
            end;
          TDSSQLDATETIME:
            begin
              DatDBDATETIME := PDBDATETIME(
                FPLainDriver.dbRetData(FHandle, ParamIndex))^;
              DefVarManager.SetAsDateTime(Temp,
                DatDBDATETIME.dtdays + 2 + (DatDBDATETIME.dttime / 25920000));
            end;
          else
            Temp := NullVariant;
        end
      else
        case FPLainDriver.dbRetType(FHandle, ParamIndex) of
          DBLIBSQLCHAR, DBLIBSQLBINARY:
            begin
              DatLen := FPLainDriver.dbRetLen(FHandle, ParamIndex);
              SetLength(DatBytes, DatLen);
              Move(PAnsiChar(FPLainDriver.dbRetData(FHandle, ParamIndex))^,
                DatBytes[0], Length(DatBytes));
              DefVarManager.SetAsString(Temp, String(BytesToStr(DatBytes)));
            end;
          DBLIBSQLINT1:
            DefVarManager.SetAsInteger(Temp,
              PByte(FPlainDriver.dbRetData(FHandle, ParamIndex))^);
          DBLIBSQLINT2:
            DefVarManager.SetAsInteger(Temp,
              PSmallInt(FPLainDriver.dbRetData(FHandle, ParamIndex))^);
          DBLIBSQLINT4:
            DefVarManager.SetAsInteger(Temp,
              PInteger(FPLainDriver.dbRetData(FHandle, ParamIndex))^);
          DBLIBSQLFLT4:
            DefVarManager.SetAsFloat(Temp,
              PSingle(FPLainDriver.dbRetData(FHandle, ParamIndex))^);
          DBLIBSQLFLT8:
            DefVarManager.SetAsFloat(Temp,
              PDouble(FPLainDriver.dbRetData(FHandle, ParamIndex))^);
          DBLIBSQLMONEY4:
            begin
              FPlainDriver.dbConvert(FHandle, DBLIBSQLMONEY4,
                FPlainDriver.dbRetData(FHandle, ParamIndex), 4, DBLIBSQLMONEY,
                @DatMoney, 8);
              DefVarManager.SetAsFloat(Temp, DatMoney);
            end;
          DBLIBSQLMONEY:
            DefVarManager.SetAsFloat(Temp,
              PCurrency(FPLainDriver.dbRetData(FHandle, ParamIndex))^);
          DBLIBSQLDECIMAL:
            begin
              FPLainDriver.dbConvert(FHandle, DBLIBSQLDECIMAL,
                FPLainDriver.dbRetData(FHandle, ParamIndex),
                FPLainDriver.dbRetLen(FHandle, ParamIndex),
                DBLIBSQLFLT8, @DatDouble, 8);
              DefVarManager.SetAsFloat(Temp, DatDouble);
            end;
          DBLIBSQLNUMERIC:
            begin
              FPLainDriver.dbConvert(FHandle, DBLIBSQLNUMERIC,
                FPLainDriver.dbRetData(FHandle, ParamIndex),
                FPLainDriver.dbRetLen(FHandle, ParamIndex),
                DBLIBSQLFLT8, @DatDouble, 8);
              DefVarManager.SetAsFloat(Temp, DatDouble);
            end;
          DBLIBSQLDATETIM4:
            begin
              FPLainDriver.dbConvert(FHandle, DBLIBSQLDATETIM4,
                FPLainDriver.dbRetData(FHandle, ParamIndex), 4,
                DBLIBSQLDATETIME, @DatDBDATETIME, 8);
              DefVarManager.SetAsDateTime(Temp,
                DatDBDATETIME.dtdays + 2 + (DatDBDATETIME.dttime / 25920000));
            end;
          DBLIBSQLDATETIME:
            begin
              DatDBDATETIME := PDBDATETIME(
                FPLainDriver.dbRetData(FHandle, ParamIndex))^;
              DefVarManager.SetAsDateTime(Temp,
                DatDBDATETIME.dtdays + 2 + (DatDBDATETIME.dttime / 25920000));
            end;
          else
            Temp := NullVariant;
        end;
    end;
    OutParamValues[I] := Temp;
    Inc(ParamIndex);
  end;

//Workaround for sybase. the dbCount does not work, so a select @@rowcount is
//made but this cleared the returned output parameters, so this is moved here
//after reading the output parameters
  FetchRowCount;

  DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol,
    Format('EXEC %s', [SQL]));
end;
I hope that this will help people who are forced to use MS SQL connection!

Re: FreeTDS, MS SQL, Stored procedures problem

Posted: 09.02.2016, 11:19
by marsupilami
I put these changes into the 7.2 SVN.

FreeTDS MS SQL Stored procedures problem

Posted: 19.10.2016, 22:08
by Alecarrka
Hi

I'm working on Cognos 8.4.0.
Currently I have about 10 datasources to support.

I've already created one FM model which has macros/parameters inserted in every Query Subject at DataSource Layer.

The case is that some of my reports are based on Stored Procedures MS SQL and there is a need to have same "dynamic datasource" feature pluged in...


My issue is that I can find any solution to cover the need...


Please let me know if you had any similar cases or have any ideas how to carry this out.


Thanks
LM
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited