SQL Server

PostXml vs. PostUrlEncoded

This example shows the differences in the HTTP requests sent by PostXml vs. PostUrlEncoded. With PostXml, the body of the request is the XML document itself. With PostUrlEncoded, the body of the request is composed of URL encoded params, as shown in the example below. SQL Server

-- 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.

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @strXml nvarchar(4000)
    SELECT @strXml = '<pets><lizard>Pip</lizard><dog>Fido</dog></pets>'

    -- We can log the exact HTTP requests/responses to a session log file.
    EXEC sp_OASetProperty @http, 'SessionLogFilename', 'qa_output/sessionLog.txt'

    -- Send a POST w/ content-type of "application/xml"
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/xml'

    -- Note: This example is only interested in examining (via the session log)
    -- the HTTP requests that are sent.  We're just sending the post to chilkatsoft.com
    -- even though it's not an endpoint designed to handle the POST. We don't care about
    -- the response, even if it's an error response status code..
    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostXml', @resp OUT, 'https://www.chilkatsoft.com/', @strXml, 'utf-8'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    EXEC @hr = sp_OADestroy @resp

    -- This is the HTTP request that is sent.
    -- You'll notice the body of the request is the XML document itself.

    -- 	POST / HTTP/1.1
    -- 	Content-Type: application/xml
    -- 	Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
    -- 	Connection: keep-alive
    -- 	User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0
    -- 	Accept-Language: en-us,en;q=0.5
    -- 	Accept-Encoding: gzip
    -- 	Host: www.chilkatsoft.com
    -- 	Content-Length: 48
    -- 
    -- 	<pets><lizard>Pip</lizard><dog>Fido</dog></pets>

    -- --------
    -- Now let's do a PostUrlEncoded...
    -- The XML is passed in a query param that is contained in the body of the POST.
    DECLARE @req int
    EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT

    EXEC sp_OAMethod @req, 'AddParam', NULL, 'theXml', @strXml
    EXEC sp_OAMethod @http, 'PostUrlEncoded', @resp OUT, 'https://www.chilkatsoft.com/', @req
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @req
        RETURN
      END

    EXEC @hr = sp_OADestroy @resp

    -- This is the HTTP request that is sent.
    -- You'll notice the body of the request is composed of URL encoded query params.

    -- 	POST / HTTP/1.1
    -- 	Content-Type: application/x-www-form-urlencoded
    -- 	Host: www.chilkatsoft.com
    -- 	Content-Length: 85
    -- 
    -- 	theXml=%3Cpets%3E%3Clizard%3EPip%3C%2Flizard%3E%3Cdog%3EFido%3C%2Fdog%3E%3C%2Fpets%3E
    -- 

    -- --------
    -- Let's try one more thing...
    -- If the query params are not too large, they can be passed in the URL (which means the query params are passed
    -- in the HTTP start line.  This is what happens when a URL w/ query params are pasted in a browser.
    -- The HTTP verb is "GET" instead of "POST".

    DECLARE @sbUrl int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUrl OUT

    EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, 'https://www.chilkatsoft.com/?'
    EXEC sp_OAMethod @req, 'GetUrlEncodedParams', @sTmp0 OUT
    EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, @sTmp0

    EXEC sp_OAMethod @sbUrl, 'GetAsString', @sTmp0 OUT
    PRINT 'URL with query params: ' + @sTmp0
    DECLARE @strResponse nvarchar(4000)
    EXEC sp_OAMethod @sbUrl, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'QuickGetStr', @strResponse OUT, @sTmp0

    -- The URL with query params is:  https://www.chilkatsoft.com/?theXml=%3Cpets%3E%3Clizard%3EPip%3C%2Flizard%3E%3Cdog%3EFido%3C%2Fdog%3E%3C%2Fpets%3E

    -- The HTTP request looks like this.  You can see there is no request body, and the query params
    -- are in the path part of the start line.

    -- 	GET /?theXml=%3Cpets%3E%3Clizard%3EPip%3C%2Flizard%3E%3Cdog%3EFido%3C%2Fdog%3E%3C%2Fpets%3E HTTP/1.1
    -- 	Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
    -- 	Connection: keep-alive
    -- 	User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0
    -- 	Accept-Language: en-us,en;q=0.5
    -- 	Accept-Encoding: gzip
    -- 	Host: www.chilkatsoft.com

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @req
    EXEC @hr = sp_OADestroy @sbUrl


END
GO