Error with BigDecimal used as stored procedure parameter

I have the following stored procedure:

create procedure MyNumericTestProc
(
@param1 numeric(13,2) output
)
as
begin

if (@param1 is NULL)
begin
set @param1 = 5.25
end

set @param1 = @param1 + 0.01

select @param1
end


I call it using the MS SQL Server JDBC Driver (SP3):

public class TestMyNumericTestProc
{
public static void main(String[] args)
{
try
{
testMyNumericTestProcUsingJDBC();
}
catch (ClassNotFoundexception e)
{
}

}

public void testMyNumericTestProcUsingJDBC() throws
ClassNotFoundException
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;

try {
conn = DriverManager.getConnection(

"jdbc:microsoft:sqlserver://MySystem\\MySQL2000Server:MyPort;databaseName=My
Database",
"username",
"password");
cs = conn.prepareCall("{call MyNumericTestProc(?)}");

BigDecimal d = new BigDecimal("300.10");
int scale = 3;

System.out.println("Input Value = " + d.toString());
System.out.println("Input Value Scale = " + d.scale());
System.out.println("Input Parameter Scale = " + scale);


// Set a BigDecimal inout parameter and execute call
cs.setObject(1, d, Types.DECIMAL);
cs.registerOutParameter(1, Types.DECIMAL, scale);
boolean csResult = cs.execute();

// Obtain result set
rs = cs.getResultSet();
rs.next();
d = rs.getBigDecimal(1);
System.out.println("ResultSet Value = " + d.toString());
System.out.println("ResultSet Scale = " + d.scale());

// Obtain value of the output parameter as object
Object obj = cs.getObject(1);
System.out.println("Output Param Value (as Object) = " +
((BigDecimal) obj).toString());
System.out.println("Output Param Scale (as Object) = " +
((BigDecimal) obj).scale());

// Obtain value of the output parameter as BigDeciaml
d = cs.getBigDecimal(1);
System.out.println("Output Param Value (as BigDecimal) = " +
d.toString());
System.out.println("Output Param Scale (as BigDecimal) = " +
d.scale());



} catch (SQLException e) {
e.printStackTrace();
} finally {
if (cs != null) {
try { cs.close(); }
catch (SQLException e) {;}
}
if (conn != null) {
try { conn.close(); }
catch (SQLException e) {;}
}
}
}

}

The output of executing this class is as follows:

Input Value = 300.10
Input Value Scale = 2
Input Parameter Scale = 3
ResultSet Value = 30.02
ResultSet Scale = 2
Output Param Value (as Object) = 30.020
Output Param Scale (as Object) = 3
Output Param Value (as BigDecimal) = 30.020
Output Param Scale (as BigDecimal) = 3


Note that I use BigDecimal as the parameter type (which is the recommended
type fr DECIMAL and NUMERIC).

Given the stored procedure, I would have expected the value 300.11 as the
value of the output
parameter and within the result set.

It appears there is an error when the scale of the input value and the scale
specified by the output
parameter do not match, and the input parameter is a BigDecimal.

Is this a problem within the realm of the MS JDBC Driver? If it is not how
do I determine where the
error is occuring?


.

Relevant Pages

  • Re: problem declaring SqlParameter of type Decimal
    ... I needed to supply the precision and scale to the stored procedure parameter ... > blue while none of my other SqlParameters that I have used thus far (INT, ... > UPDATE tblMain ...
    (microsoft.public.dotnet.framework.adonet)
  • Unwanted Rounding
    ... I have a decimal datatype set to precision 5 and scale 2. ... insert a new record via ASP using a stored procedure, ...
    (microsoft.public.inetserver.asp.db)
  • Re: Retrieving the stored procedure parameter list in ado.net
    ... The stored procedure part is not an issue, ... > parameters for the sp and store them in an array. ... > with param1 being required and the rest optional, ... > param3, it is going to write out the contents of param3 to be sent to ...
    (microsoft.public.dotnet.framework.adonet)
  • Retrieving the stored procedure parameter list in ado.net
    ... The stored procedure part is not an issue, what is the issue is how ... with param1 being required and the rest optional, then if pass in param1 and ... param3, it is going to write out the contents of param3 to be sent to param2 ...
    (microsoft.public.dotnet.framework.adonet)
  • Strange concurrency lesson on Update
    ... Built a stored procedure to do an update on a table with 16 fields. ... tried to update the database with the table data, I got a concurrency error. ... but not the scale. ... behold -- yes, Virginia, failing to specify the scale of your decimal fields ...
    (microsoft.public.dotnet.framework.adonet)
Loading