• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Specify SQL Table-creation Library at Runtime

    April 25, 2012 Hey, Ted

    I have an RPG programs that creates work tables in QTEMP. I’d like to be able to specify the library at runtime. I tried using a host variable for the library in a CREATE TABLE command, but that didn’t fly. I am using the system naming convention. Can you help?

    –Ben

    I wish CREATE TABLE would allow a host variable for the explicit qualifier, but it doesn’t. Here are a few ways that work.

    1. Use dynamic SQL. Embed the library name in the SQL command.

    D SqlCommand      s            256a   varying
    D WorkLib         s             10a
     /free
         *inlr = *on;
         SqlCommand = ('create table ' + %trim(WorkLib) +
                       '/SomeTable +
                           (OneFish     char(3), +
                            TwoFish     dec (5,0), +
                            RedFish     char(1), +
                            BlueFish    date)');
         exec sql
            execute immediate :SqlCommand;
    

    2. Use the CURRENT SCHEMA special register with dynamic SQL. Note that the CREATE TABLE command does not include a qualifier.

    D SqlCommand      s            256a   varying
    D WorkLib         s             10a
     /free
         *inlr = *on;
         exec sql
            set Current Schema = :WorkLib;
         SqlCommand = ('create table SomeTable +
                           (OneFish     char(3), +
                            TwoFish     dec (5,0), +
                            RedFish     char(1), +
                            BlueFish    date)');
         exec sql
            execute immediate :SqlCommand;
    

    3. Change the current library. Be aware that the system will not allow you to set the current library to QTEMP. You might want to reset it before the job ends.

    A portion of the CL caller:

    dcl    &CurLib    *char      10
               
    /* change the current library */
    rtvjoba   curlib(&CurLib)
    chgcurlib SomeLib
    
    /* do the work */
    call rpgpgm
    
    /* reset the current library */
    if (&CurLib *eq *NONE) +
      then(  chgcurlib   *CRTDFT)
      else ( chgcurlib   &CurLib)
    

    A portion of the RPG “callee”:

    exec sql
       create table SomeTable
          (OneFish     char(3),
           TwoFish     dec (5,0),
           RedFish     char(1),
           BlueFish    date);
    



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    SEQUEL Software:  FREE webinar: Application Development Control Starts with the Right Tools. April 26
    ASNA:  FREE one-day ASNA Wings® workshops: Dallas, May 10; Malvern, May 24
    COMMON:  Join us at the 2012 Conference & Expo, May 6 - 9 in Anaheim, CA

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    Kronos Takes iSeries Central on the Road, Gets SMART IBM i 7.1 Tech Refresh Sports Live Partition Mobility

    Leave a Reply Cancel reply

Volume 12, Number 11 -- April 25, 2012
THIS ISSUE SPONSORED BY:

Bytware
SEQUEL Software
WorksRight Software

Table of Contents

  • NOMAX? No Way!
  • Specify SQL Table-creation Library at Runtime
  • Getting PC5250 F1-F24 Keys to Work Correctly on HP Laptops

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle