aspose file tools*
The moose likes JDBC and the fly likes problem executing callableStatement... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "problem executing callableStatement..." Watch "problem executing callableStatement..." New topic
Author

problem executing callableStatement...

Vin Man
Greenhorn

Joined: Jun 13, 2001
Posts: 26
Hi,
I have the following code ...
.
.
.
prepAllParms(request);
System.out.println("successful returned from prepAllParms");
Connection con;
CallableStatement cs;
int status = 0;
String Msg;
//java.util.Date tmpday = new java.util.Date();
System.out.println("before declaring tmpday");
java.sql.Date tmpday;// = new java.sql.Date(tmpday.getDate());
try{
con = TMSConnection.getTMSDBConnection();
cs = con.prepareCall(
"{? = call speedZone.SpeedZoneInventoryInsert(?, ?, ?, ?, ?,?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?)}");
cs.registerOutParameter(1, java.sql.Types.INTEGER);
System.out.println("just registered the return value");
if (routePrefix.trim().equals("")) {
cs.setNull(2, java.sql.Types.CHAR);
}
else{
cs.setString(2, routePrefix );
}
System.out.println("trying to assign the route number parm");
if (routeNumber.trim().equals("")) {
cs.setNull(3, java.sql.Types.CHAR);
}
else{
cs.setString(3, routeNumber );
}
System.out.println("just assigned routenumber param");
//System.out.println("route suffix = '" + routeSuffix + "'");
if (routeSuffix.trim().equals("")) {
System.out.println("trying to set route suffix to null ");
cs.setNull(4, java.sql.Types.CHAR);
}
else{
System.out.println("assigned the routesuffix");
cs.setString(4, routeSuffix );
}
System.out.println("just assigned route suffix param: '" + routeSuffix + "'");
if (NodeStart.trim().equals("")) {
cs.setNull(5, java.sql.Types.CHAR);
}
else{
cs.setString(5, NodeStart );
}
System.out.println("just assigned nodestart param: '" + NodeStart + "'");
/* String Subdivision;
String Comments;*/
if (OffsetStart.equals("-99")) {
cs.setNull(6, java.sql.Types.NUMERIC);
}
else{
cs.setFloat(6, Float.parseFloat(OffsetStart));
}
System.out.println("just assigned offsetstart param: '" + OffsetStart + "'");
if (NodeEnd.trim().equals("")) {
cs.setNull(7, java.sql.Types.CHAR);
}
else{
cs.setString(7, NodeEnd );
}
System.out.println("just assigned nodeend param: '" + NodeEnd + "'");
if (OffsetEnd.equals("-99")) {
cs.setNull(8, java.sql.Types.NUMERIC);
}
else{
cs.setFloat(8, Float.parseFloat(OffsetEnd));
}
if (StartMilePost.equals("-99")) {
cs.setNull(9, java.sql.Types.NUMERIC) ;
}
else{
cs.setFloat(9, Float.parseFloat(StartMilePost));
}
cs.setString(10, Direction );
if (SpeedLimit.trim().equals("")) {
cs.setNull(11, java.sql.Types.CHAR);
}
else{
cs.setInt(11, Integer.parseInt(SpeedLimit) );
}
if (SpeedLimit.trim().equals("")) {
cs.setNull(11, java.sql.Types.NUMERIC);
}
else{
cs.setInt(12, Integer.parseInt(TrkSpeedLimit) );
}
cs.setString(13, Authority );
cs.setString(14, Application );
DateFormat df = DateFormat.getDateInstance();
//study date;
try {
cs.setDate(15, new java.sql.Date(df.parse(StudyDate).getDate()));
}
catch(ParseException ParseErr){
cs.setNull(15, java.sql.Types.DATE);
}
System.out.println("just assigned studydate param");
// ResolutionDate
try {
cs.setDate(16, new java.sql.Date(df.parse(ResolutionDate).getDate()));
}
catch(ParseException ParseErr){
cs.setNull(16, java.sql.Types.DATE);
}
//SignInstalledDate
try {
cs.setDate(17, new java.sql.Date(df.parse(SignInstalledDate).getDate()));
}
catch(ParseException ParseErr){
cs.setNull(17, java.sql.Types.DATE);
}
//Status message
cs.registerOutParameter(18, java.sql.Types.VARCHAR);
System.out.println("before call EXECUTE");
try {
cs.execute();
}
catch(Exception e){
Msg = "problem executing the callable statement: " + e.getMessage();
System.out.println(Msg);
}
.
.
.
when I ran it, I got the following on the sysoutput area - this includes the debug messages resulted from the System.out.println's.
about to call prepAllParms
successful returned from prepAllParms
before declaring tmpday
just registered the return value
trying to assign the route number parm
just assigned routenumber param
assigned the routesuffix
just assigned route suffix param: ' S'
just assigned nodestart param: '45700'
just assigned offsetstart param: '0.0'
just assigned nodeend param: '183245'
just assigned studydate param
before call EXECUTE
sql problem null
does anyone know what is the problem here? Thanks in advance.
Vin Man
Greenhorn

Joined: Jun 13, 2001
Posts: 26
just want to clearify the problem a little here...
the SpeedZoneInventoryInsert stored function returns an integer to indicate the status of the executing the SP: negative for problem and positive for lack thereof. It accepts 17 parameters, all of which are "in" except for the last one, which is of type char that will contain an error message should the status code returned is negative. Before the execute of the callableStatement cs, all I want to do is to set value of the params appropriately. That is, if I have a value for a given param, I set it to that value; otherwise, I set it to null using cs.setNull(#, typeOfParam).
From the output of my code, the problematic line of code is where I call the cs.execute(), but the exception.getMessage is not returning anything meaningful (null).
Vin Man
Greenhorn

Joined: Jun 13, 2001
Posts: 26
hello,
Further reviews indicate that the exception was not generated from calling cs.execute(), but on obtaining the value returned by the SP...
try{
cs.execute();
}
catch ( ){
..
}
status = cs.getInt(1);
Why though???
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: problem executing callableStatement...