473,372 Members | 1,258 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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

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
5 5128
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
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
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

"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Paul Reddin | last post by:
Hi, (V8.1 FP2) Trying to run SQLJ.INSTALL_JAR, we get the following error SQL1131N DARI (Stored Procedure) process has been terminated abnormally. SQLSTATE=38503 Not sure why this has...
9
by: Janick | last post by:
With DB2 7.2 we used to call methods from other SQLJ Stored Procedures, as it was not possible to call SPs via the SQL Call-statement from within SPs. So we always had a method like this: ...
10
by: Raquel | last post by:
UDB PE 8.1 on Win XP. ok, this function caused a lot of grief today. My stored procedures are java stored procedures, all FENCED and directly placed in \sqllib\function directory (not built into...
4
by: Raquel | last post by:
A very simple piece of SQLJ: try { #sql sqlj4_iterator = {SELECT PHONENO FROM DB2ADM.EMPLOYEE WHERE WORKDEPT = 'D11'}; while(sqlj4_iterator.next()) {...
2
by: Raquel | last post by:
UDB V8.1.3 on XP. While trying out SQLj procedures, I just 'compile' my stored procedure code (by command sqlj program-name.sqlj) and run it. That's it. So, how and when is db2profc and all the...
7
by: Jean-Marc Blaise | last post by:
Hi, The dev center calls sqlj.DB2_UPDATEJARINFO ('JMARC.JMB','JMB','file:JMB.sqlj') to update the sqlj routine source. I tried in CLP from the directory containing jar and sqlj files (Windows...
0
by: Sahadev K | last post by:
Here is the question 1. I have two stored procedures P1 and P2. 2. I want to call stored procedure P2 from P1. 3. Stored Procedure P2 returns a result set(as an open cursor). 4. In P1, I want to...
9
by: serge | last post by:
/* Subject: How to build a procedure that returns different numbers of columns as a result based on a parameter. You can copy/paste this whole post in SQL Query Analyzer or Management Studio...
4
by: Janick Bernet | last post by:
When using SQLJ stored procedures in DB2 9, one has to manually create a Connextioncontext if the procedure should be threadsafe. Now to use this context one has also to prexif each #sql {}...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.