(SQL Server) Transfer a File using Sockets (TLS or non-TLS)

Demonstrates how to two programs, one a socket writer and the other a socket reader, can transfer a file. The connection can be TLS or a regular non-encrypted TCP connection.

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
    DECLARE @iTmp0 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.

    -- On the sending side, we'll load the file into a BinData object and send.
    -- On the receiving side, we'll read from the socket connection into a BinData, and save to a file.
    -- This example assumes the file is not crazy-large, and that the entire contents
    -- can fit into memory.  
    -- (If the file is too large for memory, there are other ways to send. It just involves streaming or 
    -- sending the file chunk-by-chunk..)

    -- This section of code is for the sender.
    DECLARE @bdToSend int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdToSend OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @bdToSend, 'LoadFile', @success OUT, 'somePath/someFile.dat'
    -- Assume success for the example...

    DECLARE @sndSock int
    -- Use "Chilkat_9_5_0.Socket" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Socket', @sndSock OUT

    DECLARE @bUseTls int
    SELECT @bUseTls = 1
    DECLARE @port int
    SELECT @port = 5555
    DECLARE @maxWaitMs int
    SELECT @maxWaitMs = 5000
    EXEC sp_OAMethod @sndSock, 'Connect', @success OUT, 'some_domain_or_ip.com', @port, @bUseTls, @maxWaitMs
    -- Assume success for the example...

    -- Tell the receiver how many bytes are coming.
    DECLARE @numBytes int
    EXEC sp_OAGetProperty @bdToSend, 'NumBytes', @numBytes OUT
    DECLARE @bBigEndian int
    SELECT @bBigEndian = 1
    EXEC sp_OAMethod @sndSock, 'SendInt32', @success OUT, @numBytes, @bBigEndian

    -- Send the file data (sends the entire contents of bdToSend).
    EXEC sp_OAMethod @sndSock, 'SendBd', @success OUT, @bdToSend, 0, 0

    -- Get an acknowledgement.
    EXEC sp_OAMethod @sndSock, 'ReceiveInt32', @success OUT, @bBigEndian
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @sndSock, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @bdToSend
        EXEC @hr = sp_OADestroy @sndSock
        RETURN
      END

    -- Did the receiver get the correct number of bytes?
    EXEC sp_OAGetProperty @sndSock, 'ReceivedInt', @iTmp0 OUT
    IF @iTmp0 <> @numBytes
      BEGIN

        PRINT 'The receiver did not acknowledge with the correct number of bytes.'
        EXEC @hr = sp_OADestroy @bdToSend
        EXEC @hr = sp_OADestroy @sndSock
        RETURN
      END


    PRINT 'File sent!'

    -- ------------------------------------------------------------------------------------
    -- The code below is for the receiving side (running on some other computer..)

    DECLARE @listenSock int
    -- Use "Chilkat_9_5_0.Socket" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Socket', @listenSock OUT

    EXEC sp_OAMethod @listenSock, 'BindAndListen', @success OUT, 5555, 25
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @listenSock, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @bdToSend
        EXEC @hr = sp_OADestroy @sndSock
        EXEC @hr = sp_OADestroy @listenSock
        RETURN
      END

    -- Get the next incoming connection
    -- Wait a maximum of 20 seconds (20000 millisec)
    DECLARE @rcvSock int

    EXEC sp_OAMethod @listenSock, 'AcceptNextConnection', @rcvSock OUT, 20000
    EXEC sp_OAGetProperty @listenSock, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @listenSock, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @bdToSend
        EXEC @hr = sp_OADestroy @sndSock
        EXEC @hr = sp_OADestroy @listenSock
        RETURN
      END

    -- The sender will first send the big-endian integer for the number of bytes
    -- that are forthcoming..
    EXEC sp_OAMethod @rcvSock, 'ReceiveInt32', @success OUT, @bBigEndian
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rcvSock, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rcvSock

        EXEC @hr = sp_OADestroy @bdToSend
        EXEC @hr = sp_OADestroy @sndSock
        EXEC @hr = sp_OADestroy @listenSock
        RETURN
      END

    DECLARE @numBytesComing int
    EXEC sp_OAGetProperty @rcvSock, 'ReceivedInt', @numBytesComing OUT

    -- Receive that many bytes..
    DECLARE @bdReceived int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdReceived OUT

    EXEC sp_OAMethod @rcvSock, 'ReceiveBdN', @success OUT, @numBytesComing, @bdReceived
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rcvSock, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rcvSock

        EXEC @hr = sp_OADestroy @bdToSend
        EXEC @hr = sp_OADestroy @sndSock
        EXEC @hr = sp_OADestroy @listenSock
        EXEC @hr = sp_OADestroy @bdReceived
        RETURN
      END

    -- Acknowledge the sender by sending back the number of bytes we received.
    EXEC sp_OAGetProperty @bdReceived, 'NumBytes', @iTmp0 OUT
    EXEC sp_OAMethod @rcvSock, 'SendInt32', @success OUT, @iTmp0, @bBigEndian

    -- Close the connection.
    DECLARE @maxWaitMs int
    SELECT @maxWaitMs = 20
    EXEC sp_OAMethod @rcvSock, 'Close', @success OUT, @maxWaitMs
    EXEC @hr = sp_OADestroy @rcvSock

    -- Save the received data to a file.
    EXEC sp_OAMethod @bdReceived, 'WriteFile', @success OUT, 'somePath/someFile.dat'
    -- Assume success for the example...


    PRINT 'File received!'

    EXEC @hr = sp_OADestroy @bdToSend
    EXEC @hr = sp_OADestroy @sndSock
    EXEC @hr = sp_OADestroy @listenSock
    EXEC @hr = sp_OADestroy @bdReceived


END
GO