Topic
  • 6 replies
  • Latest Post - ‏2012-11-29T06:05:21Z by B.Hauser
SystemAdmin
SystemAdmin
3129 Posts

Pinned topic Calling Stored Procedures Question?

‏2004-03-26T15:40:52Z |
Is there a way to call a stored procedure from an external application (like a VB.NET app) without having to specify the libary/collection it is defined in?

I want to have both a production (WEBPROD library) and a test (WEBTEST library) environment with access controlled by the user profile/job description library list so I don't have to develop/test in the production system.

Am also open to other ways of approaching this. Thanks in advance for any suggestions!
Updated on 2012-11-29T06:05:21Z at 2012-11-29T06:05:21Z by B.Hauser
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Calling Stored Procedures Question?

    ‏2004-05-22T23:23:47Z  
    I am also having this issue because I am trying to implement our change management software over a set of libraries (SQL Collections) that contain SQL stored procedures (compiled C with embedded SQL) which call external programs (RPG ILE). Today we have 2 test libraries and a production library and the stored procedures and the RPG ILE programs must exist in each library because the external program and files within the stored procedure are qualified to the library that the stored proceudre lives in. In order to promote the software up to the next level I cannot have qualified libraries in the source code. I also have limited knowledge of SQL, initiating stored procedures via ODBC, etc... I have been reading all the manauls related to these subjects and am frustrated as I cannot find an answer to this issue. As an AS/400 bigot, starting on the S/38 in 1981, we were taught that library qualication was a bad thing and library lists were the way to handle finding the right object. Now I cannot find an example that doesn't stipulate the external program in the stored procedure as lib.pgm or lib/pgm. Any assitance in this area would be greatly appreciated.
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Calling Stored Procedures Question?

    ‏2004-05-24T15:32:47Z  
    I am also having this issue because I am trying to implement our change management software over a set of libraries (SQL Collections) that contain SQL stored procedures (compiled C with embedded SQL) which call external programs (RPG ILE). Today we have 2 test libraries and a production library and the stored procedures and the RPG ILE programs must exist in each library because the external program and files within the stored procedure are qualified to the library that the stored proceudre lives in. In order to promote the software up to the next level I cannot have qualified libraries in the source code. I also have limited knowledge of SQL, initiating stored procedures via ODBC, etc... I have been reading all the manauls related to these subjects and am frustrated as I cannot find an answer to this issue. As an AS/400 bigot, starting on the S/38 in 1981, we were taught that library qualication was a bad thing and library lists were the way to handle finding the right object. Now I cannot find an example that doesn't stipulate the external program in the stored procedure as lib.pgm or lib/pgm. Any assitance in this area would be greatly appreciated.

    Not sure what problem you are hitting? Are you saying that you're forced to
    qualify the external program name on a CREATE PROCEDURE statement?

    Kent Milligan, DB2 & BI team
    PartnerWorld for Developers, iSeries
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> www.iseries.ibm.com/db2
    (opinions stated are not necessarily those of my employer)
  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Calling Stored Procedures Question?

    ‏2004-06-01T14:33:04Z  
    I am also having this issue because I am trying to implement our change management software over a set of libraries (SQL Collections) that contain SQL stored procedures (compiled C with embedded SQL) which call external programs (RPG ILE). Today we have 2 test libraries and a production library and the stored procedures and the RPG ILE programs must exist in each library because the external program and files within the stored procedure are qualified to the library that the stored proceudre lives in. In order to promote the software up to the next level I cannot have qualified libraries in the source code. I also have limited knowledge of SQL, initiating stored procedures via ODBC, etc... I have been reading all the manauls related to these subjects and am frustrated as I cannot find an answer to this issue. As an AS/400 bigot, starting on the S/38 in 1981, we were taught that library qualication was a bad thing and library lists were the way to handle finding the right object. Now I cannot find an example that doesn't stipulate the external program in the stored procedure as lib.pgm or lib/pgm. Any assitance in this area would be greatly appreciated.
    We use Implementer for change control, and we have solved the problem by
    having it do a search and replace for each promotion. Search for the dev
    library name, replace with the library name of the target environment.

    Yeah, it's a shame you can't use library lists with SQL, but there is no
    other solution, AFAIK.

    Chris Falter

  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Calling Stored Procedures Question?

    ‏2004-06-01T20:45:15Z  
    We use Implementer for change control, and we have solved the problem by
    having it do a search and replace for each promotion. Search for the dev
    library name, replace with the library name of the target environment.

    Yeah, it's a shame you can't use library lists with SQL, but there is no
    other solution, AFAIK.

    Chris Falter

    As I posted earlier, stored procedures and SQL can use library lists. What
    specific problem are you having?

    Kent Milligan, DB2 & BI team
    PartnerWorld for Developers, iSeries
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> www.iseries.ibm.com/db2
    (opinions stated are not necessarily those of my employer)

  • SystemAdmin
    SystemAdmin
    3129 Posts

    Re: Calling Stored Procedures Question?

    ‏2012-11-28T17:56:24Z  
    As I posted earlier, stored procedures and SQL can use library lists. What
    specific problem are you having?

    Kent Milligan, DB2 & BI team
    PartnerWorld for Developers, iSeries
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> www.iseries.ibm.com/db2
    (opinions stated are not necessarily those of my employer)

    I have just started to use procedures and running into the same issue as described. Did anybody find out how to use library list with *SQL?
  • B.Hauser
    B.Hauser
    253 Posts

    Re: Calling Stored Procedures Question?

    ‏2012-11-29T06:05:21Z  
    I have just started to use procedures and running into the same issue as described. Did anybody find out how to use library list with *SQL?
    You need to be more specific.
    What naming conventions are you using, *SYS or *SQL Naming convention.

    With System Naming convention the library list is searched to find all database objects, i.e. tables, views, stored procedures ...

    With SQL Naming convention the default/current schema (which is always only a single library!) is searched to find tables and views, while the SQL path is searched to find other database objects, such as stored procedures and User defined functions. The SQL path represents a list of schemas/libraries and is searched like a library list. Special value *LIBL can be used to set the current path.

    The default/current schema can be modified by executing the SQL command SET SCHEMA, while the SQL path can be modified with SET PATH

    For more information please check the following links:
    System versus SQL Naming - Part I
    System versus SQL Naming - Part II

    Birgitta