problem with TDateTime function parameter in IRepository

[Sergey Burov]

ICustomRepository = interface(IRepository<TCustomEntity, Integer>)
    ['{4CEFBF25-BAC1-42CD-BCAA-7321D9B4EFED}']
    [Query('select * from t_table where f_date = :0')]
    function FindByDate(const ADate: TDateTime): IList<TCustomEntity>;  
  end;

when we call funciton FindByDate with parameter for example now(), we receive type cast error double to date from postgres.

we found that RTTI invoke this funciton with parameter extended type insted datetime

did you have any solution for this problem?

[Stefan Glienke]

Look into FromArgsToConstArray - since the parameters are passed as array of TVarRec it loses the exact information that it was a TDateTime and not an Extended. It is not great and passing as TValue array would have been better but this is how it was when we integrated it into Spring4d and I did not refactor it yet. In a future version we might change the related method signatures to array of TValue.

I am not sure how postgres handles date parameters and in what format it expects them - what you can try is transferring them as string with the correct format. For that add some code to the tkFloat case in the routine i mentioned before.

[Inkvizi]

I look into function FromArgsToConstArray(const args: TArray<TValue>): TArray<TVarRec>; In args parameters for example in topic we have args[i].Kind is tkFloat and TVarData(args[i].AsVariant).VType is varDouble.

How we can find out in this routine that actually args[i] is Date?

[Stefan Glienke]

check the typeinfo of the TValue

FWIW after reading this: https://www.postgresql.org/docs/9.1/static/datatype-datetime.html I think that you should take TDate parameter and pass its value in ISO-8601. I would interpret TDateTime as timestamp

[Inkvizi]

Is code like this

    tkFloat:
      begin
        if SameText(value.TypeInfo.Name, 'TDate') or (SameText(value.TypeInfo.Name, 'TDateTime')) or
          SameText(value.TypeInfo.Name, 'TTime') then
        begin
          New(item.VVariant);
          item.VVariant^ := value.AsVariant;
          TVarData(item.VVariant^).VType := varDate;
          item.VType := vtVariant;
        end else begin
          New(item.VExtended);
          item.VExtended^ := value.AsExtended;
          item.VType := vtExtended;
        end;
      end;

will be correct to solve this issue? Or maybe it's a better way to wait a change of the related method signatures to array of TValue?

[Stefan Glienke]

Ah, I did not think of using a Variant to transport the information about it being a date - try it and let me know if that solves the issue for you. Also it would be nice if you can test the other postgres types such as time and timestamp as well.

As for changing the signatures this will not happen before 1.3.

[Inkvizi]

I'm try for parameter TDate in Delphi and column type Date in postgres. It's work. I will test other postgres types and write here result.

[Inkvizi]

I received next results from tests

1) For Delphi TDate and Postgres Date - Done

2) For Delphi TDateTime and Postgres TimeStamp - Done

3) For Delphi TDateTime and Postgres TimeStampTZ - Done

4) For Delphi TTime and Postgres Time - Fail with postgres cast exception

5) For Delphi TTime and Postgres TimeTZ - Fail with postgres cast exception

For our issue with TDate the solution with variant is suitable. For using TTime in query parameters as temporary solution can be next additional code

    varDate:
    begin
      if ABS(Double(value)) < 1.0  then
        Result := From<TTime>(TVarData(value).VDate)
      else
      begin
        Result := From<TDateTime>(TVarData(value).VDate)
      end;
    end;

In unit Spring class function TValueHelper.FromVariant(const value: Variant): TValue; But this is bad solution. Only for temporary.

[Stefan Glienke]

fixed issue #199 (refactoring to change array of const to array of TValue)

→ <<cset 33e3232ba720>>

[Stefan Glienke]

fixed issue #199 (refactoring to change array of const to array of TValue)

→ <<cset 269b3a930b86>>

[Inkvizi]

Is it possible to include array of or IList<T> to permitted function parameters in IRepository? It can be use with IN criteria in queries.

[Stefan Glienke]

More details please

[Inkvizi]

For case

 ICustomRepository = interface(IRepository<TCustomEntity, Integer>)
    ['{4CEFBF25-BAC1-42CD-BCAA-7321D9B4EFED}']
    [Query('select * from t_table where f_name in (:0)')]
    function FindByName(const ANameList: IList<string>): IList<TCustomEntity>;  
  end;

or

ICustomRepository = interface(IRepository<TCustomEntity, Integer>)
    ['{4CEFBF25-BAC1-42CD-BCAA-7321D9B4EFED}']
    [Query('select * from t_table where f_name in (:0)')]
    function FindByName(const ANameList: array of string): IList<TCustomEntity>;  
  end;

[Stefan Glienke]

Added as #217

[Inkvizi]

Thank you.

[Stefan Glienke]

fixed issue #199 (refactoring to change array of const to array of TValue)

→ <<cset 835cf8a6b9c9>>