Get the Files Transferred by an SFTP Sync

See more SFTP Examples

Demonstrates the Chilkat SFtp.GetSyncedFiles method, which reports the relative paths processed by the most recent SyncTreeUpload or SyncTreeDownload. The only argument is a StringTable that receives the paths. Directory paths end with / so they can be distinguished from files.

Note: This example uses relative local paths, which are resolved against the application's current working directory. Absolute local paths may also be used. Supply the paths appropriate to your own environment.

-- 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 SFtp.GetSyncedFiles method, which reports the relative paths processed by the
    --  most recent SyncTreeUpload or SyncTreeDownload.  The only argument is a StringTable that
    --  receives the paths.

    DECLARE @sftp int
    EXEC @hr = sp_OACreate 'Chilkat.SFtp', @sftp OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  Connect, authenticate, and initialize the SFTP subsystem.
    DECLARE @port int
    SELECT @port = 22
    EXEC sp_OAMethod @sftp, 'Connect', @success OUT, 'sftp.example.com', @port
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    --  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.
    DECLARE @password nvarchar(4000)
    SELECT @password = 'mySshPassword'

    EXEC sp_OAMethod @sftp, 'AuthenticatePw', @success OUT, 'mySshLogin', @password
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

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

    --  Perform a sync.
    DECLARE @mode int
    SELECT @mode = 5
    DECLARE @recurse int
    SELECT @recurse = 1
    EXEC sp_OAMethod @sftp, 'SyncTreeDownload', @success OUT, '/var/www/html', 'qa_output/website', @mode, @recurse
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    --  Get the list of files (and, for downloads, directories) that were actually transferred.
    --  Directory paths end with "/" so they can be told apart from file paths.
    DECLARE @synced int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @synced OUT

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

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


    PRINT 'Synced ' + @n + ' item(s):'
    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 @sftp
            EXEC @hr = sp_OADestroy @synced
            RETURN
          END

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

    EXEC sp_OAMethod @sftp, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @sftp
    EXEC @hr = sp_OADestroy @synced


END
GO