PRB: Decimal Values Passed to a Stored Procedure Get Truncated

• ActiveX Data Objects (ADO), versions 1.0, 1.5, 2.0, 2.1 SP2
• Microsoft Visual Basic Enterprise Edition for Windows, versions 4.0, 5.0, 6.0

SYMPTOMS

Values passed into a Stored Procedure may lose their decimal values.

CAUSE

This depends on the ability of the backend server to define constraints or numeric scale for arguments used by a stored procedure. This is possible under SQL Server, but it is not a feature in Oracle.

RESOLUTION

If you are using Oracle and Remote Data Objects (RDO) the workaround is to change the Parameters Type property to rdNumeric. If you are using ActiveX Data Objects (ADO) and Oracle, define an appropriate Parameter object(s) and set the Parameter's NumericScale property accordingly.

Under SQL Server a numeric scale or a constraint must be defined on the stored procedures parameter. If this is defined, then decimal values will be received by the stored procedure. If the parameters are not defined with numeric scale then the decimal values will be truncated.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Use the following sample SQL Server Scripts to generate the stored procedures and table. The first stored procedure illustrates the problem. The second stored procedure demonstrates how to define input parameters with numeric scale:




   if exists (select * from sysobjects where id =
   object_id('dbo.numericscale') and sysstat & 0xf = 3
      drop table dbo.numericscale
   GO

   CREATE TABLE dbo.numericscale (
      mynum numeric(5, 2) NULL ,
      mydec decimal(5, 2) NULL
   )
   GO

   if exists (select * from sysobjects where id =
   object_id('dbo.SimpleSQL1') and sysstat & 0xf = 4)
      drop procedure dbo.SimpleSQL1
   GO

   CREATE PROCEDURE SimpleSQL1 @input1 numeric, @input2 decimal
   AS
   INSERT into numericscale values (@input1, @input2)
   GO

   if exists (select * from sysobjects where id =
   object_id('dbo.SimpleSQL2') and sysstat & 0xf = 4)
      drop procedure dbo.SimpleSQL2
   GO

   CREATE PROCEDURE SimpleSQL2 @input1 numeric(5,2), @input2
    decimal(5,2)
   AS
   INSERT into numericscale values (@input1, @input2)
   GO 

1. Add a reference to Microsoft ActiveX Data Objects in the project.



2. Add the following ADO code to the default form's Load method:




   Dim cn As ADODB.Connection
   Dim cm1 As ADODB.Command
   Dim cm2 As ADODB.Command
   Dim pm1 As ADODB.Parameter
   Dim pm2 As ADODB.Parameter
   Dim rs as ADODB.Recordset

   Set cn = New ADODB.Connection
   Set cm1 = New ADODB.Command
   Set cm2 = New ADODB.Command
   Set pm1 = New ADODB.Parameter
   Set pm2 = New ADODB.Parameter
   Set rs = New ADODB.Recordset

   With cn
      .ConnectionString = "DRIVER={SQL SERVER};" & _
                       "SERVER=<server_name>;" & _
                       "UID=sa;PWD="
      .Open
      .DefaultDatabase = "pubs"
   End With

   With pm1
      .Direction = adParamInput
      .Name = "param1"
      .NumericScale = 2  ' this corresponds to the second sp's numeric
                         ' scale.
      .Precision = 10
      .Size = 19
      .Type = adNumeric
      .Value = 3.2
   End With

   With pm2
      .Direction = adParamInput
      .Name = "param2"
      .NumericScale = 2
      .Precision = 10
      .Size = 19
      .Type = adNumeric
      .Value = 3.2
   End With

   With cm1
   Set .ActiveConnection = cn
      .CommandType = adCmdStoredProc
      .CommandText = "simplesql1"
      .Parameters.Append pm1
      .Parameters.Append pm2
      .Parameters(0).Value = 3.25
      .Parameters(1).Value = 4.26
   End With
   cm1.Execute

   Set cm1.ActiveConnection = nothing

   With cm2
   Set .ActiveConnection = cn
      .CommandType = adCmdStoredProc
      .CommandText = "simplesql2"
      .Parameters.Append pm1
      .Parameters.Append pm2
      .Parameters(0).Value = 5.35
      .Parameters(1).Value = 6.46
   End With

   cm2.Execute
   Set cm1 = Nothing
   Set cm2 = Nothing

   Set rs = cn.Execute("select * from numericscale")

   While Not rs.EOF
      Debug.Print rs(0).Name & ": " & rs(0)
      Debug.Print rs(1).Name & ": " & rs(1)
      rs.MoveNext
   Wend

   rs.Close
   Set rs = Nothing
   cn.Close
   Set cn = Nothing

   Unload Me 

3. Run the form.

1. Create a new Visual Basic (VB) Standard EXE project.



2. Add a Project reference to Microsoft Remote Data Objects.



3. Add the following code to the default form's Load method:




   Dim en As rdoEnvironment
   Dim cn As rdoConnection
   Dim rs As rdoResultset
   Dim rq1 As rdoQuery
   Dim rq2 As rdoQuery
   Dim val1 As Double
   Dim val2 As Double

   val1 = 8.2
   val2 = 9.2

   Set en = rdoEngine.rdoEnvironments(0)
   en.CursorDriver = rdUseOdbc

   Set cn = en.OpenConnection("",rdDriverNoPrompt,," & _
                              "DRIVER={SQL Server};" & _
                              "Server=matthofa;" & _
                              "UID=sa;PWD=;" & _
                              "DATABASE=pubs")

   Set rq1 = cn.CreateQuery("", "{Call simplesql1(?,?) }")
   rq1.rdoParameters(0).Direction = rdParamInput
   rq1.rdoParameters(0).Value = val1
   rq1.rdoParameters(1).Direction = rdParamInput
   rq1.rdoParameters(1).Value = val2

   Set rq2 = cn.CreateQuery("", "{Call simplesql2(?,?) }")
   rq2.rdoParameters(0).Direction = rdParamInput
   rq2.rdoParameters(0).Value = val1
   rq2.rdoParameters(1).Direction = rdParamInput
   rq2.rdoParameters(1).Value = val2

   rq1.Execute
   rq2.Execute
   rq1.Close
   rq2.Close

   Set rs = cn.OpenResultset("select * from numericscale")
   While Not rs.EOF
      Debug.Print rs(0).Name & ": " & rs(0)
      Debug.Print rs(1).Name & ": " & rs(1)
      rs.MoveNext
   Wend

   cn.Close
   en.Close
   Unload Me 

4. Run the application.

Additional query words:

Keywords : kbADO kbDatabase kbMDAC kbOracle kbRDO kbRDO200 kbStoredProc kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:1.0,1.5,2.0,2.1 SP2,4.0,5.0,6.0
Platform : WINDOWS
Issue type : kbprb