#344 JDBC DB2400 stored procedure issues

[WBois]

We're not doing a lot of SQL here for new database
stuff, but we are building a lot of stored procedures
for client queries and database updates from the web.
So my interest in this product was more specific to
stored procedures, however I'm having some
difficulties. I tried a JDBC connection to my 400
which works fine. The object list shows all my
procedures in my selected schema, when I click on a
procedure object the work panel shows an Info and
Column tabs, the Column tab however, doesn't show any
procedure parameter information. When I use an ODBC
connection instead, then I get all the parameter info
in the Column tab. I believe I'm using a current
version of the JT400.jar. I'm not an expert in any of
this stuff, but I believe I have everything configured
correctly. I was wondering if you've experienced this
limitation with the JDBC connection.
What are the advantages or disadvantages of
using a JDBC connection over the ODBC connection?

My second biggest problem is the SQL editor doesn't
like running our stored procedure scripts. These
scripts compile just fine using IBM's OPSNAV
(Operations Navigator) RUNSQLSTM processor in program
CWBUNDBS.EXE. Squirrel has difficulty parsing the
multiple SQL statements that are typical of a stored
procedure created via OPSNAV. Even if I try to
highlight and run the specific create procedure
statement portion, it fails on invalid
END_OF_STATEMENT errors . I've tried toggling on and
off the ; delimiter but still can't get the procedure
created through the Squirrel editor.
What are your experiences with this? Any workarounds
or special techniques I should be using? Also does
Squirrel provide a way to test stored procedures with
INOUT or OUT parameters? I don't have the client
language skills to develop something quick to test
these kinds of stored procedures before I hand them
off to our Java developers for use. Thanks for you
time. Attached is a sample of a typical DB2400 stored
procedure script file.

[WBois]

sample DB2400 stored procedure script

[Gerd Wagner]

Logged In: YES
user_id=776065

None of us has access to DB 400 to reproduce your problem.
But I would really be interested in it. So here is my
proposal: If you could give me access to DB 400 via VPN
1. I would take care of your problem(s)
2. I would try to write a DB 400 plugin for SQuirreL that
would allow you to script views and stored procedures from
within SQuirreL's object tree. I know how to do that on DB2
for Linux and hope it wouldn't be much harder on DB400.

Im closing this bug. In case you could give me access to
DB400 please feel free to reopen it and contact me at
gerdwagner@users.sourceforge.net

[Rob Manning]

Logged In: YES
user_id=1287991

While you are mulling Gerd's proposal, I noticed something
in your attached stored proc script that you might like
to fix. You see, SQuirreL interprets two consecutive eol
chars as a statement separator as well as the semi-colon.
In your script proc called "AMSGEN.SP_RTV_LOCATIONS_LIST"
you seem to have two end-of-lines between your proc args and
the BEGIN keyword. My guess is that if you are placing your
cursor on the line of code that contains the "CREATE"
keyword, then the following is all that is being sent to the
server by SQuirreL:

CREATE PROCEDURE AMSGEN.SP_RTV_LOCATIONS_LIST ( )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC SP_RTV_LOCATIONS_LIST
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT

And since this isn't a valid procedure def, the server
responds with invalid end-of-statement errors. Try removing
the extra eol chars from your procedure def and see if that
doesn't help.

Rob

[WBois]

Logged In: YES
user_id=1457930

Thank you Gerd and Rob for your responses. Unfortunately,
the option of a VPN connection to one of our ISeries for
you to test out your plugins is not going to be possible.
If it were up to me I would try to arrange something but, I
already know the answer without even asking. Rob, per your
suggestion, I will try to remove the double EOL characters
from my script and try again to compile the stored
procedure. I hope you can find someone that could allow you
access to their DB2400 so that you could enhance the
product. I know there is a community of ISeries/AS400
programmers out there looking for a better SQL tool than
the one that IBM provides with Operations Navigator. Again
thanks for your time.

[Daniel_Linyu]

Logged In: YES
user_id=1483750

The problem is the terminator-";" at the end of each
statement in the procedure.

Actually, the statement being sent to the server by
SQuirreL is :

CREATE PROCEDURE AMSGEN.SP_RTV_LOCATIONS_LIST ( )
DYNAMIC RESULT SETS 1
.
.
.

ORDER BY Location_Code

I found SQuirreL just using ";" as the sql statement
separator, and I guess it will meets the same problem in
oracle env(also contains ";" terminator in procedure), but
in MS SQLserver env, it may work fine because it doesn't
contains ";" in the procedure.

Daniel

[Daniel_Linyu]

Logged In: YES
user_id=1483750

I solved this problem by adding some codes in
net.sourceforge.squirrel_sql.fw.sql.QueryTokenizer

Daniel

[WBois]

Logged In: YES
user_id=1457930

Hi Daniel, could you clarify your comment plaese? Are you
saying you have modified Squirrel so that it will parse and
compile my DB2 400 Stored Procedure scripts correctly? If
so,what do I need to do to get this update? Thanks.

[Daniel_Linyu]

Logged In: YES
user_id=1483750

wbois，

Yes，I modified some Squirrel code to make your procedure
scripts can be parsed and compiled.

Because I am not a member of Squirrel-Developers, so I
don't know how to add the code to Squirrel's offical
release, but I can give you my code personally.

You can request the code by sending a Email to me
Daniel.Linyu@Gmail.com).

Daniel