SQL Server

Upload Bytes to an FTP Server (BinData)

Demonstrates the Chilkat Ftp2.PutFileBd method, which uploads the bytes in a BinData to a remote path. The first argument is the BinData and the second is the remote file path. No encoding conversion is performed.

Note: The local paths are relative to the application's current working directory. Absolute paths may also be used. Supply the paths appropriate to your own environment.

Background: When the content already lives in memory — generated, decompressed, or pulled from a database — this uploads it directly with no intermediate local file. It uses STOR, so an existing remote file is replaced, and the bytes are sent exactly as held, making it the correct choice for binary data.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the Ftp2.PutFileBd method, which uploads the bytes in a BinData to a remote path.
    --  The 1st argument is the BinData and the 2nd is the remote file path.  No encoding conversion is
    --  performed.

    DECLARE @ftp int
    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', 'ftp.example.com'
    EXEC sp_OASetProperty @ftp, 'Username', 'myFtpLogin'

    --  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.
    EXEC sp_OASetProperty @ftp, 'Password', 'myPassword'

    EXEC sp_OAMethod @ftp, 'Connect', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    --  Put the bytes to upload into a BinData.  (Here they are loaded from a local file, but the
    --  bytes could come from anywhere.)
    DECLARE @bd int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

    EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, 'qa_data/image.png'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @bd, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    EXEC sp_OAMethod @ftp, 'PutFileBd', @success OUT, @bd, 'public_html/image.png'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    EXEC sp_OAGetProperty @bd, 'NumBytes', @iTmp0 OUT

    PRINT 'Uploaded ' + @iTmp0 + ' bytes.'

    EXEC sp_OAMethod @ftp, 'Disconnect', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    EXEC @hr = sp_OADestroy @ftp
    EXEC @hr = sp_OADestroy @bd


END
GO