List a Remote Directory on an SFTP Server

See more SFTP Examples

Demonstrates the Chilkat SFtp.ReadDirListing method, which reads all remaining entries from an open directory handle into an SFtpDir object. The first argument is the handle returned by OpenDir and the second is the SFtpDir that receives the listing. The example iterates the entries with NumFilesAndDirs and FileAt, reading each SFtpFile's Filename, IsDirectory, Size64, and LastModifiedTimeStr.

-- 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 @sTmp1 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the SFtp.ReadDirListing method, which reads all remaining entries from an open
    --  directory handle into an SFtpDir object.  The 1st argument is the handle (from OpenDir) and
    --  the 2nd is the SFtpDir that receives the listing.

    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

    --  Open the remote directory to be listed.  OpenDir returns a handle string.
    DECLARE @handle nvarchar(4000)
    EXEC sp_OAMethod @sftp, 'OpenDir', @handle OUT, 'subdir'
    EXEC sp_OAGetProperty @sftp, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    --  Read all directory entries into an SFtpDir object.
    DECLARE @dirListing int
    EXEC @hr = sp_OACreate 'Chilkat.SFtpDir', @dirListing OUT

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

    --  The listing has been read, so the directory handle can be closed now.
    EXEC sp_OAMethod @sftp, 'CloseHandle', @success OUT, @handle
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        EXEC @hr = sp_OADestroy @dirListing
        RETURN
      END

    --  Iterate the entries.  Entries are in server order (Chilkat does not sort the listing).
    DECLARE @n int
    EXEC sp_OAGetProperty @dirListing, 'NumFilesAndDirs', @n OUT


    PRINT 'Directory contains ' + @n + ' entries:'

    DECLARE @fileObj int
    EXEC @hr = sp_OACreate 'Chilkat.SFtpFile', @fileObj OUT

    DECLARE @i int

    SELECT @i = 0
    WHILE @i <= @n - 1
      BEGIN
        --  FileAt fills the SFtpFile object with the entry at the given index.
        EXEC sp_OAMethod @dirListing, 'FileAt', @success OUT, @i, @fileObj
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @dirListing, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @sftp
            EXEC @hr = sp_OADestroy @dirListing
            EXEC @hr = sp_OADestroy @fileObj
            RETURN
          END

        EXEC sp_OAGetProperty @fileObj, 'IsDirectory', @iTmp0 OUT
        IF @iTmp0
          BEGIN

            EXEC sp_OAGetProperty @fileObj, 'Filename', @sTmp0 OUT
            PRINT '  [DIR]  ' + @sTmp0
          END
        ELSE
          BEGIN
            --  Size64 is a 64-bit size; LastModifiedTimeStr is the modification time as text.

            EXEC sp_OAGetProperty @fileObj, 'Filename', @sTmp0 OUT

            EXEC sp_OAGetProperty @fileObj, 'Size64', @iTmp0 OUT

            EXEC sp_OAGetProperty @fileObj, 'LastModifiedTimeStr', @sTmp1 OUT

            PRINT '  ' + @sTmp0 + '  (' + @iTmp0 + ' bytes, modified ' + @sTmp1 + ')'
          END
        SELECT @i = @i + 1
      END

    EXEC sp_OAMethod @sftp, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @sftp
    EXEC @hr = sp_OADestroy @dirListing
    EXEC @hr = sp_OADestroy @fileObj


END
GO