SQL Server Stored Procedure Examples

Chilkat • HOME • Android™ • ASP • Visual Basic • VB.NET • C# • iOS (IPhone) • Objective-C • C++ • C • MFC • Delphi • FoxPro • Java • Perl •
PHP Extension • PHP ActiveX • Python • PowerShell • Ruby • SQL Server • VBScript

SQL Server
Stored Procedure Examples

Quick Start
Encryption
File Access
IMAP
POP3
SMTP
Email Object
DKIM / DomainKey
FTP
HTML Conversion
HTTP
MHT
MIME
NTLM
RSA
Diffie-Hellman
DSA
Socket
Spider
SSH Key
SSH
SSH Tunnel
SFTP
String
Tar
Upload
XML
XMP
Zip

Amazon S3
Bz2
CSV
FileAccess
Byte Array
RSS
Atom
Self-Extractor

Receiving a String

Demonstrates how to receive character data on a socket connection. The code for this example immediately follows the session logs (below). You should first read throught the code w/ comments and then refer back to these session logs...

SessionLog for 1st ReceiveString method call

SPACE chars are shown as \x20. It is easy to see that the accented characters (á, é, í, ó, ú, ý) are each represented by a single byte in the received data: \xE1, \xE9, 0xED, \xF3, \xFA, and \xFD. 

ReceiveString1: HTTP/1.1\x20200\x20OK\r\n
	Content-Length:\x20140\r\n
	Content-Type:\x20text/html\r\n
	Last-Modified:\x20Thu,\x2001\x20Oct\x202009\x2012:29:48\x20GMT\r\n
	Accept-Ranges:\x20bytes\r\n
	ETag:\x20\"2e66a6dd9242ca1:28b\"\r\n
	Server:\x20Microsoft-IIS/6.0\r\n
	X-Powered-By:\x20ASP.NET\r\n
	Date:\x20Thu,\x2001\x20Oct\x202009\x2013:56:53\x20GMT\r\n
	\r\n
	<html>\r\n
	<head>\r\n
	<meta\x20http-equiv=\"content-type\"\x20content=\"text/html;charset=iso-8859-1\"
	>\r\n
	</head>\r\n
	<body>\r\n
	\xE1,\x20\xE9,\x20\xED,\x20\xF3,\x20\xFA,\x20\xFD\r\n
	</body>\r\n
	</html>

SessionLog for 2nd ReceiveString method call

It is easy to see that the accented characters (á, é, í, ó, ú, ý) are each represented by two bytes in the received data: \xC3\xA1, \xC3\xA9, etc. 

ReceiveString1: HTTP/1.1\x20200\x20OK\r\n
	Content-Length:\x20141\r\n
	Content-Type:\x20text/html\r\n
	Last-Modified:\x20Thu,\x2001\x20Oct\x202009\x2012:29:48\x20GMT\r\n
	Accept-Ranges:\x20bytes\r\n
	ETag:\x20\"a6968dd9242ca1:28b\"\r\n
	Server:\x20Microsoft-IIS/6.0\r\n
	X-Powered-By:\x20ASP.NET\r\n
	Date:\x20Thu,\x2001\x20Oct\x202009\x2013:56:53\x20GMT\r\n
	\r\n
	<html>\r\n
	<head>\r\n
	<meta\x20http-equiv=\"content-type\"\x20content=\"text/html;charset=utf-8\">\r\n
	</head>\r\n
	<body>\r\n
	\xC3\xA1,\x20\xC3\xA9,\x20\xC3\xAD,\x20\xC3\xB3,\x20\xC3\xBA,\x20\xC3\xBD\r\n
	</body>\r\n
	</html>

Download Chilkat Socket ActiveX

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

    DECLARE @success int

    EXEC sp_OAMethod @socket, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        PRINT 'Failed to unlock component'
        RETURN
      END

    --  To demonstrate receiving character (i.e. not binary) data,
    --  this example will connect to the chilkatsoft.com web server
    --  and download a very short and simple HTML page.
    --  (Note: You would typically use the Chilkat HTTP component
    --  to communicate with a web server.  We only do it here to
    --  provide an example that is runnable such that socket peer
    --  (i.e. the other end of the socket connection) is already
    --  available.

    --  So.. let's first connect the socket to the remote peer.
    DECLARE @ssl int

    SELECT @ssl = 0
    DECLARE @maxWaitMillisec int

    SELECT @maxWaitMillisec = 20000
    DECLARE @port int

    SELECT @port = 80
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, 'www.chilkatsoft.com', @port, @ssl, @maxWaitMillisec
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Set maximum timeouts for reading an writing (in millisec)
    EXEC sp_OASetProperty @socket, 'MaxReadIdleMs', 10000
    EXEC sp_OASetProperty @socket, 'MaxSendIdleMs', 10000

    --  Send a simple HTTP GET request to download
    --  an HTML file on the server named "iso_chars.html"
    --  This web page contains the following characters
    --  using the iso-8859-1 encoding:
    --  á, é, í, ó, ú, ý

    --  The Chilkat Socket API has a convenience method named
    --  BuildHttpGetRequest to make it easy to send an HTTP GET
    --  for a URL:
    DECLARE @getRequest nvarchar(4000)

    EXEC sp_OAMethod @socket, 'BuildHttpGetRequest', @getRequest OUT, 'http://www.chilkatsoft.com/iso_chars.html'

    --  Send the GET request to the HTTP server:
    EXEC sp_OAMethod @socket, 'SendString', @success OUT, @getRequest
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Before calling ReceiveString to get the HTTP response,
    --  let's turn on session logging so we can examine the
    --  actual bytes received:
    EXEC sp_OASetProperty @socket, 'KeepSessionLog', 1

    --  IMPORTANT:
    --  Tell the component the character encoding of the string
    --  data to be received by setting the StringCharset property.
    --  This is extremely important.  The socket component/library must know how
    --  to interpret the received bytes.  For example,
    --  the character "á" will be represented as a single byte
    --  having the hex value 0xE1 when encoded using iso-8859-1,
    --  but will arrive as a sequence of 2 bytes having the values
    --  0xC3 0xA1 when encoded using utf-8.
    --  Given that strings are returned as Unicode or utf-8 in most
    --  programming languages, the socket component must know how
    --  to interpret the received bytes to properly construct the
    --  string to be returned to the caller.
    EXEC sp_OASetProperty @socket, 'StringCharset', 'iso-8859-1'

    --  OK, get the response:
    DECLARE @response nvarchar(4000)

    EXEC sp_OAMethod @socket, 'ReceiveString', @response OUT
    IF @response Is NULL 
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    --  Examine the received string:

    PRINT @response

    PRINT '---------------------------'

    --  Important: ReceiveString may not receive the entire response.
    --  It simply receives whatever data is already available from
    --  the connected peer.  To simplify the example, we're assuming
    --  the complete response is received -- and it is likely
    --  because the response is short.

    --  OK, let's examine the session log to see the actual bytes
    --  received.  (The session logging was turned on just
    --  before the call to ReceiveBytes, so we won't see the activity
    --  prior to it.)
    --  The SessionLogEncoding property controls the output format
    --  of the session log.  It defaults to "esc" for escaped ASCII, which
    --  is a good way of viewing mostly US-ASCII character data.
    --  We'll be able to see the binary bytes in between the printable
    --  ASCII characters as hex values escaped with "\x".
    EXEC sp_OAGetProperty @socket, 'SessionLog', @sTmp0 OUT

    PRINT @sTmp0

    --  (Refer to the SessionLog for the 1st ReceiveString above for the expected results.)

    --  Close the connection with the server
    --  Wait a max of 20 seconds (20000 millsec)
    EXEC sp_OAMethod @socket, 'Close', NULL, 20000

    --  -----------------------------------------------------------------
    --  Now we'll download a utf-8 encoded HTML page
    --  to see how the bytes received for the same characters
    --  are different.  The Socket component must know how
    --  to interpret these bytes as characters, and (as discussed
    --  before) this is the purpose of the StringCharset property.

    --  Connect to the chilkatsoft.com HTTP server again.
    EXEC sp_OAMethod @socket, 'Connect', @success OUT, 'www.chilkatsoft.com', @port, @ssl, @maxWaitMillisec
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  This time, get an HTML page where the characters
    --  are encoded using utf-8.
    EXEC sp_OAMethod @socket, 'BuildHttpGetRequest', @getRequest OUT, 'http://www.chilkatsoft.com/utf8_chars.html'

    --  Send the GET request to the HTTP server:
    EXEC sp_OAMethod @socket, 'SendString', @success OUT, @getRequest
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Tell the component to interpret the received bytes as
    --  utf-8.  (This only applies to methods that receive strings,
    --  such as ReceiveString, ReceiveUntilMatch, etc.
    --  Methods that receive bytes, such as ReceiveBytes, do
    --  not try to interpret the bytes received from the connected
    --  peer -- they simply return the bytes received.)
    EXEC sp_OASetProperty @socket, 'StringCharset', 'utf-8'

    --  Clear the session log so we can examine it again for the next
    --  call to ReceiveString:
    EXEC sp_OAMethod @socket, 'ClearSessionLog', NULL

    EXEC sp_OAMethod @socket, 'ReceiveString', @response OUT
    IF @response Is NULL 
      BEGIN
        EXEC sp_OAGetProperty @socket, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    --  The characters should display correctly:

    PRINT '---------------------------'

    PRINT @response

    PRINT '---------------------------'

    --  Examine the session log.  You'll see that the actual bytes
    --  received for the characters were 2-bytes/char.  Because
    --  the StringCharset was correctly set, the socket component
    --  knew how to interpret the bytes to arrive at the appropriate
    --  characters.
    EXEC sp_OAGetProperty @socket, 'SessionLog', @sTmp0 OUT

    PRINT @sTmp0

    --  (Refer to the SessionLog for the 2nd ReceiveString above for the expected results.)

    EXEC sp_OAMethod @socket, 'Close', NULL, 20000

END
GO
 

© 2000-2010 Chilkat Software, Inc. All Rights Reserved.