(SQL Server) Asynchronous FTP Download

The Chilkat FTP component supports asynchronous uploads and downloads. This examples demonstrates doing a download in a background thread. The download is started by calling AsyncGetFileStart. While the download is in progress, your program may do other tasks. You may periodically check the AsyncFinished property to determine when the transfer is finished. The example below provides the remainder of the details.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @ftp int
    -- Use "Chilkat_9_5_0.Ftp2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Ftp2', @ftp OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @ftp, 'Hostname', 'ftp.myftpserver.com'
    EXEC sp_OASetProperty @ftp, 'Username', 'test'
    EXEC sp_OASetProperty @ftp, 'Password', 'test'

    -- Connect and login to the FTP server.
    DECLARE @success int
    EXEC sp_OAMethod @ftp, 'Connect', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    DECLARE @localFilename nvarchar(4000)
    SELECT @localFilename = 'hamlet.xml'
    DECLARE @remoteFilename nvarchar(4000)
    SELECT @remoteFilename = 'hamlet.xml'

    -- Begin the download in a background thread.
    -- Only 1 background upload or download may be active at any time.
    -- (per instance of an FTP object)
    EXEC sp_OAMethod @ftp, 'AsyncGetFileStart', @success OUT, @remoteFilename, @localFilename
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    -- The application is now free to do anything else
    -- while the file is downloading.
    -- For this example, we'll simply sleep and periodically
    -- check to see if the transfer if finished.  While checking
    -- however, we'll report on the progress in both number
    -- of bytes tranferred and performance in bytes/second.
    EXEC sp_OAGetProperty @ftp, 'AsyncFinished', @iTmp0 OUT
    WHILE @iTmp0 <> 1
      BEGIN

        EXEC sp_OAGetProperty @ftp, 'AsyncBytesReceived', @iTmp0 OUT

        PRINT @iTmp0 + ' bytes received'
        EXEC sp_OAGetProperty @ftp, 'DownloadTransferRate', @iTmp0 OUT

        PRINT @iTmp0 + ' bytes per second'

        -- Sleep 1 second.
        EXEC sp_OAMethod @ftp, 'SleepMs', NULL, 1000

      END
    EXEC sp_OAGetProperty @ftp, 'AsyncBytesReceived', @iTmp0 OUT

    PRINT @iTmp0 + ' total bytes received'
    EXEC sp_OAGetProperty @ftp, 'DownloadTransferRate', @iTmp0 OUT

    PRINT @iTmp0 + ' final bytes per second'

    -- Did the download succeed?
    EXEC sp_OAGetProperty @ftp, 'AsyncSuccess', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN

        PRINT 'File Downloaded!'
      END
    ELSE
      BEGIN
        -- The error information for asynchronous ops
        -- is in AsyncLog as opposed to LastErrorText
        EXEC sp_OAGetProperty @ftp, 'AsyncLog', @sTmp0 OUT
        PRINT @sTmp0
      END

    EXEC sp_OAMethod @ftp, 'Disconnect', @success OUT

    EXEC @hr = sp_OADestroy @ftp


END
GO