Comments (18)
This works for me on an RPG program that is NOT registered as a stored procedure that returns a resultset. Found out recently you don't need to register a SPROC with DB2 if it returns a resultset which my sample RPG does. The program call takes in 2 parameters. I think you may have to pass parms in text format, but that's a small price to pay to have the equivalent of a straight up program call instead of a stored procedure without registering with the database. Feel just like calling a SPROC though.
I am using XMLSERVICE via HTTP calls from .Net.
db2=*LOCAL&uid=USER01&pwd=PASS01&ipc=/tmp/xmlservicei&ctl=*sbmjob *cdata&xmlin=<?xml version='1.0'?>
<?xml-stylesheet type='text/xsl' href='/DemoXslt.xsl'?>
<script>
<sql>
<options options='noauto' autocommit='off'/>
</sql>
<sql>
<connect conn='myconn' options='noauto'/>
</sql>
<sql>
<prepare conn='myconn'><![CDATA[call monoi.mnhello01 ('aaaaaaaaaa','bbbbbbbbbb')]]></prepare>
</sql>
<sql>
<execute/>
</sql>
<sql>
<describe desc='col'/>
</sql>
<sql>
<fetch block='all' desc='on'/>
</sql>
<sql>
<free/>
</sql>
</script>&xmlout=500000
from xmlservice.
@Louis-7 You can call xmlservice like this:
IPC: '*NA' or IPC key path
CTL: '*here' for stateless or other flags in plugipc.h
XMLIN:
<?xml version=''1.0''?> <myscript> <pgm name=''XMLTEST'' lib=''JIMOXML'' error=''fast''> <parm io=''out''> <data type=''10A''></data> </parm> </pgm> </myscript>
from xmlservice.
Hey @jimoibm, so I suppose to create a PGM called XMLTEST and use it to call the stored procedure?
I tried this as well:
<?xml version="1.0"?> <xmlservice><myscript><sql><query stmt="main" error="fast"><![CDATA[call MYLIB.SPTEST()]]></query></sql></myscript></xmlservice>
I execute a query in my SP and open a cursor. Xmlservice always returns me error. The response looks like:
...
<error>
<sqlcode>466</sqlcode>
<sqlstate>0100C</sqlstate>
<errnoxml>1500001</errnoxml>
<xmlerrmsg>
<![CDATA[SQL fail]]>
</xmlerrmsg>
<xmlhint>
<![CDATA[0100C:466:1 result sets are available from procedure XXXX]]>
</xmlhint>
</error>
<error>
<sqlcode>466</sqlcode>
<sqlstate>0100C</sqlstate>
<errnoxml>1500001</errnoxml>
<xmlerrmsg>
<![CDATA[SQL fail]]>
</xmlerrmsg>
<xmlhint>
<![CDATA[0100C:466:1 result sets are available from procedure XXXX]]>
</xmlhint>
</error>
<jobinfo>
<jobipc></jobipc>
<jobipcskey>FFFFFFFF</jobipcskey>
<jobname>QSQSRVR</jobname>
<jobuser>QUSER</jobuser>
<jobnbr>742573</jobnbr>
<jobsts>*ACTIVE</jobsts>
<curuser>XXXX</curuser>
<ccsid>37</ccsid>
<dftccsid>37</dftccsid>
<paseccsid>0</paseccsid>
<langid>ENU</langid>
<cntryid>US</cntryid>
<sbsname>QSYSWRK</sbsname>
<sbslib>QSYS</sbslib>
<curlib></curlib>
<syslibl>QSYS QSYS2 QHLPSYS QUSRSYS</syslibl>
<usrlibl>TECHLIB QGPL QTEMP</usrlibl>
<jobcpffind>see log scan, not error list</jobcpffind>
</jobinfo>
...
I think your solution and mine are both working, but how can I get the return value from a SP? Can I get the return value in PGM?
from xmlservice.
@Louis-7 Yes and no. You need to create your own pgm but being called by xmlservice. Your web application talks with xmlservice in xml format. xmlservice calls your own pgm.
from xmlservice.
I see, but there is no way to get return value from SP?
from xmlservice.
@Louis-7 I am not sure I understand your question. xmlservice is just a broker that passes the dataIn and dataOut between your web app and your background program.. guess the return value you meant was output data? in and out data are both passed with address... they can be passed in/out...
from xmlservice.
Yes, output data. It should be returned in the response body. For example, I send a db query request to xmlservice, it should return the query result to me. I'd like to get the stored procedure executed result in xmlservice output data.
Maybe this question is not related to xmlservice itself but IBM i.
from xmlservice.
Can you show me more details on what you did?
from xmlservice.
Sure. This is my SP for the test, just do a simple query ->
CREATE PROCEDURE MYLIB.SPTEST ( )
RESULT SETS 1
LANGUAGE SQL
SPECIFIC MYLIB.DBSPTEST
P1 : BEGIN
-- Declare cursor
DECLARE CURSOR1 CURSOR WITH RETURN TO CALLER
FOR
SELECT * FROM A_TABLE;
-- Cursor left open for client application
OPEN CURSOR1;
END P1
Here is what I sent to xmlservice (with POST method) ->
ctl:*here *cdata(on)
db2:*LOCAL
pwd: my_password
uid: my_user
xmlin:<?xml version="1.0"?>↵<xmlservice><myscript><sql><query stmt="main" error="fast"><![CDATA[call MYLIB.SPTEST()]]></query></sql></myscript></xmlservice>
I expect this SP return the query result in xmlservice response body. But the actual result is it returns me the errors (I post the errors in the previous comment). The reason I said it may not the xmlservice problem is I call this SP on green screen (with 'strsql') and the result are same 1 result sets are available from procedure SPTEST in MYLIB.
I'd like to know how can I get that query result?
from xmlservice.
OK.. You are not using xmlservice to call your program but SQL to DB2. Yes the result should be in the xml body returned. Maybe you can try to verify your SQL statements outside xmlservice or check the joblog that has been indicated in the error body. If you figured out any error pointing to xmlservice, please let me know.
from xmlservice.
I tried to call SP in Data Studio and I can get the query result. But on green screen - NO. I'm sure my SP can execute successfully. Yes, I guess I should check the joblog. Thanks.
from xmlservice.
@jimoibm , I found another way to call the stored procedure on IBM i. I can execute the command on the green screen but it can't be executed with xmlservice.
IBM i command:
RUNSQL SQL('call myctl.mysp(''parm1'',''parm2'')')
The xml I sent:
<?xml version="1.0"?>
<xmlservice><cmd exec='rexx'>RUNSQL SQL('call myctl.mysp(''parm1'',''parm2'')')</cmd></xmlservice>
Part of the return message.
<joblog job='QSQSRVR' user='QUSER' nbr='869325'>
<![CDATA[Cause . . . . . : A delimiter is missing between two values or a delimiter
that is not valid was found. Recovery . . . : Change the character that
is not valid or if a delimiter is missing insert one. More information on
delimiters can be found in the Information Center.
CPD0020 Diagnostic 30 05/09/19 23:57:22.852066 QCANPARS QSYS 094F QC2SYS QSYS *STMT
From user . . . . . . . . . : ZZLIU
To module . . . . . . . . . : QC2SYS
To procedure . . . . . . . : system
Statement . . . . . . . . . : 13
Message . . . . : Character 'p' not valid following string ''' '.
Cause . . . . . : A delimiter is missing between two values or a delimiter
that is not valid was found. Recovery . . . : Change the character that
is not valid or if a delimiter is missing insert one. More information on
delimiters can be found in the Information Center.
CPD0020 Diagnostic 30 05/09/19 23:57:22.852074 QCANPARS QSYS 094F QC2SYS QSYS *STMT
From user . . . . . . . . . : ZZLIU
To module . . . . . . . . . : QC2SYS
To procedure . . . . . . . : system
Statement . . . . . . . . . : 13
Message . . . . : Character ''' not valid following string ') '.
Cause . . . . . : A delimiter is missing between two values or a delimiter
that is not valid was found. Recovery . . . : Change the character that
is not valid or if a delimiter is missing insert one. More information on
delimiters can be found in the Information Center.
CPF0001 Escape 30 05/09/19 23:57:22.852245 QCADRV2 QSYS 053F QCMDEXC QSYS 012F
From user . . . . . . . . . : ZZLIU
Message . . . . : Error found on *N command.
Cause . . . . . : The system detected errors in the command. Recovery . . .
: See the previously listed messages in the job log. Correct the errors
and then try the command again. *N instead of a command name means that the
name had not been determined before the error was found.]]>
</joblog>
I'm very confused with these error messages, shouldn't I wrap the parameters with the single quotation marks?
from xmlservice.
Looks like you are running SQL using a REXX command ? What are you expecting to come back from that ? I don't believe you will get any results back.
from xmlservice.
Instead of calling a SP you should be able to call your program directly via SQL using the same call format and if it returns a resultset instead of parms you can consume your results just like an SQL query. Not perfect but I just had to do something similar with the Mono .Net data access.
from xmlservice.
@richardschoen , I'd like to execute the stored procedure whether it returns the result or not. What I'm facing is I haven't found a way to execute my SP successfully on IBM i.
I tried to call SP via SQL (my second reply in this issue #35 (comment)).
As far as I know, you can't call SP directly by using SQL on IBM i. If it's possible could you share some example to show the xml you send to IBM i?
from xmlservice.
Sample Minimal SQLRPGLE
*
* This program is a template for performing work and then returning
* parms in a single record resultset with multiple field values.
* This would also work to return multiple result records as well.
*
* Create a single element DS for returning info from the call
D RtnResults DS Dim(1) Qualified
D RtnCode 1A
D RtnParm1 100A Varying
D RtnParm2 100A Varying
*
D RowCount S 4S 0
*
D ErrorNull ds qualified
D BytesProv 10i 0 inz(0)
D BytesAvail 10i 0 inz(0)
*
D INPARM1 S 10A
D INPARM2 S 10A
*
* Parameter list
*
C *Entry Plist
C Parm INPARM1
C Parm INPARM2
/Free
// Do some work here
Monitor;
// Set row count because we are returning 1 row
RowCount=1;
// Simply return our inbound parms.
// Normally return useful data.
RtnResults(1).RtnCode = '0';
// Set return parms and trim trailing whitespace
RtnResults(1).RtnParm1 = %trimr(INPARM1);
RtnResults(1).RtnParm2 = %trimr(INPARM2);
ON-ERROR;
// Misc error occurred
RtnResults(1).RtnCode = '3';
ENDMON;
// Pass the result set back to caller
EXEC SQL SET RESULT SETS ARRAY :RtnResults FOR :RowCount ROWS;
// Exit the program
*Inlr = *ON;
return;
/End-Free
from xmlservice.
Thank you @richardschoen , this is very helpful. I don't know it can call an RPG program in a tag before, let me have a try.
from xmlservice.
Finally, I call the SP with "RUNSQL" command, the reason I got the error before is the IBM i user I used to send xmlservice do not have enough authority to update the data in DB. 😅
Call SP with PGM and then call PGM with xmlservice is my backup plan.
from xmlservice.
Related Issues (20)
- DATA tags in output have wrong converted chars after migrated to new build environment HOT 8
- XMLSTOREDP service program not exporting RUNASCII HOT 1
- xmlcgi only supports password length less/equal to 10 HOT 1
- Multiple result sets HOT 2
- Add support for Free Format parameter declaration HOT 3
- Changing 'commit' option in XML SQL statement does NOT work HOT 7
- Itoolkit's iPgm - Library setup HOT 17
- How to deal with DBCS chars at IBMi db2 HOT 1
- How to fix IPLUG15M in QXMLSERV type *N not found. error HOT 2
- Stored procedures truncate result HOT 3
- How to use pure xml to call PGM/CMDwith Chinese Chars (937)? HOT 5
- Drop calling /usr/lib/start32 to bring PASE up
- spawn call overwrites instead of ORs flags HOT 1
- [feature] job name will always be XMLSERVICE HOT 2
- Qp0sDisableSignals ??? HOT 5
- Call to service program functions don't have %PARMS() set properly - ie with *NOPASS parameters and <pgm mode="ile"> HOT 3
- Calls to programs or service program functions with mode="opm" do not handle *OMIT parameters properly HOT 1
- Fix copyright headers on source files
- Properly escape content in built CDATA blobs HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from xmlservice.