on 03-13-2014 7:51 AM
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
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>
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.