Re: How to use a host variable with the SQL-function HttpGetClob() ?

Thank you all for spending your time looking at my problem and answering my
questions.

There was nothing wrong with the SQL host-variable.

I have an URL for WS, which consists of 2 parts:
1. the base URL - which I read from DB2 table
2. query string - which I create from in the program from data

My mistake was that I quoted the URL, i.e. I created with this statement
STRING
QUOTE
FUNCTION TRIM(URL-BASE-STRING)
* Query String
'?kmenoveCislo='
WS-BSSTAMMNR
'&poradoveCislo='
WS-BSVNR
QUOTE
DELIMITED BY SIZE
INTO URL-STRING
END-STRING

Now after I removed the QUOTEs, everything works fine:
STRING
FUNCTION TRIM(URL-BASE-STRING)
* Query String
'?kmenoveCislo='
WS-BSSTAMMNR
'&poradoveCislo='
WS-BSVNR
DELIMITED BY SIZE
INTO URL-STRING
END-STRING


Best Regards
Roman

On Mon, Feb 12, 2018 at 5:23 PM, Roman Miklos <rmiklos.work@xxxxxxxxx>
wrote:

Hello,

I'm using SQL-function HttpGetClob to call a webservice as described in
this article:
https://www.itjungle.com/2017/09/18/guru-consuming-rest-web-
service-using-sql-get/


I tried the RPG-example from the article and it works fine when using host
variable for the URL (i.e. :WebServiceUrl)

But it doesn't work in COBOL.


When I try in COBOL this, then it works:

DECLARE-CURSOR-FOR-SELECT.
* Compose SQL SELECT-Statement for calling WS
STRING
'SELECT '
'SYSTOOLS.HTTPGETCLOB('
FUNCTION TRIM(URL-STRING)
", '') "
'FROM SYSIBM.SYSDUMMY1'
DELIMITED BY SIZE
INTO SQLSOURCE
END-STRING

* Declare SQL-Statement SELECTWS
EXEC SQL
DECLARE SELECTWS STATEMENT
END-EXEC

* Declare Cursor for SQL-Statement
EXEC SQL
DECLARE CLOOPWS CURSOR FOR SELECTWS
END-EXEC

* Prepare SQL-Statement from the string SQLSOURCE
EXEC SQL
PREPARE SELECTWS FROM :SQLSOURCE
END-EXEC
.

The webservice call is succesfull and I get the resulting JSON.


But when I try to do it as in the RPG-example from the mentioned article,
i.e. with the host variable, then it doesn't work:

DECLARE-CURSOR-FOR-SELECT.
* Declare Cursor for SQL-Statement
EXEC SQL
DECLARE CLOOPWS CURSOR FOR
SELECT
SYSTOOLS.HTTPGETCLOB(:URL-STRING, '')
FROM SYSIBM.SYSDUMMY1
END-EXEC

Opening the cursor is succesfull, I get:
SQLSTATE = '00000'
SQLCODE = 000000000

but the execution of fetch fails:
EXEC SQL
FETCH NEXT FROM CLOOPWS INTO :SQL-RS:SQL-NULLIND
END-EXEC

I get the error:
SQLSTATE = '38000'
SQLCODE = -000004302


Declaration of my Host variable is:
01 URL-STRING PIC X(256).

I tried to declare the host variable as VARCHAR too, i.e. like this but it
didn't help:
01 URL-STRING.
49 URL-STRING-LEN PIC S9(4) COMP-4.
49 URL-STRING-CONTENT PIC X(255).


IMO, when it works with the host variable in RPG, it should work in COBOL
too.

Could you please say me what I'm doing wrong with the host variable ?
Or how should I declare the host variable, so it would work?

Kindly Regards
Roman