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

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
    ACCEPTED ANSWER

    Re: Calling Stored Procedures Question?

    ‏2004-05-22T23:23:47Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Calling Stored Procedures Question?

      ‏2004-05-24T15:32:47Z  in response to SystemAdmin

      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
      ACCEPTED ANSWER

      Re: Calling Stored Procedures Question?

      ‏2004-06-01T14:33:04Z  in response to SystemAdmin
      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
        ACCEPTED ANSWER

        Re: Calling Stored Procedures Question?

        ‏2004-06-01T20:45:15Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

          Re: Calling Stored Procedures Question?

          ‏2012-11-28T17:56:24Z  in response to SystemAdmin
          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
            222 Posts
            ACCEPTED ANSWER

            Re: Calling Stored Procedures Question?

            ‏2012-11-29T06:05:21Z  in response to SystemAdmin
            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