(SQL Server) Iterate over Files and Directories in Filesystem Directory Tree

Demonstrates how to use the Chilkat DirTree object/class to iterate over the names of files and sub-directories in a directory tree.

This example iterates over a directory tree that contains these files and sub-directories:

a
    images (sub-directory)
        dudeA.gif
        Thumbs.db
    hamlet.xml
    pigs.xml
    Setup.exe
b
    yyy (sub-directory)
    zzz (sub-directory)
        HelloWorld123.txt
        test123.txt
    dude.gif
    dudeC.gif
    hello.txt
    Thumbs.db
test123.txt

(sub-directory)
Relative Path: a
Absolute Path: c:\temp\abc123\a
Absolute UNC Path: \\?\c:\temp\abc123\a
----
(sub-directory)
Relative Path: b
Absolute Path: c:\temp\abc123\b
Absolute UNC Path: \\?\c:\temp\abc123\b
----
Size in bytes: 15
Relative Path: test123.txt
Absolute Path: c:\temp\abc123\test123.txt
Absolute UNC Path: \\?\c:\temp\abc123\test123.txt
----
Size in bytes: 279658
Relative Path: a\hamlet.xml
Absolute Path: c:\temp\abc123\a\hamlet.xml
Absolute UNC Path: \\?\c:\temp\abc123\a\hamlet.xml
----
(sub-directory)
Relative Path: a\images
Absolute Path: c:\temp\abc123\a\images
Absolute UNC Path: \\?\c:\temp\abc123\a\images
----
Size in bytes: 8463
Relative Path: a\pigs.xml
Absolute Path: c:\temp\abc123\a\pigs.xml
Absolute UNC Path: \\?\c:\temp\abc123\a\pigs.xml
----
Size in bytes: 24576
Relative Path: a\Setup.exe
Absolute Path: c:\temp\abc123\a\Setup.exe
Absolute UNC Path: \\?\c:\temp\abc123\a\Setup.exe
----
Size in bytes: 6221
Relative Path: b\dude.gif
Absolute Path: c:\temp\abc123\b\dude.gif
Absolute UNC Path: \\?\c:\temp\abc123\b\dude.gif
----
Size in bytes: 6221
Relative Path: b\dudeC.gif
Absolute Path: c:\temp\abc123\b\dudeC.gif
Absolute UNC Path: \\?\c:\temp\abc123\b\dudeC.gif
----
Size in bytes: 13
Relative Path: b\hello.txt
Absolute Path: c:\temp\abc123\b\hello.txt
Absolute UNC Path: \\?\c:\temp\abc123\b\hello.txt
----
Size in bytes: 10240
Relative Path: b\Thumbs.db
Absolute Path: c:\temp\abc123\b\Thumbs.db
Absolute UNC Path: \\?\c:\temp\abc123\b\Thumbs.db
----
(sub-directory)
Relative Path: b\yyy
Absolute Path: c:\temp\abc123\b\yyy
Absolute UNC Path: \\?\c:\temp\abc123\b\yyy
----
(sub-directory)
Relative Path: b\zzz
Absolute Path: c:\temp\abc123\b\zzz
Absolute UNC Path: \\?\c:\temp\abc123\b\zzz
----
Size in bytes: 6221
Relative Path: a\images\dudeA.gif
Absolute Path: c:\temp\abc123\a\images\dudeA.gif
Absolute UNC Path: \\?\c:\temp\abc123\a\images\dudeA.gif
----
Size in bytes: 6144
Relative Path: a\images\Thumbs.db
Absolute Path: c:\temp\abc123\a\images\Thumbs.db
Absolute UNC Path: \\?\c:\temp\abc123\a\images\Thumbs.db
----
Size in bytes: 6080
Relative Path: b\zzz\HelloWorld123.txt
Absolute Path: c:\temp\abc123\b\zzz\HelloWorld123.txt
Absolute UNC Path: \\?\c:\temp\abc123\b\zzz\HelloWorld123.txt
----
Size in bytes: 15
Relative Path: b\zzz\test123.txt
Absolute Path: c:\temp\abc123\b\zzz\test123.txt
Absolute UNC Path: \\?\c:\temp\abc123\b\zzz\test123.txt
----

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)
    DECLARE @dirTree int
    -- Use "Chilkat_9_5_0.DirTree" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.DirTree', @dirTree OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Specify the root of the directory tree to be traversed.
    EXEC sp_OASetProperty @dirTree, 'BaseDir', 'c:/temp/abc123'

    -- Indicate that we want to recursively traverse the tree.
    EXEC sp_OASetProperty @dirTree, 'Recurse', 1

    DECLARE @success int

    -- Begin the directory tree traversal.
    EXEC sp_OAMethod @dirTree, 'BeginIterate', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dirTree, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @dirTree
        RETURN
      END

    DECLARE @doneIterating int
    EXEC sp_OAGetProperty @dirTree, 'DoneIterating', @doneIterating OUT
    WHILE @doneIterating <> 1
      BEGIN

        -- Examine the file or directory at the current traversal position:
        EXEC sp_OAGetProperty @dirTree, 'IsDirectory', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN

            PRINT '(sub-directory)'
          END
        ELSE
          BEGIN

            EXEC sp_OAGetProperty @dirTree, 'FileSize32', @iTmp0 OUT
            PRINT 'Size in bytes: ' + @iTmp0
            -- If the file size is larger than 2GB, then get the size as a decimal string.
            -- (Chilkat provides a FileSize64 method in *some* programming languages. The FileSizeStr property is added in Chilkat v9.5.0.88)

            EXEC sp_OAGetProperty @dirTree, 'FileSizeStr', @sTmp0 OUT
            PRINT 'Size in bytes: ' + @sTmp0
          END


        EXEC sp_OAGetProperty @dirTree, 'RelativePath', @sTmp0 OUT
        PRINT 'Relative Path: ' + @sTmp0

        EXEC sp_OAGetProperty @dirTree, 'FullPath', @sTmp0 OUT
        PRINT 'Absolute Path: ' + @sTmp0

        EXEC sp_OAGetProperty @dirTree, 'FullUncPath', @sTmp0 OUT
        PRINT 'Absolute UNC Path: ' + @sTmp0

        PRINT '----'

        -- Advance to the next file or sub-directory in the tree traversal.
        EXEC sp_OAMethod @dirTree, 'AdvancePosition', @success OUT
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @dirTree, 'DoneIterating', @iTmp0 OUT
            IF @iTmp0 <> 1
              BEGIN
                EXEC sp_OAGetProperty @dirTree, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
                EXEC @hr = sp_OADestroy @dirTree
                RETURN
              END
          END

        EXEC sp_OAGetProperty @dirTree, 'DoneIterating', @doneIterating OUT
      END

    EXEC @hr = sp_OADestroy @dirTree


END
GO