Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: [SOLVED] Error FDStoredProc with numeric(15,2) output param (MS SQL)



Permlink Replies: 10 - Last Post: Mar 22, 2018 10:33 PM Last Post By: Dmitry Vavilov
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
[SOLVED] Error FDStoredProc with numeric(15,2) output param (MS SQL)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 26, 2017 5:06 AM
Hi!
An error occurred when executing a stored procedure returning NUMERIC(15,2) [MSSQL]

There is a stored procedure in the database:
CREATE  PROCEDURE [dbo].[TestProc](@MyValue Numeric(15,2) OutPut) As
	SET NOCOUNT ON
	SET @MyValue = 123456789.00

After specifying the name of the procedure, two parameters are created in the FDStoredProc component (@RETURN_VALUE and @MyValue).
The @MyValue parameter has the following settings:
DataType = ftBCD
ParamType = ptInputOutput
Precision = 15
NumericScale = 2

Execution code:
FDStoredProc1.Params.ParamByName('@MyValue').Value := 0;
FDStoredProc1.ExecProc;

After calling "ExecProc", an error occurs
[FireDAC] [Phys] [ODBC] [Microsoft] [SQL Server Native Client 11.0] [SQL Server] Error converting data type numeric to decimal.

Message from SQL Server Profiler:
 
declare @p1 decimal(8,2)
set @p1=0
exec [SDB].[dbo].[TestProc] @p1 output
select @p1


How to solve a problem?
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
Re: Error FDStoredProc with numeric(15,2) output param (MS SQL)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 2, 2017 12:23 AM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
After analyzing, I found the problem in the file "FireDAC.Stan.Param" in the procedure "procedure TFDParam.GetData (ABuffer: PByte; AIndex: Integer);".
I had to do the following to solve the problem:
procedure TFDParam.GetData(ABuffer: PByte; AIndex: Integer);
...
begin
...
    ftBCD:
      begin
        GetVarData(pVar, AIndex);
        if VarIsFmtBCD(pVar^) then
          PBcd(ABuffer)^ := VarToBCD(pVar^)
        else
        begin
          cr := pVar^;
          FDCurr2Str(buff, iSize, cr, Char(FormatSettings.DecimalSeparator));
          FDStr2BCD(buff, iSize, PBcd(ABuffer)^, Char(FormatSettings.DecimalSeparator));
        end;
        PBcd(ABuffer)^.Precision := Self.Precision;                 //<--- I added this code 
        PBcd(ABuffer)^.SignSpecialPlaces := Self.NumericScale;      //<--- I added this code
      end;
    ...
end;


As it turned out, the properties "Precision" and "NumericScale" are ignored for the ftBCD parameters.
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Error FDStoredProc with numeric(15,2) output param (MS SQL)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 2, 2017 1:05 AM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
Nice. You should create an issue report with this workaround then EMB will know about this.
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
Re: Error FDStoredProc with numeric(15,2) output param (MS SQL)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 2, 2017 10:14 PM   in response to: Robert Triest in response to: Robert Triest
Robert Triest wrote:
Nice. You should create an issue report with this workaround then EMB will know about this.

Do you mean this "quality.embarcadero.com"?
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Error FDStoredProc with numeric(15,2) output param (MS SQL)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 7, 2017 3:58 AM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
Yes, indeed.
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
Re: Error FDStoredProc with numeric(15,2) output param (MS SQL)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 7, 2017 2:36 AM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
I improved my code
if Self.Precision <> 0 then
  PBcd(ABuffer)^.Precision := Self.Precision;
if Self.NumericScale <> 0 then
  PBcd(ABuffer)^.SignSpecialPlaces := Self.NumericScale;
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
Re: Error FDStoredProc with numeric(15,2) output param (MS SQL)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 7, 2017 10:21 PM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
I improved my code again:
if Self.Precision <> 0 then
begin
  PBcd(ABuffer)^.Precision := Self.Precision;
  if Self.NumericScale <> 0 then
    PBcd(ABuffer)^.SignSpecialPlaces := Self.NumericScale;
end;
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
Re: Error FDStoredProc with numeric(15,2) output param (MS SQL)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 14, 2017 4:01 AM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
With Precision = 15 Scale = 2, the INPUT parameter with a value "9" turns to "900000000000000.00". Maybe the problem is in ODBCBcd2Numeric?
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Error FDStoredProc with numeric(15,2) output param (MS SQL)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 14, 2017 9:00 AM   in response to: Dmitry Vavilov in response to: Dmitry Vavilov
I posted patch: https://forums.embarcadero.com/thread.jspa?threadID=267221

--
With best regards,
Dmitry
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
Re: Error FDStoredProc with numeric(15,2) output param (MS SQL)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 14, 2017 8:09 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
I posted patch: https://forums.embarcadero.com/thread.jspa?threadID=267221

--
With best regards,
Dmitry

Thank you

P.S.
Did you miss the parentheses?
        if (Connection.DriverKind = dkTeradata) or
           (Connection.DriverKind in [dkSQLSrv, dkSQLNC, dkSQLOdbc, dkFreeTDS, dkSQLSrvOther]) and
             (ParamType in [SQL_PARAM_INPUT_OUTPUT, SQL_PARAM_OUTPUT]) then begin

Should it be so?
        if (Connection.DriverKind = dkTeradata) or
          ((Connection.DriverKind in [dkSQLSrv, dkSQLNC, dkSQLOdbc, dkFreeTDS, dkSQLSrvOther]) and
             (ParamType in [SQL_PARAM_INPUT_OUTPUT, SQL_PARAM_OUTPUT])) then begin


Edited by: Dmitry Vavilov on Nov 14, 2017 8:30 PM
Dmitry Vavilov

Posts: 29
Registered: 9/23/17
Re: Error FDStoredProc with numeric(15,2) output param (MS SQL)
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 13, 2017 9:35 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
I posted patch: https://forums.embarcadero.com/thread.jspa?threadID=267221

--
With best regards,
Dmitry

Did you miss the parentheses?
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02