Re: Map Null Indicator to DS in trigger program

This is a document I wrote. If you want I can send you original word doc.

Using Nulls In RPG and SQL



If you have a table that supports nulls, you either need to read the record
in RPG and see if fields in the table are null or set the null condition
when you insert the record



The trick is not to put a comma between the list of variables. SQL
Pre-Compiler sees this and says this must be an indicator array.



In this example, I am using named indicators rather than NullInd(1), etc.
NullInd is mapped to dsNullInd so dsNullInd and the array NullInd are the
same thing.



Note that because I am using qualified data structures I can use the same
name for the null fields as the record fields.



/copy *libl/qsrcf,cb_std_con



d dsRecord...

d ds Qualified

d CustName...

d Like(StdNam)

d CustLibrary...

d Like(StdNam)



d dsNullInd...

d ds Qualified

d CustName...

d Like(StdIntSml)

d Inz(cSqlNotNullIndicator)

d CustLibrary...

d Like(StdIntSml)

d Inz(cSqlNotNullIndicator)



d ptrNullInd...

d s Like(StdPtr)

d Inz(%Addr(dsNullInd))



d NullInd...

d s Like(StdIntSml)

d Dim(2)

d Based(ptrNullInd)



d CustNameNull…

d s Like(StdIntSml)

d Inz(cSqlNotNullIndicator)



Exec Sql Select Field1,

Field2

Into :dsRecord :NullInd

From FileY;



If dsNullInd.CustName = cSqlNullIndicator; // If null in database.

// Do something

EndIf;



Or



Exec Sql Fetch From C1

Into :dsRecord :NullInd;



If dsNullInd.CustLibrary = cSqlNotNullIndicator; // If not null.

// Do Something

EndIf



Or



CustNameNull = cSqlNotNullIndicator;



Exec Sql Update FileY

Set(CustName = :CustName :CustNameNull)



Or if null. (No value)



CustNameNull = cSqlNullIndicator;



Exec Sql Update FileY

Set(CustName = :CustName :CustNameNull)



Or



dsNullInd.CustName = cSqlNullIndicator;



Exec Sql Insert Into FileY

Values(:dsRecord :NullInd);



RPG ends up looking like:



//****ec Sql Insert Into FileY

//**** Values(:dsRecordOut :NullInd);




C EVAL SQL_00005 = DSRECORD.CUSTNAME


C EVAL SQL_00006 = DSRECORD.CUSTLIBARY

C EVAL SQL_00007 = NULLIND(1)
C EVAL SQL_00008 = NULLIND(2)

On Tue, Oct 5, 2010 at 3:59 AM, Obelix-it <nb@xxxxxxxxxxxxx> wrote:

Hi to all.

I've searched on Google for some easy solution to this, but it seems
there's nothing about.

We're starting to use Null-capabel field in or DB mainly for RI-related
needs. Everuthing's goes ok, except for a trouble.

We use a lot trigger program to perform sone calculation based on
changed to data. Usually, we defined the trigger in a way that allows
change to PF to be transparet to the trigger: just recompile the trigger
(we've wrote an in.house proc that update the PF, recompile dependecies
and trigger without need to remember to do it) but, using null map
indicator, i didn't have any way to match the null-indicator to the
file-based DS. i just need to *count* the fileds and then say "if
NullInd(x) then".

This is oblviously prone to error if a field will be added *before* the
null-capable fields (and, of course, causes the code to be less clear).

Is there any way to map the null-field map to the DS ??? since the DS
iself is declared basing on the file, it has the field null-capable, so
i suppose it has it's' own null-field map, and ti would be nice to be
able to copy or override this with the one coming from the Trigger's
parameter.

Any hint will be appreciated. Thanks.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.