Nullable<T> Type returns param type of ftBlob and causes Firedac to fail with error

[Todd Flora]

Using the Nullable<T> type causes issues when saving data. Specifically the type of the param for the insert statement is set to ftBlob instead of the Nullable's generic type and this causes Oracle / Firedac to fail with the following exception:

The offending code seems to be in the Spring.Persistence.SQL.Params unit in the FromTypeInfoToFieldType method. This method just assumes all tkRecord types are blobs and therefore does not look at the underlying generic type of this special record type.

function FromTypeInfoToFieldType(ATypeInfo: PTypeInfo): TFieldType;
begin
  case ATypeInfo.Kind of
    tkUnknown: Result := ftUnknown;
    tkInteger: Result := ftInteger;
    tkChar, tkLString, tkString: Result := ftString;
    tkEnumeration, tkSet: Result := ftInteger;
    tkFloat: Result := ftFloat;
    tkClass: Result := ftBlob;
    tkWChar, tkWString, tkUString: Result := ftWideString;
    tkVariant: Result := ftVariant;
-> tkArray, tkRecord, tkInterface, tkDynArray: Result := ftBlob;<-----See Here
    tkInt64: Result := ftLargeint;
    tkClassRef: Result := ftReference;
    tkPointer: Result := ftReference;
    else
      Result := ftUnknown;
  end;
end;

Not only should the type actually probably be the underlying nullable type but also when dealing with Oracle there is a special field type for Blobs called ftOraBlob and ftOraClob that will need to be used, I believe.

[Todd Flora]

Here is what I have done for now to fix this. I am sure there is a better implementation but this is working for now. Like moving this method into the Adapter so that DB Specific code can be written there.

In Spring.Persistence.SQL.Params Unit modify the FromTypeInfoToFieldType method to handle Nullable Types and also to recognize when Oracle is the database so as to use ftOraBlob

Also Handle DateTime Type as DateTime and not Float.

function FromTypeInfoToFieldType(ATypeInfo: PTypeInfo; IsOracle : Boolean): TFieldType;

  function GetNullableTypeInfo : PTypeInfo;
  var
    ctx : TRTTIContext;
    rType : TRttiType;
    AValue : TRttiField;
  begin
    ctx := TRTTIContext.Create;
    try
      rType := ctx.GetType(ATypeInfo);
      AValue := rType.GetField('FValue');
      Result := AValue.FieldType.Handle
    finally
      Ctx.Free;
    end;
  end;

begin
  if (ATypeInfo.Kind = tkRecord) and
     (ATypeInfo.TypeName.StartsWith('Nullable')) then
    AtypeInfo := GetNullableTypeInfo;

  if ATypeInfo.TypeName = 'TDateTime' then
    Result := ftDateTime
  else
  case ATypeInfo.Kind of
    tkUnknown: Result := ftUnknown;
    tkInteger: Result := ftInteger;
    tkChar, tkLString, tkString: Result := ftString;
    tkEnumeration, tkSet: Result := ftInteger;
    tkFloat: Result := ftFloat;
    tkClass: if IsOracle then Result := ftOraBlob else Result := ftBlob;
    tkWChar, tkWString, tkUString: Result := ftWideString;
    tkVariant: Result := ftVariant;
    tkArray, tkRecord, tkInterface, tkDynArray: if IsOracle then Result := ftOraBlob else Result := ftBlob;
    tkInt64: Result := ftLargeint;
    tkClassRef: Result := ftReference;
    tkPointer: Result := ftReference;
    else
      Result := ftUnknown;
  end;
end;

Modify the SetParamTypeFromTypeInfo method to pass IsOracle.

Declaration:

    procedure SetParamTypeFromTypeInfo(ATypeInfo: PTypeInfo; IsOracle : Boolean);

Body of Method

procedure TDBParam.SetParamTypeFromTypeInfo(ATypeInfo: PTypeInfo; IsOracle : Boolean);
begin
  FTypeInfo := ATypeInfo;
  FParamType := FromTypeInfoToFieldType(FTypeInfo, IsOracle);
end;

Modify the two places where SetParamTypeFromTypeInfo method is called to pass true or false for the IsOracle param.

Spring.Persistence.Adapters.Oracle

procedure TOracleStatementAdapter.SetParam(const param: TDBParam);
var
  paramName: string;
begin
  paramName := param.Name;
  //strip leading : in param name because ADO does not like them
  if (param.Name <> '') and StartsStr(':', param.Name) then
    paramName := Copy(param.Name, 2, Length(param.Name));

  if VarIsEmpty(param.Value) or VarIsNull(param.Value) then
  begin
    //if we set param value to Null, we must provide correct field type to Oracle, otherwise it will raise an error
    Statement.Parameters.ParamByName(paramName).Value := Null;
    param.SetParamTypeFromTypeInfo(param.TypeInfo, True);  <---- HERE
    Statement.Parameters.ParamByName(paramName).DataType := param.ParamType;
  end
  else
    Statement.Parameters.ParamValues[paramName] := param.Value;
end;

Spring.Persistence.SQL.Commands.Abstract Unit

unction TAbstractCommandExecutor.CreateParam(const entity: TObject;
  const paramField: TSQLParamField): TDBParam;
var
  LVal, LRes: TValue;
  bFree: Boolean;
begin
  Result := TDBParam.Create;
  Result.Name := paramField.ParamName;
  LVal := TRttiExplorer.GetMemberValueDeep(entity, paramField.Column.MemberName);
  //convert/serialize objects to stream. If value is nullable or lazy get it's real value
  if LVal.IsObject and LVal.TryConvert(TypeInfo(TStream), LRes, bFree) then
    LVal := LRes.AsObject;

  Result.Value := TUtils.AsVariant(LVal);
  if CanUpdateParamFieldType(Result.Value) then
    Result.SetParamTypeFromTypeInfo(paramField.Column.MemberType, Connection.GetQueryLanguage = qlOracle);  < ---- HERE

  if bFree then
    FreeValueObject(LVal);
end;

[Cesar Romero]

I think that FromTypeInfoToFieldType should be in a class, then we can override in the adapter code.

[Stefan Glienke]

I took a quick look into the code and it seems possible that we change the code to let the ISQLGenerator create the TDBParam instances. Then we could make Oracle create it's own derivate (TOracleParam or something like that) that has the desired behavior.

[Todd Flora]

Yes Sounds good Guys. I am definitely in favor. We are trying to decide which ORM tool to use and so far Marshmallow is the winner. I am just trying to get everything working for a discussion with Senior staff today so that I can demo it.

BTW I have looked at EntityDac, Aurelius, Mormot, DORM and for our purposes and for our current architecture Marshmallow fits very well.

[Linas Naginionis]

fixes #76 fixes #77 SQL generators now responsible for creating TDBParams

→ <<cset 76a9423afc6b>>