Wednesday, June 16, 2021

Getting results from dynamically built SQL statements

get results from dynamic sql

I have written about executing dynamic generated SQL in a RPG program before, all of the examples were to do something and not to return results from those statements.

This post will show how you can build SQL Select statements in a variable, execute them, and get the results returned.

I need to give credit to Hassan Farooqi for providing me with the information to show a simple way to do this. The code is based upon examples he shared with me. His were far more complicated than what I am showing here, but I just want to show simple of examples of what you can do. You can make yours as complicated as you like.

All of the examples have three steps:

  1. Create a SQL statement in a variable
  2. Use the PREPARE SQL statement to take the contents of the variable and create the SQL executable version of the same
  3. Execute the prepared statement using the EXECUTE SQL statement

I am not going to go into too much detail about the PREPARE and EXECUTE SQL statements as I did in an earlier post you can read here.

In my first example I want to get a copy of the number of rows/records in TESTFILE.

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

03  dcl-s String char(100) ;
04  dcl-s Records int(10) ;


12  String = 'VALUES (SELECT COUNT(*) FROM TESTFILE) INTO ?' ;

13  exec sql PREPARE S0 FROM :String ;
14  exec sql EXECUTE S0 USING :Records ;

15  dsply ('Records = ' + %char(Records)) ;

Line 1: For the past five+ years all of my RPG code is totally free format.

Line 2: My favorite control options.

Line 3: String is the variable that will contain the SQL statements I will be building. I have defined it here.

Line 4: The result from this statement will be returned into Records.

I know there are lines missing from this example. They will be added in later examples.

Line 12: I am using the VALUES statement here to place the results into a variable. At present the variable is represented by a question mark ( ? ). If I give the variable here like this:

String = 'VALUES (SELECT COUNT(*) FROM TESTFILE) INTO :Records'

When run the above statement returns the error SQL code of -518.

Line 13: The SQL statement is prepared by the PREPARE.

Line 14: The SQL statement EXECUTE executes the prepared statement. This is where I give which variable the results are returned into, it "substitutes" (replaces) the question mark in the original SQL statement.

Line 15: The following value is displayed:

DSPLY  Records = 4

The next example is closet to the one that Hassan shared. He needed to find the maximum value in different columns in different files/tables:

05  dcl-s MaxValue packed(6) ;


16  String = 'VALUES (SELECT MAX(FLD001) FROM TESTFILE) INTO ?' ;

17  exec sql PREPARE S0 FROM :String ;
18  exec sql EXECUTE S0 USING :MaxValue ;

19  dsply ('Maximum value in FLD001 = ' + %char(MaxValue)) ;

Line 5: The variable to contain the value from the result of the SQL statement.

Line 16: I use the MAX for SQL to return the largest value from the column FLD001 in TESTFILE. When I need to do the same for another column and file I just need to replace the column name and table name in String.

Line 18: The result is returned into MaxValue variable.

Line 19: The retuned result is shown by line 19:

DSPLY  Maximum value in FLD001 = 999999

The last example is the most complicated. I want to build a Select statement to return the values of all the columns in one row from TESTFILE.

06  dcl-ds Data qualified ;
07    F1 packed(6) ;
08    F2 char(10) ;
09    F3 date ;
10  end-ds ;

11  exec sql SET OPTION DATFMT = *ISO ;


20  String = 'VALUES (SELECT * FROM TESTFILE  +
                       WHERE FLD001 = 2 +
                       FETCH FIRST ROW ONLY) INTO ?' ;
21  clear Data ;

22  exec sql PREPARE S0 FROM :String ;
23  exec sql EXECUTE S0 USING :Data ;

24  *inlr = *on ;

Lines 6 – 10: This data structure is defined so that its subfields are identical to the fields in TESTFILE.

Line 11: I need to set the date format that the date is returned in the results so that it will match this program's date format. I do this using the SET OPTION SQL statement.

Line 20: I want to return all of the columns from TESTFILE, and for a row where FLD001 is 2. I have given the FETCH FIRST ROW ONLY in case there is more than one row where FLD001 = 2.

Line 21: If I did not have this line to clear the data structure I got a "data decimal" error when I ran the program.

Line 23: I am returning the results into the data structure.

Line 24: I have put a debug breakpoint on this line so I can see the value in the Data data structure:

> EVAL data
DATA.F1 = 000002.
DATA.F2 = 'SECOND    '
DATA.F3 = '2021-04-01'

The row's data was returned successfully.

 

Thank you to Hassan for his generosity in sharing his example.

 

You can learn more about the VALUES SQL statement from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

9 comments:

  1. Alvaro CarreƱo CaicedoJune 16, 2021 at 9:43 AM

    Love this

    ReplyDelete
  2. Thanks, so grateful for your knowledge sharing!

    ReplyDelete
  3. If you initialize the data structure in your declaration, do you still have to clear it/get the data decimal error?

    ReplyDelete
    Replies
    1. It depends...

      If you are only going to use the DS once then you do not have to.

      If you are going to use it more than once there is a danger that data will remain in the DS from the prior time you used it.

      If find RESET is better than a CLEAR when you want to re-initialize a DS.

      Delete
  4. Thanks a ton for this. All tutorials suggested to use cursor to fetch a value dynamically. You nailed it bro.

    ReplyDelete
  5. Hi Simon, your post are wonderful and your language refreshing. I cannot be thankful enough.

    I am trying to use a code learnt from here...

    dcl-proc bookWordCount;
    dcl-pi bookWordCount int(3);
    @VERC char(8);
    @BSQ# packed(3);
    @BUKN char(17);
    end-pi;
    dcl-s String char(100);
    dcl-s Records packed(3);
    String = 'VALUES (SELECT SUM(BWRDC) FROM LKJVBP WHERE BVERC +
    = @VERC and BBSQ# = @BSQ# and BBUKN = @BUKN) INTO ?';
    EXEC SQL PREPARE S0 FROM :String;
    EXEC SQL EXECUTE S0 USING :Records;
    return Records;
    end-proc bookWordCount;

    Though the query fetches the proper result when run outside the program, within the same 'Records' always seem to return a value of '0'. I am not sure where I am going wrong. Any word from you will be greatly appreciated.

    Regards

    ReplyDelete
    Replies
    1. Looks like the values assigned in the where clause has to be host variable with the colon(':').
      It works fine when where clause is written as:

      WHERE BVERC = :@VERC and BBSQ# = :@BSQ# and BBUKN = :@BUKN

      Thanks for posting my question and for your time.

      Regards

      Delete

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.