SQL Stored Procedure - IBM AS400

This is a discussion on SQL Stored Procedure - IBM AS400 ; Hi All,
I have written one SQL stored procedure to return a record set to the
calling program. in this Stored procedure i am fetching data from the
data base files.
Now the problem is that the application(calling program) is ...

SQL Stored Procedure

Hi All,

I have written one SQL stored procedure to return a record set to the
calling program. in this Stored procedure i am fetching data from the
data base files.
Now the problem is that the application(calling program) is unable to
fetch all the data which is returning in record set. But if i am
sending some constant values then the application can fetch the data
easily.So i cant find out where is the problem?

Please help.

Thanks
Mohan

Re: SQL Stored Procedure

mohan.agrawal19@gmail.com wrote:
> Hi All,
>
> I have written one SQL stored procedure to return a record set to the
> calling program. in this Stored procedure i am fetching data from the
> data base files.
> Now the problem is that the application(calling program) is unable to
> fetch all the data which is returning in record set. But if i am
> sending some constant values then the application can fetch the data
> easily.So i cant find out where is the problem?

What does your procedure look like? What application
or client is calling it?

Re: SQL Stored Procedure

il 23/05/2007 12.17, Scrive mohan.agrawal19@gmail.com 40749624:
> Hi All,
>
> I have written one SQL stored procedure to return a record set to the
> calling program. in this Stored procedure i am fetching data from the
> data base files.
> Now the problem is that the application(calling program) is unable to
> fetch all the data which is returning in record set. But if i am
> sending some constant values then the application can fetch the data
> easily.So i cant find out where is the problem?
>
I'sure I didn't thoroughly what you said, but I'm aware that stored
procedures returning resalt sets are not available for programs, but
only for clients (jdbc/odbc/cli).

Re: SQL Stored Procedure

On May 24, 3:11 pm, "Dr.UgoGagliardelli" wrote:
> il 23/05/2007 12.17, Scrive mohan.agrawa...@gmail.com 40749624:> Hi All,
>
> > I have written one SQL stored procedure to return a record set to the
> > calling program. in this Stored procedure i am fetching data from the
> > data base files.
> > Now the problem is that the application(calling program) is unable to
> > fetch all the data which is returning in record set. But if i am
> > sending some constant values then the application can fetch the data
> > easily.So i cant find out where is the problem?
>
> I'sure I didn't thoroughly what you said, but I'm aware that stored
> procedures returning resalt sets are not available for programs, but
> only for clients (jdbc/odbc/cli).
>
> --
> Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherA˝e joAlcoolInside
> Spaccamaroni andate a cagare/Spammers not welcome/Spammers vŃo Ó merda
> Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
> schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

Yes RPG4 doesn't currently directly support receiving a Result Set
(RS). There is however an excellent article on page 27 of April 2007
System i News (News/400) which shows how to access the RS using Java
methods from RPG4.

John Garrould

Re: SQL Stored Procedure

il 24/05/2007 22.05, Scrive John Garrould 40720576:
> On May 24, 3:11 pm, "Dr.UgoGagliardelli"
> wrote:
>> il 23/05/2007 12.17, Scrive mohan.agrawa...@gmail.com 40749624:> Hi All,
>>
>>> I have written one SQL stored procedure to return a record set to the
>>> calling program. in this Stored procedure i am fetching data from the
>>> data base files.
>>> Now the problem is that the application(calling program) is unable to
>>> fetch all the data which is returning in record set. But if i am
>>> sending some constant values then the application can fetch the data
>>> easily.So i cant find out where is the problem?
>> I'sure I didn't thoroughly what you said, but I'm aware that stored
>> procedures returning resalt sets are not available for programs, but
>> only for clients (jdbc/odbc/cli).
>>
>> --
>> Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherA˝e joAlcoolInside
>> Spaccamaroni andate a cagare/Spammers not welcome/Spammers vŃo Ó merda
>> Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
>> schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'
>
> Yes RPG4 doesn't currently directly support receiving a Result Set
> (RS). There is however an excellent article on page 27 of April 2007
> System i News (News/400) which shows how to access the RS using Java
> methods from RPG4.
>
> John Garrould
>
I have nothing against RPG, but, in this particuar case, I prefer to
program in Java.

Why not RPG? CLI allows you to process result sets and CLI is
definitely available to any HLL on System i.

Re: SQL Stored Procedure

il 25/05/2007 23.14, Scrive Elvis 40758768:
> On May 25, 1:15 pm, "Dr.UgoGagliardelli"
[...]
>>>>> I have written one SQL stored procedure to return a record set to the
>>>>> calling program.
[...]
>>> Yes RPG4 doesn't currently directly support receiving a Result Set
>>> (RS). There is however an excellent article on page 27 of April 2007
>>> System i News (News/400) which shows how to access the RS using Java
>>> methods from RPG4.
>>> John Garrould
>> I have nothing against RPG, but, in this particuar case, I prefer to
>> program in Java.
> Why not RPG? CLI allows you to process result sets and CLI is
> definitely available to any HLL on System i.
>
He was speaking about using JDBC Java classes in RPGle, that are usable
but they are absolutely uncomfortable due to RPG language structure
that's not object oriented at all.
CLI is another option (or maybe not an option al all), very different
from JDBC, and even if CLI functions are available to any language that
partecipate to ILE, you shoud at least newly protopype them all, as in
QUSRSYS you have only C headers. Further more, CLI is not very
comfortable if used with C language, so they are even less in RPGle.

The original poster claimed about storage procedure returning result
sets, maybe his approach simply failed because he didn't realized that
those procs coudn't be used in RPG, so, if he wants to use procs in rpg
or any other ILE language or host based language, he should have chosen
a different approach from the beginning. The effort balance between
changing RPGs to use Java and changing RPGs to get their own result
sets, should be evident.
--
Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherA˝e joAlcoolInside
Spaccamaroni andate a cagare/Spammers not welcome/Spammers vŃo Ó merda
Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

Re: SQL Stored Procedure

Dr.UgoGagliardelli wrote:
> He was speaking about using JDBC Java classes in RPGle, that are usable
> but they are absolutely uncomfortable due to RPG language structure
> that's not object oriented at all.

I didn't see a prior reference to Java... at least, I can't find one in
the thread. Not disputing; just a comment.

> CLI is another option (or maybe not an option al all), very different
> from JDBC, and even if CLI functions are available to any language that
> partecipate to ILE, you shoud at least newly protopype them all, as in
> QUSRSYS you have only C headers. Further more, CLI is not very
> comfortable if used with C language, so they are even less in RPGle.

Note that the Sorcerer's Guide redbook has included a CD with all source
since first publication. The later .PDF has an associated download of
the RPGISCOOL library. All original source is there, including the
example SQL-CLI prototypes and programming. (Source could be text-copied
from the .PDF if necessary.)

Re: SQL Stored Procedure

Off topic... but _how_ can text-copy from .PDF be usable, esp. for
[fixed format or even simply formatted] code? I have always had the
biggest problem with the .pdf simply because text copy from a .pdf is
unusable in my experience. Simple text seems to me irretrievable at
best; i.e. I have never found a way to extract the original plain text
from a .pdf file. Perhaps my distaste for .pdf has blinded me in my
ability to search and find a way?

FWiW I believe the path to Java is visible in the posts; perhaps your
news server missed a post or two? Went something like: SQL proc return
result set to [SQL] RPG -> jdbc/odbc/cli can [not expressly precluding
RPG with CLI] -> Java methods from RPG [ref to article]. The "He" in
the most first paragraph seems not to refer to the original poster, but
to the person pointing out Java methods in the referenced article. Only
the second paragraph calls out the OP.

Regards, Chuck
-- All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

Thomas wrote:
> Dr.UgoGagliardelli wrote:
>
>> He was speaking about using JDBC Java classes in RPGle, that are
>> usable but they are absolutely uncomfortable due to RPG language
>> structure that's not object oriented at all.
>
> I didn't see a prior reference to Java... at least, I can't find one in
> the thread. Not disputing; just a comment.
>
>
>> CLI is another option (or maybe not an option al all), very different
>> from JDBC, and even if CLI functions are available to any language
>> that partecipate to ILE, you shoud at least newly protopype them all,
>> as in QUSRSYS you have only C headers. Further more, CLI is not very
>> comfortable if used with C language, so they are even less in RPGle.
>
> Note that the Sorcerer's Guide redbook has included a CD with all source
> since first publication. The later .PDF has an associated download of
> the RPGISCOOL library. All original source is there, including the
> example SQL-CLI prototypes and programming. (Source could be text-copied
> from the .PDF if necessary.)
>
> Redbook link:
>
> http://www.redbooks.ibm.com/abstracts/sg245402.html
>
> The associated download is in the [Additional Material] link.
>
> Lots of good stuff in there even if SQL-CLI is never used.
>

Re: SQL Stored Procedure

CRPence wrote:
> Off topic... but _how_ can text-copy from .PDF be usable, esp. for
> [fixed format or even simply formatted] code? I have always had the
> biggest problem with the .pdf simply because text copy from a .pdf is
> unusable in my experience. Simple text seems to me irretrievable at
> best; i.e. I have never found a way to extract the original plain text
> from a .pdf file. Perhaps my distaste for .pdf has blinded me in my
> ability to search and find a way?

No disagreement there from me. It's purely a "final resort" only used
"if necessary". I share a seemingly permanent distaste for .PDFs for
that and other reasons. I won't discard an option, though, just because
it's difficult in cases where no other option is available. The earlier
Book Manager CDs and formats from IBM were far better in that respect.
> FWiW I believe the path to Java is visible in the posts; perhaps your
> news server missed a post or two? Went something like: SQL proc return
> result set to [SQL] RPG -> jdbc/odbc/cli can [not expressly precluding
> RPG with CLI] -> Java methods from RPG [ref to article]. The "He" in
> the most first paragraph seems not to refer to the original poster, but
> to the person pointing out Java methods in the referenced article. Only
> the second paragraph calls out the OP.

Seems plausible and acceptable. I saw all the posts, but wanted a clear
reference rather than one that was only my inference. The "He" wasn't
clearly established. If it referred to the OP, then the context would
have dictated responses.

But with vague context, the avoidance of CLI becomes a matter of
opinion. I've used CLI/RPG for various unusual purposes and find it a
handy way to resolve a number of problems. Link to the Redbook and note
for the [Additional Material] might help others get a start. An
alternative is to leave everyone thinking that IBM never released
anything in the way of RPG prototypes(, and perhaps even
Dr.UgoGagliardelli might be pleased to find available sources). The
conclusion that developers are on their own in unexplored territory
becomes more likely.

I realize that Java might be a better solution in many cases and I've
felt that IBMers have tended to propose Java solutions over RPG more and
more often as time has gone by. It doesn't fit my environment at the
moment, though, and I suspect that there are others in similar
circumstances.

SQL CLI in RPG is worth learning overall, and it's the best tool in many
environments.

Re: SQL Stored Procedure

Thomas writes:
> I realize that Java might be a better solution in many cases and I've
> felt that IBMers have tended to propose Java solutions over RPG more

My general impresson after having worked with this for a couple of
years, is that the Java Toolbox feels like that you are talking to a
remote server instead of getting access to the local machine.

--
Thorbj°rn Ravn Andersen

Re: SQL Stored Procedure

Thorbjoern Ravn Andersen wrote:
> Thomas writes:
>
>> I realize that Java might be a better solution in many cases and I've
>> felt that IBMers have tended to propose Java solutions over RPG more
>
> My general impresson after having worked with this for a couple of
> years, is that the Java Toolbox feels like that you are talking to a
> remote server instead of getting access to the local machine.

AFAIK, that's not far off. IIRC, it uses the LIPI interfaces. Then
again, SQL CLI is similar in its effect; that's part of its value -- you
write pretty much the same code whether the database is local or remote.
That's not an overwhelming advantage; it merely has its place.

But I don't avoid connecting to the servers under some circumstances
even when they're the local servers. I've written to the distributed
program call host server for example to resolve one category of problems.

Re: SQL Stored Procedure

Well you are talking to a remote server. It's just that sometimes the
remote server happens to be on the same machine. :-)

It can be extremely handy in some circumstances. I can recall an
occasion when we had a particular application fail on Live, and it had
to be run on Backup (a different AS/400) instead; another application
queried the data output from the failing application, and since this
querying application was written in Java, it was easy to leave the
Java application running on Live and to reconfigure it to look at the
data in Backup environment instead.

Re: SQL Stored Procedure

"walker.l2" writes:
> Well you are talking to a remote server. It's just that sometimes the
> remote server happens to be on the same machine. :-)

I fully agree that the concept is smart. It is much the same way of
thinking that made the X Windows system well liked.

I would, however, have appreciated if it was intensely optimized for
running on the same machine, and that remote access was just an extra
benefit.

As I see it now, it is actually hindering the tight integration
between the "old AS/400 world" and the Java world since it is the
defacto standard, and it is only the unoptimized version that is open
sourced.

--
Thorbj°rn Ravn Andersen

Re: SQL Stored Procedure

There are some optimizations available. There is the convenience
method:

AS400 my400 = new AS400();

which works when the JVM is running on the same AS/400 you want to
attach to. There is the native JDBC driver. And if you use the
official IBM code rather than JTOpen there is also the jt400Native.jar
which is an AS/400 optimised version of jt400.jar. I don't think you
will see much improvement in this area though since IBM seems to want
to replace the optimised 64-bit JVM with a cross-platform 32-bit one.

We use the Toolbox a fair bit for integration, since we still find it
quicker to knock a simple report using COBOL and PRTFs than it is to
get into Java printing, and end-of-day processes are still usually
better as COBOL batches than procedural Java. Generally though for
application-to-application integration we prefer to use queues (in
particular, MQ) to make things technology independent.