SQL Server

Delete Local Files that Do Not Exist on the FTP Server

See more FTP Examples

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

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  This example requires the Chilkat API to have been previously unlocked.
    --  See Global Unlock Sample for sample code.

    DECLARE @ftp int
    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.
    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
    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
    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