ILEastic and NOXDB - Calling stored Procedure #163
Description
To whom it may concern :-)
I tried to call an stored Prodedure fom a service programm that implements the data layer for an ileastic program. Whatever i do the call to the stored procedure fails:
Here's the stored procedure that works in other enviromnets perfecly alright:
create or replace procedure IASTIC_SMP.SP_GETCUSTOMERS (
in P_FIRMA NUMERIC (2, 0),
in P_SUCHBEGRIFF varchar(32) default null
)
dynamic result SETS 1
language sql
specific IASTIC_SMP.SP_GETCST
not deterministic
modifies sql data
called on null INPUT
program type SUB
concurrent ACCESS RESOLUTION default
set option ALWBLK = * ALLREAD,
ALWCPYDTA = * optimize,
commit = * none,
DECRESULT = (31, 31, 00),
DYNDFTCOL = * no,
DYNUSRPRF = * user,
SRTSEQ = * hex
begin
declare DATEN insensitive cursor for
select
K.FIRMA,
K.IDENT,
trim(K.KUNDENNAME_01) as KUNDENNAME_01,
trim(K.KUNDENNAME_02) as KUNDENNAME_02,
trim(K.KUNDENNAME_03) as KUNDENNAME_03,
trim(K.TELEFON) as TELEFON,
trim(K.FAX) as FAX,
trim(K.HANDY) as HANDY,
trim(K.E_MAIL) as E_MAIL,
trim(K.IBAN) as IBAN,
trim(K.URL) as URL,
trim(A.STRASSE) as STRASSE,
trim(A.LANDES_KENNZEICHEN) as LANDES_KENNZEICHEN,
trim(A.PLZ) as PLZ,
trim(A.ORT) as ASORT,
A.DATUM_NEUERFASSUNG,
trim(A.BENUTZER_NEUERFASSUNG) as BENUTZER_NEUERFASSUNG,
A.DATUM_AENDERUNG,
trim(A.BENUTZER_AENDERUNG) as BENUTZER_AENDERUNG
from
KUNDEN_DATEN K
join ADRESS_DATEN A on K.ID = ADRESSEN_ID
where
FIRMA = P_FIRMA and
((upper(K.KUNDENNAME_01 || K.KUNDENNAME_02 || K.KUNDENNAME_03 ) like upper('%' || trim(P_SUCHBEGRIFF) || '%') or P_SUCHBEGRIFF is null))
order by
FIRMA,
IDENT;
open DATEN;
end;
Heres the RPGLE procedure:
dcl-proc CSTDTA_getCustomers export;
dcl-pi *n int(10);
inFirma zoned(2) const;
inSuchbegriff varchar(32) const;
outCustomerdataJSON varchar(524284);
end-pi;
dcl-s inParms pointer inz(*null);
dcl-s result pointer inz(*null);
dcl-s retCode int(10) inz(SQL__OK);
monitor;
json_setDelimiters(json_DELIMITERS);
inParms = json_newObject();
json_setNum(inParms : 'P_FIRMA' : inFirma);
if inSuchbegriff <> *blanks;
json_setStr(inParms : 'P_SUCHBEGRIFF' : inSuchbegriff);
endif;
result = json_sqlExecuteRoutine(
'IASTIC_SMP.SP_GETCUSTOMERS'
: inParms
: JSON_ROWARRAY + JSON_META + JSON_GRACEFUL_ERROR
: *off
);
if result = *null;
retCode = IL_HTTP_NOT_FOUND;
else;
outCustomerdataJSON = json_asJsonText(result);
retCode = IL_HTTP_OK;
endif;
on-error;
retCode = SQL__TECHNICAL_ERROR;
endmon;
return retCode;
on-exit;
json_delete(result);
json_delete(inParms);
end-proc;
The error mesage from NoxDB:
{"success":false,"msg":"Routine IASTIC_SMP.SP_SP_GETCUSTOMERS is not
found or not called qualified","stmt":"IASTIC_SMP.SP_GETCUSTOMERS"
I checked *LIBL at runtime. *LIBL is OK.
Any help would be appreciated.
Regards