Behaviour of SYSTOOLS / HTTPGETBLOB

[Bratmaxxe]

Hi @ll,

I've a question regarding the behavior of an HTTPGETBLOB call.

I created an SQL UDF which is calling an web-service:

CREATE FUNCTION PGM.MYFUNC (                
        PNR DECIMAL(7, 0) )              
        RETURNS CHAR(50)                  
        LANGUAGE RPGLE                      
        SPECIFIC PGM.MYFUNC                  
        NOT DETERMINISTIC
        NOT FENCED                                            
        READS SQL DATA                      
        RETURNS NULL ON NULL INPUT                    
        EXTERNAL NAME 'PGM/MYFUNC(SQL_FUNC)'
        PARAMETER STYLE GENERAL ;

and a RPG-Program with embedded SQL and also Serviceprogram (for use in other sql statements):

     PSQL_FUNC         B                   EXPORT
     DSQL_FUNC         PI            50A
     D PNR                            7  0
      *
     D WEB_URL         S            255C
     D VALIDITY_DTE    S               D
     D SQL_RTN         S             50A
      *
     C                   MONITOR
      *
     C                   TIME                    VALIDITY_DTE
      /FREE
            CALLP INIT_SAP_ENV();

            WEB_URL = 'https://xxx:xxx@xxxx.de/' +
                      'employee-data-transfer/api/employee/' + %CHAR(PNR) +
                      '/birthplace?validityDate=' + %CHAR(VALIDITY_DTE);
      /END-FREE
      *
     C/EXEC SQL
     C+   SELECT RESULT.* into :SQL_RTN
     C+   FROM XMLTABLE(
     C+        '$result'
     C+         PASSING
     C+         XMLPARSE(DOCUMENT SYSTOOLS.HTTPGETBLOB(
     C+                  cast(:WEB_URL as char(255) ccsid 273), '')
     C+                 ) as "result"
     C+                   COLUMNS birthplace CHAR(50) PATH 'birthplace'
     C+                 ) AS result
     C/END-EXEC
     C                   IF        SQLCOD = *ZEROS
     C                   RETURN    SQL_RTN
     C                   ENDIF
      *
     C                   ON-ERROR
     C                   RETURN    *BLANKS
      *
     C                   ENDMON
      *
     PSQL_FUNC         E

In the procedure INIT_ENV there are some calls to set up the environment:

CALL QCMDEXC('ADDENVVAR ENVVAR(JAVA_HOME) VALUE(''/QOpenSys/QIBM/ProdData/JavaVM/jdk60/64bit'') REPLACE(*YES)');

CALL QCMDEXC('ADDENVVAR ENVVAR(QIBM_JAVA_PROPERTIES_FILE) VALUE(''/QIBM/userdata/java400/my.properties'') REPLACE(*YES)');


The web service is designed to return a 404 message if no data was found (e.g. invalid personnel number).

The return value is then as follows:

- 38000(-443)[IBM][System i Access ODBC Driver]SQL0443 (0,60 secs)

- S1000(-4302)[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL4302 - Die gespeicherte Java-Proz. oder benutzerdef. Funktion SYSTOOLS.HTTPGETBLOB mit dem spezifischen Namen HTTPG00001 wird mit der Ausnahmebed. https://xxx:xxx@xxx.de/employee-data...ate=2017-11-16 abgebrochen. (0,48 secs)


Is this behavior normal? Did I do something wrong?

I am grateful for any help!

Best regards,
BM

[paolinosal]

HI BM,

I'll start by step.

1. the java version 1.6 on the 31 of december will be out of date, I'll suggest to you to upgrade to a new version, check into your licpgm option 10 to see which jvm is installed into your system.
2. Are your sure then the web service return a BLOB? Could it be a CLOB?
3. Before doing the xmlparse the web service return an XML?
I suggest to you to try with Run Sql Script of the IBM ACS or Navigator, and try:

Select SYSTOOLS.HTTPGETCLOB('https://xxx:xxx@xxxx.de/..., '') from sysibm/sysdummy1;

Did it return an xml as you expected?

4. Try with the xmlparse as you need.

Bye

[Bratmaxxe]

Hi paolinosal,

many thanks for your reply.

1. LICPGM shows following jvm's:

5761JV1 *COMPATIBLE J2SE 5.0 32 Bit
5761JV1 *COMPATIBLE J2SE 5.0 64 Bit
5761JV1 *COMPATIBLE Java SE 6 32 Bit
5761JV1 *COMPATIBLE Java SE 6 64 Bit
5761JV1 *COMPATIBLE J2SE 1.4 64 Bit

2. Changed the returntype to CLOB and rebuilt the webservice that the ws does not return a 404 error but an empty XML structure - seems to work without error.

3. WS returns definately an XML

Do you think an upgrade of the java version might be helpful in anyway?

================================================== ======

Now a new problem occurs:

- a second webservice returns 2 values (MEDRESULT and NEXTDATE)
- the ws call is running without problems in an sql client

- same program logic in an embedded sql returns sqlcod -305
- this is really weird, bcs. the webservice returns not null - just an empty xml structure if no data ist found

If i do set the host variables (5i 0) for nullhandling, the indicators show -1 (null value returned).
How is that possible?

================================================== ======

Best regards
BM

[paolinosal]

Hi,
Yes I think you should be plain an upgrade to Java 1.7 or newer.