AW: SQL Stored Procedure

Hi,

there are some differences between SQL and System-Naming, not only for the
qualified notation, but especially for the unqualified notation.

1. Specifying a file (table or view) with unqualified notation:
   - System-Naming
     The library list is searched and the first occurence
     in the library list is used.
     With system naming files (tables) located in several
     libraries (schemas) can be used in the same procedure
     or even statement.

   - SQL-Naming
     The library (schema) with the same name as the user profile is used.
     To change the library (schema), you have to use the SQL statement
     SET CURRENT SCHEMA MySchema.
     With SQL naming only files (tables) from a single library (Schema)
     can be used. If files (tables) located in several libraries (Schemas)
     must be used, you have to qualify them.
     With RUNSQLSTM or CRTSQLRPGI the current library (schema)
     can be specified with the option DFTRDBCOL.
     Within embedded SQL the DFTRDBCOL-Option also can be set with a
     SET OPTION-Statement.

2. Unqualified Procedures and User Defined Functions
   - System Naming
     The library list is uesd.

   - SQL-Naming
     For CREATE, DROP, GRANT, REVOKE and COMMENT, the library (schema)
     is used with the name of the user profile is used, as long as
     DFTRDBCOL is not specified or the current schema is not set.

     For all other SQL-Statements, the database manager searches
     the SQL path and selects the first schema in the path such
     that the schema contains a procedure with the same
     name and number of parameters.
     The SQL path can be set by using the SQL statement SET PATH.
     Within the SQL-Statement SET PATH up to 268 libraries (schemas)
     can be listed.
     The SQL path is searched from the first specified library (schema)
     to the last one.

     Setting the current schema does not affect the SQL path.
     If the library (schema) specified with SET CURRENT SCHEMA must be
     searched within the SQL path, it must be specified there.

Yet an other tip:
Because a procedure can be overloaded (you can have several procedures
with the same name in the same schema with a different number of
parameters), you should either use the specific name of the procedure or
list the parameter description in the DROP PROCEDURE statement.

1. DROP SPECIFIC PROCEDURE SpecificName
2. DROP PROCEDURE MyProcedure(Dec(5, 2), Char(10) ...);

Birgitta

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Banerjee, Amitava
Gesendet: Dienstag, 13. Dezember 2005 19:21
An: rpg400-l@xxxxxxxxxxxx
Betreff: SQL Stored Procedure


Hi All,
I am having some problems while working/creating SQL procedure.

Sample:

DROP PROCEDURE DB2BETT001;
CREATE PROCEDURE DB2BETT001 (
IN @EXTOYT CHAR (32)
 )LANGUAGE SQL MODIFIES SQL DATA BEGIN

DELETE FROM LIBRARY01.FILE1
WHERE  EXTOYT = @EXTOYT;
END;


Questions:
1. Can the dot (.) in "delete from library01.file1" be replaced by slash
('/')? Like library01/file1? What is the difference?

2. What we are seeing is if we hardcode the library in CREATE PROCEDURE
statement (CREATE PROCEDURE PROCLIB.DB@BETT001), it is compiling fine with
the dot, otherwise we are giving slash. Is there any link between these two
things? Or are we doing all wrong?

3. If we don't want to hardcode the library in CREATE PROCEDURE statement,
where does the object goes? I thought (somebody told me) that it goes to the
top most library in the library list. But that is not happening. The object
is going to the user's, who ever is signed in, default library. How do we
control that?

4. We are using the following command to create the objects
RUNSQLSTM SRCFILE(PROCLIB/QSQLSRC) SRCMBR(DB2BETT001) COMMIT(*NONE)
NAMING(*SQL) DYNUSRPRF(*OWNER)

Please let me know if I am missing something, doing all wrong.

Thank you
Amitava

--------------------------------------------------------

This message (including any attachments) is only for the use of the
person(s) for whom it is intended. It may contain Mattel confidential,
proprietary and/or trade secret information. If you are not the intended
recipient, you should not copy, distribute or use this information for any
purpose, and you should delete this message and inform the sender
immediately.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.