RE: Embedded SQL Select Into

If you have date or time fields in an External DS you may need to
specify these on the CRTSQLRPGI command:

DATFMT(*ISO) DATSEP('/') TIMFMT(*HMS) TIMSEP(':')

I have had issues when these values are used:
DATFMT(*JOB) DATSEP(*JOB) TIMFMT(*JOB) TIMSEP(*JOB)

For some reason the system cannot determine what format to use when *JOB
is specified.

I also have to specify this at the top of the SQL RPGLE program.

EXEC SQL SET OPTION Datfmt = *Iso
,Timfmt = *Iso;


If your identity columns are SQL Integers you may need to include the
H-spec "ExtBinInt(*Yes)" for the program to correctly convert the value
to a 10i0 instead of a 9b0 field definition.

Most of the time I find it easier to use COALESCE or IFNULL to give a
default value to a null field than it would be to process an SQL result
with nulls.

If you want to include nulls you will also need to include a null
indicator ds that has a 5i0 entry for each field and has the same
DIM/OCCURS as your external DS.


D WkMblSesP E DS Dim(1000)
D Qualified
D ExtName(MBLSESP)

D nlMblSesP E DS Dim(1000) QUALIFIED INZ
D nlfields 5i 0 DIM(FieldCount)
--or--
D nlMblSesP E DS Dim(1000) QUALIFIED INZ
D nlfield1 5i 0
D nlfield2 5i 0
D nlfield3 5i 0

Exec SQL
Fetch C1 For 1000 Rows
Into :WkMblSesP :nlMblSesP;

Then for each element processed in WkMblSesP, you will need to check the
nlMblSesP for each field to see if its null.
For idx = 1 to SQLERRD(3);
// set some default value for each null capable field.
IF nlMblSesP(idx).nlfields(1) < 0;
WkMblSesP(idx).Field1 = somedefault;
Endif;
IF nlMblSesP(idx).nlfields(2) < 0;
WkMblSesP(idx).Field2 = somedefault;
Endif;
// if using the definition of nlMblSesP that doesn't have an array of
5i0:

IF nlMblSesP(idx). nlfield1 < 0;
WkMblSesP(idx).Field2 = somedefault;
Endif;


Use WkMblSesP(idx) for processing.
Endfor;

You shouldn't need to use an array for the fields as long as the
nlMBLSesP data structure contains one 5i0 field for each field in the
External DS.
As far as I know, you are required to map all fields, not just null
capable fields. However, you do not have to check the null indicator for
fields that you know will not be null.



Chris Hiebert
Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the
author and do not necessarily represent those of the company.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Saturday, November 02, 2013 5:57 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Embedded SQL Select Into

Definitely has timestamps. And varchar. And identity. Hmmm....I may do
some tests and 'skinny' down the fields to see what's legal. Thanks!