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 ...

+ Reply to Thread
Results 1 to 15 of 15

Thread: SQL Stored Procedure

  1. 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


  2. 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?

  3. 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).


    --
    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'

  4. 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


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

    --
    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'

  6. Re: SQL Stored Procedure

    On May 25, 1:15 pm, "Dr.UgoGagliardelli"
    wrote:
    > 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.
    >
    > --
    > 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'- Hide quotedtext -
    >
    > - Show quoted text -


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


  7. 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'

  8. 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.)

    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.

    --
    Tom Liotta
    http://zap.to/tl400

  9. 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.
    >


  10. 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.

    --
    Tom Liotta
    http://zap.to/tl400

  11. 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

  12. 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.

    It's one more tool to have available.

    --
    Tom Liotta
    http://zap.to/tl400

  13. 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.


  14. 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

  15. 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.


+ Reply to Thread