Thursday, September 1, 2022

Making the source code line longer for RUNSQLSTM

I always place the code I use to create DDL tables, indexes, views, etc. in a source member. It has always frustrated me that the code could not go beyond the 80th column, without the compiling failing. As part of IBM i 7.5 and 7.4 TR6 a new special value is allowed in the Run SQL Statement command, RUNSQLSTM.

In the RUNSQLSTM command there is a parameter for the source margins, MARGINS, that allows me to give the position of the right margin of the code within the member. The default is 80, but now there is a keyword I can use in its place: *SRCFILE. This "tells" the compiler to use the entire length of the source member, rather than the first 80 characters only.

If I made a SQL statement that is greater than 80 characters in a source member, like this:


...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
CREATE OR REPLACE TABLE RPGPGM1.TESTTABLE (C1 VARCHAR(30),C2 VARCHAR(30)) ON REPLACE DELETE ROWS ;  

And I try to compile the source member with the default values of RUNSQLSTM I get an error:

RUNSQLSTM or RUNSQL command failed.

If I look in the compile listing I see that the compiler cropped the statement at the 80th position:


Record  *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
      1 CREATE OR REPLACE TABLE RPGPGM1.TESTTABLE (C1 VARCHAR(30),C2 VARCHAR(30)) ON REP

If I change the value of the margins keyword to *SRCFILE:

                        Run SQL Statements (RUNSQLSTM)

Type choices, press Enter.

Source file  . . . . . . . . . . SRCFILE     DEVSRC    
  Library  . . . . . . . . . . .               MYLIB     
Source member  . . . . . . . . . SRCMBR      SQLTABLE  
Source stream file . . . . . . . SRCSTMF     
                   
Commitment control . . . . . . . COMMIT      *NONE
Naming . . . . . . . . . . . . . NAMING      *SYS

                           Additional Parameters

Severity level . . . . . . . . . ERRLVL      20  
Source margins:                  MARGINS
  Right margin . . . . . . . . .             *SRCFILE

The compile is successful. And when I look at the compile listing I can see that compiler handles the entire source member's line:


Record  *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8
      1 CREATE OR REPLACE TABLE RPGPGM1.TESTTABLE (C1 VARCHAR(30),C2 VARCHAR(30)) ON REP
      1 LACE DELETE ROWS ;

You may not consider this to be a big deal, but it will save me a lot of time reformatting statements I have copied from ACS's Run SQL Scripts into a source member.

 

This article was written for IBM i 7.5 and 7.4 TR6.

3 comments:

  1. Interesting trick but, if you write the sql in ACS Run SQL Scripts and you formatted it you obtain this :
    CREATE OR REPLACE TABLE RPGPGM1.TESTTABLE (
    C1 VARCHAR(30),
    C2 VARCHAR(30)
    )
    ON REPLACE DELETE ROWS;
    that is more readeable the 1 line.

    Don't you think ?

    ReplyDelete
    Replies
    1. I agree what you give is more readable, and is how I normally write the CREATE TABLE statement. I needed to have a statement that is more than 80 characters to illustrate this improved feature.

      Delete
  2. Nice. Just put this to good use.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.