SOAP Request and response directly from SQL Server Instance

#1

I have built a VB application that reads values from SQL server and
uses one of the selected values to send a soap request. The
application then takes the responses and stores them in a table. The
question here and never even considered it is, can I send the SOAP
request directly from the server is a stored procedure and eliminate
the middleware?

#2

Spero (robspero (AT) gmail (DOT) com) writes:

Quote:

I have built a VB application that reads values from SQL server and
uses one of the selected values to send a soap request. The
application then takes the responses and stores them in a table. The
question here and never even considered it is, can I send the SOAP
request directly from the server is a stored procedure and eliminate
the middleware?

I guess you could do it in a stored procedure written in the CLR, but
whether really is a good thing, I am not so sure.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#3

Sorry Erland! But what do you mean "written in the CLR"?

#4

Spero (robspero (AT) gmail (DOT) com) writes:

Quote:

Sorry Erland! But what do you mean "written in the CLR"?

A stored procedure written in C# or VB .Net (or any other .Net language).

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#5

I am really finding it hard to believe that this question does not
come u more. I already have the soap structure completed, I can pull
values from ,y table tou phrae my request. I just can not believe that
this is more commonly used method to eliminate the middel ware and
have the server do the work. I can not find one example. The
applicartion is already completed in vb.net and just want the server
to make these request. I can not find one example of a stored proc
that does anything in anyway like what I need! I mean am I just asking
the question incorrectly?

#6

Spero (robspero (AT) gmail (DOT) com) writes:

Quote:

I am really finding it hard to believe that this question does not
come u more. I already have the soap structure completed, I can pull
values from ,y table tou phrae my request. I just can not believe that
this is more commonly used method to eliminate the middel ware and
have the server do the work. I can not find one example. The
applicartion is already completed in vb.net and just want the server
to make these request. I can not find one example of a stored proc
that does anything in anyway like what I need! I mean am I just asking
the question incorrectly?

If you already have the code in VB .Net, all you need is to package it
as a stored procedure. If you have no experience of using the CLR in
SQL Server, you can read about it in Books Online. Since you already
..Net you should get going quickly.

However, I am not sure that if I were the DBA, that I would like to see
server threads to be stalled while waiting for reponse from a web service.
Then again, it depends on what environment your application will run in.
If this is intended for desktops solutions that will run on Express,
there will be no DBA to hassle you. :-)

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

#7

Well yes, but the application operates as one function and I was
thinking of the SOAP request to build a data warehouse. Best way to
explain it is that the desktop app is for a user to type in GOOG so
they can get the current trading price, the SOAP request from the
server would be like an hourly request for GOOG to build a data
warehouse so that GOOG can be trended with my source data. I truly
appreciate you taking the time with your responses, they have been an
eye opener for me!

#8

Well yes, but the application operates as one function and I was
thinking of the SOAP request to build a data warehouse. Best way to
explain it is that the desktop app is for a user to type in GOOG so
they can get the current trading price, the SOAP request from the
server would be like an hourly request for GOOG to build a data
warehouse so that GOOG can be trended with my source data. I truly
appreciate you taking the time with your responses, they have been an
eye opener for me!

#9

Hi Spero,

On Sep 15, 4:20*am, Spero <robsp... (AT) gmail (DOT) com> wrote:

Quote:

I have built a VB application that reads values from SQL server and
uses one of the selected values to send a soap request. The
application then takes the responses and stores them in a table. The
question here and never even considered it is, can I send the SOAP
request directly from the server is a stored procedure and eliminate
the middleware?

FWIW, below is how I've done it with Oracle. (There is a full blown
SOAP
package that can be added to your database but the HTTP stuff is there
by
default (at least where we are) so I stuck with poking the headers in
myself.)

In case some have not seen or used Oracle Pipelined functions
before, these are a couple of examples using the XML-processing
functions
from my example below: -

select * from TABLE(Test_SOAP.wsFullName('ABC123Z') );

*OR*

select a.person_id,
coalesce((select
b.column_value
from
TABLE(Test_SOAP.wsFullName(a.person_id)) b)
, 'Not Found') as FullName
from
access_user a
;

To search for Employees by name you can try the following: -

select * from TABLE(Test_SOAP.wsUserDetails('m','r') );

*OR to search and join with matching Local users in ACCESS_USER
table*

select
a.*,
b.active_flag
from
TABLE(Test_SOAP.wsUserDetails('mah','ri') ) a,
access_user b
where
b.Person_Id = a.PersonId ;

Cheers Richard Maher

Oracle example follows: -

Quote:

Only really relevant if you happen to also be using Oracle, but
below is some stuff I've been testing out recently. It uses UTL_HTTP,
Pipelined User Defined Functions, CLOBS, and XMLTABLES to call SOAP
Web-Services directly from the database and pipe the resultant XML out as
a
database table that can be joined, sorted etc. Quite sexy and very useful
IMHO.

Cheers Richard Maher

create or replace
TYPE TestStaffDetails IS OBJECT (
PersonID varchar2(8),
GivenName varchar2(60),
Surname varchar2(60),
PersonType varchar2(30),
PersonSubType varchar2(30),
EmailActive varchar2(15),
DisplayEmail varchar2(100),
OtherEmail varchar2(100)
);

create or replace PACKAGE Test_SOAP AS

TYPE UserDetails IS TABLE OF TestStaffDetails;

TYPE UserFullName IS TABLE OF varchar2(100);

FUNCTION wsUserDetails(surname in varchar2, first_name in varchar2)
RETURN UserDetails PIPELINED;

FUNCTION wsFullName(PersonID in varchar2)
RETURN UserFullName PIPELINED;

END Test_SOAP;

create or replace
PACKAGE BODY Test_SOAP AS

FUNCTION wsUserDetails(surname in varchar2, first_name in varchar2)
RETURN UserDetails PIPELINED
AS
env clob;
soap_resp clob := empty_clob;
resp_str varchar2(32767);
http_req utl_http.req;
http_resp utl_http.resp;
resp sys.xmltype;
in_xml sys.xmltype;
url
varchar2(200):='https://test.my.stuff/staffquery/staffquery.asmx';
BEGIN

IF (coalesce(length(surname),0) = 0 OR
coalesce(length(first_name),0)
= 0) THEN
RAISE_APPLICATION_ERROR(-20002, 'Invalid argument');
END IF;

-- generate_envelope(req, env);
env:='<?xml version="1.0" encoding="utf-8"?
soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
soap:Body
UserDetails xmlns="https://www.my.stuff/staffQuery/"
FirstName>'|| first_name ||'</FirstName
Surname>'|| surname ||'</Surname
/UserDetails
/soap:Body
/soap:Envelope>';
UTL_HTTP.SET_WALLET ( 'file:' || '/app/ssl/my.stuff', 'mypsswd' );
http_req := utl_http.begin_request(url, 'POST','HTTP/1.1');
utl_http.set_body_charset(http_req, 'UTF-8');
-- utl_http.set_proxy('proxy:80?, NULL);
-- utl_http.set_persistent_conn_support(TRUE);
-- UTL_HTTP.set_authentication(http_req, ", '3', 'Basic', TRUE );
utl_http.set_header(http_req, 'Content-Type', 'text/xml');
utl_http.set_header(http_req, 'Content-Length', length(env));
utl_http.set_header(http_req, 'SOAPAction',
'https://www.my.stuff/staffQuery/UserDetails');
utl_http.write_text(http_req, env);
http_resp := utl_http.get_response(http_req);

dbms_lob.createTemporary(soap_resp, true);
dbms_lob.open(soap_resp, dbms_lob.lob_readwrite);

BEGIN
LOOP
utl_http.read_text(http_resp, resp_str, 32767);
dbms_lob.writeappend (soap_resp, LENGTH(resp_str), resp_str);
END LOOP;

EXCEPTION
WHEN utl_http.end_of_body THEN
NULL;
END;

utl_http.end_response(http_resp);
resp := xmltype.createxml(soap_resp).extract(
'/soap:Envelope/soap:Body/child::node()'
,
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
);

dbms_lob.close(soap_resp);
dbms_lob.freeTemporary(soap_resp);

for i in (
select
Staff.PersonID,
Staff.GivenName,
Staff.Surname,
Staff.PersonType,
Staff.PersonSubType,
Staff.EmailActive,
Staff.DisplayEmail,
Staff.OtherEmail
from
xmltable
( XmlNamespaces('https://www.my.stuff/staffQuery/' as SQ),
'/SQ:UserDetailsResponse'
passing resp
columns
xmlresult xmltype path
'SQ:UserDetailsResult/StaffList/Staff'
) soapXML
, xmltable
( '*'
passing soapXML.xmlresult
columns
PersonId varchar2(8) path 'PersonID',
GivenName varchar2(60) path 'PersonPreferredGivenName',
Surname varchar2(60) path 'PersonPreferredSurname',
PersonType varchar2(30) path 'PersonType',
PersonSubType varchar2(30) path 'PersonSubType',
EmailActive varchar2(15) path 'EmailActive',
DisplayEmail varchar2(100) path 'DisplayEmail',
OtherEmail varchar2(100) path 'OtherMail'
) Staff )
loop
pipe row (TestStaffDetails(i.PersonID,
i.GivenName,
i.Surname,
i.PersonType,
i.PersonSubType,
i.EmailActive,
i.DisplayEmail,
i.OtherEmail));
-- dbms_output.put_line('hello'||i.PersonID);
end loop;

RETURN;

END;

FUNCTION wsFullName(PersonID in varchar2)
RETURN UserFullName PIPELINED
AS
env clob;
http_req utl_http.req;
http_resp utl_http.resp;
resp sys.xmltype;
in_xml sys.xmltype;
url
varchar2(200):='https://test.my.stuff/staffquery/staffquery.asmx';
BEGIN

IF (coalesce(length(PersonID),0) = 0) THEN
RAISE_APPLICATION_ERROR(-20002, 'Invalid argument');
END IF;

-- generate_envelope(req, env);
env:='<?xml version="1.0" encoding="utf-8"?
soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
soap:Body
FullName xmlns="https://www.my.stuff/staffQuery/"
StaffNumber>'|| PersonID ||'</StaffNumber
/FullName
/soap:Body
/soap:Envelope>';
UTL_HTTP.SET_WALLET ( 'file:' || '/app/ssl/my.stuff', 'mypsswd' );
http_req := utl_http.begin_request(url, 'POST','HTTP/1.1');
utl_http.set_body_charset(http_req, 'UTF-8');
-- utl_http.set_proxy('proxy:80?, NULL);
-- utl_http.set_persistent_conn_support(TRUE);
-- UTL_HTTP.set_authentication(http_req, ", '3', 'Basic', TRUE );
utl_http.set_header(http_req, 'Content-Type', 'text/xml');
utl_http.set_header(http_req, 'Content-Length', length(env));
utl_http.set_header(http_req, 'SOAPAction',
'https://www.my.stuff/staffQuery/FullName');
utl_http.write_text(http_req, env);
http_resp := utl_http.get_response(http_req);
utl_http.read_text(http_resp, env);
utl_http.end_response(http_resp);
resp := xmltype.createxml(env).extract(
'/soap:Envelope/soap:Body/child::node()'
,
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
);

for i in (
select
soapXML.FullName
from
xmltable
( XmlNamespaces('https://www.my.stuff/staffQuery/' as SQ),
'/SQ:FullNameResponse'
passing resp
columns
FullName varchar2(100) path 'SQ:FullNameResult'
) soapXML )
loop
IF i.FullName is not null THEN
IF substr(i.FullName, 1, 1) = 'Y' THEN
pipe row (substr(i.FullName,2));
-- dbms_output.put_line('hello'||coalesce(i.FullName, 'not
found'));
END IF;
END IF;
end loop;

RETURN;

END;

END Test_SOAP;

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SOAP Request and response directly from SQL Server Instance

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

SOAP Request and response directly from SQL Server Instance - 09-15-2010 , 07:20 AM

Re: SOAP Request and response directly from SQL Server Instance - 09-15-2010 , 04:44 PM

Re: SOAP Request and response directly from SQL Server Instance - 09-15-2010 , 07:16 PM

Re: SOAP Request and response directly from SQL Server Instance - 09-16-2010 , 02:27 AM

Re: SOAP Request and response directly from SQL Server Instance - 09-16-2010 , 09:43 PM

Re: SOAP Request and response directly from SQL Server Instance - 09-17-2010 , 04:11 PM

Re: SOAP Request and response directly from SQL Server Instance - 09-17-2010 , 07:10 PM

Re: SOAP Request and response directly from SQL Server Instance - 09-17-2010 , 07:10 PM

Re: SOAP Request and response directly from SQL Server Instance - 09-18-2010 , 07:53 PM