--ASA 9.0.2 now support client side HTTP calls and SOAP calls.  
--The sp_generate_soap_cl_proc.sql provides the 
--sp_generate_soap_client_procedures logic which attempts to generate 
--stored procedures wrappers to call remote webservices from within ASA 
--based off of WSDL exposed by external Webservices.  
--There are five parameters: URL, method and usesoapaction.

-- *The URL is required which should provide WSDL defining the Webservice.
-- * The method parameter allows the use to filter what is generated to a 
-- specific web method. Many WSDL documents define several services, this 
-- * The user id if URL requires authorization.
-- * The password if URL requires authorization.
-- * The usesoapaction parameter allows the users to generate the procedure 
-- targetname parameter via soapaction information from the WSDL.  
-- From testing experience, this parameter is useful with ASA Version 9.0.2.

--The SQL output is returned to the client and also printed on the 
--ASA Message area/console, so that you can review the SQL and decide to implement it.

--Example call 
--call sp_generate_soap_client_procedures('www.xignite.com/xQuotes.asmx?WSDL','%',NULL,NULL,'0')
--call sp_generate_soap_client_procedures('www.xignite.com/xQuotes.asmx?WSDL')
--call sp_generate_soap_client_procedures(http://www.webservicex.net/WeatherForecast.asmx?WSDL , GetWeatherByZipCode)

call sa_make_object( 'procedure', 'sp_generate_soap_client_procedures', 'dbo')
go

alter procedure dbo.sp_generate_soap_client_procedures
	 ( in urlendpoint long varchar, method long varchar default '%' ,
	  in uid char(128) default null, in pwd char(128) default null, in usesoapaction char(1) default '0')

begin
declare namespc char(1024);
declare rpcnamespc char(1024);
declare nmsclause char(1024);
declare wsdlns char(1024);
declare endpoint2 char(1024);
declare messagein char(128);
declare prefixns char(128);
declare tmpurlendpoint char(128);
declare cmd long varchar;
declare inargs long varchar;
declare wsdl long varchar;
declare local temporary table temptbl (servicename char(128) , --unique, 
	 endpoint char(1024), style char(24), style2 char(24),inputmessage char(128), nilable char(10))
	 on commit preserve rows;
--Evalute WSDL URL and add user /password info if provided.

set tmpurlendpoint=urlendpoint;
if lower(substring(urlendpoint,0,7))='http://' then
	set tmpurlendpoint=substring(urlendpoint,8);
end if;
if lower(substring(urlendpoint,0,-5))<>'?wsdl' and lower(substring(urlendpoint,0,-5))<>'.wsdl' then
	set tmpurlendpoint=tmpurlendpoint||'?WSDL';
end if;
if uid is not null and pwd is not null then
	set tmpurlendpoint = uid||':'||pwd||'@'||tmpurlendpoint;
end if;

-- Comment generated code and report to user where the generation occurs.
message 'Please review ASA Server messages window for procedure creation.' to client;
message '--The following was generated by calling:';
message '--call sp_generate_soap_client_procedures('''||urlendpoint||''','''||method||''','||
	if uid is null then 'NULL,' else ''''||uid||''',' endif||
	if pwd is null then 'NULL,' else ''''||pwd||''',' endif||
	if usesoapaction is null then 'NULL)' else ''''||usesoapaction||''')' endif;
--Get WSDL
set wsdl = (select value from sp_request_get_wsdl(tmpurlendpoint) with (attribute char(128), value long varchar) where attribute ='Body');

if lower(substring(wsdl,0,5))	!= '<?xml' then
	message 'Invalid WSDL';
	return null;
end if;
set wsdlns = '<definitions xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:mp="urn:ianywhere-com:asa-xpath-metaprop" xmlns= "http://schemas.xmlsoap.org/wsdl/"  />';


--get the Target Namespace
set namespc=(select att from openxml( wsdl, '/*:definitions',1,wsdlns)
	with ( att varchar(1024) '@*:tns')) ;
if namespc is null then
	set namespc=(select att from openxml( wsdl, '//definitions',1,wsdlns)
		with ( att varchar(1024) '@*:targetNamespace'));

end if;
set wsdlns= INSERTSTR( 244, wsdlns, ' xmlns:tns="'||namespc||'" targetNamespace="'||namespc||'" ');
--Get style, input messages, and final endpoints
insert into temptbl
select inputparms.servicename, endpoint, if style ='document' then 'SOAP:DOC' else if style ='rpc' then 'SOAP:RPC' else null endif endif as style, 
	if style2 ='document' then 'SOAP:DOC' else if style2 ='rpc' then 'SOAP:RPC' else null endif endif as style2,inputmessage,nilable
from
	(select servicename, endpoint, style,style2 from openxml(wsdl,'//*:binding/*:operation',1,wsdlns) 
	with (servicename char(128) '@*:name', endpoint char(1024) '*:operation/@*:soapAction',
		 style char(24) './*:operation/@*:style', style2 char(24) '../*:binding/@*:style') )
	as style_and_url  (servicename,endpoint, style, style2 )
	natural join
	(select servicename,inputmessage,nilable from openxml(wsdl,'//*:portType/*:operation',1,wsdlns) 
	with (servicename char(128) '@*:name', inputmessage char(128) '*:input/@*:message' , nilable char(10) '@*:nilable') )
	as inputparms (servicename,inputmessage,nilable)
	where style is not null or style2 is not null ;
--
--Loop through services and identify parameters
--
FOR services AS curs CURSOR FOR
--SELECT servicename, endpoint, style, style2, inputmessage
SELECT servicename, max(endpoint) as endpoint, max(style) as style, max(style2) as style2, max(inputmessage) as inputmessage
FROM temptbl where servicename like method
group by servicename
DO
	--Expect a tns: or some sort of prefix
	set prefixns = substring(inputmessage,0,locate(inputmessage,':'));
	
	set messagein = ( select messageelement from openxml(wsdl,'//*:message/*:part',1,wsdlns) 
			with (servicename_arg char(40) '../@*:name' ,part char(40) '@*:name', messageelement char(40) '@*:element')
			where servicename_arg=substring(inputmessage,length(prefixns)+1) and messageelement is not null and part='parameters');
	--Parameter may be an abstract name attemt tp find base data type for abtract names.  Loop 3 time through abstract names
	-- as abstract names may further resolve to abstract names.
	if messagein is not null then
		set inargs = fn_findargs(wsdl,wsdlns,servicename,substring(messagein,length(prefixns)+1),3);
	else

		-- From RPC style WSDL, RPC styles may offer parameters as base in the input message part elements.
		set inargs = (select list(fn_conv_xschemad_to_sqldatatype(part,messageelement,nilable,length,scl,prec),' ' ) 
					from openxml(wsdl,'//*:message/*:part',1,wsdlns) 
				with (servicename_part char(40) '../@*:name' ,part char(40) '@*:name', messageelement char(40) '@*:type',
				nilable char(10) '@*:nillable', length int '@*:length', scl int '@*:totalDigits',prec int '@*:fractionDigits') 
				where servicename_part =substring(inputmessage,length(prefixns)+1) );
		--set inargs = replace(inargs, ',,',',');

	end if;
	set endpoint2 = (select max(part) from openxml(wsdl,'//*:service/*:port/*:address',1,wsdlns) 
			with (part char(1024) '@*:location'));
	-- RPC styles sometimes identify namesspace in the binding/operation elements for the service.  If this is found use this value in the
	-- NAMESPACE clause.

	--set rpcnamespc=(select att from openxml( wsdl, '/*:definitions',1,wsdlns) with ( att varchar(1024) '@*:namespace')) ;
	set rpcnamespc=(select att  from openxml( wsdl, '/*:definitions/*:binding/*:operation/*:input/*:body',1,wsdlns) 
		with ( att varchar(124) '@*:namespace' , name char(128) '../../@*:name') where name = servicename);
	if rpcnamespc is null then
		set nmsclause = ' NAMESPACE '''||namespc||''' ';
	else
		set nmsclause = ' NAMESPACE '''||rpcnamespc||''' ';
	end if;

	-- The WSDL describing ASA SOAP is correct but the ASA server does not proxy the service through the DISH service properly.
	-- The ASA server will be fixed and this option will not be required in Version 9.0.2.

	if usesoapaction='1' then
		set nmsclause = ' NAMESPACE '''||
			(select substring(soapaction,0,length(soapaction)-length(servicename)-1) from openxml(wsdl, '//*:definitions/*:binding/*:operation/soap:operation', 1, wsdlns)
			 with (soapaction char(1024) '@*:soapAction', servicname char(128) '../@*:name') where  servicename= servicname)
			||''' ';
	end if;
	if style is not null then
		set cmd = 'call sa_make_object(''procedure'','''||servicename||''')\ngo\n';
		set cmd = cmd||'alter procedure "'||servicename||'" ('||substring(inargs,0,length(inargs)-1)||' )\n     URL '''||endpoint2||
--	set cmd = 'create procedure '||servicename||' ('||inargs||' ) URL '''||endpoint||
			'''\n     TYPE '''||style||''''||nmsclause||'\ngo\n';
		set inargs =' ';
	else
		set cmd = 'call sa_make_object(''procedure'','''||servicename||''')\ngo\n';
		set cmd = cmd||'alter procedure "'||servicename||'" ('||substring(inargs,0,length(inargs)-1)||' ) \n     URL '''||endpoint2||
			'''\n     TYPE '''||style2||''''||nmsclause||'\ngo\n';
	end if;
	message cmd to console;
	message cmd to client;
	-- execute immediate cmd;
END FOR;
end
go

call sa_make_object('function','fn_findargs', 'dbo')
go

alter function dbo.fn_findargs( in inwsdl long varchar, in inwsdlns long varchar, servname char(128), inputmessage char(128), recurslevels int default 3) 
returns long varchar
begin
declare i int;
declare args long varchar;
declare currabstracttype char(128);
declare abstracttype char(128);
declare foundtype char(128);
declare foundtDgt int;
declare foundfDgt int;
declare foundlength int;
declare local temporary table typelist (typename char(128) , --unique, 
	 part char(128), type char(128), nillable char(10),
	 alength int, atDgt int, afDgt int);
declare local temporary table simpletypelist (servicename char(128) , --unique, 
	 part char(128), type char(128),
	 length int, tDgt int, fDgt int);
--	 on commit preserve rows;
set i = 0;
insert into typelist
select typename,part,typ,nillable,length,totalDigit,fractionDigit from openxml(inwsdl,'//*:types/*:schema/*:element/*:complexType/*:sequence/*:element',1,inwsdlns) 
			with (typename char(40) '../../../@*:name' ,part char(40) '@*:name', typ char(40) '@*:type', nillable char(10) '@*:nillable',
		length int '@*:length',totalDigit int '@*:totalDigits',fractionDigit int '@*:fractionDigits');

insert into simpletypelist
select simpletype,part,typ,length,totalDigit,fractionDigit from openxml(inwsdl,'//*:types/*:schema/*:simpleType',1,inwsdlns) 
		with (simpletype char(40) '@*:name' ,part char(40) '@*:name', typ char(40) '*:restriction/@*:base',
		length int '@*:length',totalDigit int '@*:totalDigits',fractionDigit int '@*:fractionDigits');

set abstracttype = servname;
for types as curs_type cursor for select part,type,nillable,alength,atDgt,afDgt from typelist where typename=abstracttype
do
	if substring(type,0,2)='s:' then
		set args = args || fn_conv_xschemad_to_sqldatatype(part,type,nillable,alength,atDgt,afDgt);
	elseif substring(type,0,4)='xs:' then
		set args = args || fn_conv_xschemad_to_sqldatatype(part,type,nillable,alength,atDgt,afDgt);
	elseif substring(type,0,4)='xsd:' then
		set args = args || fn_conv_xschemad_to_sqldatatype(part,type,nillable,alength,atDgt,afDgt);
	else	
		lbl:
		while (i<recurslevels) loop
			if substring(type,0,4) = 'tns:' or substring(foundtype,0,4) = 'tns:' then
				set currabstracttype= substring(type,5);  
				set foundtype = (select type as simple from simpletypelist where servicename=currabstracttype);	
				if substring(foundtype,0,4) = 'tns:' then
					set currabstracttype=foundtype;
				elseif substring(foundtype,0,2) = 's:' then 
					set foundtDgt = (select tDgt as simple from simpletypelist where servicename=currabstracttype);	
					set foundfDgt = (select fDgt as simple from simpletypelist where servicename=currabstracttype);	
					set foundlength = (select length as simple from simpletypelist where servicename=currabstracttype);	
					set args = args ||  fn_conv_xschemad_to_sqldatatype(abstracttype,foundtype,nillable,foundlength,foundtDgt,foundfDgt);
					leave lbl;
				elseif substring(foundtype,0,3) = 'xs:' then 
					set foundtDgt = (select tDgt as simple from simpletypelist where servicename=currabstracttype);	
					set foundfDgt = (select fDgt as simple from simpletypelist where servicename=currabstracttype);	
					set foundlength = (select length as simple from simpletypelist where servicename=currabstracttype);	
					set args = args ||  fn_conv_xschemad_to_sqldatatype(abstracttype,foundtype,nillable,foundlength,foundtDgt,foundfDgt);
					leave lbl;
				elseif substring(foundtype,0,4) = 'xsd:' then 
					set foundtDgt = (select tDgt as simple from simpletypelist where servicename=currabstracttype);	
					set foundfDgt = (select fDgt as simple from simpletypelist where servicename=currabstracttype);	
					set foundlength = (select length as simple from simpletypelist where servicename=currabstracttype);	
					set args = args ||  fn_conv_xschemad_to_sqldatatype(abstracttype,foundtype,nillable,foundlength,foundtDgt,foundfDgt);
					leave lbl;
				end if; 
			end if;
			set i = i + 1;
		end loop lbl
	end if;
end for;

return(args);
end
go


call sa_make_object('function','fn_conv_xschemad_to_sqldatatype', 'dbo')
go

alter function dbo.fn_conv_xschemad_to_sqldatatype(in part char(128), in inputschema char(128) , nilable char(10) default null,
				length int default null, totalDgts int default null, fractionDgts int default null)
returns long varchar
begin
declare inargs long varchar;
declare stringstr char(128);
declare inschema char(128);
declare typestr char(128);
declare binarystr char(128);
set stringstr = 'long varchar';
set binarystr = 'long varbinary';
set part = '"'||part||'"';
if substring(inputschema,0,3) = 'xs:' then
	set inschema = substring(inputschema,4);
elseif substring(inputschema,0,2) = 's:' then 
	set inschema = substring(inputschema,3);
elseif substring(inputschema,0,4) = 'xsd:' then
	set inschema = substring(inputschema,5);
end if;
	CASE inschema
		WHEN 'string' THEN 
			if length is null then
				set inargs = inargs || part ||' ' || stringstr ||  fn_nilsyntax(nilable);
			else
				if length <=32767 then
					set inargs = inargs || part ||' varchar(' || length||')'||  fn_nilsyntax(nilable);
				else 
					set inargs = inargs || part ||' ' || stringstr ||  fn_nilsyntax(nilable);
				end if; 
			end if;
		WHEN 'boolean' THEN
			set inargs = inargs || part|| ' BIT  ' ||  fn_nilsyntax(nilable);
		WHEN 's:decimal' THEN 
			if totalDgt is null and fractionDgt is null then
				set inargs = inargs || part ||' decimal ' ||  fn_nilsyntax(nilable);
			elseif totalDgt is not null  and fractionDgt is not null then
				set inargs = inargs || part ||' decimal(' || totalDgt||','||fractionDgt||')'||  fn_nilsyntax(nilable);
			elseif totalDgt is null  and fractionDgt is not null then
				set inargs = inargs || part ||' decimal( 30,'||fractionDgt||')'||  fn_nilsyntax(nilable);
			elseif totalDgt is not null  and fractionDgt is null then
				set inargs = inargs || part ||' decimal(' || totalDgt||',6)'||  fn_nilsyntax(nilable);
			end if;	
		WHEN 'duration' THEN --Has numeric property but probably needs to be time aware so treating it like string.
			set inargs = inargs || part ||' '|| stringstr ||  fn_nilsyntax(nilable);
		WHEN 'gYearMonth' THEN --Has numeric property but probably needs to be time aware so treating it like string.
			set inargs = inargs ||part|| ' ' || stringstr ||  fn_nilsyntax(nilable);
		WHEN 'gYear' THEN --not sure but it is a numeric
			set inargs = inargs ||part|| ' '|| 'integer  ' ||  fn_nilsyntax(nilable);
		WHEN 'gYearMonth' THEN --Has numeric property but probably needs to be time aware so treating it like string.
			set inargs = inargs || stringstr ||  fn_nilsyntax(nilable);
		WHEN 'gDay' THEN --not sure but it is a numeric
			set inargs = inargs || part || ' ' || 'integer  ' ||  fn_nilsyntax(nilable);
		WHEN 'hexBinary' THEN 
			if length is null then
				set inargs = inargs || part ||' ' || binarystr ||  fn_nilsyntax(nilable);
			else
				if length <=32767 then
					set inargs = inargs || part ||' varbinary(' || length||')'||  fn_nilsyntax(nilable);
				else 
					set inargs = inargs || part ||' ' || binarystr ||  fn_nilsyntax(nilable);
				end if; 
			end if; 
		WHEN 'base64Binary' THEN 
			if length is null then
				set inargs = inargs || part ||' ' || binarystr ||  fn_nilsyntax(nilable);
			else
				if length <=32767 then
					set inargs = inargs || part ||' varbinary(' || length||')'||  fn_nilsyntax(nilable);
				else 
					set inargs = inargs || part ||' ' || binarystr ||  fn_nilsyntax(nilable);
				end if; 
			end if; 
		WHEN 'anyURI' THEN 
			set inargs = inargs || part || ' ' || stringstr  ||  fn_nilsyntax(nilable);
		WHEN 'anyURI' THEN 
			set inargs = inargs || part || ' ' || stringstr  ||  fn_nilsyntax(nilable);
		WHEN 'long' THEN 
			set inargs = inargs || part || ' ' || 'BIGINT '  ||  fn_nilsyntax(nilable);
		WHEN 'integer' THEN 
			set inargs = inargs || part || ' ' || 'INTEGER '  ||  fn_nilsyntax(nilable);
		WHEN 'int' THEN 
			set inargs = inargs || part || ' ' || 'INT '  ||  fn_nilsyntax(nilable);
		WHEN 'nonPositiveInteger' THEN 
			set inargs = inargs || part || ' ' || 'INT '  ||  fn_nilsyntax(nilable);
		WHEN 'negativeInteger' THEN 
			set inargs = inargs || part || ' ' || 'INT '  || fn_nilsyntax(nilable);
		WHEN 'short' THEN 
			set inargs = inargs || part || ' ' || 'SHORTINT '  || fn_nilsyntax(nilable);
		WHEN 'byte' THEN 
			set inargs = inargs || part || ' ' || 'BYTE '  || fn_nilsyntax(nilable);
		ELSE 
			message '--Defaulting '||part|| ' as type '''||inputschema||''' to String';
			set inargs = inargs ||part || ' ' ||  stringstr || fn_nilsyntax(nilable);
	END CASE;
	set inargs = inargs || ',';
return(inargs);
end
go
call sa_make_object( 'procedure', 'sp_request_get_wsdl', 'dbo')
go
ALTER PROCEDURE "dbo"."sp_request_get_wsdl"(suffix char(1024)) url 'http://!suffix' type 'http:get'
go
call sa_make_object( 'function', 'fn_nilsyntax', 'dbo')
go

alter function dbo.fn_nilsyntax (in arg char(10))
returns char(20)
begin
if arg='false' then
	return(' ');
else
	return(' DEFAULT NULL ');
end if;
end;

