SQL Server
SQL Server
Peoplevox SaveData
See more Peoplevox Examples
Demonstrates how to call the Peoplevox SaveData SOAP method. This example adds a new carrier (DHL) to the system.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/SaveData
-- 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:SaveData>
-- <peop:saveRequest>
-- <peop:TemplateName>Carriers</peop:TemplateName>
-- <peop:CsvData>CSV_DATA</peop:CsvData>
-- <peop:Action>0</peop:Action>
-- </peop:saveRequest>
-- </peop:SaveData>
-- </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:SaveData>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:saveRequest>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:TemplateName>Carriers</peop:TemplateName>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:CsvData>CSV_DATA</peop:CsvData>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' <peop:Action>0</peop:Action>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' </peop:saveRequest>' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbSoapXml, 'Append', @success OUT, ' </peop:SaveData>' + 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 @sbCsvData int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbCsvData OUT
EXEC sp_OAMethod @sbCsvData, 'Append', @success OUT, 'Name,Reference' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbCsvData, 'Append', @success OUT, 'DHL,D0001'
DECLARE @numReplacements int
EXEC sp_OAMethod @sbCsvData, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @sbSoapXml, 'Replace', @numReplacements OUT, 'CSV_DATA', @sTmp0
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/SaveData'
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 @sbCsvData
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 @sbCsvData
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
-- A successful response is shown below.
-- To parse a successful response:
DECLARE @reference nvarchar(4000)
EXEC sp_OAMethod @xmlResponse, 'ChilkatPath', @reference OUT, 'soap:Body|SaveDataResponse|SaveDataResult|Statuses|IntegrationStatusResponse|Reference|*'
PRINT 'Reference = ' + @reference
DECLARE @status nvarchar(4000)
EXEC sp_OAMethod @xmlResponse, 'ChilkatPath', @status OUT, 'soap:Body|SaveDataResponse|SaveDataResult|Statuses|IntegrationStatusResponse|Status|*'
PRINT 'Status = ' + @status
-- <?xml version="1.0" encoding="utf-8" ?>
-- <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
-- <soap:Body>
-- <SaveDataResponse xmlns="http://www.peoplevox.net/">
-- <SaveDataResult>
-- <ResponseId>0</ResponseId>
-- <TotalCount>1</TotalCount>
-- <Detail />
-- <Statuses>
-- <IntegrationStatusResponse>
-- <Reference>D0001</Reference>
-- <Status>Success</Status>
-- <LineNo>0</LineNo>
-- </IntegrationStatusResponse>
-- </Statuses>
-- <ImportingQueueId>0</ImportingQueueId>
-- <SalesOrdersToDespatchIds />
-- </SaveDataResult>
-- </SaveDataResponse>
-- </soap:Body>
-- </soap:Envelope>
EXEC @hr = sp_OADestroy @sbSoapXml
EXEC @hr = sp_OADestroy @sbCsvData
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @xmlResponse
END
GO