Using Select Into with Execute Immediate

The idea for this post came from a question I was asked about using the Execute Immediate SQL statement. The questioner asked why he was unable to execute a Select Into statement using Execute Immediate. His RPG source code compiled, but when called the program would error with a SQL code of -312, or message id SQL0312:

Variable RESULT not defined or not usable for reason code 8. 8 -- The host variable is specified in dynamic SQL. Host variables are not valid in dynamic SQL. Parameter markers must be used instead.

I found a solution for him. I could resist playing with what I found and created a far more complicated scenario that I am sharing with you in this post.

I have a SQL Table, PERSON, that exists in more than one library. How could I create a dynamic SQL statement, which would be in a variable, where I could fetch data:

• From different Person Tables
• Change the selection criteria
• Change which column was returned

The SQL statement would only ever return one row of results.

I created two programs, as the questioner had stated that parameters would be passed from the first program, PROGRAM1, and the SQL statement would be built and executed in a second program, PROGRAM2.

Let me start my describing what PROGRAM1 does. It will make it easier to understand PROGRAM2 if you know what parameters and the values passed to it.

01 **free 02 ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) ; 03 dcl-pr SecondProgram extpgm('PROGRAM2') ; 04 *n varchar(20) const ; // First name 05 *n varchar(20) const ; // Last name 06 *n varchar(50) const ; // Column name 07 *n varchar(21) const ; // File name 08 *n varchar(100) ; // Returned value 09 end-pr ; 10 dcl-proc Main ; 11 dcl-s Returned varchar(100) ; 12 SecondProgram('THOMAS' : 'MCNULTY' : 'PLACEBIRTH' : 'PERSON' : Returned) ; 13 DisplayReturnedValue(Returned) ; 14 SecondProgram('REG' : 'ALLEN' : 'CHAR(PLAYED)' : 'MYLIB.PERSON' : Returned) ; 15 DisplayReturnedValue(Returned) ; 16 SecondProgram('MARK' : 'JONES' : 'CHAR(DTEBIRTH,USA)' : 'MYLIB2/PERSON' : Returned) ; 17 DisplayReturnedValue(Returned) ; 18 end-proc ;

Line 1: Why would I write anything that was not modern RPG? There are no columns in my code!

Line 2: My control options. As I will be using a Main procedure I need the MAIN control option. Which is followed by my favorite option, *SRCSTMT. Finally I need to direct that this program will not execute in the default activation group.

Lines 3 – 9: The parameter definition and parameters passed to the second program, PROGRAM2. Those of you familiar with my work know that I do not give names to the parameters, as there is no point as I will be hard coding strings into them.

Lines 4 – 7: These are the "input parameters" passed to the other program. Notice that they are all defined as variable length character, VARCHAR, and I have defined them as constant values, CONST, so that the called program cannot change the values contained with them.

Line 8: This parameter will contain the value returned from the called program, therefore, it cannot be a constant value.

Line 10: Start of the Main procedure. Using the Main procedure stops the logic for the RPG cycle being inserted into the program when it is compiled/created.

Line 11: This is the definition for the variable that will contain the value returned from the called program.

Lines 12, 14, 16: I am calling the second program three times. On line 3 I gave the called program a better name, SecondProgram, that is more descriptive to the second program's name. The called program's parameters in order are:

1. The person's first name
2. Person's last name
3. The column I want the information retrieved from. As I am returning a character value from the called program I use the Character scalar function, CHAR, to convert non-character data to character
4. The library and file I am to retrieve the result from. I cannot include '*LIBL' for the library name for the file. The value on line 12 will do the same as it uses the Table that is first in the library list. The parameter in lines 14 and 16 show that I can use either the dot ( . ) or slash ( / ) as the separator character
5. The variable that will contain what is returned from the called program

Lines 13, 15, and 17: A subprocedure is called that will format the value in the returned string and display it. The subprocedure is:

19 dcl-proc DisplayReturnedValue ; 20 dcl-pi *n ; 21 TooLong varchar(100) ; 22 end-pi ; 23 dcl-s DisplayValue varchar(40) ; 24 DisplayValue = TooLong ; 25 dsply ('Returned ' + DisplayValue) ; 26 end-proc ;

I am using the Display operation code, DSPLY, to show the result returned from the second program. The returned variable can have up to 100 characters. The DSPLY can only display up to 52 characters. In this subprocedure I take the first 40 characters from the result field and move it into another variable I can then use with the DSPLY.

Lines 20 - 22: The procedure interface for the variable passed to this subprocedure.

Line 23: Definition of the variable I will use with DSPLY.

Line 24: Move the value in the passed variable to the one defined in the line above.

Line 25: Display the value in the variable.

Having explained what is passed to the second program I can now describe what it does:

01 **free 02 ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) ; 03 dcl-proc Main ; 04 dcl-pi *n ; 05 FirstName varchar(20) const ; 06 LastName varchar(20) const ; 07 ColumnName varchar(50) const ; 08 FileName varchar(21) const ; 09 Result varchar(100) ; 10 end-pi ; 11 dcl-s Statement varchar(200) ; 12 exec sql SET OPTION DATFMT = *ISO, TIMFMT = *ISO ; 13 exec sql CREATE OR REPLACE VARIABLE MYLIB.MY_VARIABLE VARCHAR(100) ; 14 Statement = 'SET MY_VARIABLE = (SELECT ' + ColumnName + ' + FROM ' + FileName + ' WHERE FNAME = ''' + FirstName + ''' + AND LNAME = ''' + LastName + ''')' ; 15 exec sql EXECUTE IMMEDIATE :Statement ; 16 exec sql SET :Result = MYLIB.MY_VARIABLE ; 17 on-exit ; 18 exec sql DROP VARIABLE MYLIB.MY_VARIABLE ; 19 end-proc ;

Lines 4 – 10: The procedure interface for this program has to be a procedure interface definition within the Main procedure.

Line 11: This variable will be used to contain the built SQL statement.

Line 12: I always use the Set Option statement to make sure the dates and times are returned in the format I expect.

While the documentation for the Execute Immediate in IBM's documentation states you can use the SET, it does add a footnote that the variable the result is placed in must be a Global Variable.

Line 13: Here I am creating a Global variable. I have defined it as a variable character of up to 100 characters.

Line 14: Creating my SQL statement in the variable Statement.

Line 15: I am executing the created SQL statement. It populates the Global variable with the result of the SQL Select statement.

Line 16: I am updating the RPG variable Result with the value in the Global variable.

Lines 17 and 18: I have added an On Exit group at the end of this subprocedure to ensure that no matter how this subprocedure ends the Global Variable will be dropped/deleted.

After compiling both programs I add a debug breakpoint on line 17 of the second program so I can see what the SQL statement is.

The first time the second program is called the SQL statement is:

EVAL Statement STATEMENT = ....5...10...15...20...25...30...35...40...45...50...55...60 1 'SET MY_VARIABLE = (SELECT PLACEBIRTH FROM PERSON WHERE FNAME' 61 ' = 'THOMAS' AND LNAME = 'MCNULTY') '

Which successfully returns Thomas McNulty's place of birth:

DSPLY Returned SALFORD

Next statement requested the number of soccer games Reg Allen played:

EVAL Statement STATEMENT = ....5...10...15...20...25...30...35...40...45...50...55...60 1 'SET MY_VARIABLE = (SELECT CHAR(PLAYED) FROM MYLIB.PERSON WHE' 61 'RE FNAME = 'REG' AND LNAME = 'ALLEN') '

Which returned the number, as a character value:

DSPLY Returned 75

Lastly what is Mark James's date of birth:

EVAL Statement STATEMENT = ....5...10...15...20...25...30...35...40...45...50...55...60 1 'SET MY_VARIABLE = (SELECT CHAR(DTEBIRTH,USA) FROM MYLIB/PER' 61 'SON WHERE FNAME = 'MARK' AND LNAME = 'JONES') '

This was returned in USA format:

DSPLY Returned 06/15/1933

I admit I have made this a lot more complex than what I was asked about. But you can now see how you can use the Execute Immediate statement to return a single result from a Select statement.

 

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

 

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