ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Free-Format SQL in V5R4

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Free-Format SQL in V5R4

    Free-Format SQL in V5R4


    Note 1 ==> each sql statement must begin with EXEC SQL and end with a
    a semicolon.

    Note 2 ==> The EXEC SQL keywords must be on the same line, yet the remaining
    sql string can be on multiple lines.

    PHP Code:

    /free

      setup
    ()
      
    exec sql open C1;
      
    exec sql fetch C1 /*comment*/ into :somedatastructure;
      
    exec sql close C1//single line comment
      
      
    if sqlcode 0;
         
    exec sql set :somedatastructure.HV1 = :HV1;
         
    exec sql simple comment
           set 
    :HV3 'ab+
               cdef+         
                 ghijklmnopqr....'
    ;
      endif; 


    /
    end-free 



    PHP Code:

    D HV1          S                  10i  0
    D HV2          S                  10i  0
    D SETUP        PR               
    /free

                setup
    ();
                
    exec sql open C1;
                
    exec sql open C2;
     
    c           return

    P  SETUP      B
    D             PI

      
    /free

        exec sql 
    declare C1 cursor for
                 
    select from mylib/mytable
                 where col1 
    = :hv1;

        
    exec sql declare C2 cursor for
                 
    select from mylib/mytable2
                 where col3 
    = :hv2;
      /
    end-free

    P  SETUP     E 



    crtsqlrpgi obj(mylib/myprogram)
    srcfile(mylib/mysrc) commit(*NONE)
    output(*PRINT) RPGPPOPT(*LVL1)
    COMPILEOPT('DFTACTGRP(*NO)
    ACTGRP(*CALLER) DEFINE(USE_DBGTABLE)')

    PHP Code:

    P SETUP        B
    D              PI

                   
    /free

        
    /if DEFINED(USE_DBGTABLE)
     
        
    exec sql declare C1 cursor for
                 
    select from mylib/debugTable
                 where col1 
    = :hv1;

        
    exec sql declare C2 cursor for
                 
    select from mylib/debugTable
                 where col3 
    = :hv2;
       /ELSE

        
    exec sql declare C1 cursor for
                 
    select from mylib/mytable
                 where col1 
    = :hv1;

        
    exec sql declare C2 cursor for
                 
    select from mylib/mytable2
                 where col3 
    = :hv2;


       /ENDIF

     

      /
    end-free

    P  SETUP     E 




    Note 3 ==> Three different types of comments are allowed see above.
    Bracketed comments (/*...*/)are allowed between
    positions 8 and 80 and can span multiple lines.

    single line comments (//) starts with // and ends with the
    end of the line.

    simple comment (little foggy on this)

    Note 4 ==> Declare cursor no longer has to be placed prior to
    referencing the cursor.

    Note 5 ==> It looks like the compiler will allow for an array of
    datastructures only for SQL FETCH and INSERT.
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

  • #2
    Re: Free-Format SQL in V5R4

    Other than writing SQL in /free (which IS a big improvement), has IBM made it less verbose to set up, or permit us to use read and chain verbs with SQL declared files.

    Comment


    • #3
      Re: Free-Format SQL in V5R4

      Marty - I have the books call me later....they charged me $41 to ship....

      I have a whole article on improvements...just no time to write them all
      Ill try to get more up this week.


      Jamie
      Attached Files
      All my answers were extracted from the "Big Dummy's Guide to the As400"
      and I take no responsibility for any of them.

      www.code400.com

      Comment

      Working...
      X