File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes CachedRowSet - Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "CachedRowSet - Stored Procedure" Watch "CachedRowSet - Stored Procedure" New topic
Author

CachedRowSet - Stored Procedure

J. Alenis
Greenhorn

Joined: Jul 12, 2004
Posts: 16
I would like to get the data from a Stored Procedure into a CachedRowSet. Is there anyone here who had some expierence with this.

I tried:
con = DriverManager.getConnection("jdbc b2://localhost:50000/DB", "User", "passwd");

crs.setCommand("Call DB.STORPROC(?,?,'a>''111111111111''')");

crs.execute(con);

But that doesn't work, also the parameters are not set for the call if I do it this way.
Gunjan Sahay
Greenhorn

Joined: Aug 07, 2002
Posts: 11
try breaking it all up:

Class.forName("sun.jdbc.odbc.JdbcOdbcdriver");
crs.setUserName("username");
crs.setPassword("pwd");
crs.setUrl("jdbc dbc:myDSN");
crs.setCommand("Call DB.STORPROC(?,?,'a>''111111111111''')");
crs.execute();
J. Alenis
Greenhorn

Joined: Jul 12, 2004
Posts: 16
Sorry, but that didn't do it.

When I want to do the crs.execute(); I get the following error:

com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: DB.STOPRO;PROCEDURE
at com.ibm.db2.jcc.c.yc.e(yc.java:1512)
at com.ibm.db2.jcc.c.yc.a(yc.java:1112)
at com.ibm.db2.jcc.a.db.h(db.java:148)
at com.ibm.db2.jcc.a.db.d(db.java:70)
at com.ibm.db2.jcc.a.r.d(r.java:75)
at com.ibm.db2.jcc.a.sb.j(sb.java:196)
at com.ibm.db2.jcc.c.yc.m(yc.java:1109)
at com.ibm.db2.jcc.c.zc.cb(zc.java:1810)
at com.ibm.db2.jcc.c.zc.d(zc.java:2235)
at com.ibm.db2.jcc.c.zc.S(zc.java:557)
at com.ibm.db2.jcc.c.zc.executeUpdate(zc.java:540)
at com.sun.rowset.internal.CachedRowSetReader.readData(Unknown Source)
at com.sun.rowset.CachedRowSetImpl.execute(Unknown Source)
at com.sun.rowset.CachedRowSetImpl.execute(Unknown Source)
at be.minfin.rdc.utils.AskRDCdb2file.execSPvect(AskRDCdb2file.java:505)
at be.minfin.rdc.test.javacust.main(javacust.java:51)
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
What's SQLCODE -440 in DB2? What's SQLSTATE 42884? They'll be listed in the manual. Syntax error at a guess.

How about putting that nasty-looking expression with all the quotes in it in a String, adding a third parameter to the CallableStatement and then using cstmt.setString(3, myNastyString);? I'd bet that's the source of your grief.

Jules
J. Alenis
Greenhorn

Joined: Jul 12, 2004
Posts: 16
When I do the Call in a DB2 window the Call works:

c:\>DB2 "Call DB.STOPRO(?,?,'nn>''111111111111''')"

but in Java's crs.setCommand it doesn't work although it's also a simple SQL statement.
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Did you try out my suggestion?
J. Alenis
Greenhorn

Joined: Jul 12, 2004
Posts: 16
Yes I tried it this way:

String nasty= "'nn>''111111111111''')";

crs.setString(3,nasty);
crs.setCommand("Call DB.STOPRO(?,?,");
crs.execute();

When I put the setString after the setCommand I get:
java.sql.SQLException: missing parameter: 1
somkiat puisungnoen
Ranch Hand

Joined: Jul 04, 2003
Posts: 1312
Originally posted by J. Alenis:
Yes I tried it this way:

String nasty= "'nn>''111111111111''')";

crs.setString(3,nasty);
crs.setCommand("Call DB.STOPRO(?,?,");
crs.execute();

When I put the setString after the setCommand I get:
java.sql.SQLException: missing parameter: 1




Change code follow me ::




SCJA,SCJP,SCWCD,SCBCD,SCEA I
Java Developer, Thailand
J. Alenis
Greenhorn

Joined: Jul 12, 2004
Posts: 16
Well I changed it like this:

crs.setCommand("Call DB.STOPRO(?,?,");
crs.setString(1,"nn>");
crs.setString(2,"111111111111)");
crs.execute();

and I get:

com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: ,;VENSOC.AANGIFTE1(?,?

SQL -104: An unexpected token was found following text. Expected tokens may include: token-list.
somkiat puisungnoen
Ranch Hand

Joined: Jul 04, 2003
Posts: 1312
In your code




Change to



I think, it can work....
J. Alenis
Greenhorn

Joined: Jul 12, 2004
Posts: 16
I'm sorry to inform you that it doesn't work.

Now I get:

com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -440, SQLSTATE: 42884, SQLERRMC: DB.STOPRO;PROCEDURE

SQL -440: No authorized routine named routine-name of type routine-type having compatible arguments was found.
somkiat puisungnoen
Ranch Hand

Joined: Jul 04, 2003
Posts: 1312
SQL -440: No authorized routine named routine-name of type routine-type having compatible arguments was found.


In this error message :

- you must check argument of YOUR Store procedure, which it's same as in java code or not ??
somkiat puisungnoen
Ranch Hand

Joined: Jul 04, 2003
Posts: 1312
For more detail...

Please post your Store procedure ....
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
OK, let me try and make what I meant a little clearer. Try this:

I'm assuming that the string you're trying to pass in the third parameter is a>'111111111111'.
You'll need to change <ParamType> and <ParamValue> in my example above to the appropriate values for your query. So if the first parameter is an int and the second a varchar, you'd use setInt() and setString().

The example assumes that they are input parameters. I don't think that output parameters are legal for a RowSet so if your SP uses them that may give you problems (or they may just be ignored).

Hope this solves it for you.

Jules
Dirk Schreckmann
Sheriff

Joined: Dec 10, 2001
Posts: 7023
Moving this to the JDBC forum...


[How To Ask Good Questions] [JavaRanch FAQ Wiki] [JavaRanch Radio]
J. Alenis
Greenhorn

Joined: Jul 12, 2004
Posts: 16
I Thought that everything was ok, because I didn't receive the error code anymore. But I don't get any results either.

This is my code:

public static void main(String[] args)
{
Connection con = null;
try
{
Class.forName("com.ibm.db2.jcc.DB2Driver");
CachedRowSet crs = new CachedRowSetImpl();
ResultSet rs = null;

//con = DriverManager.getConnection("jdbc b2://localhost:50000/DB", "login", "pwd");
//Check for warnings generated during connect

crs.setUsername("login");
crs.setPassword("pwd");
crs.setUrl("jdbc b2://localhost:50000/DB");

/* Works fine:
crs.setCommand("SELECT * from DB.TABLE");
*/

/* Does not work:
crs.setCommand("Call DB.STOPRO(?,?,?)");
//crs.setInt(1,0);
//crs.setString(2,"");
//crs.setString(3,"nn>''111111111111''");
//crs.registerOutParameter (1, Types.VARCHAR);
//crs.registerOutParameter (2, Types.INTEGER);
*/
crs.setCommand("CALL DB.SELSTOPRO"); // Stored Procedure that only does a select
crs.execute();
System.out.println("Size: " + crs.size() + " records");
// Returns: Size: 0 records
}
catch(SQLException sqle)
{
sqle.printStackTrace();
}
catch (ClassNotFoundException cnfe)
{
cnfe.printStackTrace();
}
catch (FileNotFoundException cnfe)
{
cnfe.printStackTrace();
}
}
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
I've not looked at Tiger's CachedRowSet but it doesn't look like you connect here, unless setCommand() or execute() do that implicitly. I guess your other SQL/SP wouldn't work if it didn't connect somehow.

Please post your SP code (using UBB CODE tags). I'm guessing that if your other SP is doing more than just a SELECT you may need to use the CRS equivalent of Statement.getMoreResults() and getUpdateCount(). If you search this forum I posted some succinct code to do this very recently.

Jules
J. Alenis
Greenhorn

Joined: Jul 12, 2004
Posts: 16
But the CALL with just a SELECT doesn't work either.
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Have you tried "call" in all lower case?
Raj Chila
Ranch Hand

Joined: Mar 18, 2004
Posts: 128

Hi,

Seems like you are using the CachedRowset as a Prepared Statment? I feel for sure that if you substiture the '?' with the actual values..you could get some luck..

All the best
 
jQuery in Action, 2nd edition
 
subject: CachedRowSet - Stored Procedure
 
Similar Threads
java.sql.SQLException: invalid name pattern
Call a stored Proc from JavaSript? Is it possible?
Stored Procedure within DAO class ?
Hibernate / Stored Procedure / Reporting
Oracle Stored Procedure Problem...