Calling a SP from JAVA - LUW

Jay Huffman

Calling a SP from JAVA - LUW
All -
I have a simple SP that I can run directory from a command prompt-
db2 "call simple1(10,?)" - works just great.

db2 "call simple1(10,?)"

Value of output parameters
--------------------------
Parameter Name : OUT_ARG
Parameter Value : 15

Return Status = 15






Inside my java code -

try {

Class.forName(myDriver).newInstance();

db2Conn = DriverManager.getConnection (DBCONN, USER, PASWD);
st = db2Conn.createStatement();


String tmp_proc = "Call SIMPLE1(10,?)";

CallableStatement cStmt = db2Conn.prepareCall("CALL db2admin.simple1
(10,?)");

System.out.println("After the prepareCall statement ......");

cStmt.execute();



my error message is:

After the prepareCall statement ......
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0100E Wrong number of
parameters. SQLSTATE=07001
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(
SQLExceptionGenerator.java:275)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(
SQLExceptionGenerator.java:217)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(
SQLExceptionGenerator.java:449)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(
DB2PreparedStatement.java:2199)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute(
DB2PreparedStatement.java:3975)
at call_sp1.RuneMe(call_sp1.java:135)
at call_sp1.run(call_sp1.java:57)
at call_sp1.main(call_sp1.java:49)
The query took: 3837 millisends to execute.



Any assistance would be appreciated .....



--
Thanks for your assistance,
*****************************************
*** Jay Huffman
*** "Learn All That Is Learnable"
*****************************************

---------------------------------------------------------------------------------
Welcome to the DB2 User Group - Central Pennsylvania list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/DB2-UG-Central-PA.html. From that page select "Join or Leave the list". The IDUG Listserv FAQ is at http://www.idugdb2-l.org. The DB2 User Group - Central PA List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Jay Huffman

Calling a SP from JAVA - LUW
(in response to Jay Huffman)
All -
I have a simple SP that I can run directory from a command prompt-
db2 "call simple1(10,?)" - works just great.

db2 "call simple1(10,?)"

Value of output parameters
--------------------------
Parameter Name : OUT_ARG
Parameter Value : 15

Return Status = 15






Inside my java code -

try {

Class.forName(myDriver).newInstance();

db2Conn = DriverManager.getConnection (DBCONN, USER, PASWD);
st = db2Conn.createStatement();


String tmp_proc = "Call SIMPLE1(10,?)";

CallableStatement cStmt = db2Conn.prepareCall("CALL db2admin.simple1
(10,?)");

System.out.println("After the prepareCall statement ......");

cStmt.execute();



my error message is:

After the prepareCall statement ......
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0100E Wrong number of
parameters. SQLSTATE=07001
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(
SQLExceptionGenerator.java:275)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(
SQLExceptionGenerator.java:217)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(
SQLExceptionGenerator.java:449)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(
DB2PreparedStatement.java:2199)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute(
DB2PreparedStatement.java:3975)
at call_sp1.RuneMe(call_sp1.java:135)
at call_sp1.run(call_sp1.java:57)
at call_sp1.main(call_sp1.java:49)
The query took: 3837 millisends to execute.



Any assistance would be appreciated .....



--
Thanks for your assistance,
*****************************************
*** Jay Huffman
*** "Learn All That Is Learnable"
*****************************************

---------------------------------------------------------------------------------
Welcome to the DB2 User Group - Central Pennsylvania list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/DB2-UG-Central-PA.html. From that page select "Join or Leave the list". The IDUG Listserv FAQ is at http://www.idugdb2-l.org. The DB2 User Group - Central PA List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the DB2 User Group - Central Pennsylvania list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/DB2-UG-Central-PA.html. From that page select "Join or Leave the list". The IDUG Listserv FAQ is at http://www.idugdb2-l.org. The DB2 User Group - Central PA List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Rick Stoner

Re: Calling a SP from JAVA - LUW
(in response to Jay Huffman)
I think that you need to resolve the "?" with a host variable from your
program.

You could try adding something like this after the prepare and before
the execute() statement:

cStmt.setString(1,"Your_string_value");


Rick Stoner,
Systems Architect
Bookspan, Inc.


________________________________

From: DB2 User Group - Central Pennsylvania List
[mailto:[login to unmask email] On Behalf Of Jay Huffman
Sent: Monday, December 19, 2005 12:15 PM
To: [login to unmask email]
Subject: Calling a SP from JAVA - LUW


All -
I have a simple SP that I can run directory from a command prompt-
db2 "call simple1(10,?)" - works just great.

db2 "call simple1(10,?)"

Value of output parameters
--------------------------
Parameter Name : OUT_ARG
Parameter Value : 15

Return Status = 15








Inside my java code -

try {

Class.forName(myDriver).newInstance();

db2Conn = DriverManager.getConnection (DBCONN, USER, PASWD);
st = db2Conn.createStatement();


String tmp_proc = "Call SIMPLE1(10,?)";

CallableStatement cStmt = db2Conn.prepareCall("CALL
db2admin.simple1(10,?)");

System.out.println("After the prepareCall statement ......");

cStmt.execute();



my error message is:

After the prepareCall statement ......
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0100E Wrong number
of parameters. SQLSTATE=07001
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException
(SQLExceptionGenerator.java:275)
at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExcepti
onGenerator.java:217)
at
COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptio
nGenerator.java :449)
at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(DB2PreparedStatement.
java:2199)
at
COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute(DB2PreparedStatement.j
ava:3975)
at call_sp1.RuneMe(call_sp1.java:135)
at call_sp1.run(call_sp1.java:57)
at call_sp1.main(call_sp1.java:49)
The query took: 3837 millisends to execute.



Any assistance would be appreciated .....




--
Thanks for your assistance,
*****************************************
*** Jay Huffman
*** "Learn All That Is Learnable"
*****************************************

------------------------------------------------------------------------
--------- Welcome to the DB2 User Group - Central Pennsylvania list. To
unsubscribe, go to the archives and home page at
http://www.idugdb2-l.org/archives/DB2-UG-Central-PA.html. From that page
select "Join or Leave the list". The IDUG Listserv FAQ is at
http://www.idugdb2-l.org. The DB2 User Group - Central PA List Admins
can be reached at [login to unmask email] Find out
the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the DB2 User Group - Central Pennsylvania list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/DB2-UG-Central-PA.html. From that page select "Join or Leave the list". The IDUG Listserv FAQ is at http://www.idugdb2-l.org. The DB2 User Group - Central PA List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm