SQL Server

Read Text at a 32-bit Offset from a Remote SFTP File

Demonstrates the Chilkat SFtp.ReadFileText32 method, which reads up to a number of bytes from a specific 32-bit byte offset of an open handle and decodes them to text. The arguments are the handle, the offset, the maximum number of bytes, and the charset.

Background: Reading from an explicit offset lets you fetch just the part of a file you need — a header, a record at a known position, a slice of a large log — without downloading everything before it. As with offset writes, the offset counts bytes rather than characters, so it must respect the encoded width of any multibyte content.

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.ReadFileText32 method, which reads up to a number of bytes from a
    --  specific 32-bit byte offset of an open handle and decodes them to text.  The 1st argument is
    --  the handle, the 2nd is the offset, the 3rd is the maximum number of bytes, and the 4th 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

    DECLARE @handle nvarchar(4000)
    EXEC sp_OAMethod @sftp, 'OpenFile', @handle OUT, 'subdir/record.dat', '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 5 bytes starting at byte offset 10, without first reading the bytes before it.
    DECLARE @offset int
    SELECT @offset = 10
    DECLARE @numBytes int
    SELECT @numBytes = 5
    DECLARE @text nvarchar(4000)
    EXEC sp_OAMethod @sftp, 'ReadFileText32', @text OUT, @handle, @offset, @numBytes, 'utf-8'
    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

    PRINT @text

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

    EXEC sp_OAMethod @sftp, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @sftp


END
GO