• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

looking for help with datatype match

 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yeah, it does work without assigning it in the println(with same results)
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic