This project is read-only.

documentation and usage examples

Sep 30, 2011 at 4:09 PM
Edited Sep 30, 2011 at 4:10 PM

I love the project,and have used all the functionality except one,which i'm not sure what it's purpose is:

MSPGetFileSpecs

 for a known file, i can run

EXECUTE dbo.MSPGetFileSpecs 'C:\Data','contents.txt',NULL,NULL,NULL

but i don't get results from the proc, and i'm not sure what it does or was supposed to do.

to help myself out, i've create some usage documentation...i'm leaving it here for you to decide if it's worthy to include in your documentation.

 Thanks for your Efforts!

 --Lowell

 

 

 

--Elliot Whitlow's project! awesome
--http://nclsqlclrfile.codeplex.com/

--MFBreakFileNameWithPathForFileName
--  Parameters: @FileNameWithPath
--  purpose: given a full file path, return just the Filename
--  usage: SELECT dbo.MFBreakFileNameWithPathForFileName('C:\Data\Imports\d\contents.txt')--returns 'contents.txt'

--MFBreakFileNameWithPathForFilePath
--  Parameters:@FileNameWithPath
--  purpose: given a full file path, return the path without the file name
--  usage: SELECT dbo.MFBreakFileNameWithPathForFilePath('C:\Data\Imports\d\contents.txt')--returns 'C:\Data\Imports\d\'

--MFFileExistsCheck
--  Parameters: @FilePath,@FileName
--  purpose: given a path and filename, check if the file exists 
--  usage: 
/*         SELECT dbo.MFFileExistsCheck('C:\Data','contents.txt') --returns 0 or 1 for false/true
           SELECT dbo.MFFileExistsCheck('C:\Data\','contents.txt') --returns 0 or 1 for false/true
           SELECT dbo.MFFileExistsCheck('C:\Data\','\contents.txt') --returns 0 or 1 for false/true
           SELECT dbo.MFFileExistsCheck('C:\Data','\contents.txt') --returns 0 or 1 for false/true
*/
--MFFolderExistsCheck
--  Parameters: @FullDirectoryPath
--  purpose:  given a path, check if the path exists /is valid
--  usage: 
/*         SELECT dbo.MFFolderExistsCheck('C:\Data') --returns 0 or 1 for false/true
           SELECT dbo.MFFolderExistsCheck('C:\Data\') --returns 0 or 1 for false/true
*/
--MFGetFileImage
--  Parameters: @FilePath, @FileName
--  purpose: given a path and filename, return the varbinary of the file to store in the database.
--  usage: 
/*        CREATE TABLE myImages(id int,filename varchar(200),rawimage varbinary(max) )
           INSERT INTO myImages(id,filename,rawimage)
             SELECT 1,'fedora_spinner.gif',dbo.MFGetFileImage('C:\Data\','fedora_spinner.gif' )
*/
--MSPCopyFile
--  Parameters: @SourceFilePath,@SourceFileName,@DestinationFilePath,@DestinationFileName,@OverwriteExistingFileFlag
--  purpose: Disk operations for copying files from one disk location to another, with optional overwrite if existing.
--  usage: EXECUTE  dbo.MSPCopyFile 'C:\Data','contents.txt','C:\Data','contents.bak',1 

--MSPDeleteFile
--  Parameters: @FilePath,@FileName
--  purpose: given a path and filename, delete an exisitng file from disk
--  usage:  EXECUTE  dbo.MSPDeleteFile 'C:\Data','contents.bak'

--MSPDeleteFiles
--  Parameters: @FilePath,@FilePattern
--  purpose: given a path and pattern to match against, delete all files within the Directory
--  usage: EXECUTE  dbo.MSPDeleteFiles 'C:\Data','*.bak'

--MSPGetFileSpecs
--  Parameters: @FilePath,@FileName,@FileSize,@FileDt,@FileExistFlag
--  purpose: unknown
--  usage: EXECUTE dbo.MSPGetFileSpecs 'C:\Data','contents.txt',NULL,NULL,NULL

--MSPMakeDirectory
--  Parameters: @FullDirectoryPath
--  purpose: given a string, will create a directory and multiple subdirectories in the string.
--  usage: EXECUTE  dbo.MSPMakeDirectory 'C:\Data\SQLCLR\ALLPROCS' --assume only C:\Data Exists:, creates .\SQLCLR and \ALLPROCS beneath that.

--MSPMoveFile
--  Parameters: @SourceFilePath,@SourceFileName,@DestinationFilePath,@DestinationFileName,@OverwriteExistingFileFlag
--  purpose:  Disk operations for movinging files from one disk location to another, with optional overwrite if existing.
--  usage: EXECUTE  dbo.MSPMoveFile 'C:\Data','contents.txt','C:\Data','contents.bak',1 

--MSPSaveFileImage
--  Parameters: @FilePath,@FileName,@FileBytes
--  purpose: given an varbinary image column in a table, write that image to disk
--  usage: 
/*         --assumes table and the file from the example above for dbo.MFGetFileImage exists already.
           declare  @myfile varbinary(max)
           SELECT @myfile = rawimage FROM myImages WHERE id = 1
           EXEC dbo.MSPSaveFileImage 'C:\Data','spinning.gif',@myfile
*/
--MFBreakFileNameWithPath
--  Parameters: @FileNameWithPath
--  purpose: given a string containing both path and filename, returns a two column results table with Filename and File Path as seperate columns
--  usage: 
/*         SELECT * FROM dbo.MFBreakFileNameWithPath('C:\Data\contents.txt')
           SELECT * FROM dbo.MFBreakFileNameWithPath('C:\Data\')
*/
--MFGetDirectoryList
--  Parameters: @Directory
--  purpose: given a string containing a path returns a three column results table with path,name and fullfilename and path
--  usage:  SELECT * FROM dbo.MFGetDirectoryList('C:\Data\')

--MFGetFileSpecs
--  Parameters: @FilePath, @FileName
--  purpose: given a filename, return  a three column results table with size,last modified date and whehther the file exists that you queried
--  usage: 
/*         --assumes table and the file from the example above for dbo.MFGetFileImage exists already.
           SELECT * FROM dbo.MFGetFileSpecs( 'C:\Data','spinning.gif')
           SELECT * FROM dbo.MFGetFileSpecs( 'C:\Data','spinfning.gif')
*/

Oct 5, 2011 at 10:44 PM

First of all, thank you for your compliments and interest in the project.

The MSPGetFileSpecs sproc is used to directly populate variables, it is similar MFGetFileSpecs.  They have different purposes but if you look at the code in the assembly I believe it is actually the same.  When using the function you get a table return of three columns, this requires you to build a statement like:

SELECT @FileSize = @FileSize, @FileDt = FileDt, @FileExistFlag = FileExistFlag FROM dbo.MFGetFileSpecs( 'C:\data\', 'filename.ext'

) 

 

to populate variables, or you can use the sproc this way:

EXEC dbo.MSPGetFileSpecs @FilePath = 'C:\data\', @FileName = 'filename.ext', @FileSize = @OFileSize OUTPUT, @FileDt = @OFileDt OUTPUT, @FileExistFlag = @OFileExistFlag OUTPUT

Basically it is just a different way to get the same data, and I had a use for both when I wrote it.  I think I understand your confusion, the sproc returns no visible output, but 3 of the 5 parameters are output.  I will incorporate your documentation into the project, I have been meaning to write some anyway.

CEWII