Integrator SQL Service SP procedure call- JSON parameter format?

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
User avatar
Jiri
Posts: 45
Joined: Thu Feb 11, 2016 11:41 pm

Integrator SQL Service SP procedure call- JSON parameter format?

Post by Jiri » Fri Jan 24, 2020 12:05 am

MS/SQL Server version 2016 allows to pass parameters in JSON format. Is possible to use this from LANSA via Integrator?

If 'standard' parameter is used, the SQL syntaxe is for example:

Exec ptc.delene_provize '1234567890'

In LANSA, following code is used:
DEF_LIST NAME(#INP_LIST) FIELDS(#UPSTSPS) TYPE(*WORKING) ENTRYS(1)
......
Change #UPSTPS '1234567890'
INZ_LIST NAMED(#INP_LIST)
* Set up the list so that it is passed to the remote procedure when the EXECUTE is run
#JSMCMD := 'Set Parameter(*LIST) Service_List(UPSTSPS)'
USE BUILTIN(JSMX_COMMAND) WITH_ARGS(#JSMHND #JSMCMD) TO_GET(#JSMSTS #JSMMSG #INP_LIST)
* set up the query expression
#JSMCMD := 'EXECUTE CALL("ptc.delene_provize(?)") CALLTYPE(*QUERY) SERVICE_LIST(UPSTSPS AD#VM Y_KOEF1 Y_KOEF2 H_S1)'
USE BUILTIN(JSMX_COMMAND) WITH_ARGS(#JSMHND #JSMCMD) TO_GET(#JSMSTS #JSMMSG #RET_LST)


and it works fine.

The JSON parameter format (used in another SP) looks in SQL syntaxe:
exec CekarnaTest '{"priklad": 3,"jmeno":"Jana"}'

I tried to write similar logic as in first example:
DEF_LIST NAME(#inp_list) FIELDS(#VF_ELTX_1) TYPE(*WORKING) ENTRYS(1)
....
#VF_ELTX_1 := '{"priklad": 3,"jmeno":"Jana"}'
INZ_LIST NAMED(#INP_LIST)
* Set up the list so that it is passed to the remote procedure when the EXECUTE is run
#JSMCMD := 'Set Parameter(*LIST) Service_List(VF_ELTX_1)'
USE BUILTIN(JSMX_COMMAND) WITH_ARGS(#JSMHND #JSMCMD) TO_GET(#JSMSTS #JSMMSG #INP_LIST)
* set up the query expression
#JSMCMD := 'EXECUTE CALL("CekarnaTest(?)") CALLTYPE(*QUERY) SERVICE_LIST(VF_ELTX_1 NAME1 NAME2)'
USE BUILTIN(JSMX_COMMAND) WITH_ARGS(#JSMHND #JSMCMD) TO_GET(#JSMSTS #JSMMSG #OUT_LIST)


... but does not work. I get error message:
JSON text is not properly formatted. Unexpected character ‘.’ Is found at position 0

Does not anybody from you experimenced with it? Any idea, how to pass the parameters?

Post Reply