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:
- Create a SQL statement in a variable
- Use the PREPARE SQL statement to take the contents of the variable and create the SQL executable version of the same
- 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.
Love this
ReplyDeleteExcellent example
ReplyDeleteThanks to Hassan and Simon!
ReplyDeleteThanks, so grateful for your knowledge sharing!
ReplyDeleteIf you initialize the data structure in your declaration, do you still have to clear it/get the data decimal error?
ReplyDeleteIt depends...
DeleteIf 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.
Thanks a ton for this. All tutorials suggested to use cursor to fetch a value dynamically. You nailed it bro.
ReplyDeleteHi Simon, your post are wonderful and your language refreshing. I cannot be thankful enough.
ReplyDeleteI 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
Looks like the values assigned in the where clause has to be host variable with the colon(':').
DeleteIt 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