PeopleService Connection via Database
Marissa Kitz (Unlicensed)
Owned by Marissa Kitz (Unlicensed)
How to connect to the PeopleService web service using Databases
Oracle Documentation: https://technology.amis.nl/2015/05/11/invoke-a-rest-service-from-plsql-make-an-http-post-request-using-utl_http-in-oracle-database-11g-xe/
MS SQL: https://blogs.msdn.microsoft.com/sqllive/2008/06/18/accessing-rest-based-web-services-using-sql-clr/
Oracle :
Oracle PeopleService Call Expand source
create user wc identified by wc default tablespace users temporary tablespace temp;
grant create procedure to wc;
grant execute on utl_http to wc;
grant create session to wc;
grant create type to wc;
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'Layer7_Dev.xml',
description => 'A test of the Web Service functionality',
principal => 'WC',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
end;
begin
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'Layer7_Dev.xml',
host => 'gw-dev.its.yale.edu',
lower_port => 443,
upper_port => NULL);
end;
connect wc/wc
create type person_type as object
(
UPI varchar2(20),
NETID varchar2(20),
AFFILIATION varchar2(200),
PRIMARY_AFFILIATION varchar2(60),
LEGAL_FIRST_NAME varchar2(60),
LEGAL_LAST_NAME varchar2(60)
);
create type people as table of person_type;
create or replace package ws_consumer_pkg
as
function get_web_request(p_uri varchar2,
p_user_name varchar2,
p_password varchar2)
return clob;
function post_web_request(p_uri varchar2,
p_post_body varchar2,
p_user_name varchar2,
p_password varchar2)
return clob;
function get_people_data(p_uri varchar2,
p_post_body varchar2,
p_user_name varchar2,
p_password varchar2)
return people pipelined;
end ws_consumer_pkg;
create or replace package body ws_consumer_pkg
as
function get_web_request(p_uri varchar2,
p_user_name varchar2,
p_password varchar2)
return clob
as
l_request utl_http.req;
l_response utl_http.resp;
l_buffer varchar2(32767);
l_clob clob;
begin
dbms_lob.createtemporary(l_clob,false);
utl_http.set_detailed_excp_support(true);
utl_http.set_wallet('file:/etc/ORACLE/WALLETS/oracle', 'oracle007');
l_request := utl_http.begin_request(p_uri, 'GET', 'HTTP/1.1');
if ( p_user_name is not null and p_password is not null )
then
utl_http.set_authentication(l_request, p_user_name, p_password);
end if;
utl_http.set_header(l_request, 'user-agent', 'mozilla/4.0');
l_response := utl_http.get_response(l_request);
begin
loop
utl_http.read_text(l_response, l_buffer, length(l_buffer));
dbms_lob.writeappend(l_clob, length(l_buffer), l_buffer);
end loop;
exception
when utl_http.end_of_body
then
utl_http.end_response(l_response);
end;
return l_clob;
end get_web_request;
function post_web_request(p_uri varchar2,
p_post_body varchar2,
p_user_name varchar2,
p_password varchar2)
return clob
as
l_request utl_http.req;
l_response utl_http.resp;
l_buffer varchar2(32767);
l_clob clob;
begin
dbms_lob.createtemporary(l_clob,false);
utl_http.set_detailed_excp_support(true);
utl_http.set_wallet('file:/etc/ORACLE/WALLETS/oracle', 'oracle007');
utl_http.set_transfer_timeout(600);
l_request := utl_http.begin_request(p_uri, 'POST', 'HTTP/1.1');
if ( p_user_name is not null and p_password is not null )
then
utl_http.set_authentication(l_request, p_user_name, p_password);
end if;
utl_http.set_header(l_request, 'user-agent', 'mozilla/4.0');
utl_http.set_header(l_request, 'Content-Type', 'text/xml');
utl_http.set_header(l_request, 'Content-Length', length(p_post_body));
utl_http.write_text(l_request, p_post_body);
l_response := utl_http.get_response(l_request);
begin
loop
utl_http.read_text(l_response, l_buffer, length(l_buffer));
dbms_lob.writeappend(l_clob, length(l_buffer), l_buffer);
end loop;
exception
when utl_http.end_of_body
then
utl_http.end_response(l_response);
end;
return l_clob;
end post_web_request;
function get_people_data(p_uri varchar2,
p_post_body varchar2,
p_user_name varchar2,
p_password varchar2)
return people
pipelined
as
l_clob clob;
begin
l_clob := post_web_request(p_uri, p_post_body, p_user_name, p_password);
for i in (
with xdata as
( select xmltype(l_clob) as data from dual )
SELECT t.*
FROM xdata,
XMLTABLE(xmlnamespaces ('urn:edu.yale/people' as "ph"),
'/ph:People/ph:Person'
PASSING xdata.data
COLUMNS UPI VARCHAR2(30) PATH './ph:Identifiers/ph:Upi',
NETID VARCHAR2(30) PATH './ph:Identifiers/ph:NetId',
AFFILIATION VARCHAR2(200) PATH './ph:Identifiers/ph:Affiliation',
PRIMARYAFFILIATION VARCHAR2(60) PATH './ph:Identifiers/ph:PrimaryAffiliation',
LEGAL_FIRST_NAME VARCHAR2(60) PATH './ph:Names/ph:LegalName/ph:FirstName',
LEGAL_LAST_NAME VARCHAR2(60) PATH './ph:Names/ph:LegalName/ph:LastName'
) t
)
loop
pipe row(person_type(i.UPI, i.NETID, i.AFFILIATION, i.PRIMARYAFFILIATION,i.LEGAL_FIRST_NAME, i.LEGAL_LAST_NAME));
end loop;
end;
end ws_consumer_pkg;
select *
from table(ws_consumer_pkg.get_people_data(
'https://gw-dev.its.yale.edu/soa-gateway/PeopleHub/PeopleService',
'<Request>
<Header>
<PageNumber>1</PageNumber>
<RecordsPerPage>50</RecordsPerPage>
</Header>
<AttributeFilter>
<Contact>Y</Contact>
<Demographic>Y</Demographic>
<Position>Y</Position>
<Appointment>Y</Appointment>
<Enrollment>Y</Enrollment>
<Organization>Y</Organization>
<Status>Y</Status>
</AttributeFilter>
<!--<PopulationFilter>
<Operator>AND</Operator>
<Primary_EDU_Person_Affiliation cmp="IN">EMPLOYEE</Primary_EDU_Person_Affiliation>
<Department_Code cmp="in">HPC111,HPC222</Department_Code>
<Planning_Unit_Code cmp="in">HPCC4003,HPCC4001</Planning_Unit_Code>
</PopulationFilter>-->
</Request>','s_ph_ybt_fin','xxxxxxxxx'
)
)
select *
from table(ws_consumer_pkg.get_people_data(
'https://gw-dev.its.yale.edu/soa-gateway/PeopleHub/PeopleService',
'<Request>
<Header>
<PageNumber>1</PageNumber>
<RecordsPerPage>50</RecordsPerPage>
</Header>
<AttributeFilter>
<Contact>Y</Contact>
<Demographic>Y</Demographic>
<Position>Y</Position>
<Appointment>Y</Appointment>
<Enrollment>Y</Enrollment>
<Organization>Y</Organization>
<Status>Y</Status>
</AttributeFilter>
<!--<PopulationFilter>
<Operator>AND</Operator>
<Primary_EDU_Person_Affiliation cmp="IN">EMPLOYEE</Primary_EDU_Person_Affiliation>
<Department_Code cmp="in">HPC111,HPC222</Department_Code>
<Planning_Unit_Code cmp="in">HPCC4003,HPCC4001</Planning_Unit_Code>
</PopulationFilter>-->
</Request>','s_ph_ybt_fin','xxxxxxxxxx'
)
)
where Upi='10000051'
MS SQL:
MSSQL Code 1 Expand source
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
using System.Globalization;
using Microsoft.SqlServer.Server;
using System.Net;
using System.Text;
using System.IO;
public partial class UserDefinedFunctions
{
public static SqlXml ConvertString2SqlXml(string xmlData)
{
UTF8Encoding encoding = new UTF8Encoding();
MemoryStream m = new MemoryStream(encoding.GetBytes(xmlData));
return new SqlXml(m);
}
[return: SqlFacet(MaxSize = -1)]
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlXml PostWebRequest(SqlString uri, SqlString postData, SqlString username, SqlString password)
{
SqlString document = null;
byte[] postBytes = Encoding.UTF8.GetBytes(Convert.ToString(postData));
WebRequest request = null;
WebResponse response = null;
Stream datastream = null;
StreamReader reader = null;
try
{
request = WebRequest.Create(Convert.ToString(uri));
if (Convert.ToString(username) != null && Convert.ToString(password) != null)
{
request.Credentials = new NetworkCredential(Convert.ToString(username), Convert.ToString(password));
}
((HttpWebRequest)request).UserAgent = "CLR web client on SQL Server";
request.Method = "POST";
request.ContentType = "text/xml";
Stream requestStream = request.GetRequestStream();
requestStream.Write(postBytes, 0, postBytes.Length);
requestStream.Close();
response = request.GetResponse();
datastream = response.GetResponseStream();
reader = new StreamReader(datastream);
document = (SqlString)reader.ReadToEnd();
reader.Close();
datastream.Close();
response.Close();
}
finally
{
reader.Close();
datastream.Close();
response.Close();
}
return (ConvertString2SqlXml(document.ToString()));
}
[return: SqlFacet(MaxSize = -1)]
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlXml GetWebRequest(SqlString uri, SqlString username, SqlString password)
{
SqlString document = null;
WebRequest request = null;
WebResponse response = null;
Stream datastream = null;
StreamReader reader = null;
try
{
request = WebRequest.Create(Convert.ToString(uri));
if (Convert.ToString(username) != null && Convert.ToString(password) != null)
{
request.Credentials = new NetworkCredential(Convert.ToString(username), Convert.ToString(password));
}
((HttpWebRequest)request).UserAgent = "CLR web client on SQL Server";
response = request.GetResponse();
datastream = response.GetResponseStream();
reader = new StreamReader(datastream);
document = (SqlString)reader.ReadToEnd();
reader.Close();
datastream.Close();
response.Close();
}
finally
{
reader.Close();
datastream.Close();
response.Close();
}
return (ConvertString2SqlXml(document.ToString()));
}
}
MSSQL Code 2 Expand source
create ASSEMBLY WsConsumer
WITH PERMISSION_SET=EXTERNAL_ACCESS;
GO
create FUNCTION dbo.fn_get_webrequest(
@uri nvarchar(max),
@user nvarchar(255)=NULL,
@passwd nvarchar(255)=NULL
)
RETURNS xml
AS
EXTERNAL NAME WsConsumer.UserDefinedFunctions.GetWebRequest;
GO
create FUNCTION dbo.fn_post_webrequest(
@uri nvarchar(max),
@postdata nvarchar(max),
@user nvarchar(255)=NULL,
@passwd nvarchar(255)=NULL
)
RETURNS xml
AS
EXTERNAL NAME WsConsumer.UserDefinedFunctions.PostWebRequest;
GO
CREATE FUNCTION [dbo].[PeopleHubService](@puri nvarchar(max),
@PostData nvarchar(max),
@pusername nvarchar(60),
@ppassword nvarchar(60))
RETURNS @returnPeopleInformation TABLE
(
Upi nvarchar(20),
NetId nvarchar(10),
Affiliation nvarchar(100),
PrimaryAffiliation nvarchar(50) NULL,
LegalFirstName nvarchar(50) NULL,
LegalLastName nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE @username nvarchar(60) = @pusername, --N's_ph_msgIII_its',
@password nvarchar(60) = @ppassword, --xxxxxxxx',
@retvar xml,
@uri nvarchar(max) = @puri --N'https://gw-dev.its.yale.edu/soa-gateway/PeopleHub/PeopleService'
set @retvar = dbo.fn_post_webrequest(@uri, @postdata, @username, @password)
insert into @returnPeopleInformation
--;WITH XMLNAMESPACES ('urn:edu.yale/people' AS ph)
select a.b.value('(./*:Identifiers/*:Upi)[1]','nvarchar(100)') as Upi
,a.b.value('(./*:Identifiers/*:NetId)[1]','nvarchar(100)') as NetId
,a.b.value('(./*:Affiliations/*:Affiliation)[1]','nvarchar(100)') as Affiliation
,a.b.value('(./*:Affiliations/*:PrimaryAffiliation)[1]', 'nvarchar(100)') as PrimaryAffiliation
,a.b.value('(./*:Names/*:LegalName/*:FirstName)[1]', 'nvarchar(100)') as LegalFirstName
,a.b.value('(./*:Names/*:LegalName/*:LastName)[1]', 'nvarchar(100)') as LegalLastName
from @retvar.nodes('(/*:People/*:Person)') as a(b)
return
END:
GO
declare @uri nvarchar(max) = N'https://gw-dev.its.yale.edu/soa-gateway/PeopleHub/PeopleService',
@postdata nvarchar(max) =
N'<Request>
<Header>
<PageNumber>2</PageNumber>
<RecordsPerPage>100</RecordsPerPage>
</Header>
<AttributeFilter>
<Contact>Y</Contact>
<Demographic>Y</Demographic>
<Position>Y</Position>
<Appointment>Y</Appointment>
<Enrollment>Y</Enrollment>
<Organization>Y</Organization>
<Status>Y</Status>
</AttributeFilter>
<PopulationFilter>
<Operator>AND</Operator>
<Primary_EDU_Person_Affiliation cmp="IN">EMPLOYEE</Primary_EDU_Person_Affiliation>
<!--<Department_Code cmp="in">HPC111,HPC222</Department_Code>
<Planning_Unit_Code cmp="in">HPCC4003,HPCC4001</Planning_Unit_Code>-->
</PopulationFilter>
</Request>',
@username nvarchar(60) = N's_ph_ybt_fin',
@password nvarchar(60) = N'xxxxxxxxxx'
select * from dbo.PeopleHubService(@uri, @postdata, @username, @password)
Related articles
-
Page:
-
Page:
-
Page:
-
Page:
-
Page: