File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

looking for help with datatype match

 
Charlie Petrie
Greenhorn
Posts: 10
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33699
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Charlie,
Why are you doing the assignment and println in the same step? Does it work if you just do:
 
Charlie Petrie
Greenhorn
Posts: 10
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yeah, it does work without assigning it in the println(with same results)
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33699
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Charlie,
Are you still having a problem? If so, can you clarify what you are looking for?
 
Charlie Petrie
Greenhorn
Posts: 10
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33699
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 33699
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic