SQL Server

Get the Files Affected by an FTP Sync

Demonstrates the Chilkat Ftp2.GetSyncedFiles method, which reports the relative paths affected by the most recent synchronization. The only argument is a StringTable that receives the paths (uploaded, downloaded, or deleted).

Background: A sync call reports only overall success, but you usually want to know what actually changed — for logging, for triggering follow-up work on just the new files, or to confirm an incremental sync did the minimal transfer. An empty result is meaningful too: it means everything was already up to date. Call it immediately after the sync, before another operation replaces the recorded list.

Chilkat SQL Server Downloads

Download Chilkat for SQL 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 @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the Ftp2.GetSyncedFiles method, which reports the relative paths affected by the
    --  most recent synchronization operation.  The only argument is a StringTable that receives the
    --  paths.

    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', 'myFtpLogin'

    --  Normally you would not hard-code the password in source.  You should instead obtain it
    --  from an interactive prompt, environment variable, or a secrets vault.
    EXEC sp_OASetProperty @ftp, 'Password', 'myPassword'

    EXEC sp_OAMethod @ftp, 'Connect', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    EXEC sp_OAMethod @ftp, 'ChangeRemoteDir', @success OUT, 'public_html'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    --  Perform a sync.
    DECLARE @mode int
    SELECT @mode = 6
    EXEC sp_OAMethod @ftp, 'SyncLocalTree', @success OUT, 'qa_output/site', @mode
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    --  Get the list of files that were uploaded, downloaded, or deleted by that sync.
    DECLARE @synced int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @synced OUT

    EXEC sp_OAMethod @ftp, 'GetSyncedFiles', NULL, @synced

    DECLARE @n int
    EXEC sp_OAGetProperty @synced, 'Count', @n OUT

    PRINT 'Affected files: ' + @n
    DECLARE @i int

    SELECT @i = 0
    WHILE @i <= @n - 1
      BEGIN
        DECLARE @relPath nvarchar(4000)
        EXEC sp_OAMethod @synced, 'StringAt', @relPath OUT, @i
        EXEC sp_OAGetProperty @synced, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 = 0
          BEGIN
            EXEC sp_OAGetProperty @synced, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @ftp
            EXEC @hr = sp_OADestroy @synced
            RETURN
          END

        PRINT @relPath
        SELECT @i = @i + 1
      END

    EXEC sp_OAMethod @ftp, 'Disconnect', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        EXEC @hr = sp_OADestroy @synced
        RETURN
      END

    EXEC @hr = sp_OADestroy @ftp
    EXEC @hr = sp_OADestroy @synced


END
GO