(SQL Server) FTP XCRC

Demonstrates how to tell Chilkat FTP2 to automatically verify an upload using XCRC. XCRC is an FTP extension supported by most FTP servers. If your FTP server does not support XCRC, then no XCRC command will be sent to verify the upload.

The XCRC command asks the FTP server to send an CRC checksum (hash) for the contents of a file. The client can then verify that the checksum matches the local file. The FTP2 Put* methods automatically do this when the AutoXcrc property is turned on.

Note: In order for the FTP2 component to know whether XCRC is supported by your FTP server, you must have the AutoFeat property turned on (which is the default setting). The AutoFeat property tells the FTP2 component to automatically request the FTP server features via the FEAT command after connecting.

Note: XCRC is new functionality that is present in the pre-release downloads at http://www.chilkatsoft.com/preRelease.asp, or in official new versions dated after 30-Oct-2007. If you have trouble, please contact support@chilkatsoft.com.

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', 'ftp.example.com'
    EXEC sp_OASetProperty @ftp, 'Username', 'login'
    EXEC sp_OASetProperty @ftp, 'Password', 'password'

    -- Turn on the AutoXcrc property:
    EXEC sp_OASetProperty @ftp, 'AutoXcrc', 1

    -- Turn on session logging.  We'll want to examine it to
    -- see the XCRC command:
    EXEC sp_OASetProperty @ftp, 'KeepSessionLog', 1

    -- Connect 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

    -- Change to the remote directory where the file will be uploaded.
    EXEC sp_OAMethod @ftp, 'ChangeRemoteDir', @success OUT, 'junk'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    -- Upload a file.
    DECLARE @localFilename nvarchar(4000)
    SELECT @localFilename = 'hamlet.xml'
    DECLARE @remoteFilename nvarchar(4000)
    SELECT @remoteFilename = 'hamlet.xml'

    EXEC sp_OAMethod @ftp, 'PutFile', @success OUT, @localFilename, @remoteFilename
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ftp, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ftp
        RETURN
      END

    -- If we examine the session log, we'll see the XCRC command:
    EXEC sp_OAGetProperty @ftp, 'SessionLog', @sTmp0 OUT
    PRINT @sTmp0

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


    PRINT 'File Uploaded!'

    -- Here is the session log showing the XCRC command:

    -- 220 Serv-U FTP Server v6.3 for WinSock ready...
    -- .
    -- USER example-code.com
    -- 331 User name okay, need password.
    -- .
    -- PASS ****
    -- 230 User logged in, proceed.
    -- .
    -- TYPE I
    -- 200 Type set to I.
    -- .
    -- SYST
    -- 215 UNIX Type: L8
    -- .
    -- FEAT
    -- 211-Extension supported
    --  CLNT
    --  MDTM
    --  MDTM YYYYMMDDHHMMSS[+-TZ];filename
    --  SIZE
    --  SITE PSWD;EXEC;SET;INDEX;ZONE;CHMOD;MSG
    --  REST STREAM
    --  XCRC filename;start;end
    --  MODE Z
    --  MLST Type*;Size*;Create;Modify*;Win32.ea*;
    -- 211 End
    -- .
    -- CWD junk
    -- 250 Directory changed to /junk
    -- .
    -- PORT 192,168,1,103,5,144
    -- 200 PORT Command successful.
    -- .
    -- STOR hamlet.xml
    -- 150 Opening BINARY mode data connection for hamlet.xml.
    -- 226 Transfer complete.
    -- .
    -- XCRC hamlet.xml
    -- 250 3C1933B

    EXEC @hr = sp_OADestroy @ftp


END
GO