SQL Server

SSH ChannelReceiveUntilMatchN (Match Any of Several Prompts)

Demonstrates ChannelReceiveUntilMatchN, which receives until any one of several patterns is matched. This is useful when driving an interactive session where the shell prompt differs from device to device.

Note: This example deliberately requests a PTY. Matching a shell prompt only works in an interactive session — without a PTY the shell runs non-interactively and never prints a prompt at all. For scripted use, omit the PTY and match on a marker your own command echoes.

Background: Real sessions have more than one possible next output, so waiting for one specific string risks hanging when something else appears. Matching a set lets the code proceed on whichever actually occurs — a user prompt, a root prompt, or a device-specific one. A "dumb" terminal type is requested so the remote side does not mix ANSI escape sequences into the output, which would otherwise complicate matching. Remember that retrieving text clears the receive buffer; leaving a prompt buffered would make the next receive match immediately and return the wrong output.

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

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

    --  Demonstrates ChannelReceiveUntilMatchN, which receives until ANY one of several patterns is
    --  matched.  This is useful when driving an interactive session where the shell prompt varies
    --  from device to device.
    --  
    --  Note: This example deliberately DOES request a PTY.  Matching a shell prompt only works in an
    --  interactive session -- without a PTY the shell runs non-interactively and never prints a
    --  prompt at all.  For scripted use where no prompt is needed, omit the PTY and match on a
    --  marker your own command echoes instead.

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

    --  The remote shell prompt may be any of these, depending on the device.
    DECLARE @saPrompts int
    EXEC @hr = sp_OACreate 'Chilkat.StringArray', @saPrompts OUT

    EXEC sp_OAMethod @saPrompts, 'Append', @success OUT, '~$'
    EXEC sp_OAMethod @saPrompts, 'Append', @success OUT, 'mars#'
    EXEC sp_OAMethod @saPrompts, 'Append', @success OUT, 'jupiter%'
    EXEC sp_OAMethod @saPrompts, 'Append', @success OUT, 'chilkat$'
    EXEC sp_OAMethod @saPrompts, 'Append', @success OUT, 'admin>'

    DECLARE @hostname nvarchar(4000)
    SELECT @hostname = 'ssh.example.com'
    DECLARE @port int
    SELECT @port = 22
    EXEC sp_OAMethod @ssh, 'Connect', @success OUT, @hostname, @port
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        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 @ssh, 'AuthenticatePw', @success OUT, 'mySshLogin', @password
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    DECLARE @channelNum int
    EXEC sp_OAMethod @ssh, 'OpenSessionChannel', @channelNum OUT
    IF @channelNum < 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    --  Request a pseudo-terminal so the shell runs interactively and prints prompts.  A "dumb"
    --  terminal is used so that ANSI escape sequences are not mixed into the output.
    DECLARE @termType nvarchar(4000)
    SELECT @termType = 'dumb'
    DECLARE @widthInChars int
    SELECT @widthInChars = 120
    DECLARE @heightInChars int
    SELECT @heightInChars = 40
    DECLARE @pixWidth int
    SELECT @pixWidth = 0
    DECLARE @pixHeight int
    SELECT @pixHeight = 0
    EXEC sp_OAMethod @ssh, 'SendReqPty', @success OUT, @channelNum, @termType, @widthInChars, @heightInChars, @pixWidth, @pixHeight
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    EXEC sp_OAMethod @ssh, 'SendReqShell', @success OUT, @channelNum
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    --  IMPORTANT: Set a read timeout before receiving until a match.  ReadTimeoutMs defaults to 0,
    --  which means no limit -- without it, this call waits forever if the received data never
    --  contains a match.
    EXEC sp_OASetProperty @ssh, 'ReadTimeoutMs', 15000

    DECLARE @caseSensitive int
    SELECT @caseSensitive = 1

    --  Read the initial login text up to the first prompt.
    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatchN', @success OUT, @channelNum, @saPrompts, 'utf-8', @caseSensitive
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    DECLARE @cmdOutput nvarchar(4000)
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @cmdOutput OUT, @channelNum, 'utf-8'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    PRINT @cmdOutput

    --  ---- 1st command ----
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, 'cd /tmp' + CHAR(10), 'utf-8'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatchN', @success OUT, @channelNum, @saPrompts, 'utf-8', @caseSensitive
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    --  Retrieving the text also clears the receive buffer, which matters: if a prompt were left
    --  buffered, the next receive would match it immediately and return the wrong output.
    EXEC sp_OAMethod @ssh, 'GetReceivedText', @cmdOutput OUT, @channelNum, 'utf-8'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    PRINT @cmdOutput

    --  ---- 2nd command ----
    EXEC sp_OAMethod @ssh, 'ChannelSendString', @success OUT, @channelNum, 'ls' + CHAR(10), 'utf-8'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    EXEC sp_OAMethod @ssh, 'ChannelReceiveUntilMatchN', @success OUT, @channelNum, @saPrompts, 'utf-8', @caseSensitive
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    EXEC sp_OAMethod @ssh, 'GetReceivedText', @cmdOutput OUT, @channelNum, 'utf-8'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    PRINT @cmdOutput

    --  Finish the session.
    EXEC sp_OAMethod @ssh, 'ChannelSendEof', @success OUT, @channelNum
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    EXEC sp_OAMethod @ssh, 'ChannelReceiveToClose', @success OUT, @channelNum
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    EXEC sp_OAMethod @ssh, 'GetReceivedText', @cmdOutput OUT, @channelNum, 'utf-8'
    EXEC sp_OAGetProperty @ssh, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        EXEC @hr = sp_OADestroy @saPrompts
        RETURN
      END

    PRINT @cmdOutput

    EXEC sp_OAMethod @ssh, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @ssh
    EXEC @hr = sp_OADestroy @saPrompts


END
GO