Servlets&JDBC-CallableStatement

[Marina]

I use CallableStatement to call stored procedure.
Stored Procedure has two output parameter. When I want to read these output parameters, I am getting or Exception
(Stack Trace:java.lang.NumberFormatException:
at InsertNewContactServlet.doPost
InsertNewContactServlet.java:154)
or empty paramateters, while stored procedure always returns something. When I am getting Exception it complains exactly on this code: int temp=call.getInt(16);
But database is always updated.
Can anybody help me with this?
This is the code:

CallableStatement call=conn.prepareCall("{call manage_contact(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
call.setString(1,req.getParameter("txtCustomerIDHidden"));
call.setString(2,req.getParameter("txtLocationIDHidden"));
call.setNull(3, Types.VARCHAR);
call.setInt(4,1);
if(req.getParameter("txtLastName").equals(""))
   call.setNull(5, Types.VARCHAR);
else
   call.setString(5,req.getParameter("txtLastName"));
call.setString(6,req.getParameter("txtFirstName"));
if(req.getParameter("txtDepartment").equals(""))
   call.setNull(7, Types.VARCHAR);
else
   call.setString(7,req.getParameter("txtDepartment"));
call.setString(8,req.getParameter("txtPhoneNo"));
call.setString(9,phoneLongDistance);
if(req.getParameter("txtExt").equals(""))
   call.setNull(10, Types.VARCHAR);
else
   call.setString(10,req.getParameter("txtExt"));
if(req.getParameter("txtFaxNo").equals(""))
   call.setNull(11, Types.VARCHAR);
else
   call.setString(11,req.getParameter("txtFaxNo")); call.setString(12,faxLongDistance); //extrafax
if(req.getParameter("txtEmail").equals(""))
    call.setNull(13, Types.VARCHAR);
else
    call.setString(13,req.getParameter("txtEmail"));
if(req.getParameter("txtNotes").equals(""))
   call.setNull(14, Types.VARCHAR);
else
   call.setString(14,req.getParameter("txtNotes"));
call.setString(15, "Shipping");
call.registerOutParameter(16,Types.INTEGER);
call.registerOutParameter(17,Types.VARCHAR);
call.execute(); //execute stored procedure
int temp=call.getInt(16);
out.println("output parameter1 is"+call.getString(16));
out.println("output parameter2 "+call.getString(17));

[This message has been edited by Jim Yingst (edited September 05, 2000).]

[Carl Trusiak]

What are the data types that the stored procedure returns? If it returns char or varchar, you can't get it with a getInt() even if it always returns a varchar that contains numbers only. Use the getString() and use a conversion method to make it into an int.

[Marina]

Originally posted by Carl Trusiak:
What are the data types that the stored procedure returns? If it returns char or varchar, you can't get it with a getInt() even if it always returns a varchar that contains numbers only. Use the getString() and use a conversion method to make it into an int.

One of output parameters is int in the database, so in servlet I register it with call.registerOutParameter(16,Types.INTEGER);
and I am reading it as call.getInt(16)
Another parameter is nvarchar in the database, so I register it in servlet as call.registerOutParameter(17,Types.VARCHAR) and I read it as call.getString(17). So, first output parameter in stored procedure is int and I am reading it as int.
Why it doesn't work? What's wrong here?

[Jim Yingst]

Well I'm not sure what's going on here either, but why don't you try changing the getInt(16) to a getString(16) and see what happens? You probably also need to change the registerOutParameter() to match to make this work. If it works, try printing out whatever the string is to see if that gives any clue what's going on. Two possible causes I can think of are (1) the manage_contact procedure is somehow outputting a string, or (2) your driver is converting all output to strings. The latter sounds stupid I know, but I think I once observed that situation using the standard JDBC/ODBC bridge driver. (Which I guess is one reason why that driver isn't recommended.) Good luck.

[Mark MSmith]

Hi. I have this same problem too and happened to try your string idea already. . . it crashed tomcat with an illegal operation error! I keep getting the same numberFormat exception. I'm running tomcat with apache (both latest versions) on win 98 SE. I'm using the standard sun.jdbc.obdc.JdbcOdbc drivers to interface with MS SQL Server 7. Any suggestions to get out parameters from a stored procedure would be appreciated