SELECT_SQL performs only 1 time

[Joerg Hamacher]

Hi,

I have a RDMLX function that accesses data base files via SELECT_SQL.
This function is called by a RDML function and this RDML function is called by a RPG programm on our IBMi.
When I call the RPG programme via the command line, it works fine the first time.
But if I call it again immediately afterwards, it is immediately terminated again. It is started and the first steps are executed, but the SQL command is no longer executed - theoretically it looks to me as if the SELECT_SQL is not reading from the beginning again, but simply continues where it left off.

What could be the reason for this?

KInd regards,
Joerg

[atostaine]

Is it your SQL options in the RPG program? You can setup them using EXEC SQL OPTIONS or some options are available when you compile it.

Art

[Tim McEntee]

Hi Joerg

Calling RDMLX from RDML and vice-versa can be problematic. It looks like the program or the SQL are being remembered. Are you using *HEAVYUSAGE.

Try putting the RDMLX in a reusable part. In the RDML function define the reusable as *DYNAMIC.

Before you execute, create the reusable, execute it, then when it returns destroy the reusable.
On the second execute it should create a new object.

That might fix your problem.

Tim

[Joerg Hamacher]

Hi Art, hi Tim,

@ Art
I am sorry but I do not know what options you mean, Art.
I have a RPGLE programme that is callign LANSA objedts UD§CALL1, UD§CALL2 and in the third step my RDML function / process.
And this RDML function calls the RDMLX function with the SQL (to be precise there are more than 1 SELECT_SQLs in this RDMLX).

@ Tim
Because I do not know how to use DEF_COM and SET_REF in RDML I wrote another little RDMLX function that calls the new reusable part.
So now the RDML that is called by my RPG programme calls a function that does just this:

Function Options(*DIRECT *LIGHTUSAGE) Rcv_Ds(DS_MandantFilialeCodeJahr1Jahr2)
Define_Com Class(#WHPS_DBINF_HISTORY_TRANSFER_RUP) Name(#WHDBINF) Reference(*DYNAMIC)
Set_Ref Com(#WHDBINF) To(*CREATE_AS #WHPS_DBINF_HISTORY_TRANSFER_RUP) Com_Error(*IGNORE)
#WHDBINF.m_Verarbeitung I_Smhmandan(#SMHMANDAN) I_Smhfilial(#SMHFILIAL) I_Smhjahr4(#SMHJAHR4) I_Smhjahr42(#SMHJAHR42)
Set_Ref Com(#WHDBINF) To(*NULL)
Return

But that doesn't change anything - the first call works fine, the following calls no longer read anything with SQL_SELECT.

These are the first statements in the methodroutine of my resuable part WHPS_DBINF_HISTORY_TRANSFER_RUP:

Mthroutine Name(m_Verarbeitung)
Define_Map For(*INPUT) Class(#SMHMANDAN) Name(#i_SMHMANDAN)
Define_Map For(*INPUT) Class(#SMHFILIAL) Name(#i_SMHFILIAL)
Define_Map For(*INPUT) Class(#SMHJAHR4) Name(#i_SMHJAHR4)
Define_Map For(*INPUT) Class(#SMHJAHR42) Name(#i_SMHJAHR42)

#SMHMANDAN := #i_SMHMANDAN
#SMHFILIAL := #i_SMHFILIAL
#SMHJAHR4 := #i_SMHJAHR4
#SMHJAHR42 := #i_SMHJAHR42

Select Fields(#g_WHPS_DBINF_History) From_File(WHPS_DBINF_History)
Delete From_File(WHPS_DBINF)
Endselect

#SMHDATUMV := ((#SMHJAHR4 - 1900) * 10000) + 101
#SMHDATUMB := ((#SMHJAHR4_2 - 1900) * 10000) + 1231
#SMHDATUM8 := 19000101
#SQL_Statement := "DFMANR = " + #SMHMANDAN.AsString + " AND DFFFNR = " + #SMHFILIAL + " AND DFSAAR = 'A' AND DFRART IN('R', 'B') AND (DFREDA BETWEEN " + #SMHDATUMV.AsString + " AND " + #SMHDATUMB.AsString + ") AND DFDFAA IN('AT', 'UV') AND DFDFLF <> 999"

Select_Sql Fields(#DFMANR #DFFFNR #DFSAAR #DFDFAA #DFDFBA #DFDFBE #DFDFS1 #DFVPNE #DFLMEN #DFDFVK #DFN6NT #DFDFLF #DFDFLE #DFRGNR #DFREDA #DFRART #DFBAKZ #DFDFS2 #DFICSA #DFIDSA #DFIESA #DFDFJN #DFILSA #DFDFPN #DFWCCD #DFDADS #DFDADN #DFDNA1 #DFDFAB #DFMWMO #DFSKPR #DFDFMG #DFARTN #DFERLK #DFATGR #DFRBGR #DFDSTS #DFEPNE #DFD7VL #DFD8VL #DFDFRG #DFBDNU #DFBENU #DFL8DT #DFGTNU #DFDFV1 #DFDFV2 #DFDFBN #DFGODT #DFDFN3 #DFCJNM #DFL7DT #DFMSNT #DFIFSA #DFIGSA #DFDF1N #DFIIVL #DFIJVL #DFIKVL #DFCKNM #DFEZDT #DFAETM #DFGFCD #DFREKZ #DFREDM #DFGRNU #DFGSNU #DFOGPC) From_Files((DBINF)) Where(#SQL_Statement) Order_By(DFRGNR) Io_Status(#IO$STS) Io_Error(*NEXT)
...
Endselect

The DELETE operation is executed every time, the SELECT_SQL only the first time.

If I am calling it with 2 following SBMJOB commands it works correctly.

Kind regards,

Jörg

[kno_dk]

Hi

what if you have all the sql command in one field and then use using in the select_sql like this:
#sqlcmd := 'SELECT SUM(orllla), ORLACT FROM ' + #DATALIB + 'orlplkpf where ORLFRM = ' + #HEFIRMA.AsString + ' and ORLNUM = ' + #ORHnum.AsString + ' and ORLLIN = ' + #ORLlin.AsString + ' Group by orlact'
Select_Sql Fields(#orllla #ORLACT) Using(#sqlcmd)
Endselect

/klaus