Calling QAS Pro Web or Pro OnDemand from SQL

Summary

Customers whose product architecture relies heavily on Microsoft SQL may wish to call QAS Pro Web or Pro OnDemand directly from a SQL query. This can be accomplished on Windows using the OLE Automation stored procedures within SQL and the Microsoft Core XML Services (MSXML) library.
This Tech Doc assumes familiarity with SQL Server Management Studio and an understanding of SOAP/XML.

 Solution

Prerequisites

This solution has been tested on the following platforms:

• SQL 2005 and Windows XP
• SQL 2008 and Windows 7

I would also expect it to work with SQL 2000 and Windows Server 2000 & 2003 though these setups have not been tested. Version 3.0 of the MSXML library (MSXML.3.0.dll) was used for this solution; it is provided as part of Internet Explorer so should already be present.

SQL Setup

Use of the MSXML library relies on the OLE Automation stored procedures (sp_OACreate, sp_OAMethod, sp_OAGetErrorInfo). By default these are disabled; to enable them execute the follow script (in SSMS):

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Constructing the request

The MSXML library allows us to build and post an HTTP request directly to the Pro Web or Pro OnDemand service. In order to do this we must first create an instance of the MSXML library, then set the HTTP parameters and payload. In this case we are communicating with a SOAP web service, so the payload is our SOAP (XML) DoSearch request. These steps can be broken down as follows:

1. Declare SQL variables

-- To hold HTTP object handle, SOAP packet XML string and 'send' method string
DECLARE @objHTTP int
DECLARE @strSoapPacket varchar(8000)
DECLARE @strMethod varchar(4000)

-- To hold return values and error info for sp_OA calls
DECLARE @retVal int
DECLARE @strReturn varchar(8000)
DECLARE @strSource varchar(8000)
DECLARE @strDescription varchar(8000)

-- To hold POST HTTP status and text
DECLARE @strStatusText varchar(8000)
DECLARE @strStatus varchar(8000)

-- To hold POST response text (SOAP response) and XML
DECLARE @strResponseText varchar(8000)
DECLARE @strResponseXML varchar(8000)
DECLARE @strResponseBody varchar(8000)

2. Declare DoSearch SOAP packet (with customer’s OnDemand credentials). Replace this with a Pro Web SOAP packet if used with QAS Pro Web.

SET @strSoapPacket = N'<?xml version="1.0" encoding="utf-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ond="http://www.qas.com/OnDemand-2010-01"><soapenv:Header><ond:QAQueryHeader><ond:QAAuthentication><ond:Username>USER_NAME</ond:Username><ond:Password>PASSWORD</ond:Password></ond:QAAuthentication><ond:Security></ond:Security></ond:QAQueryHeader></soapenv:Header><soapenv:Body><ond:QASearch Localisation="?"><ond:Country>GBR</ond:Country><ond:Engine Flatten="true" Intensity="Exact" PromptSet="Optimal" Threshold="5" Timeout="10000"></ond:Engine><ond:Layout>QADefault</ond:Layout><ond:Search>SW46AA</ond:Search></ond:QASearch></soapenv:Body></soapenv:Envelope>'
 

3. Create HTTP object

EXEC @retVal = sp_OACreate 'MSXML2.ServerXMLHTTP', @objHTTP OUT -- non version specific
--EXEC @retVal = sp_OACreate 'MSXML2.ServerXMLHTTP.3.0', @objHTTP OUT -- msxml3.dll
--EXEC @retVal = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @objHTTP OUT -- msxml6.dll
IF ISNULL(@retVal, 0) != 0
BEGIN
PRINT ' RetVal: ' + CAST(@retVal as varchar)
EXEC sp_OAGetErrorInfo @retVal, @strSource OUT, @strDescription OUT
PRINT ' Error: ' + CAST(@retVal AS NVARCHAR(40)) + ' from Source: ' + @strSource + ' with Description: ' + @strDescription
END
 

4. Set the request type and endpoint

EXEC @retVal = sp_OAMethod @objHTTP, 'open', NULL, 'POST', 'https://ws.ondemand.qas.com/ProOnDemand/V2/ProOnDemandService.asmx?/DoSearch', 'false'
IF ISNULL(@retVal, 0) != 0
BEGIN
PRINT ' RetVal: ' + CAST(@retVal as varchar)
EXEC sp_OAGetErrorInfo @retVal, @strSource OUT, @strDescription OUT
PRINT ' Error: ' + CAST(@retVal AS NVARCHAR(40)) + ' from Source: ' + @strSource + ' with Description: ' + @strDescription
END

5. Set HTTP request headers

EXEC @retVal = sp_OAMethod @objHTTP, 'SetRequestHeader', NULL, 'Content-Type', 'text/xml;charset=UTF-8'
IF ISNULL(@retVal, 0) != 0
BEGIN
PRINT ' RetVal: ' + CAST(@retVal as varchar)
EXEC sp_OAGetErrorInfo @retVal, @strSource OUT, @strDescription OUT
PRINT ' Error: ' + CAST(@retVal AS NVARCHAR(40)) + ' from Source: ' + @strSource + ' with Description: ' + @strDescription
END

EXEC @retVal = sp_OAMethod @objHTTP, 'SetRequestHeader', NULL, 'SOAPAction', '"http://www.qas.com/OnDemand-2010-01/DoSearch"'
IF ISNULL(@retVal, 0) != 0
BEGIN
PRINT ' RetVal: ' + CAST(@retVal as varchar)
EXEC sp_OAGetErrorInfo @retVal, @strSource OUT, @strDescription OUT
PRINT ' Error: ' + CAST(@retVal AS NVARCHAR(40)) + ' from Source: ' + @strSource + ' with Description: ' + @strDescription
END

DECLARE @len VARCHAR(40)
SET @len = CAST(len(@strSoapPacket) AS VARCHAR)
EXEC @retVal = sp_OAMethod @objHTTP, 'SetRequestHeader', NULL, 'Content-Length', @len
IF ISNULL(@retVal, 0) != 0
BEGIN
PRINT ' RetVal: ' + CAST(@retVal as varchar)
EXEC sp_OAGetErrorInfo @retVal, @strSource OUT, @strDescription OUT
PRINT ' Error: ' + CAST(@retVal AS NVARCHAR(40)) + ' from Source: ' + @strSource + ' with Description: ' + @strDescription
END
 

6. Post HTTP request. Notice that the quotation marks in the SOAP packet have been replaced with pairs of single quotes and the entire string has been wrapped in double quotes. This is to comply with SQL string conventions and is mandatory.

SET @strSoapPacket = REPLACE(@strSoapPacket,'"','''')
SET @strMethod = 'send("'+ @strSoapPacket + '")'
EXEC @retVal = sp_OAMethod @objHTTP, @strMethod, NULL
IF ISNULL(@retVal, 0) != 0
BEGIN
PRINT ' RetVal: ' + CAST(@retVal as varchar)
EXEC sp_OAGetErrorInfo @retVal, @strSource OUT, @strDescription OUT
PRINT ' Error: ' + CAST(@retVal AS NVARCHAR(40)) + ' from Source: ' + @strSource + ' with Description: ' + @strDescription
END
 

7. Get status and response, print to screen

EXEC sp_OAGetProperty @objHTTP, 'StatusText', @strStatusText OUT
EXEC sp_OAGetProperty @objHTTP, 'Status', @strStatus OUT
EXEC sp_OAGetProperty @objHTTP, 'ResponseText', @strResponseText OUT
EXEC sp_OAGetProperty @objHTTP, 'ResponseXML', @strResponseXML OUT
EXEC sp_OAGetProperty @objHTTP, 'ResponseBody', @strResponseBody OUT

EXEC sp_OADestroy @objHTTP OUT

PRINT 'Status: ' + ISNULL(@strStatus, '')
PRINT 'StatusText: ' + ISNULL(@strStatusText, '')
PRINT 'ResponseText: ' + ISNULL(@strResponseText, '')
PRINT 'ResponseXML: ' + ISNULL(@strResponseXML, '')
PRINT 'ResponseBody: ' + ISNULL(@strResponseBody, '')
 

Response

The expected response from the procedure above is:

Status: 200
StatusText: OK
ResponseText: <?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Header><QAInformation xmlns="http://www.qas.com/OnDemand-2010-01"><StateTransition>PickList</StateTransition><CreditsUsed>0</CreditsUsed></QAInformation></soap:Header><soap:Body><QASearchResult xmlns="http://www.qas.com/OnDemand-2010-01"><QAPicklist MoreOtherMatches="true" OverThreshold="true"><FullPicklistMoniker>GBR|807101|0eSGBRDwTbBwAAAAABAVNXNDZBQQAqAwAA</FullPicklistMoniker><PicklistEntry FullAddress="true"><Moniker>GBR|807101|0JOGBRDwTbBwAAAAABAwEAAAABbsLXUgAhAAIAAAAAAAAA..9kAAAAAP....8AAAAAAAAAAABTVzQ2QUEA</Moniker><PartialAddress>2 Brayburne Avenue, LONDON SW4 6AA</PartialAddress><Picklist>2 Brayburne Avenue, LONDON</Picklist><Postcode>SW4 6AA</Postcode><Score>100</Score></PicklistEntry><PicklistEntry FullAddress="true"><Moniker>GBR|807101|0qOGBRDwTbBwAAAAABAwEAAAABbsLYUgAgAAAAADQAAP..ZAAAAAD.....AAAAAAAAAAAAU1c0NkFBAA--</Moniker><PartialAddress>4 Brayburne Avenue, LONDON SW4 6AA</PartialAddress><Picklist>4 Brayburne Avenue, LONDON</Picklist><Postcode>SW4 6AA</Postcode><Score>100</Score></PicklistEntry><PicklistEntry FullAddress="true"><Moniker>GBR|807101|0ROGBRDwTbBwAAAAABAwEAAAABbsLYUgAgAAAAADUAAP..ZAAAAAD.....AAAAAAAAAAAAU1c0NkFBAA--</Moniker><PartialAddress>5 Brayburne Avenue, LONDON SW4 6AA</PartialAddress><Picklist>5 Brayburne Avenue, LONDON</Picklist><Postcode>SW4 6AA</Postcode><Score>100</Score></PicklistEntry><PicklistEntry FullAddress="true"><Moniker>GBR|807101|0dOGBRDwTbBwAAAAABAwEAAAABbsLYUgAgAAAAADYAAP..ZAAAAAD.....AAAAAAAAAAAAU1c0NkFBAA--</Moniker><PartialAddress>6 Brayburne Avenue, LONDON SW4 6AA</PartialAddress><Picklist>6 Brayburne Avenue, LONDON</Picklist><Postcode>SW4 6AA</Postcode><Score>100</Score></PicklistEntry><PicklistEntry FullAddress="true"><Moniker>GBR|807101|0JOGBRDwTbBwAAAAABAwEAAAABbsLaUgAgAAAAADgAAP..ZAAAAAD.....AAAAAAAAAAAAU1c0NkFBAA--</Moniker><PartialAddress>8 Brayburne Avenue, LONDON SW4 6AA</PartialAddress><Picklist>8 Brayburne Avenue, LONDON</Picklist><Postcode>SW4 6AA</Postcode><Score>100</Score></PicklistEntry><Prompt>Enter selection</Prompt><Total>5</Total></QAPicklist></QASearchResult></soap:Body></soap:Envelope>
ResponseXML:
ResponseBody: ?????????•???????????????????????????????????????????•??????????????????????????•?????????????????????=???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????=????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????'?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????`??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

As you can see, the ResponseText variable contains the SOAP/XML response packet which can then be parsed (potentially using the MSXML DOM) to extract the partial addresses and store the associated monikers.
Note that this is only a partial example; a full solution would need to implement a subsequent DoGetAddress call with the moniker of the desired address. The process would remain fundamentally the same, though the SOAP packet string, SOAPAction property and Content-Length property would need to be redefined.
 

Troubleshooting

• If you’re getting either of the below errors check that the SOAP packet string has been correctly defined with single quotation marks and wrapped in send(“ ”) as detailed in section 6 of constructing the request.

Error: -2147211454 from Source: ODSOLE Extended Procedure with Description: Traversal string: Bad whitespace.
Error: -2147211483 from Source: ODSOLE Extended Procedure with Description: sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

• Usual Pro Web troubleshooting techniques still apply – can you view the WSDL? Try sending requests using SOAP UI or similar.