2 messages in com.mysql.lists.javaRE: passing parameters to store proc
FromSent OnAttachments
Srivats08 Nov 2005 04:37 
Frondoni, Giorgio08 Nov 2005 06:55 
Subject:RE: passing parameters to store proc
From:Frondoni, Giorgio (gior@TransCore.com)
Date:11/08/2005 06:55:26 AM
List:com.mysql.lists.java

Yes, you should set to null the other parameters that you don't want to pass (
setNull() method). On the other side, make sure that the sp handles null input appropriately.

Giorgio Frondoni AVP, Chief of System Development TransCore phone: (858) 826-4750 cell: (760) 214-4092

-----Original Message----- From: Srivats [mailto:scsh@riskspan.com] Sent: Tuesday, November 08, 2005 4:38 AM To: ja@lists.mysql.com Subject: passing parameters to store proc

Hi All,

Suppose I have a store proc which accepts 4 input parameters and I do a CallableStatement.setObject( 1, "..,,," ); and execute the query .

CallableStatement functionCall = dbCon_.prepareCall("{call test1( ?,?,?,?) }"); functionCall.setObject( "param1","A10001" ); functionCall.setObject( "param2","19830529" ); functionCall.execute();

Exception raised

java.sql.SQLException: No value specified for parameter 3 at
com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:1253) at
com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:1201) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:710) at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:520)

during runtime. Should i set the other 2 input parameters to null, even when I dont want to pass these parameters to the SP. In this statement call test1( ?,?,?,?), should the number of parameters be the
number of parameters which the store procedure accepts or the number of parameters that
i want to pass. How should this be done when my SP accepts 4 parameters and i want to
pass only any two of them

Thks

PS: Looking for an early reply