Re: SQLDA

[Ron@xxxxxxxxxx]

<<Does anyone have a good sample of Using the SQLDA in an RPG program where
<<the file name is completely dynamic?  In other words, to determine the
field
<<names and values on-the-fly, after determining the file name.  Or, a good
<<book of examples in RPG?

Nelson,

I have a work in progress that does that, but it's thousands of lines long.
So I just pulled out parts of it here, but hopefully it will help some.

The basic idea here is to fetch a record into a long data structure and
then parse it out (accounting for packed fields, date fields, etc.) The
descriptions of the fields come from the data structure SQLVAR which comes
from the SQL_VAR array. Each element in the SQL_VAR array holds an
occurance of the data structure SQLVAR. In this example there is only one
occurance for each field (or column) in the file (or row). It doesn't have
to be a one to one relationship - it depends on the USING clause of the
DESCRIBE statement.

SQLVAR has fields SQLNAME, SQLLEN, and SQLTYPE (and others). Unfortunately,
the SQLTYPE is not RPG types (P,N, etc). You have to interpret the type
code but theres a good table in the SQL manual that does that. SQLLEN also
needs some tweaking before you can use it. I show in this example how to
unpack a field (which I think I picked up from Barbra from this list).

HTH

Ron Hawkins


D FinalDs         Ds          5000
D  FF1                         512

C/Exec SQL
C+ Include SQLDA
C/End-Exec

 * Maximum of 500 fields in the file
C                   Eval      SQLN = 500

 * Open cursor
C/Exec SQL
C+  Open C7
C/End-Exec
 * Open cursor
C/Exec SQL
C+ Describe TotalRead Into :SqlDa Using System Names
C/End-Exec
 * Get the record
C/Exec SQL
C+ FETCH NEXT FROM C7 for 1 ROWS USING DESCRIPTOR :SQLDA INTO
C+ :Finalds:fieldnull
C/End-Exec
c                   If        SqlCod <> 100

 * Format the final line
c                   Eval      Ix = 1
c                   Eval      Ix2 = 1
 * Must parse out each field to account for packed data fields
c                   do        SQLD          Fd
c                   Eval      SqlVar = Sql_Var(FD)

 * Packed
c                   Select
c                   When      Sqltype = 0485
c     SqlLen        Div       256           PackLen
c                   Div       2             PackLen
c                   mvr                     PackRmd
c                   eval      PackLen = PackLen + PackRmd
c                   Eval      PackedDs = *allx'00'
c                   Evalr     PackedDs = %subst(FinalDs:Ix2:PackLen)
c                   Eval      ZonedData = PackedData