SQL Server
SQL Server
Peoplevox GetReportData
See more Peoplevox Examples
Demonstrates how to export data from a Peoplevox Warehouse Management System (WMS) using a system report template.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- Sends a POST that looks like this:
-- POST /PEOPLEVOX_CLIENT_ID/resources/integrationservicev4.asmx HTTP/1.1
-- Content-Type: text/xml;charset=UTF-8
-- SOAPAction: http://www.peoplevox.net/GetReportData
-- Content-Length: (automatically computed and added by Chilkat)
-- Host: qac.peoplevox.net
--
-- <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:peop="http://www.peoplevox.net/">
-- <soap:Header>
-- <peop:UserSessionCredentials>
-- <peop:UserId>PEOPLEVOX_USER_ID</peop:UserId>
-- <peop:ClientId>PEOPLEVOX_CLIENT_ID</peop:ClientId>
-- <peop:SessionId>PEOPLEVOX_SESSION_ID</peop:SessionId>
-- </peop:UserSessionCredentials>
-- </soap:Header>
-- <soap:Body>
-- <peop:GetReportData>
-- <peop:getReportRequest>
-- <peop:TemplateName>Item movement history</peop:TemplateName>
-- <peop:PageNo>1</peop:PageNo>
-- <peop:ItemsPerPage>20</peop:ItemsPerPage>
-- <peop:OrderBy>[Date timestamp]</peop:OrderBy>
-- <peop:Columns>[Item code],[Date timestamp],[From],[To],[Quantity],[Comments]</peop:Columns>
-- <peop:SearchClause>([Date timestamp] > DateTime(2016,01,01,09,00,00))</peop:SearchClause>
-- </peop:getReportRequest>
-- </peop:GetReportData>
-- </soap:Body>
-- </soap:Envelope>
--
-- Notice that a UserId is needed here. This is different than the username required for Peoplevox authentication.
-- The UserId for the admin account is 1.
--
DECLARE @sbSoapXml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSoapXml OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, '<?xml version="1.0" encoding="utf-8"?>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:peop="http://www.peoplevox.net/">' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <soap:Header>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:UserSessionCredentials>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:UserId>PEOPLEVOX_USER_ID</peop:UserId>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:ClientId>PEOPLEVOX_CLIENT_ID</peop:ClientId>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:SessionId>PEOPLEVOX_SESSION_ID</peop:SessionId>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' </peop:UserSessionCredentials>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' </soap:Header>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <soap:Body>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:GetReportData>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:getReportRequest>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:TemplateName>Item movement history</peop:TemplateName>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:PageNo>1</peop:PageNo>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:ItemsPerPage>20</peop:ItemsPerPage>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:OrderBy>[Date timestamp]</peop:OrderBy>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:Columns>[Item code],[Date timestamp],[From],[To],[Quantity],[Comments]</peop:Columns>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:SearchClause>([Date timestamp] > DateTime(2016,01,01,09,00,00))</peop:SearchClause>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' </peop:getReportRequest>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' </peop:GetReportData>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' </soap:Body>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, '</soap:Envelope>'
DECLARE @req int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @req, 'SendCharset', 1
EXEC sp_OASetProperty @req, 'Charset', 'utf-8'
EXEC sp_OAMethod @req, 'AddHeader', NULL, 'Content-Type', 'text/xml'
EXEC sp_OAMethod @req, 'AddHeader', NULL, 'SOAPAction', 'http://www.peoplevox.net/GetReportData'
EXEC sp_OASetProperty @req, 'Path', '/PEOPLEVOX_CLIENT_ID/resources/integrationservicev4.asmx'
EXEC sp_OAMethod @sbSoapXml, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @req, 'LoadBodyFromString', @success OUT, @sTmp0, 'utf-8'
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OASetProperty @http, 'FollowRedirects', 1
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpSReq', @success OUT, 'qac.peoplevox.net', 443, 1, @req, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbSoapXml
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- We should expect a 200 response if successful.
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Response StatusCode = ' + @iTmp0
EXEC sp_OAGetProperty @resp, 'StatusLine', @sTmp0 OUT
PRINT 'Response StatusLine: ' + @sTmp0
PRINT 'Response Header:'
EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbSoapXml
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
RETURN
END
DECLARE @xmlResponse int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlResponse OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @xmlResponse, 'LoadXml', @success OUT, @sTmp0
EXEC sp_OAMethod @xmlResponse, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
DECLARE @detail nvarchar(4000)
EXEC sp_OAMethod @xmlResponse, 'ChilkatPath', @detail OUT, 'soap:Body|GetReportDataResponse|GetReportDataResult|Detail|*'
DECLARE @csv int
EXEC @hr = sp_OACreate 'Chilkat.Csv', @csv OUT
EXEC sp_OASetProperty @csv, 'HasColumnNames', 1
EXEC sp_OAMethod @csv, 'LoadFromString', @success OUT, @detail
EXEC sp_OAGetProperty @csv, 'NumRows', @iTmp0 OUT
PRINT 'NumRows = ' + @iTmp0
EXEC sp_OAGetProperty @csv, 'NumColumns', @iTmp0 OUT
PRINT 'NumColumns = ' + @iTmp0
-- Iterate over the rows, getting the ItemCode, Name, and Barcode
DECLARE @i int
SELECT @i = 0
DECLARE @numRows int
EXEC sp_OAGetProperty @csv, 'NumRows', @numRows OUT
WHILE @i < @numRows
BEGIN
EXEC sp_OAMethod @csv, 'GetCellByName', @sTmp0 OUT, @i, 'Item code'
PRINT 'Item code: ' + @sTmp0
EXEC sp_OAMethod @csv, 'GetCellByName', @sTmp0 OUT, @i, 'Date timestamp'
PRINT 'Date timestamp: ' + @sTmp0
EXEC sp_OAMethod @csv, 'GetCellByName', @sTmp0 OUT, @i, 'From'
PRINT 'From: ' + @sTmp0
EXEC sp_OAMethod @csv, 'GetCellByName', @sTmp0 OUT, @i, 'To'
PRINT 'To: ' + @sTmp0
EXEC sp_OAMethod @csv, 'GetCellByName', @sTmp0 OUT, @i, 'Quantity'
PRINT 'Quantity: ' + @sTmp0
EXEC sp_OAMethod @csv, 'GetCellByName', @sTmp0 OUT, @i, 'Comments'
PRINT 'Comments: ' + @sTmp0
PRINT '-'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @sbSoapXml
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @xmlResponse
EXEC @hr = sp_OADestroy @csv
END
GO