OAR and joined LF's

[jayvaughn]

I wasn't sure whether to post this in the RPG or SQL... but here goes...

Can an OAR handler pgm be utilized on an f-spec that contains a joined logical file?

the idea behind the handler is to convert the RLA to SQL.

[JonBoy]

A handler can be used for any file that can be processed by an RPG program - so yes.

In fact if you google for Open Access, Dan Cruikshank, and SQL you will find a number of articles by Dan describing the basics of the OA handlers he developed at IBM Lab Services to do exactly what you want to do.

[jayvaughn]

thank you JonBoy - i did the search and found the articles by Dan - no mention of joined logical files that I saw... but I'll take your word for it.

[JonBoy]

They are "just files". From the perspective of the compiler it doesn't even know the difference. That is all taken care of by the OS. Really the compiler doesn't know much more than is it keyed or not, have null capable fields or not - and that's about it. Beyond the list and types of fields of course!

[jayvaughn]

thx JonBoy... would you have access to a sample OAR handler pgm that converts RLA to SQL - we have hundreds of pgms with many different files that we want to attempt to apply a handler pgm to convert the RLA to SQL. HOPING we can do that with one generic handler pgm... i'm very new to all this so not sure if it is possible... though what i have read so far, these handler pgms can be very flexible.

btw, i'm working from Dan's pdf at... http://www.omniuser.org/downloads/Ac...RPGEdition.pdf

oh wow, the following link paired with Dan's is very powerful combo... thx! https://www.ibm.com/developerworks/i...sql/index.html

[JonBoy]

Sorry - no.

But I thought Dan's articles included sample code? He never made his code fully available, he offered it as part of an on-site database modernization workshop that he used to give for IBM Lab Services. He has now retired and I don't know if he or IBM are making the code available. I may be able to put you in touch with him if you are interested.

I have many examples but none oriented towards SQL. If you haven't already found it you should find my template program described here: http://ibmsystemsmag.com/ibmi/develo...ess-templates/ the link for the actual download code is at the end.

[jayvaughn]

you are right - he includes snippets, but when combined with the developerworks link, the gaps pretty much get filled in. this is really good stuff.

[JonBoy]

Good - if you have any problems holler. As I said you should find my template a useful starting point as it includes all the standard stuff that every handler needs. Your biggest issue will be handling stuff like SETLL and READE which require you to persist a cursor etc. All good clean fun - let us know how you get on.

[jayvaughn]

Wait... "your templates"?

Author of those templates is Jon Paris (and Susan Gartner of Partner/400) a guy I've been learning from day one as an as/400 developer 20 years ago.

Could it be? JonBoy = the Jon Paris???

I should have mentioned I have already been looking at your templates even before you suggested it and found it in a Google search. It was very easy to follow along. But then gravitated towards the links specifically catering to RLA to SQL decoupling. But man, if there's ever been a new concept I've tried to learn and it had Jon Paris on it, that was my go-to.

[JonBoy]

Yep - c'est moi! You've unmasked my secret identity. Good luck with your OA ventures - glad to have been of help.

[jayvaughn]

thank you JonBoy - i really love the new IBM i stuff - thanks alot for your guidance

[jayvaughn]

JonBoy - could you take a peep at this pgm and tell me why the cursor fetch is returning an error?

everything works great (no errors) until it does the...

exec sql
fetch first from c1 into : o_dsInpRcd
: o_indArrary:

which returns sqlstate = '22018'

the open c1 (just prior to the fetch) does indeed show the correct key values passed from rpg pgm and no sqlstate error there...

joblog shows...

2 > call tstcode006
PREPARE of statement S1 completed.
ODP reused.
Cursor C1 opened.
Character in CAST argument not valid.
4>> dspjoblog

[jayvaughn]

making progress - had a like when i should have had a likeds

errr - everything now works except... statement 250 pointer not set for location referenced
pretty much the same prior posted source code is relevant to l_inpNullMap



246 l_dsRpgStateInfo.old_row_p = %alloc(p_dsInput.inputBufferLe
247 l_dsOldRow = l_dsInpRcd;
248
249 // map null indicators
250 l_inpNullMap = *off;
251 for i = 1 to p_dsInput.inputNullMapLen;
252 if l_indArray(i) <> 0;
253 l_inpNullMap(i) = *on;
254 endif;
255 endfor;
256
257 p_dsInput.found = *on;
258 endif;
259
260 return 0;
More...
Debug . . .

F3=End program F6=Add/Clear breakpoint F10=Step F11=Display variable
F12=Resume F17=Watch variable F18=Work with watch F24=More keys
Pointer not set for location referenced. +


I'll admit i'm a novice with pointer usage - how do i allocate memory to p-dsinput.inputnullmap?

d l_inpNullMap s n dim(%elem(g_indArray))
d based(p_dsInput.inputNullMap)


> EVAL p_dsinput
P_DSINPUT.LENGTH = 528
P_DSINPUT.RESERVED1 = 0
P_DSINPUT.PARAMETERFORMAT = 'ROAC0100'
P_DSINPUT.USERAREA = SPP:*NULL
P_DSINPUT.STATEINFO = SPP:F36FA9D10E002800
P_DSINPUT.RECORDLEVELS = SPP5381687E20027C0
P_DSINPUT.INPUTBUFFER = SPP5381687E2002700
P_DSINPUT.INPUTNULLMAP = SPP:*NULL
P_DSINPUT.OUTPUTBUFFER = SPP:*NULL
P_DSINPUT.OUTPUTNULLMAP = SPP:*NULL
P_DSINPUT.NAMESVALUES = SPP:*NULL
P_DSINPUT.KEY = SPP:E09DC3588D009D50
P_DSINPUT.KEYNULLMAP = SPP:*NULL

[Barbara Morris]

You shouldn't try to allocate memory to anything except the stateInfo subfield of the parameter.

If the inputNullMap pointer is null, it means that the RPG program didn't have ALWNULL(*USRCTL) as an H spec keyword or command parameter.

Even if you could set the inputNullMap pointer when it's null, RPG wouldn't notice it after your handler returned since it would only be looking at the data in the null map if it expected it.

[jayvaughn]

Ah Barbara, of course. I've dealt with that before. Glad it's just that and not pointer related. Thanks much.