(SQL Server) Delete Local Files that Do Not Exist on the FTP Server

Demonstrates how to get a list of local files in a directory tree that do not exist on the FTP server.

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
    -- 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.example.com'
    EXEC sp_OASetProperty @ftp, 'Username', 'login'
    EXEC sp_OASetProperty @ftp, 'Password', 'password'

    EXEC sp_OASetProperty @ftp, 'KeepSessionLog', 1

    -- 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

    -- Set the current remote directory to the root of
    -- the remote tree to be compared.
    EXEC sp_OAMethod @ftp, 'ChangeRemoteDir', @success OUT, 'abc123'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    -- Recursively descend the local directory tree
    -- and find the files that exist locally but not remotely.
    -- These are the files what would be uploaded via
    -- the SyncRemoteTree method call with mode = 1.
    -- (Mode 1 would upload all files that do not exist on the FTP server.)

    -- The actual uploading is avoided by setting the preview-only argument to 1.
    DECLARE @mode int
    SELECT @mode = 1
    DECLARE @descendTree int
    SELECT @descendTree = 1
    DECLARE @previewOnly int
    SELECT @previewOnly = 1
    EXEC sp_OAMethod @ftp, 'SyncRemoteTree2', @success OUT, '/temp/abc123', @mode, @descendTree, @previewOnly
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    -- The files what would've been uploaded are now available in the SyncPreview property,
    -- which contains a list of local file paths, one per line.
    -- A program can iterate over them like this:
    DECLARE @sa int
    -- Use "Chilkat_9_5_0.StringArray" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringArray', @sa OUT

    EXEC sp_OAGetProperty @ftp, 'SyncPreview', @sTmp0 OUT
    EXEC sp_OAMethod @sa, 'LoadFromText', NULL, @sTmp0

    DECLARE @fac int
    -- Use "Chilkat_9_5_0.FileAccess" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT

    DECLARE @numFiles int
    EXEC sp_OAGetProperty @sa, 'Count', @numFiles OUT
    DECLARE @i int
    SELECT @i = 0
    DECLARE @localFilePath nvarchar(4000)

    WHILE (@i < @numFiles)
      BEGIN
        EXEC sp_OAMethod @sa, 'GetString', @localFilePath OUT, @i

        PRINT @localFilePath

        -- An application can delete the file using Chilkat's file access object,
        -- or it can choose to use the native file API available in the programming language:
        EXEC sp_OAMethod @fac, 'FileDelete', @success OUT, @localFilePath
        IF @success <> 1
          BEGIN

            PRINT 'Failed to delete: ' + @localFilePath
          END

        SELECT @i = @i + 1
      END

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

    EXEC @hr = sp_OADestroy @ftp
    EXEC @hr = sp_OADestroy @sa
    EXEC @hr = sp_OADestroy @fac


END
GO