SQL Server
SQL Server
IMAP Use Existing SSH Tunnel
Demonstrates how to connect to an IMAP server using an existing SSH tunnel. This creates a new channel within the SSH tunnel, which can be a TCP or TLS connection, and is connected to a remote IMAP server through port forwarding.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
-- 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 assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @tunnel int
EXEC @hr = sp_OACreate 'Chilkat.Socket', @tunnel OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @sshHostname nvarchar(4000)
SELECT @sshHostname = 'sftp.example.com'
DECLARE @sshPort int
SELECT @sshPort = 22
-- Connect to an SSH server and establish the SSH tunnel:
EXEC sp_OAMethod @tunnel, 'SshOpenTunnel', @success OUT, @sshHostname, @sshPort
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
RETURN
END
-- Authenticate with the SSH server via a login/password
-- or with a public key.
-- This example demonstrates SSH password authentication.
-- Note: This is not authenticating with the IMAP server, it is
-- for authenticating with the SSH server, which is separate.
EXEC sp_OAMethod @tunnel, 'SshAuthenticatePw', @success OUT, 'mySshLogin', 'mySshPassword'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
RETURN
END
-- OK, the SSH tunnel is setup. Now open a channel within the tunnel.
-- Once the channel is obtained, the Socket API may
-- be used exactly the same as usual, except all communications
-- are sent through the channel in the SSH tunnel.
-- Any number of channels may be created from the same SSH tunnel.
-- Multiple channels may coexist at the same time.
DECLARE @imap int
EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
-- Let the IMAP object also use the SSH tunnel
EXEC sp_OAMethod @imap, 'UseSshTunnel', @success OUT, @tunnel
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
RETURN
END
-- Connect to an IMAP server via the already established SSH tunnel.
-- Because the SSH tunnel has been previously setup,
-- this does not establish a direct connection with the IMAP
-- server. It directs the SSH server to establish the connection.
-- In this example, the IMAP server requires SSL/TLS. The TLS connection
-- will be enclosed within the SSH tunnel.
EXEC sp_OASetProperty @imap, 'Ssl', 1
EXEC sp_OASetProperty @imap, 'Port', 993
EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.my-imap-server.com'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
RETURN
END
-- Authenticate with the IMAP server via the SSH tunnel.
EXEC sp_OAMethod @imap, 'Login', @success OUT, 'myLogin', 'myPassword'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
RETURN
END
-- Select an IMAP mailbox
EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
RETURN
END
-- How many messages in Inbox?
DECLARE @msgCount int
EXEC sp_OAGetProperty @imap, 'NumMessages', @msgCount OUT
IF @msgCount = 0
BEGIN
PRINT 'No messages found.'
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
RETURN
END
DECLARE @upperBound int
SELECT @upperBound = 10
IF @msgCount < @upperBound
BEGIN
SELECT @upperBound = @msgCount
END
-- Download up to the 1st 10 messages.
DECLARE @email int
EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
DECLARE @i int
DECLARE @bUid int
SELECT @bUid = 0
SELECT @i = 1
WHILE @i <= @upperBound
BEGIN
EXEC sp_OAMethod @imap, 'FetchEmail', @success OUT, 0, @i, @bUid, @email
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @email
RETURN
END
EXEC sp_OAGetProperty @email, 'From', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @email, 'Subject', @sTmp0 OUT
PRINT @sTmp0
SELECT @i = @i + 1
END
-- Disconnect from the IMAP server.
-- The SSH tunnel remains open.
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @email
RETURN
END
-- It is possible to re-use the existing SSH tunnel for the next connection:
EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.my-imap-server2.com'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @email
RETURN
END
-- Review the LastErrorText to see that the connection was made via the SSH tunnel:
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
-- This closes the SSH channel, which is the connection with the IMAP server.
-- It does not close the SSH tunnel
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @email
RETURN
END
-- Finally, close the SSH tunnel.
-- Note: If other objects are using the same SSH tunnel, the tunnel is not
-- actually closed. It is only closed when the last object using the tunnel closes it.
-- At this point, both the imap and tunnel objects have references to the internal
-- SSH tunnel. We need to close each.
-- This call doesn't actually close the tunnel yet, because the tunnel object still
-- holds an internal reference.
EXEC sp_OAMethod @imap, 'SshCloseTunnel', @success OUT
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @email
RETURN
END
-- This actually closes the SSH tunnel because it's the last object using it.
EXEC sp_OAMethod @tunnel, 'SshCloseTunnel', @success OUT
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @tunnel, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @email
RETURN
END
PRINT 'IMAP pre-existing SSH tunnel example completed.'
EXEC @hr = sp_OADestroy @tunnel
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @email
END
GO