Generic program to use SQL count for any file

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.