Error using datetime parameter on firedac stored procedure component

0
votes

I have a FDStored procedure that has a datetime parameter:

create procedure [dbo].[p_gl_for_ap]
(@inMode        char(1),  --I=Invoice, C=Check
 @inId_Invoice  integer,  --reqd for invoice or credit memo maintenance, I  
 @inReg         char(3),  --reqd for check update or void, C
 @inCheckNo     integer,  --reqd for check update or void, C
@inIs_Reversal char,     --Y only if Invoice Delete or Check Void   
 @inDt_Rev      datetime, --reqd only for reversal
 @inDt          datetime, --optl G/L tran date; normally null
 @inId_glsrcjrn varchar(10),
 @inId_create   integer,        
 @ret           integer output)
AS
declare....

and I have a FDStoredProc component using the stored procedure: (following is from component to code)

var
  spGLForAP: TFDStoredProc;

  spGLForAP := TFDStoredProc.Create(Self);

  spGLForAP.Name := 'spGLForAP';
  spGLForAP.Connection := dmConnect.cnxData;
  with spGLForAP.FormatOptions.MapRules.Add do begin 
    SourceDataType := dtDateTime;
    TargetDataType := dtDateTimeStamp;
  end;
  spGLForAP.StoredProcName := 'p_gl_for_ap';
  with spGLForAP.ParamData.Add do begin 
    Position := 1;
    Name := 'RESULT';
    DataType := ftInteger;
    ParamType := ptResult;
  end;
  with spGLForAP.ParamData.Add do begin 
    Position := 2;
    Name := 'inMode';
    DataType := ftFixedChar;
    ParamType := ptInput;
    Size := 1;
  end;
  with spGLForAP.ParamData.Add do begin 
    Position := 3;
    Name := 'inId_Invoice';
    DataType := ftInteger;
    ParamType := ptInput;
  end;
  with spGLForAP.ParamData.Add do begin 
    Position := 4;
    Name := 'inReg';
    DataType := ftFixedChar;
    ParamType := ptInput;
    Size := 3;
  end;
  with spGLForAP.ParamData.Add do begin 
    Position := 5;
    Name := 'inCheckNo';
    DataType := ftInteger;
    ParamType := ptInput;
  end;
  with spGLForAP.ParamData.Add do begin 
    Position := 6;
    Name := 'inIs_Reversal';
    DataType := ftFixedChar;
    ParamType := ptInput;
    Size := 1;
  end;
  with spGLForAP.ParamData.Add do begin 
    Position := 7;
    Name := 'inDt_Rev';
    DataType := ftDateTime;
    FDDataType := dtDateTimeStamp;
    NumericScale := 3;
    ParamType := ptInput;
  end;
  with spGLForAP.ParamData.Add do begin 
    Position := 8;
    Name := 'inDt';
    DataType := ftDateTime;
    FDDataType := dtDateTimeStamp;
    NumericScale := 3;
    ParamType := ptInput;
  end;
  with spGLForAP.ParamData.Add do begin 
    Position := 9;
    Name := 'inId_glsrcjrn';
    DataType := ftString;
    ParamType := ptInput;
    Size := 10;
  end;
  with spGLForAP.ParamData.Add do begin 
    Position := 10;
    Name := 'inId_create';
    DataType := ftInteger;
    ParamType := ptInput;
  end;
  with spGLForAP.ParamData.Add do begin 
    Position := 11;
    Name := 'ret';
    DataType := ftInteger;
    ParamType := ptInputOutput;
  end;

I am initializing with this code:

function tdmAP.DoGLForAP(whichInvoice: integer; hasDelete: Boolean): 

integer;
begin
   spGLForAP.Params.ClearValues;
   spGLForAP.ParamByName('inMode').AsString := 'I';
   spGLForAP.ParamByName('inId_Invoice').AsInteger := whichInvoice;
   spGLForAP.ParamByName('inReg').AsString := '';
   spGLForAP.ParamByName('inCheckNo').AsInteger := 0;
   if hasDelete then
   begin
      spGLForAP.ParamByName('inIs_Reversal').AsString := 'Y';
      spGLForAP.ParamByName('indt_Rev').value := Date;
   end
   else
   begin
      spGLForAP.ParamByName('inIs_Reversal').AsString := 'N';
      spGLForAP.ParamByName('indt_Rev').AsDateTime := Date;
   end;

   spGLForAP.ParamByName('indt').AsDateTime := Date;
   spGLForAP.ParamByName('inId_glsrcjrn').AsString := '';
   spGLForAP.ParamByName('inId_create').AsInteger := LoginRec.LoginUserId;;

   try
      spGLForAP.Prepare;
      spGLForAP.Execute;
      Result := spGLForAP.ParamByName('ret').AsInteger;
   except
      on E:Exception do
      begin
         ShowMessage('Error executing stored procedure p_gl_for_ap: ' + e.Message);
         result := -1;
      end;
   end;


end;

but I keep getting error back from firedac complaining about the parameter type changing: error on execute I have tried using the datatype mapping. I have tried using this code: spGLForAP.ParamByName('indt_Rev').value = 0;

and spGLForAP.ParamByName('indt_Rev').AsDateTime := Date;

and spGLForAP.ParamByName('indt_Rev').AsDateTime := now;

I have also tried changing the datatypes on the two date parameters from ftTimeStamp to ftDateTime, repreparing the query after setting the parameters types, and just about anything else I can think of. obviously, I'm missing something...

using Delphi 10.2.2 Tokyo, against mssql server 2008R2.

note: in this particular case, I'm trying to set the inDt_rev and inDt to 0. but can't seem to successfully set them to any value.

2
delphifiredac
Why you are using dtDateTimeStamp here while the data type of your column is DateTime not TimeStamp?? - Ilyes
You just missed @ prefixes at your parameter names and mixed the map rule direction. For parameters it goes like TargetDataType maps to SourceDataType. But I would simply lose this manual parameter preparation (would not mess with internally used FDDataType, no setting of precision and so on) and let FireDAC Prepare the parameter collection from metadata (keeping just that mapping). - Victoria

2 Answers

1
votes

Try to change this part of your code:

  with spGLForAP.ParamData.Add do begin 
    Position := 7;
    Name := 'inDt_Rev';
    DataType := ftDateTime;
    FDDataType := dtDateTimeStamp;
    NumericScale := 3;
    ParamType := ptInput;
  end;
  with spGLForAP.ParamData.Add do begin 
    Position := 8;
    Name := 'inDt';
    DataType := ftDateTime;
    FDDataType := dtDateTimeStamp;
    NumericScale := 3;
    ParamType := ptInput;
  end;

to this one:

  with spGLForAP.Params.Add do begin
    Name := '@inDt_Rev';
    DataType := ftTimeStamp;
    ParamType := ptInput;
  end;
  with spGLForAP.Params.Add do begin
    Name := '@inDt';
    DataType := ftTimeStamp;
    ParamType := ptInput;
  end;

and use Value property instead of .AsDateTime accessor like:

spGLForAP.Params.ParamByName('@inDt').Value := Now;

or use AsSQLTimeStamp accessor:

// uses Data.SqlTimSt;
spGLForAP.Params.ParamByName('@inDt').AsSQLTimeStamp := DateTimeToSQLTimeStamp(Now);

because FireDAC maps such parameter as dtDateTimeStamp type (for which is the AsSQLTimeStamp accessor for).

0
votes

You can create a custom data type mapping rule: http://docwiki.embarcadero.com/RADStudio/XE5/en/Data_Type_Mapping_(FireDAC)

And map dtTimeStamp into dtDateTime.

  // --------------------------- MAP RULES ------------------------- \\
  with dm6.fdDB.FormatOptions do begin
    OwnMapRules := True;
    with MapRules.Add do begin
      SourceDataType := dtDateTimeStamp;
      TargetDataType := dtDateTime;
    end;
  end;