Get the Byte Count of the Last SFTP Read

See more SFTP Examples

Demonstrates the Chilkat SFtp.LastReadNumBytes method, which returns the number of bytes received by the most recent read for a handle. The only argument is the handle.

-- 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.LastReadNumBytes method, which returns the number of bytes received by
    --  the most recent read for a handle.  The only argument is the handle.

    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

    DECLARE @handle nvarchar(4000)
    EXEC sp_OAMethod @sftp, 'OpenFile', @handle OUT, 'subdir/data.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

    DECLARE @bd int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

    DECLARE @chunkSize int
    SELECT @chunkSize = 4096
    DECLARE @reading int
    SELECT @reading = 1
    WHILE @reading
      BEGIN
        EXEC sp_OAMethod @sftp, 'ReadFileBd', @success OUT, @handle, @chunkSize, @bd
        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 @bd
            RETURN
          END

        --  Report how many bytes the last read actually returned.  A normal EOF read reports 0.
        DECLARE @lastCount int
        EXEC sp_OAMethod @sftp, 'LastReadNumBytes', @lastCount OUT, @handle


        PRINT 'Last read returned ' + @lastCount + ' bytes.'

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

    EXEC sp_OAGetProperty @bd, 'NumBytes', @iTmp0 OUT

    PRINT 'Total: ' + @iTmp0 + ' bytes.'

    EXEC sp_OAMethod @sftp, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @sftp
    EXEC @hr = sp_OADestroy @bd


END
GO