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
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;