(SQL Server) Inovis VAN FTP/SSL (EDI)

Demonstrates how to connect to the Inovis EDI VAN (FTP/SSL).

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @ftp int
    -- Use "Chilkat_9_5_0.Ftp2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Ftp2', @ftp OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @ftp, 'Hostname', 'ftpssl.gateway.inovisworks.net'
    EXEC sp_OASetProperty @ftp, 'Username', 'myLogin'
    EXEC sp_OASetProperty @ftp, 'Password', 'myPassword'

    -- The Inovis EDI VAN requires Passive connections:
    EXEC sp_OASetProperty @ftp, 'Passive', 1

    -- Note: The Inovis documentation mentions an outbound
    -- high port range of 40000-49999.  In passive mode, the
    -- FTP server chooses the port number for the data
    -- connection.  The Inovis server will choose a port number
    -- in the range 40000-49999.  To establish the data connection,
    -- your firewall must allow outgoing connections in that port
    -- range. 

    -- This tells Chilkat FTP2 to establish
    -- an AUTH TLS secure channel after connecting
    -- on the standard FTP port 21.
    EXEC sp_OASetProperty @ftp, 'AuthTls', 1

    -- You'll probably need this property set to 1 if you're
    -- behind a NAT router or firewall.
    EXEC sp_OASetProperty @ftp, 'PassiveUseHostAddr', 1

    -- The Ssl property is for establishing an implicit SSL connection
    -- on port 990.  Do not set it to 1.  The Inovis VAN
    -- uses explicit encryption (SSL/TLS).
    EXEC sp_OASetProperty @ftp, 'Ssl', 0

    -- The Chilkat FTP component automatically sends a "FEAT";
    -- command after connecting.  This allows the component
    -- to better know the FTP server's capabilities.
    -- The Invois server doesn't recognize the FEAT command,
    -- so disable it:
    EXEC sp_OASetProperty @ftp, 'AutoFeat', 0

    -- Connect, convert to TLS, and login to the FTP server.
    DECLARE @success int
    EXEC sp_OAMethod @ftp, 'Connect', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END
    ELSE
      BEGIN
        -- LastErrorText contains information even when
        -- successful. This allows you to visually verify
        -- that the secure connection actually occurred.
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
      END


    PRINT 'Connection to Inovis EDI VAN OK!'

    -- Do whatever you're doing to do ...
    -- upload files, download files, etc...

    EXEC sp_OAMethod @ftp, 'Disconnect', @success OUT

    EXEC @hr = sp_OADestroy @ftp


END
GO