Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

I am trying to use jdbc to call sp procedure in UDB 8.1

 
Kelly Kong
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to use jdbc to call sp procedure in UDB 8.1, the following is the jdbc code.
------------------------------------------------------------------------------------------
con = getConnection();
cstmt = con.prepareCall("call db2admin.cj_sp_auth(?,?,?,?,?)");
cstmt.setString(1, userName);
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.registerOutParameter(4, Types.CHAR);
cstmt.registerOutParameter(5, Types.INTEGER);

// Execute the stored procedure call.
cstmt.execute();
------------------------------------------------------------------------------------------
my stored procedure: (very simple)
CREATE PROCEDURE DB2ADMIN.CJ_SP_AUTH ( IN I_USERNAME VARCHAR(10),
OUT O_PASSWORD VARCHAR(20),
OUT GRDBSTA INT,
OUT V_MESSAGE VARCHAR(254),
OUT GRDBDES INT
)
SPECIFIC CJ_SP_AUTH
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare variable
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SE1: BEGIN
SET GRDBDES=CAST(SQLSTATE AS INT);
GET DIAGNOSTICS EXCEPTION 1 V_MESSAGE = MESSAGE_TEXT ;
SET GRDBSTA=00002;
END SE1;
DECLARE EXIT HANDLER FOR NOT FOUND
NF1: BEGIN
SET GRDBDES=CAST(SQLSTATE AS INT);
GET DIAGNOSTICS EXCEPTION 1 V_MESSAGE = MESSAGE_TEXT ;
SET GRDBSTA=00001;
END NF1;
SET GRDBDES=0;
SET V_MESSAGE='';
SET GRDBSTA=0;

SET O_PASSWORD = '';

SELECT A.PASSWORD INTO O_PASSWORD FROM DB2ADMIN.USER A WHERE A.USERNAME = I_USERNAME;

END P1
-----------------------------------------------------------------------------------------

it threw exception at cstmt.execute()

is there anybody who could know what caused the exception? thanks in advance

2005-02-17 13:38:17,578 [ca.cjc.clinic.components.common.dao.BaseDAO] - BaseDAO execute:call db2admin.cj_sp_auth(?,?,?,?,?)

[2/17/05 13:38:17:578 EST] 4d7d6063 ExceptionUtil E CNTR0020E: Non-application exception occurred while processing method "authenticate" on bean "BeanId(MyTest#MyTestEJB.jar#ClinicBaseController, null)". Exception data: java.lang.UnsatisfiedLinkError: SQLBindChar
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute(Unknown Source)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.execute(WSJdbcPreparedStatement.java:468)
at ca.cjc.clinic.components.common.dao.BaseDAO.execute(BaseDAO.java:271)
at ca.cjc.clinic.components.clinicbase.dao.ClinicBaseDAO.authentication(ClinicBaseDAO.java:59)
at ca.cjc.clinic.components.clinicbase.ejb.controller.ClinicBaseControllerBean.authenticate(ClinicBaseControllerBean.java:51)
at ca.cjc.clinic.components.clinicbase.ejb.controller.EJSRemoteStatelessClinicBaseController_d4a4178f.authenticate(EJSRemoteStatelessClinicBaseController_d4a4178f.java:22)
at ca.cjc.clinic.components.clinicbase.ejb.controller._ClinicBaseController_Stub.authenticate(_ClinicBaseController_Stub.java:260)
at ca.cjc.clinic.components.clinicbase.delegates.ClinicBaseDelegate.authenticate(ClinicBaseDelegate.java:40)
at ca.cjc.clinic.login.action.LoginAction.doExecute(LoginAction.java:41)
at ca.cjc.clinic.common.action.BaseAction.execute(BaseAction.java:45)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:948)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:530)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:176)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:201)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:182)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:610)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:435)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic