cancel
Showing results for 
Search instead for 
Did you mean: 

Accessing external web service with non-constant URL

former_member329524
Active Participant
0 Kudos

Hello, all

I am looking in the documentation on accessing external web services, but either I am looking in the wrong place, or the documentatoin is lacking info.

My clients have several web services in the local network (regular services, not DB-based), the have the same interface, but different URL's.

Versions of the DB are 11, 12, and 16.

First of all, I do not see in the specs an option for a non-hardcoded URL. The logic says that this has to be possible, but I cannot find it.

Second, I need to see an example of accessing an XML or JSon based service, I cannot find it in the specs either.

Can anyone point me to a document with examples?

Thank you

Arcady

Accepted Solutions (1)

Accepted Solutions (1)

JasonHinsperger
Advisor
Advisor
0 Kudos

The following will call a web service with whatever URL you pass in as the argument "myurl".  I think that is what you are looking for.


CREATE PROCEDURE cli_test2( myurl LONG VARCHAR )

RESULT( httpheaders long varchar, httpvalues long varchars

URL '!myurl'

To deal with the resulting data in XML, use the OPENXML() function.

eg. To turn an XML list of employees that looks like this:

<root>

<row EmployeeID="102" Surname="Whitney" GivenName="Fran" StartDate="1984-08-28"/>
...
</root>

into a table of results, you would do this (where xmlgetemplist() is the web service call):


CREATE OR REPLACE PROCEDURE xmlgetemplist() RESULT( httpheader long varchar, httpbody long varchar)

URL 'http://localhost/demo/xmlEmployeeList'

TYPE 'HTTP:GET';

create variable res long varchar;

-- call the web service

select httpbody into res from xmlgetemplist() where httpheader = 'Body'

-- extract the XML elements into a SQL result set

select * from openXML( res, '/root/row' ) WITH ( EmployeeID INT '@EmployeeID',

       GivenName    CHAR(20) '@GivenName',

       Surname      CHAR(20) '@Surname',

       PhoneNumber  CHAR(10) '@Phone');

To deal with the resulting data in JSON, use the sp_parse_json() procedure.

eg.

To turn a JSON formatted list of employees that looks like this:

[

{

"EmployeeID": 102,

"Surname": "Whitney",

"GivenName": "Fran",

"StartDate": "1984-08-28",

"TerminationDate": null

},

...

]

into a table of results, you would do this (where jsongetemplist() is the web service call):


CREATE OR REPLACE PROCEDURE jsongetemplist() RESULT( httpheader long varchar, httpbody long varchar)

URL 'http://localhost/demo/jsonEmployeeList'

TYPE 'HTTP:GET';

create variable foo long varchar;

--call the webservice

select httpbody into foo from jsongetemplist() where httpheader = 'Body';


--turn the json result into a structured array of data

-- this step is required because of less structured nature of JSON

call sp_parse_json( 'output_array', foo);


--extract the JSON elements from the output array into a SQL result set

SELECT  output_array[[row_num]].EmployeeID as EmployeeID,

               output_array[[row_num]].SurName as SurName,

               output_array[[row_num]].GivenName as GivenName,

               output_array[[row_num]].StartDate as StartDate,

               output_array[[row_num]].TerminationDate as EndDate

FROM sa_rowgenerator(1, CARDINALITY(output_array))


Hope this helps,

--Jason

former_member329524
Active Participant
0 Kudos

Thank you, Jason

If I need to pass additional arguments to the external web-service, are the all have to pe part of this single URL argument?

JasonHinsperger
Advisor
Advisor
0 Kudos

No, you can pass multiple arguments separately if you need to.  The server will automatically add any arguments you supply in the procedure call to the URL in the request as a parameter in the URL.  For example, the following procedure declaration:


CREATE PROCEDURE cli_test2( arg1 int, arg2 char(32), myurl LONG VARCHAR )

RESULT( httpheaders long varchar, httpvalues long varchars

URL '!myurl'

should result in the database server making a web service request that looks like this.

http://myurl?arg1=arg1value&arg2=&arg2value

Answers (0)