(SQL Server) Debugging HTTP

The 1st step in debugging a failed HTTP request (or determining the reason for an unexpected response) is to get more information about what actually happened. The Chilkat HTTP component/class have properties that provide this information. The most useful is the SessionLogFilename property. It may be set to a filepath that is to be created or appended by the HTTP component. The exact HTTP requests and responses are logged to this file. The session log file can be examined to verify that the HTTP request(s) sent to the web server contain the expected data and are structurally formatted as expected.

The standard LastErrorText / LastErrorXml / LastErrorHtml properties can also provide information about what transpired. These properties are standard to all Chilkat components/classes, and contain information about what transpired (for a given object instance) for the very last method called. Regardless of the success or failure status returned by the method, the LastErrorText will always contain information. The VerboseLogging property may be set to 1 to get more detailed information in LastErrorText. (It is best to first examine the LastErrorText without VerboseLogging, and then only use VerboseLogging if necessary.)

Other properties exist to provide less detailed, but more succinct and easily accessible information, such as LastHeader, LastStatus, WasRedirected, LastContentType, etc.

Important: The SessionLogFilename, LastErrorText, and other debugging related properties apply to ALL Chilkat HTTP methods that communicate with an HTTP server.

Download Chilkat HTTP ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    --  Any string unlocks the component for the 1st 30-days.
    EXEC sp_OAMethod @http, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    --  This example demonstrates session logging via the
    --  SessionLogFilename property.  It also examines the
    --  contents of the LastErrorText and a few other properties
    --  to see what transpired in a seemingly simple HTTP GET request.

    EXEC sp_OASetProperty @http, 'SessionLogFilename', '/temp/httpSessionLog.txt'

    --  The "http://www.paypal.com" URL was chosen on
    --  purpose to demonstrate the potential complexity
    --  of a seemingly simple HTTP GET request and response.
    -- 
    --  In this case, the initial response is a 301 redirect to
    --  "https://www.paypal.com/" because all communication
    --  with PayPal must be over SSL/TLS.  The Chilkat HTTP
    --  FollowRedirects property defaults to 1, causing the
    --  redirect to be followed automatically.
    --  The request is resent using SSL/TLS and the response
    --  received is a complex one:  it is both Gzipped (compressed)
    --  and "chunked".  Internally, Chilkat automatically handles
    --  the decompression and the re-composing of the chunked
    --  response to return the simple HTML page that is the result.
    DECLARE @html nvarchar(4000)

    EXEC sp_OAMethod @http, 'QuickGetStr', @html OUT, 'http://www.paypal.com/'

    EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT

    PRINT @sTmp0

    --  Looking at the httpSessionLog, we can see the initial
    --  HTTP request, the 301 response, the subsequent
    --  HTTP GET request to follow the redirect, and the final
    --  gzipped/chunked response:

    --  
---- Sending ----
GET / HTTP/1.1
Accept: */*
Accept-Encoding: gzip
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Accept-Language: en-us,en;q=0.5
User-Agent: Chilkat/1.0.0 (+http://www.chilkatsoft.com/ChilkatHttpUA.asp)
Host: www.paypal.com
Connection: Keep-Alive


---- Received ----
HTTP/1.1 301 Moved Permanently
Date: Sat, 25 Jun 2011 14:50:36 GMT
Server: Apache
Set-Cookie: cwrClyrK4LoCV1fydGbAxiNL6iG..; domain=.paypal.com; path=/; HttpOnly
Set-Cookie: cookie_check=yes; expires=Tue, 22-Jun-2021 14:50:37 GMT; domain=.paypal.com; path=/; HttpOnly
Location: https://www.paypal.com/
Vary: Accept-Encoding
Content-Encoding: gzip
Keep-Alive: timeout=5, max=100
Connection: Keep-Alive
Transfer-Encoding: chunked
Content-Type: text/html

10
(10 bytes of binary (non-printable) data here...)

---- Sending ----
GET / HTTP/1.1
Accept: */*
Accept-Encoding: gzip
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Accept-Language: en-us,en;q=0.5
User-Agent: Chilkat/1.0.0 (+http://www.chilkatsoft.com/ChilkatHttpUA.asp)
Host: www.paypal.com
Connection: Keep-Alive


---- Received ----
HTTP/1.1 200 OK
Date: Sat, 25 Jun 2011 14:50:37 GMT
Server: Apache
Cache-Control: private
Pragma: no-cache
Expires: Thu, 05 Jan 1995 22:00:00 GMT
Set-Cookie: cwrClyrK4DoCV1fydGbAxiNL6iG=jE8s ...; domain=.paypal.com; path=/; Secure; HttpOnly
Set-Cookie: KHcl0EuY7AKSMgfvHl7J5E7hPtK=YSG ...; expires=Fri, 20-Jun-2031 14:50:38 GMT; 
 domain=.paypal.com; path=/; Secure; HttpOnly
Set-Cookie: cookie_check=yes; expires=Tue, 22-Jun-2021 14:50:38 GMT; domain=.paypal.com; path=/; 
 Secure; HttpOnly
Set-Cookie: navcmd=_home-general; domain=.paypal.com; path=/; Secure; HttpOnly
Set-Cookie: consumer_display=USER_HOMEPAGE...;
 expires=Tue, 22-Jun-2021 14:50:38 GMT; domain=.paypal.com; path=/; Secure; HttpOnly
Set-Cookie: navlns=0.0; expires=Fri, 20-Jun-2031 14:50:38 GMT; domain=.paypal.com; path=/; Secure; HttpOnly
Set-Cookie: Apache=10.73.8.36.1309013437867506; path=/; expires=Mon, 17-Jun-41 14:50:37 GMT
Vary: Accept-Encoding
Content-Encoding: gzip
Strict-Transport-Security: max-age=500
Keep-Alive: timeout=5, max=100
Connection: Keep-Alive
Transfer-Encoding: chunked
Content-Type: text/html; charset=UTF-8

1895
(The remainder of the HTTP response is binary data.)

    --  Looking at the LastErrorText, we can see detailed information
    --  about what transpired during the call.

    --  
ChilkatLog:
  QuickGetStr:
    DllDate: Jun  9 2011
    UnlockPrefix: Anything for 30-day trial
    Username: CK2007:Chilkat
    Architecture: Little Endian; 32-bit
    Language: Visual C++ 6.0
    VerboseLogging: 0
    QuickReq:
      QuickGetToOutput_OnExisting:
        simpleHttpRequest_3:
          httpMethod: GET
          requestUrl: http://www.paypal.com/
          Connecting to web server...
          httpServer: www.paypal.com
          port: 80
          ConnectTimeoutMs: 10000
          calling ConnectSocket2
          AddrInfoList:
            AddrInfo:
              ai_flags: 0
              ai_family: 2
              ai_socktype: 1
              ai_protocol: 0
              ai_addrlen: 16
              ai_canonname: (NULL)
          Connect using IPV4.
          ipAddress1: 173.0.84.2
          myIP_3: 192.168.1.121
          myPort_3: 4652
          connect successful.
          connectElapsedMs: 187
          -- BuildGetRequest --
          Not auto-adding cookies.
          sendElapsedMs: 0
          StatusCode: 301
          StatusText: Moved Permanently
          Reading response body...
          Reading chunked response
          readResponseElapsedMs: 563
          CompressedSize: 8
          UncompressedSize: 0
          redirectUrl: https://www.paypal.com/
          Using existing connection for redirect...
          RedirectGet:
            QuickGetToOutput_Redirect:
              simpleHttpRequest_3:
                httpMethod: GET
                requestUrl: https://www.paypal.com/
                Connecting to web server...
                httpServer: www.paypal.com
                port: 443
                Using HTTPS.
                ConnectTimeoutMs: 10000
                calling ConnectSocket2
                AddrInfoList:
                  AddrInfo:
                    ai_flags: 0
                    ai_family: 2
                    ai_socktype: 1
                    ai_protocol: 0
                    ai_addrlen: 16
                    ai_canonname: (NULL)
                 Connect using IPV4.
                ipAddress1: 173.0.84.2
                myIP_3: 192.168.1.121
                myPort_3: 4653
                connect successful.
                handshakeMessageType: ServerHello
                handshakeMessageLen: 0x46
                processHandshakeMessage:
                  MessageType: ServerHello
                  Processing ServerHello...
                  ServerHello:
                    MajorVersion: 3
                    MinorVersion: 1
                    SessionIdLen: 32
                    CipherSuite: RSA_WITH_3DES_EDE_CBC_SHA
                    CipherSuite: 00,0a
                    CompressionMethod: 0
                    Queueing ServerHello message.
                    ServerHello is OK.
                HandshakeQueue:
                  MessageType: ServerHello
                Dequeued ServerHello message.
                handshakeMessageType: Certificate
                handshakeMessageLen: 0x1117
                processHandshakeMessage:
                  MessageType: Certificate
                  ProcessCertificates:
                    Certificate:
                      derSize: 1615
                      certSubjectCN: www.paypal.com
                      certSerial: 2E33874F6FE2D41ED3FFFF35F6A4C918
                      certIssuerCN: VeriSign Class 3 Extended Validation SSL CA
                    Certificate:
                      derSize: 1512
                      certSubjectCN: VeriSign Class 3 Extended Validation SSL CA
                      certSerial: 5B7759C61784E15EC727C0329529286B
                      certIssuerCN: VeriSign Class 3 Public Primary Certification Authority - G5
                    Certificate:
                      derSize: 1236
                      certSubjectCN: VeriSign Class 3 Public Primary Certification Authority - G5
                      certSerial: 250CE8E030612E9F2B89F7054D7CF8FD
                      certIssuerCN: 
                    NumServerCertificates: 3
                    Queueing Certificates message...
                Dequeued Certificate message.
                handshakeMessageType: ServerHelloDone
                handshakeMessageLen: 0x0
                processHandshakeMessage:
                  MessageType: ServerHelloDone
                  Queueing HelloDone message.
                DequeuedMessageType: ServerHelloDone
                OK to ServerHelloDone!
                No client certificate required by the server.
                Encrypted pre-master secret with server certificate RSA public key is OK.
                Sending ClientKeyExchange...
                Sent ClientKeyExchange message.
                Sending ChangeCipherSpec...
                Sent ChangeCipherSpec message.
                Derived keys.
                Installed new outgoing security params.
                Sending FINISHED message..
                algorithm: des
                keyLength: 192
                Sent FINISHED message..
                ccsProtocolType: 1
                handshakeMessageType: HandshakeFinished
                handshakeMessageLen: 0xc
                processHandshakeMessage:
                  MessageType: HandshakeFinished
                  FinishedMsgLen: 12
                  Queueing Finished message.
                Dequeue the FINISHED message...
                Dequeued Finished message.
                Handshake completed successfully.
                Secure Channel Established.
                connectElapsedMs: 593
                -- BuildGetRequest --
                Not auto-adding cookies.
                sendElapsedMs: 0
                StatusCode: 200
                StatusText: OK
                Reading response body...
                No content-length header field.
                transferEncoding: chunked
                sslContentLength: 0
                extraLen: 16
                readResponseElapsedMs: 1313
                CompressedSize: 6285
                UncompressedSize: 24588
    ResponseHdrCharset: UTF-8
    NumUtf8Bytes: 24588
    Success.

END
GO