SQL Server

Read Text from a Remote SFTP File (Sequential)

Demonstrates the Chilkat SFtp.ReadFileText method, which reads up to a number of bytes from the current position of an open handle and decodes them to text. The arguments are the handle, the maximum number of bytes, and the charset. This example reads in chunks until end-of-file.

Background: Reading a file in bounded chunks keeps memory use predictable regardless of file size, which is why the loop reads a fixed amount and repeats until Eof. A subtlety worth knowing: the byte limit is applied to the encoded bytes, so a chunk boundary could in principle fall in the middle of a multibyte character — accumulating the pieces and decoding the whole avoids any such issue in practice here since the text is reassembled in a StringBuilder.

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 SFtp.ReadFileText method, which reads up to a number of bytes from the current
    --  position of an open handle and decodes them to text.  The 1st argument is the handle, the 2nd
    --  is the maximum number of bytes, and the 3rd is the charset.

    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 file for reading.
    DECLARE @handle nvarchar(4000)
    EXEC sp_OAMethod @sftp, 'OpenFile', @handle OUT, 'subdir/notes.txt', 'readOnly', 'openExisting'
    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 the file in chunks until end-of-file, accumulating the text.
    DECLARE @sbContent int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbContent OUT

    DECLARE @chunkSize int
    SELECT @chunkSize = 4096
    DECLARE @reading int
    SELECT @reading = 1
    WHILE @reading
      BEGIN
        DECLARE @chunk nvarchar(4000)
        EXEC sp_OAMethod @sftp, 'ReadFileText', @chunk OUT, @handle, @chunkSize, 'utf-8'
        EXEC sp_OAMethod @sftp, 'LastReadFailed', @iTmp0 OUT, @handle
        IF @iTmp0
          BEGIN
            EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @sftp
            EXEC @hr = sp_OADestroy @sbContent
            RETURN
          END

        EXEC sp_OAMethod @sbContent, 'Append', @success OUT, @chunk

        --  Stop once the end of the file has been reached.
        EXEC sp_OAMethod @sftp, 'Eof', @iTmp0 OUT, @handle
        SELECT @reading = Not @iTmp0
      END

    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 @sbContent
        RETURN
      END

    EXEC sp_OAMethod @sbContent, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    EXEC sp_OAMethod @sftp, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @sftp
    EXEC @hr = sp_OADestroy @sbContent


END
GO