Wednesday, September 12, 2018

Generic program to use SQL count for any file

program to get count of records from any file using sql

The germ for this post came from a question I found in an IBM i Facebook group. How could this person write a program where he would pass the name of any file to SQL and get a count of records in that file. I gave my answer to that question, but the more I pondered I came up with what I consider to be a better solution. Which is what I am going to describe in this post.

I have written about creating and executing SQL statements contained in program variables before, and this just builds upon that.

Fortunately the SQL syntax to count the number of records is the same no matter what file or table I use:

SELECT COUNT(*) FROM some_table
 WHERE some_column(s) = some_value(s)

In my opinion the WHERE selection criteria is needed as most of the time I want to know how many types of a certain type of record is in a table/file, rather than needing an entire count of the number of records in the file.

It would be easy to parameterize the table/file, and schema/library, but what about complex selection criteria?

If I needed to compare to a list of values, or need to check multiple columns/fields. I came to the conclusion that it would be better to pass an entire SQL statement to something that would execute to it, and return the count.

It sounds like a good case for using a procedure to execute the SQL statement and return the count. And this procedure should be outside of my example program so it can be used by other programs that may need the same functionality. As this is only an example I am not going to put this procedure in a service program, I am going to create it in its own module and then bind that to my example program.

Let me start with the procedure's definition. I am going to put it in a "copybook" member so that it can be used by whatever needs to call it.

01  **free
02  /if defined(CountUsingSql)

03  dcl-pr CountUsingSql packed(10) ;
04    SearchString char(1000) value ;
05  end-pr ;

06  /endif

Line 1: I need the **FREE so that I can start the rest of my code in the first position.

Lines 2 and 6: I went into details about these compiler directives in more detail before. Basically when I use the /DEFINE CountUsingSql in my procedure it will copy the procedure definition into it.

Line 3: This procedure returns a packed value that is 10 long, and with no decimal places.

Line 4: I have made the parameter to this procedure 1,000 characters long as most of the SQL statements I create are less than that.

Onto the procedure itself:

01  **free
02  ctl-opt nomain option(*nodebugio:*srcstmt) ;

03  /define CountUsingSql
04  /include mylib/devsrc,copybook
05  /undefine CountUsingSql

06  dcl-proc CountUsingSql export ;
07    dcl-pi *n packed(10) ;
08      String char(1000) value ;
09    end-pi ;

10    dcl-s Count packed(10) ;

11    exec sql SET OPTION COMMIT = *NONE, CLOSQLCSR = *ENDMOD ;

12    Count = 0 ;

13    exec sql PREPARE S0 FROM :String ;
14    exec sql DECLARE C0 CURSOR FOR S0 ;

15    exec sql OPEN C0 ;
16    if (SQLCOD < 0) ;
17      Count = SQLCOD ;
18      return Count ;
19    endif ;

20    exec sql FETCH FROM C0 INTO :Count ;
21    if (SQLCOD < 0) ;
22      Count = SQLCOD ;
23    endif ;

24    exec sql CLOSE C0 ;

25    return Count ;
26  end-proc ;

Line 2: The NOMAIN informs the compiler that there is no main procedure, therefore, this module cannot be directly called.

Line 3 - 5: By using the /DEFINE compiler directive I am saying I want to include the section from the copybook that is defined as CountUsingSql. You do not have to use the /UNDEFINE, but I always do. I have also used /INCLUDE rather than /COPY, for no other reason than I can.

Line 6: This is the start of the procedure, which ends on line 26. I need to state that my procedure is for EXPORT so that it can be called from other objects.

Line 7 – 9: The procedure interface matches the procedure definition.

Line 10: This variable will contain the count.

Line 11: I always like to place my SQL options in my source, so that they cannot be missed when the module is compiled.

Line 13: The PREPARE statement converts the contents of the String variable into a value that SQL can use.

Line 14: A cursor, C0, is defined using the value taken from String.

Line 15: The cursor is opened.

Lines 16 – 19: I lied about what the variable Count will be used for. If there is an error when the opening of the cursor is performed the SQL code, SQLCOD, will be less than zero. If the SQL code is less than zero then I am moving the value of the SQL code into Count and returning that number to the calling program. As a count of records can never be less than zero I know in the calling program when a negative value was returned then there is something wrong with the SQL statement passed.

Line 20: This is where I fetch from the cursor the count of rows/records.

Lines 21 – 23: If there was an error in the fetch then the SQL code is moved to count. I do not RETURN here as I want to close the cursor.

Line 24: The cursor is closed.

Line 25: The value of Count is returned to the calling program.

To compile a SQL RPG source member into a module I need to change the OBJTYPE parameter in the CRTSQLRPGI command:

                    Create SQL ILE RPG Object (CRTSQLRPGI)

Type choices, press Enter.

Object . . . . . . . . . . . . . OBJ          > MOD001    
  Library  . . . . . . . . . . .              >   MYLIB     
Source file  . . . . . . . . . . SRCFILE      > DEVSRC    
  Library  . . . . . . . . . . .              >   MYLIB     
Source member  . . . . . . . . . SRCMBR       > *OBJ      
Source stream file . . . . . . . SRCSTMF      >                 
                  
Commitment control . . . . . . . COMMIT       > *NONE
Relational database  . . . . . . RDB          > *LOCAL          
Compile type . . . . . . . . . . OBJTYPE      > *MODULE  <==

I need to create a binding directory, BNDDIR0, to make it easier to bind the module to the program I will be creating.

CRTBNDDIR BNDDIR(MYLIB/BNDDIR0)

And then I add my module, MOD001, to the binding directory, BNDDIR0:

ADDBNDDIRE BNDDIR(BNDDIR0)
             OBJ((MOD001 *MODULE *IMMED))

Now I can write my RPG program to call this procedure. Let me start with the definitions.

01  **free
02  ctl-opt option(*nodebugio:*srcstmt)
03           dftactgrp(*no)
04           bnddir('BNDDIR0') ;

05  /define CountUsingSql
06  /include mylib/devsrc,copybook
07  /undefine CountUsingSql

08  dcl-s SearchString char(1000) ;
09  dcl-s Count packed(10) ;

Lines 2 – 4: The control specifications for this program need to be that this program will not run in the default activation group, and I can put the name of the binding directory here too. I do this so it cannot be forgotten at compile time.

Lines 5 – 7: The code needed to copy the prototype definition of the procedure into the source of this program.

Lines 8 and 9: Definition of the parameter passed to the procedure, line 8, and the variable to contain the returned count, line 9.

Let me show the code for my first use of this procedure.

10  SearchString = 'SELECT COUNT(*) FROM FRUIT +
11                   WHERE NAME = ''APPLE'' ' ;
12  Count = CountUsingSql(SearchString) ;
13  dsply ('Count for apple = ' + %char(Count)) ;

Lines 10 and 11: I want a count of the number of rows/records where the column NAME is equal to 'APPLE' in the table/file FRUIT.

Line 12: This is the call to the procedure and the value returned is placed in the variable Count.

Line 13: I display the returned count. Which shows me:

DSPLY  Count for apple = 3

Second statement is for another file, VEGETABLE, and I want a count of the number of rows/records where the column/field NAME is 'PEA'.

14  SearchString = 'SELECT COUNT(*) FROM MYLIB.VEGETABLE +
15                   WHERE NAME = ''PEA'' ' ;
16  Count = CountUsingSql(SearchString) ;
17  dsply ('Count for pea = ' + %char(Count)) ;

Line 14: I have qualified the table/file with the schema/library.

My result is:

DSPLY  Count for pea = 2

This time I have deliberately placed an error in my SQL statement to see what is returned from the procedure.

18  SearchString = 'SELECT COUNT(*) FROM *LIBL/VEGETABLE +
19                   WHERE NAME = ''PEA'' ' ;
20  Count = CountUsingSql(SearchString) ;
21  dsply ('Count for *LIBL = ' + %char(Count)) ;

Line 18: SQL does not support *LIBL, therefore, when this statement is executed it will fail and generate a negative SQL code.

The SQL code for the error has been placed in the returned value, and when line 21 is executed the following is displayed.

DSPLY  Count for *LIBL = -514

The description for SQL code 514 is:

SQL0514

Message Text:  Prepared statement &2 not found.

Cause Text:  An attempt was made to open cursor &1 which referred to prepared statement &2.

I can debug the procedure to find more information in the SQLCA data structure and the job log. SQLCA:

EVAL sqlca


SQLCODE OF SQLCA = -514
SQLCOD OF SQLCA = -000000514.


SQLERM OF SQLCA =
          ....5...10...15...2
     1   ' ¬C0 ¬S0

Job log:

Token * was not valid. Valid tokens: ( NEW FINAL TABLE UNNEST 
  LATERAL XMLTABLE JSON_TABLE .
Prepared statement S0 not found.
SQL cursors closed.
DSPLY  Count for *LIBL = -514

This all means that the cursor C0 could not be opened as the value in S0 contains an asterisk ( * ) where one is not expected. Therefore, *LIBL cannot be used in this SQL statement.

Last SQL statement is to test that zero is returned if there are no rows/records found.

22  SearchString = 'SELECT COUNT(*) FROM FRUIT +
23                   WHERE NAME = ''CROCODILE'' ' ;
24  Count = CountUsingSql(SearchString) ;
25  dsply ('Count for crocodile = ' + %char(Count))

As a crocodile is not a fruit it is not in the FRUIT table/file, and the procedure returns a row/record count of zero.

DSPLY  Count for crocodile = 0

I am sure you can create more complex SQL statements to count the rows/records in your tables/files, no matter what it is you can use the same method described above to retrieve the count.

2 comments:

  1. "03 dftactgrp(*no) The control specifications for this program need to be that this program will not run in the default activation group"

    dftactgrp(*no) on the control spec does not mean the program will not or cannot run in the default activation group. It can, and it may. I know what you meant but the way it's worded may make the many people who struggle with activation group stuff believe that putting dftactgtrp(*no) on the control spec will cause the program not to run in the default activation group (DAG). Dftactgrp(*no) on the H spec or ctl-opt is one of the most misunderstood, misleading and confusing keywords out there. When you see Dftactgrp in the control spec, pronounce it as "OpmPgm". Is this an OPM program *yes or *no? And even that is not quite right because you can create a program of type ILE with dftactgrp(*yes), as long as it doesn't use any ILE concepts.

    The actual activation group it will run in depends on what you specify in actgrp. If that says caller, then it depends on the activation group of the calling program. Which could very well be an OPM program. Anything called from a command line runs in DAG.

    BTW, if you use free form ctl-opt and you have bnddir on there, you do not need to specify dftactgrp(*no), it's assumed. But I am with you and always spell it out so it's more apparent.

    Thank you for your awesome blog!

    ReplyDelete
  2. Hej Simon.
    If you have a sql statement and you want to know the number of records it would return, i would normaly place a select around the original select statement. for instace if you have a statement like select fielda, fieldb from file where fielda = 'ABC' i would do a statement like this :
    select count(*) from (select fielda, fieldb from file where fielda = 'ABC') as x; then i can reuse the exact same select statement witout changing select fields or anything :-)
    it makes it possible to make a routine where you can send a "normal select" and get the number back, just by adding "select count(*) from (" before the original sql and ") as x)" to the end of the original sql statement.
    Hopes my english makes sence ;-)
    Regards Jan

    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.