By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,491 Members | 3,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,491 IT Pros & Developers. It's quick & easy.

No result set being returned in SQLJ Stored Procedure ....

P: n/a
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.ref.*;
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:connection", false
);
Sproc3_Iterator sproc3_iterator = null;
// Get connection to the database

try
{
#sql [ctx] sproc3_iterator = {SELECT FIRSTNME, LASTNAME FROM
db2admin.EMPLOYEE WHERE WORKDEPT = :workdept};
rs1[0] = sproc3_iterator.getResultSet();

ctx.close();
} catch (SQLException sqlexcp)
{
sqlexcp.getMessage();
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.sproc3mtd';

TIA
Raquel.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Two obvious questions first. You probably considered these already but you
didn't *say* you have checked them so I'm going to suggest them, just in
case:
1. Are there any rows in your DB2ADMIN.Employee table?
2. Are there any rows in your DB2ADMIN.Employee table whose workdept matches
the value you gave when you ran the stored procedure?

Also, have you tried creating the stored proc in the Stored Procedure
Builder/Development Center? I just tried this, which has the same query in
it, in the Stored Procedure Builder - I'm still on DB2 V7.2 - and it worked
fine:

----------------------------------------------------------------------------
-------------------------------------
/**
* SQLJ Stored Procedure RHINO.sproc3
*/
import java.sql.*; // JDBC classes
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

#sql iterator Sproc3_Cursor1 ( String, String );

public class Sproc3
{
public static void sproc3 ( String workdept,
String[] SQLSTATE,
int[] SQLCODE,
String[] SQLMessage,
ResultSet[] rs ) throws Exception
{
Sproc3_Cursor1 cursor1 = null;
#sql cursor1 =
{
SELECT FIRSTNME, LASTNAME
FROM rhino.EMPLOYEE
WHERE WORKDEPT = :workdept
};
rs[0] = cursor1.getResultSet();

// Set return parameters
SQLSTATE[0] = "00000"; // Good SQLSTATE
SQLCODE[0] = 0; // Good SQLCODE
SQLMessage[0] = " "; // Good SQLMessage
}
}
----------------------------------------------------------------------------
-------------------------------------
It would only take a minute for you to try it there; if it works fine for
you, it would suggest that maybe your preparation procedures aren't quite
right or your table is empty or missing rows that have the workdept you
specified. Of course the program isn't above isn't identical to the one you
tried so that could also account for the difference in behaviour. The next
step is obviously to make it identical and see if it still works then. I'll
do that shortly but I just thought I'd suggest a few obvious things first to
minimize the time you need to wait for a reply.

I'm curious about your 'error' variable: what is it doing for you? You never
pass it back to the calling program and its value doesn't seem to control
the behaviour of your procedure at all. It shouldn't hurt anything but it
doesn't look like it is going to accomplish anything either.

Rhino

"Raquel" <ra****************@yahoo.com> wrote in message
news:9a**************************@posting.google.c om...
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.ref.*;
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:connection", false
);
Sproc3_Iterator sproc3_iterator = null;
// Get connection to the database

try
{
#sql [ctx] sproc3_iterator = {SELECT FIRSTNME, LASTNAME FROM
db2admin.EMPLOYEE WHERE WORKDEPT = :workdept};
rs1[0] = sproc3_iterator.getResultSet();

ctx.close();
} catch (SQLException sqlexcp)
{
sqlexcp.getMessage();
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.sproc3mtd';

TIA
Raquel.

Nov 12 '05 #2

P: n/a
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.ref.*;
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.getConnection("jdbc:default:connecti on");
DefaultContext SpContext = new DefaultContext(con);
DefaultContext.setDefaultContext(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.getMessage();
error = 'y';
}
}
}

----------------------------------------------------------------------------
----------------------------------------

The critical difference is that your program establishes the connection like
this:
SpContext ctx = new SpContext( "jdbc:default:connection", false);

while mine does it like this:
Connection con = DriverManager.getConnection("jdbc:default:connecti on");
DefaultContext SpContext = new DefaultContext(con);
DefaultContext.setDefaultContext(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.google.c om...
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.ref.*;
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:connection", false
);
Sproc3_Iterator sproc3_iterator = null;
// Get connection to the database

try
{
#sql [ctx] sproc3_iterator = {SELECT FIRSTNME, LASTNAME FROM
db2admin.EMPLOYEE WHERE WORKDEPT = :workdept};
rs1[0] = sproc3_iterator.getResultSet();

ctx.close();
} catch (SQLException sqlexcp)
{
sqlexcp.getMessage();
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.sproc3mtd';

TIA
Raquel.

Nov 12 '05 #3

P: n/a
Hi Rhino,

Thank you so much for looking into this in such detail. I am new to
stored procedures and guess that could be my (clumsy) pretext for not
declaring/returning SQLCODE from the stored procedure. Basically I
found the following two errors in my stored procedure:

1. There is no table by the name db2admin.EMPLOYEE; it is actually
db2adm.EMPLOYEE. The SQL promply generated -204 SQLCODE, but I just
could not 'see' it (because I did not handle it in the procedure).

2. The following declaration was wrong:

#sql iterator Sproc3_Iterator(String lname,String fname);

The above declaration was producing an error along the lines
"invalid column name".

This should have just been declared as:

#sql iterator Sproc3_Iterator(String,String);

My further tests indicate that if at all variables are being
defined in the declaration (variables like lname and fname defined
above), they should be the same as the column names being retreived.
In other words, the following declarating works fine:

#sql iterator Sproc3_Iterator(String firstnme,String lastname);

Thank you again for taking the time and effort to look into this
and providing me with helpful suggestions.

Regards,
Raquel.
Nov 12 '05 #4

P: n/a

"Raquel" <ra****************@yahoo.com> wrote in message
news:9a**************************@posting.google.c om...
Hi Rhino,

Thank you so much for looking into this in such detail. I am new to
stored procedures and guess that could be my (clumsy) pretext for not
declaring/returning SQLCODE from the stored procedure.
I'm fairly new to stored procedures myself so I can sympathize with you. I
found my first few programs a bit of a struggle too ;-)

You're right; error handling is quite critical to debugging your stored
procedures. It is probably excessive but my stored procedures return
SQLCODE, SQLSTATE, SQLMESSAGE, a custom written message specific to the
statement that was executed and a stacktrace. In addition, I've worked out
code that writes to a simple text file which acts as a log so that I can
track what is going on in the stored procedure; I can plug this logging code
in if the other diagnostics aren't solving the problem. Of course, most of
this would not have been necessary if I could get the IBM Distributed
Debugger to work but I never managed that ;-)
Basically I
found the following two errors in my stored procedure:

1. There is no table by the name db2admin.EMPLOYEE; it is actually
db2adm.EMPLOYEE. The SQL promply generated -204 SQLCODE, but I just
could not 'see' it (because I did not handle it in the procedure).
That's exactly why you need to be serious about error handling in your
stored procedures. An innocent mistake like this can be easily detected if
you check for it but almost impossible to find if you don't code for it.
2. The following declaration was wrong:

#sql iterator Sproc3_Iterator(String lname,String fname);

The above declaration was producing an error along the lines
"invalid column name".

This should have just been declared as:

#sql iterator Sproc3_Iterator(String,String);

My further tests indicate that if at all variables are being
defined in the declaration (variables like lname and fname defined
above), they should be the same as the column names being retreived.
In other words, the following declarating works fine:

#sql iterator Sproc3_Iterator(String firstnme,String lastname);

Thank you again for taking the time and effort to look into this
and providing me with helpful suggestions.

You're very welcome ;-)

Rhino
Nov 12 '05 #5

P: n/a
Rhino wrote:
You're right; error handling is quite critical to debugging your stored
procedures. It is probably excessive but my stored procedures return
SQLCODE, SQLSTATE, SQLMESSAGE, a custom written message specific to the
statement that was executed and a stacktrace.


Except for the stack trace, all those things are just normal practice, I'd
say.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.