SQL Server

Resolve an Absolute Remote Path with SFTP RealPath

Demonstrates the Chilkat SFtp.RealPath method, which asks the server to canonicalize a path and returns the resulting absolute remote path. The first argument is the original path and the second is an optional compose path (SFTP v5 or later); pass an empty string when no composition is needed.

Background: The server — not the client — owns the remote filesystem's layout, so path canonicalization must happen on the server. RealPath resolves ., .., and symbolic links into a single absolute path, which is the reliable way to learn the session's home directory (canonicalize .) or to turn a relative path into an absolute one before storing or displaying it. On SFTP v5 and later, the compose path can additionally join or override the original.

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.RealPath method, which asks the server to canonicalize a path and
    --  returns the resulting absolute remote path.  The 1st argument is the original path and the
    --  2nd is an optional compose path (SFTP v5+), which may be an empty string.

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

    --  Step 1: Connect the SSH transport.  Port 22 is the usual port.
    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

    --  Step 3: Open the SFTP subsystem.  This must be done after authentication and before any
    --  file or directory operations.
    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

    --  Resolve "." to the absolute path of the session's default (home) directory.  No compose path
    --  is needed, so the 2nd argument is an empty string.
    DECLARE @homeDir nvarchar(4000)
    EXEC sp_OAMethod @sftp, 'RealPath', @homeDir OUT, '.', ''
    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 'Home directory: ' + @homeDir

    --  Canonicalize a relative path with "..", resolving it to an absolute path.
    DECLARE @resolved nvarchar(4000)
    EXEC sp_OAMethod @sftp, 'RealPath', @resolved OUT, 'subdir/../otherdir', ''
    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 'Resolved path: ' + @resolved

    EXEC sp_OAMethod @sftp, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @sftp


END
GO