I got the same error you did when I used your procedure exactly as you
provided it. (I changed the table name to 'rhino.Employee ' to match my
sample tables but it was identical otherwise.)
I got your stored procedure to work fine in my system, which is running DB2
V7.2 on XP, by making these changes:
- deleted this line: #sql context SpContext;
- deleted this line: ctx.close();
- removing the '[ctx]' from the line containing the query
In short, the problem has something to do with the context.
My SQLJ is very rusty so I dug up my notes on it and found a brief
discussion of context, including an example. The example established the
context slightly differently than you did so I modified your original code
to follow the example and came up with this, which works as you probably
desired:
----------------------------------------------------------------------------
----------------------------------------
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.re f.*;
import java.io.*; // Input/Output classes
#sql iterator Sproc3_Iterator (String,String) ;
#sql context SpContext;
public class sproc3 {
public static void sproc3mtd (String workdept, ResultSet[] rs1) throws
Exception {
char error = 'n';
Connection con = DriverManager.g etConnection("j dbc:default:con nection");
DefaultContext SpContext = new DefaultContext( con);
DefaultContext. setDefaultConte xt(SpContext);
Sproc3_Iterator sproc3_iterator = null;
// Get connection to the database
try {
#sql [SpContext] sproc3_iterator = {
SELECT FIRSTNME, LASTNAME FROM rhino.EMPLOYEE WHERE WORKDEPT =
:workdept
};
rs1[0] = sproc3_iterator .getResultSet() ;
}
catch (SQLException sqlexcp) {
sqlexcp.getMess age();
error = 'y';
}
}
}
----------------------------------------------------------------------------
----------------------------------------
The critical difference is that your program establishes the connection like
this:
SpContext ctx = new SpContext( "jdbc:default:c onnection", false);
while mine does it like this:
Connection con = DriverManager.g etConnection("j dbc:default:con nection");
DefaultContext SpContext = new DefaultContext( con);
DefaultContext. setDefaultConte xt(SpContext);
Perhaps others on this newsgroup can suggest why your original code didn't
work; unfortunately I can't find the API that contains the DefaultContext
class so I don't know.
In any case, you should be able to get your stored procedure to work now,
even if I can't say exactly why it didn't work.
Rhino
"Raquel" <ra************ ****@yahoo.com> wrote in message
news:9a******** *************** ***@posting.goo gle.com...
This is a very simple DB2 SQLJ stored procedure. The problem is that it
seems to run fine but returns NOTHING. I mean..as if nothing has
happened..not resultset is returned. I am passing value 'D11' to :workdept
and I have checked in the table that 6 rows should have returned. Any
ideas why no resultset is being returned.
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.re f.*;
import java.io.*; // Input/Output classes
#sql iterator Sproc3_Iterator (String lname,String fname);
#sql context SpContext;
public class sproc3
{
public static void sproc3mtd ( String workdept,
ResultSet[] rs1 ) throws Exception
{
char error = 'n';
SpContext ctx = new SpContext( "jdbc:default:c onnection", false
);
Sproc3_Iterator sproc3_iterator = null;
// Get connection to the database
try
{
#sql [ctx] sproc3_iterator = {SELECT FIRSTNME, LASTNAME FROM
db2admin.EMPLOY EE WHERE WORKDEPT = :workdept};
rs1[0] = sproc3_iterator .getResultSet() ;
ctx.close();
} catch (SQLException sqlexcp)
{
sqlexcp.getMess age();
error = 'y';
}
}
}
The stored procedure definition is:
CREATE PROCEDURE SP3 (IN in1 CHAR(6))
LANGUAGE JAVA
PARAMETER STYLE JAVA
DYNAMIC RESULT SETS 1
FENCED THREADSAFE
EXTERNAL NAME
'sproc3.sproc3m td';
TIA
Raquel.
