Whamware.Crypt for COM

Whamware.Crypt for IIS

Whamware.Crypt for SQL Server

-- The following examples illustrate how to use xp_infinity within your SQL Scripts to perform
-- mathematical calculations on numbers longer than SQL Server native data types can handle.

-- -----------------------------------------------
-- Example script executes xp_infinity, adding two
-- string numbers and prints the result.
-- -----------------------------------------------

declare @output varchar(8000),
        @v1 varchar(8000),
        @v2 varchar(8000)

set @v1 = replicate('9', 7999) + '8'
set @v2 = '1'

exec xp_infinity @operator = 'add',
                 @output = @output OUTPUT,
                 @v1 = @v1,
                 @v2 = @v2

-- @output should equal replicate('9', 8000)

print @output

-- -----------------------------------------------
-- Example script executes xp_infinity, adding two
-- string numbers and writes the result to a file,
-- overwriting the file if it already exists.
-- -----------------------------------------------

declare @output varchar(8000),
        @v1 varchar(8000),
        @v2 varchar(8000)

set @output = '\\MyServer\MyShare\SomeDir\MyFile.SomeExt'
set @v1 = replicate('9', 7999) + '8'
set @v2 = '1'

exec xp_infinity @operator = 'add',
                 @output = @output OUTPUT,
                 @overwrite = 1,
                 @v1 = @v1,
                 @v2 = @v2

-- There should not be an ASCII text file with the name
-- \\MyServer\MyShare\SomeDir\MyFile.SomeExt
-- that contains a string of 8000 9's.

-- -----------------------------------------------
-- Example script executes xp_infinity, adding two
-- string numbers and writing the result to a
-- database column, named [MyColumn].
-- -----------------------------------------------

declare @output varchar(8000),
        @v1 varchar(8000),
        @v2 varchar(8000)

Whamware.Infinity

1.  Using xp_regexpr to determine if specific text exists in a string of character data.

exec @rc = master.dbo.xp_regexpr @action = 'FIND',
                                 @count = @count OUTPUT,
                                 @expression = 'Robert',
                                 @input = 'Robert Johnson' + char(13) + char(10) + 'Robert Jones'

-- Executing this example will return an @count value of 2. If you change the @expression value
-- to '^Robert' the @count value will change to 1. This is because by default xp_regexpr treats all
-- input as a single string and basically ignores the CRLF characters in the middle of the string. To
-- overwrite this behavior you can change the @expression to '(?m}^Robert' and you sould once
-- again see an @count value of 2. If you were to change the @expression value to 'Donald' the
-- @count parameter would return a value of 0.

2.  Using xp_regexpr to replace text within a string of character data.

exec @rc = master.dbo.xp_regexpr @action = 'REPLACE',
                                 @count = @count OUTPUT,
                                 @expression = 'Robert',
                                 @input = 'Robert Johnson, Robert Jones',
                                 @output = @output OUTPUT,
                                 @replace = 'Bob'

-- Executing this example will return an @count value of 2 and an @output value of 'Bob Johnson, Bob Jones'.

3.  Using xp_regexpr to split text within a string of character data.

exec @rc = master.dbo.xp_regexpr @action = 'SPLIT',
                                 @count = @count OUTPUT,
                   

Whamware.RegEx

1.  Retrieve the MX record from a DNS server

declare @hr int

exec @hr = master.dbo.xp_dns @action = 'MX',
                             @domain = 'www.whamware.com',
                             @host = 'ns.somednsserver.com'

2.  Retrieve the SOA record from a DNS server

declare @output varchar(8000)

exec @hr = master.dbo.xp_dns @action = 'SOA',
                             @domain = 'www.whamware.com',
                             @host = 'ns.somednsserver.com',
                             @no_results = 1,
                             @output = @output OUTPUT

3.  Retrieve the ALL record from a DNS server

declare @auth bit,
        @count int,
        @rcode int,
        @truncated bit

exec @hr = master.dbo.xp_dns @action = 'ALL',
                             @auth = @auth OUTPUT,
                             @count = @count OUTPUT,
                             @domain = 'www.whamware.com',
                             @host = 'ns.somednsserver.com',
                        

Whamware.TCP/IP

1.  Retrieve a file from an FTP server

declare @hr int

exec @hr = master.dbo.xp_ftp @action = 'get',
                             @binary_mode = 1,
                             @host = 'ftp.myserver.com',
                             @local_file = 'C:\Projects\MyDownloads\SomeFile.zip',
                             @login = 'optional_user',
                             @passive_mode = 1,
                             @password = 'optional_pwd',
                             @remote_file = './downloads/SomeFile.zip'

2.  Send a file to an FTP server

exec @hr = master.dbo.xp_ftp @action = 'put',
                             @binary_mode = 1,
                             @host = 'ftp.myserver.com',
                             @local_file = 'C:\Projects\MyDownloads\SomeFile.zip',
                             @login = 'optional_user',
                             @passive_mode = 1,
                             @password = 'optional_pwd',
                             @remote_file = './downloads/SomeFile.zip'

3.  List all files in a directory

exec @hr = master.dbo.xp_ftp @action = 'list',
                   &n

Whamware.TCP/IP

1.  Initialize a new HTTP object.

declare @handle int,
        @hr int

exec @hr = master.dbo.xp_http @action = 'INIT',
                              @handle = @handle OUTPUT

2.  Retrieve a file from a HTTP server.

exec @hr = master.dbo.xp_http @action = 'GET',
                              @handle = @handle,
                              @local_file = 'C:\MyDocuments\yourfilename.zip',
                              @no_results = 1,
                              @url = 'www.yourdomainname.com/yourfilename.zip'

3.  Send a POST request to a HTTP server

declare @output varchar(8000)

exec @hr = master.dbo.xp_http @action = 'POST',
                              @handle = @handle,
                              @no_results = 1,
                              @output = @output OUTPUT,
                              @url = 'www.yourdomainname.com/user_login',
                              @url_data = 'user_name=MyName;user_pwd=MyPassword'

Whamware.TCP/IP

1.  Ping a server.

declare @hr int

exec @hr = master.dbo.xp_ping @action = 'PING',
                              @count = 4,
                              @host = '192.168.0.1',
                              @resolve = 1

2.  Perform a Trace Route to an IP address.

declare @hr int

exec @hr = master.dbo.xp_ping @action = 'TRACERT',
                              @host = '192.168.0.1',
                              @max_hops = 32,
                              @resolve = 1

3.  Capture the output of a call to xp_ping to a temporary table.

declare @hr int

create table #ping_results ( addr varchar(30),
                             host varchar(50),
                             time int,
                             message varchar(400) )

insert into #ping_results
exec @hr = master.dbo.xp_ping @action = 'TRACERT',
                              @host = '192.168.0.1',
                              @max_hops = 32,
                              @resolve = 1

Whamware.TCP/IP

1.  Retrieve a list of messages that are in a mail box, using a single call to xp_pop3 with an @action value of 'LIST'.

declare @hr int

create table #pop3_results (
  msg_num int,
  size int
)

insert into #pop3_results
exec @hr = master.dbo.xp_pop3 @action = 'LIST',
                              @host = 'mail.some.domain.com',
                              @login = 'who.am.i',
                              @password = 'mysecretpwd'

2.  Retrieve the headers of a message using a single call to xp_pop3 with an @action value of 'TOP'.

declare @hr int

create table #pop3_results (
  msg_id varchar(128),
  msg_date datetime,
  [from] varchar(256),
  [to] varchar(256),
  cc varchar(256),
  organization varchar(128),
  priority int,
  reply_to varchar(50),
  subject varchar(256),
  charset varchar(20),
  custom_headers text
)

insert into #pop3_results
exec @hr = master.dbo.xp_pop3 @action = 'TOP',
                              @host = 'mail.some.domain.com',
                              @login = 'who.am.i',
                              @msg_num = 1,
                              @password = 'mysecretpwd'

3.  Retrieve a message using a single call to xp_pop3 with an @action value of 'RETR'.

declare @hr int

create table #pop3_results (
  msg_id varchar(128),
  msg_date datetime,
  [from] varchar(256),
  [to] varchar(256)

Whamware.TCP/IP

1.  Send a simple text message with attachments using a single call to xp_smtp with an @action value of 'SEND'.

declare @hr int

exec @hr = master.dbo.xp_smtp @action = 'SEND',
                              @attach = 'C:\SomeDocument.doc;SomeSpreadsheet.xls',
                              @bcc = 'hidden.user@some.domain.com',
                              @cc = 'carbon.copy@some.other.domain.com',
                              @from = 'who.am.i@where.am.i.com',
                              @host = 'mail.some.domain.com',
                              @login = 'who.am.i',
                              @message = 'Attached are two files. Enjoy!',
                              @password = 'mysecretpwd',
                              @subject = 'This is a test message with 2 attachments',
                              @to = 'some.recipient@some.domain.com'

2.  Send a multi-part email message with a Text part, HTML part and a file attachment.

declare @handle int,
        @hr int,
        @mixed_part int,
        @part_handle int

set @handle = 0
set @mixed_part = 0
set @part_handle = 0

exec @hr = master.dbo.xp_smtp @action = 'INIT',
                              @bcc = 'hidden.user@some.domain.com',
    &nbs

Whamware.TCP/IP

--The following examples illustrate how to use xp_textio within your SQL Scripts to IMPORT and EXPORT data to and from SQL Server.

1.  Import a text document (ASCII or Unicode) to an image column using compression.

-- -----------------------------------------------------
-- This example imports data from a document to an image
-- column and uses ZLIB compression.
-- -----------------------------------------------------
exec xp_textio @action = 'IMPORT',
               @bind = 1,
               @column = 'MyImageColumn',
               @compress = 'ZLIB',
               @database = 'MyDatabase',
               @file_name = '\\MyServer\MyShare\MyFile.txt',
               @owner = 'dbo',    -- 'dbo' is the default
               @table = 'MyTable',
               @where = '[id] = 1'

2.  Export data from an image column using decompression to a file.

-- --------------------------------------------------
-- This example exports compressed data from an image
-- column to a file
-- --------------------------------------------------
exec xp_textio @action = 'EXPORT',
               @bind = 1,
               @column = 'MyImageColumn',
               @compress = 'ZLIB',
               @database = 'MyDatabase',
               @file_name = '\\MyServer\MyShare\MyFile.txt',
               @overwrite = 1,
               @owner = 'dbo',    -- 'dbo' is the default
               @table = 'MyTable',
               @where = '[id] = 1'

3.  Import a text document (ASCII or Unicode) to a ntext column.

-- --------------------------------------------------------
-- This example imports data from text document to an ntext
-- column. If the file is ASCII, UTF7, UTF8, UTF16-BE
-- UTF16-LE, UTF32-BE or UTF32-LE it will be converted to
-- UTF16-LE before storing it in the database
-- ------

Whamware.TextIo