Visual LANSA Forum

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

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

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

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

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