SQL Server

SFTP Fsync -- Flush an Open File on the Server

See more SFTP Examples

Demonstrates how to flush the contents of an open file on the server. This example only works for servers that implement the fsync@openssh.com extension.

Note: This example requires Chilkat v9.5.0.71 or later.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    -- Pass a domain or IP address..
    EXEC sp_OAMethod @sftp, 'Connect', @success OUT, 'my-sftp-server.com', 22
    IF @success = 1
      BEGIN
        EXEC sp_OAMethod @sftp, 'AuthenticatePw', @success OUT, 'mySFtpLogin', 'mySFtpPassword'
      END
    IF @success = 1
      BEGIN
        EXEC sp_OAMethod @sftp, 'InitializeSftp', @success OUT
      END
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    -- Open a file on the server for writing.
    DECLARE @handle nvarchar(4000)
    EXEC sp_OAMethod @sftp, 'OpenFile', @handle OUT, 'myTest.txt', 'writeOnly', 'createTruncate'
    EXEC sp_OAGetProperty @sftp, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    -- Write some text to the file:
    EXEC sp_OAMethod @sftp, 'WriteFileText', @success OUT, @handle, 'ansi', 'abcdefghijklmnopqrstuvwxyz'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    -- Make sure the server flushes what we wrote to the disk..
    -- (this is requires a server that implements the fsync@openssh.com SFTP protocol extension)
    EXEC sp_OAMethod @sftp, 'Fsync', @success OUT, @handle
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    -- ....
    -- ....

    EXEC sp_OAMethod @sftp, 'WriteFileText', @success OUT, @handle, 'ansi', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END

    -- Close the file.
    EXEC sp_OAMethod @sftp, 'CloseHandle', @success OUT, @handle
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @sftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sftp
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @sftp


END
GO