SQL Server

Enumerate Active SSH Channel Numbers

Demonstrates the Chilkat Ssh.GetChannelNumber method, which returns the channel number at a zero-based enumeration index in the active channel collection. The only argument is the index — an enumeration position, not itself a channel number. Valid indexes run from 0 through NumOpenChannels - 1, and out-of-range indexes return -1.

Background: One SSH connection can carry many simultaneous channels, and this method lets you walk the live set rather than tracking every channel number yourself. The index-versus-channel-number distinction is the key point: index 0 is simply the first channel in the collection and may well be channel number 3. As channels open and close the enumeration shifts, so treat an index as valid only for the moment you read it.

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 @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the Ssh.GetChannelNumber method, which returns the channel number at a
    --  zero-based enumeration index in the active channel collection.  The only argument is the
    --  index, which is an enumeration position and not itself a channel number.

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

    DECLARE @sshPort int
    SELECT @sshPort = 22
    EXEC sp_OAMethod @ssh, 'Connect', @success OUT, 'ssh.example.com', @sshPort
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        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
        RETURN
      END

    --  Open a couple of channels so the collection has more than one entry.
    DECLARE @channelA int
    EXEC sp_OAMethod @ssh, 'OpenSessionChannel', @channelA OUT
    IF @channelA < 0
      BEGIN
        EXEC sp_OAGetProperty @ssh, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ssh
        RETURN
      END

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

    --  Enumerate the active channels.  Valid indexes are 0 through NumOpenChannels - 1.
    DECLARE @n int
    EXEC sp_OAGetProperty @ssh, 'NumOpenChannels', @n OUT

    PRINT 'Open channels: ' + @n
    DECLARE @i int

    SELECT @i = 0
    WHILE @i <= @n - 1
      BEGIN
        DECLARE @chNum int
        EXEC sp_OAMethod @ssh, 'GetChannelNumber', @chNum OUT, @i


        PRINT 'Index ' + @i + ' is channel number ' + @chNum
        SELECT @i = @i + 1
      END

    EXEC sp_OAMethod @ssh, 'Disconnect', NULL

    EXEC @hr = sp_OADestroy @ssh


END
GO