Topic
  • 3 replies
  • Latest Post - ‏2012-09-12T05:38:58Z by B.Hauser
ZachJ
ZachJ
15 Posts

Pinned topic SQL Trigger and *LIBL

‏2012-09-10T18:54:46Z |
When using an SQL Trigger, is there something I can do to force the trigger action to search the library list for unqualified tables at trigger run time? In other words to behave like *SYS naming vs *SQL naming.

I have found that I can issue an OVRDBF command from the QCMDEXC procedure to achieve this behavior. I'm not sure this is a good idea but it does behave how I would like it to. Any info would be appreciated.
Updated on 2012-09-12T05:38:58Z at 2012-09-12T05:38:58Z by B.Hauser
  • B.Hauser
    B.Hauser
    264 Posts

    Re: SQL Trigger and *LIBL

    ‏2012-09-11T04:36:24Z  
    You can't, unqualified database objects in static SQL statements in trigger programs are resolved at compile time and stored in the (trigger) program object.

    The library list set at runtime is ignored when executing static SQL statements.

    The only way to circumvent this behaviour is to use dynamic SQL. Depending on the naming convention to be used for compiling the (trigger) program either the default/current schema (SQL naming) or library list (System Naming) is searched for unqualified data access.

    For additional information please check the following articles:
    SQL versus System Naming Part 1 and Part 2
    http://www.ibm.com/developerworks/ibmi/library/i-system_sql2/index.html
    http://www.ibm.com/developerworks/ibmi/library/i-sqlnaming/index.html

    Birgitta
  • ZachJ
    ZachJ
    15 Posts

    Re: SQL Trigger and *LIBL

    ‏2012-09-11T13:55:32Z  
    • B.Hauser
    • ‏2012-09-11T04:36:24Z
    You can't, unqualified database objects in static SQL statements in trigger programs are resolved at compile time and stored in the (trigger) program object.

    The library list set at runtime is ignored when executing static SQL statements.

    The only way to circumvent this behaviour is to use dynamic SQL. Depending on the naming convention to be used for compiling the (trigger) program either the default/current schema (SQL naming) or library list (System Naming) is searched for unqualified data access.

    For additional information please check the following articles:
    SQL versus System Naming Part 1 and Part 2
    http://www.ibm.com/developerworks/ibmi/library/i-system_sql2/index.html
    http://www.ibm.com/developerworks/ibmi/library/i-sqlnaming/index.html

    Birgitta
    Than you Brigitta. Initially I was using dynamic SQL. I thought, after reading chapter 10.11 in the trigger redbook, that dynamic sql would search the library for unqualified tabels. The trigger was created with RUNSQLSTM NAMING(*SYS) and it still used the default schema. I knew the CREATE TRIGGER statement created a C object and switched to SQL naming so I assumed I was misinterpreting what I had read and moved on to another solution. So you are saying that I should be able to use dynamic SQL? If that is the case that would be fantastic.

    What about the OVRDBF in the trigger body with static SQL? That is working as desired. Do you have any thoughts on that?

    Zach
  • B.Hauser
    B.Hauser
    264 Posts

    Re: SQL Trigger and *LIBL

    ‏2012-09-12T05:38:58Z  
    • ZachJ
    • ‏2012-09-11T13:55:32Z
    Than you Brigitta. Initially I was using dynamic SQL. I thought, after reading chapter 10.11 in the trigger redbook, that dynamic sql would search the library for unqualified tabels. The trigger was created with RUNSQLSTM NAMING(*SYS) and it still used the default schema. I knew the CREATE TRIGGER statement created a C object and switched to SQL naming so I assumed I was misinterpreting what I had read and moved on to another solution. So you are saying that I should be able to use dynamic SQL? If that is the case that would be fantastic.

    What about the OVRDBF in the trigger body with static SQL? That is working as desired. Do you have any thoughts on that?

    Zach
    Please check the article SQL versus System-Naming - Part 2:
    For static and dynamic SQL statements in SQL procedures and triggers.

    Do also a reverse engineering and check the SET OPTION statement whether a default schema is set and whether this default schema must be used for dynamic SQL statement.

    If a default or current schema is set, this schema will be searched even though you are working with System Naming.

    Birgitta