wood burning stoves 2.0*
The moose likes JDBC and the fly likes looking for help with datatype match Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "looking for help with datatype match" Watch "looking for help with datatype match" New topic
Author

looking for help with datatype match

Charlie Petrie
Greenhorn

Joined: Jun 26, 2002
Posts: 10
I obviously have a datatype mismatch between my java code and the sp. the return value for the parameters used should
be 2.974548 but 2.0 is returned instead.


here's the SQLServer stored procedure (the procedure works fine):

CREATE PROCEDURE PS_PRESENT_WORTH
@discount_interest_rate DECIMAL(8,4),
@sell_out_period DECIMAL
AS
DECLARE @present_worth_of_1_per_period DECIMAL(8,6)

SELECT @present_worth_of_1_per_period = (1 - (1/POWER((1 + @discount_interest_rate), @sell_out_period)))/@discount_interest_rate

Return @present_worth_of_1_per_period
GO



here's my calling code:

import java.sql.*;

public class StoredProcCall2{
public static void main(String [] args){
StoredProcCall2 spc2 = new StoredProcCall2();
spc2.aMethod();
}
public void aMethod(){
String url;
String driver;
double a;
try{
url = "jdbc dbc:Videos";
driver = "sun.jdbc.odbc.JdbcOdbcDriver";
Class.forName(driver);
Connection connection = DriverManager.getConnection(url);
CallableStatement cstmt = connection.prepareCall("{? = call MY_PROC(?, ?)}");
cstmt.registerOutParameter(1, java.sql.Types.DOUBLE);
cstmt.setDouble(2, 0.13);
cstmt.setDouble(3, 4.0);
cstmt.execute();
System.out.println(a = cstmt.getDouble(1));
}catch(ClassNotFoundException cnfe){
System.err.println(cnfe);
}catch(SQLException sqle){
String sqlMessage = sqle.getMessage();
String sqlState = sqle.getSQLState();
int vendorCode = sqle.getErrorCode();
System.err.println("Message: " + sqlMessage);
System.err.println("SQL state: " + sqlState);
System.err.println("Vendor Code: " + vendorCode);
}
}
}

Thanks
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

Charlie,
Why are you doing the assignment and println in the same step? Does it work if you just do:


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Charlie Petrie
Greenhorn

Joined: Jun 26, 2002
Posts: 10
yeah, it does work without assigning it in the println(with same results)
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

Charlie,
Are you still having a problem? If so, can you clarify what you are looking for?
Charlie Petrie
Greenhorn

Joined: Jun 26, 2002
Posts: 10
Yes, there is still a problem. I can't get my java code to retrieve any values to the right of the decimal. The prodedure calculates a value of 2.974548 (double checked and the procedure is fine) but I only get 2.0 when I "getDouble()".
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

Charlie,
This article says that the call keyword should be before the return parameter. Could that be causing the problem?

Also, try having your stored proc return a hard coded value like 2.5.
Charlie Petrie
Greenhorn

Joined: Jun 26, 2002
Posts: 10
I hard coded 2.5 (and 4.5) in my procedure as you suggested but it only returns 2.0 (and 4.0.). As far as the article is concerned I don't know the "call" syntax for SQL Server (T-SQL).
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

Charlie,
I just noticed that the procedure is called "PS_PRESENT_WORTH", but the callable statement calls "MY_PROC." Are these somehow mapped to be the same thing?

Anyway, I'm out of ideas as to what could be causing the truncation in your code.
Charlie Petrie
Greenhorn

Joined: Jun 26, 2002
Posts: 10
No, it's ok in my code. I just wanted to give the name of the proc a generic name here to facilitate my example but forgot to change the procedure's name. My java code and procedure match in actuality. Anyway, thanks for trying.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: looking for help with datatype match
 
Similar Threads
How to connect Driver
looking for help with datatype match
reworking Access to mySQL
Is my javaBean a javaBean?
Can someone look at program