Funny because this same subject just came up on the NEWSLINK400 forum. Yes you can prepare a dynamic select statement for a cursor where you don't basically have to know anything at compile time including the columns, the files, etc. However, if you are planning on doing multiple opens and closes of such cursors and then changing the columns or something else like that, then you must re-prepare the select statement and reopen the cursor.

An SQL Prepare statement is potentially very slow because the system must build an access plan based on available access paths, etc. Therefore, SQL does allow you to place something called a parameter marker (which is a question mark) in any place where static sql allows you to place a host variable. Excluded from this are things like column lists and file names. So SQL wouldn't permit me to prepare a statement that was formed as follows.

EvalSqlStm = 'Select ? from ?'

Too bad! However, SQL would allow me to prepare the following statement.

EvalSqlStm = 'Select * From Customer Where CuName > ?

You substitute for parameter markers with the Using clause of the Open cursor SQL statement. I will show an example below. The following is a program I wrote and checked out with the debugger to make sure it was executing correctly. Notice this example does show how to use parameter markers. Notice the open cursor statement must specify the name of a host variable to substitute for the question marks. Hope this helps.

D GenCust         DS
D  CuName                       25
D  CuStat                        1

D CustAp          DS
D  CuOv30                        9P 2
D  CuOv60                        9P 2
D  CuOv90                        9P 2

D OrdDs         E DS                  ExtName( OrdHdr )

D SelectStm       S            200

D PrmChoice       S              1
D  ChoiceGenCust  C                   'G'
D  ChoiceCustAp   C                   'A'
D  ChoiceOrd      C                   'O'

D GtName          S             25    Inz( 'H' )
D GtCuOv30        S              9P 2 Inz( 100 )
D EqOhStat        S              1    Inz( 'C' )

C/Exec SQL
C+         Declare DynamCsr Cursor for
C+           DynSqlStm
C/End-Exec

C     *Entry        Plist
C                   Parm                    PrmChoice

C                   Select

C                   When      PrmChoice = ChoiceGenCust

C                   Eval      SelectStm = 'Select CuName, CuStat ' +
C                                         'From Customer '         +
C                                         'Where CuName > ?'
C/Exec SQL
C+         Prepare DynSqlStm
C+           From :SelectStm
C/End-Exec
C/Exec SQL
C+          Open DynamCsr
C+            Using :GtName
C/End-Exec

C/Exec SQL
C+          Fetch Next
C+            From DynamCsr
C+            Into :GenCust
C/End-Exec

C                   When      PrmChoice = ChoiceCustAp

C                   Eval      SelectStm = 'Select CuOv30,'        +
C                                         '       CuOv60,'        +
C                                         '       CuOv90 '        +
C                                         'From Customer '        +
C                                         'Where CuOv30 > ?'
C/Exec SQL
C+         Prepare DynSqlStm
C+           From :SelectStm
C/End-Exec
C/Exec SQL
C+          Open DynamCsr
C+            Using :GtCuOv30
C/End-Exec

C/Exec SQL
C+          Fetch Next
C+            From DynamCsr
C+            Into :CustAp
C/End-Exec

C                   When      PrmChoice = ChoiceOrd

C                   Eval      SelectStm = 'Select * '             +
C                                         'From OrdHdr '          +
C                                         'Where OhStat = ?'
C/Exec SQL
C+         Prepare DynSqlStm
C+           From :SelectStm
C/End-Exec
C/Exec SQL
C+          Open DynamCsr
C+            Using :EqOhStat
C/End-Exec

C/Exec SQL
C+          Fetch Next
C+            From DynamCsr
C+            Into :OrdDs
C/End-Exec

C                   EndSl

C/Exec SQL
C+          Close DynamCsr
C/End-Exec

C                   Eval      *INLR = *On

Mike Cravitz NEWS/400 Technical Editor

---

So far so good, but i think i did not express my problem clear enough - second try: A user enters a sql-statement like: select kskos, kv#01, kv#02 from ks, su where kskos = sukos (or any other valid select statement) My little program does not know which tables were concered and how many (and of course which type) of columns will come out of this. So my question is - is this possible and if it is how is it done.

---

I was hoping you weren't expecting to do something this dynamic. Oh well. Let's give this a whack. The SQL programming guide suggests that what you want to do is possible with the SQL DESCRIBE statement. I'm going to cut and paste from that manual and then make some comments.

===Start Manual excerpt===
8.3.2 Varying-List Select-Statements

In dynamic SQL, varying-list SELECT statements are ones for which the
number and format of result columns to be returned are not
predictable; that is, you do not know how many variables you need, or
what the data types are.  Therefore, you cannot define host
variables in advance to accommodate the result columns returned.

Note:  In REXX, steps 5b, 6, and 7 are not applicable.

If your application accepts varying-list SELECT statements, your
program has to:

1.  Place the input SQL statement into a host variable.

2.  Issue a PREPARE statement to validate the dynamic SQL statement
and put it into a form that can be run. If DLYPRP (*YES) is specified
    on the CRTSQLxxx command, the preparation is delayed until the
first time the statement is used in an EXECUTE or DESCRIBE statement,
    unless the USING clause is specified on the PREPARE statement.

3.  Declare a cursor for the statement name.

4.  Open the cursor (declared in step 3) that includes the name of the
dynamic SELECT statement.

5.  Issue a DESCRIBE statement to request information from SQL about
the type and size of each column of the result table.

    Notes:

    a.  You can also code the PREPARE statement with an INTO clause to
perform the functions of PREPARE and DESCRIBE with a single
        statement.

    b.  If the SQLDA is not large enough to contain column
descriptions for each retrieved column, the program must determine how
much
        space is needed, get storage for that amount of space, build a
new SQLDA, and reissue the DESCRIBE statement.


6.  Allocate the amount of storage needed to contain a row of
retrieved data.

7.  Put storage addresses into the SQLDA (SQL descriptor area) to tell
SQL where to put each item of retrieved data.

8.  FETCH a row.

9.  When end of data occurs, close the cursor.

10. Handle any SQL return codes that might result.

====End of Manual Excerpt== First of all, doing the things described here is non-trivial to say the least. You have to make sure you have created your SQLDA properly. You have to programatically decipher the outcome of the SQL Describe statement well enough to know the size of the buffer necessary to hold a row (or record). Or you could simply allocate a very large buffer of say 32,767 (or even smaller) if you know the maximum record length of all possible files on your system. But when you get all done, you need to parse the SQLDA to pick up the type and size of each field. If that isn't bad enough, these 10 steps probably left off the hardest part of all.

So I've performed the 10 steps above and I have a record in a dynamically allocated chunk of memory. I have in my SQLDA a description of each field in this chunk of memory. If a particular field is character, I can probably deal with that because of RPG's powerful %Subst and other string-handling BIFs. But what if the field is packed? There are 9,920 possible packed numeric descriptions. I suppose you can have a based data structure where you can overlay these 9,920 possibilities on top of each other. But it seems to me you would also need an RPG Select statement with 9,920 possible When clauses. Since that's not practical, we have to take another approach. Here is what I would do.

I would write a subprocedure which accepts a character string of any length from 1 to 16 bytes and returns the numeric value. The simplest thing to do here is have the subroutine accept a varying length field by value. That way you can pass it a fixed length field and not have to pass the length. The subroutine can use the %Len BIF to pick up the length. This subroutine would ignore the number of decimals and assume that the packed field has 0 decimals. It would then examine the string byte by byte and would build up the actual value from the packed value contained in the string. The procedure then returns this value. The SQLDA gives me the number of decimals. From that point forward you can use RPG's %Dec BIF. For example if the procedure returns a value (ignoring decimals) which you put into a 30,0 field called DecVal. Also suppose you store the number of digits into a field called Digits. And if you have placed the number of decimals into another 2,0 (or 3,0 if you're fussy about a silly performance issue) called NumDecs. Then from this point forward, you can refer to your packed field with %Dec( DecVal: NumDigits: NumDecs ).

Obviously this is a lot of work and my guess is you're going to conclude that it is not worth it. But anyway, it was worth it to learn something.

Mike Cravitz NEWS/400 Technical Editor

---

I made a mistake (surprise!). >Then from this point forward, you can refer to your packed >field with %Dec( DecVal: NumDigits: NumDecs ). You would actually have to refer to your packed field as follows... %Dec( DecVal / ( 10 ** NumDecs ): NumDigits: NumDecs ) Mike Cravitz NEWS/400 Technical Editor

---