One uses the "sp_OA*" procedures to access the FileSystemObject and return the requested information, others solve the task by implementing a CLR function. A third approach relies on "xp_cmdshell" and interpreting the output from a "dir" command. The last approach appeals the most to me, however, there's a problem if you want to be locale independent, the date and time formats produced by dir vary: dd/mm/yyyy, mm.dd.yyyy, hh:mm (24 hour format) hh:mmam/pm, etc. So the challenge is to interpret dir's output and return the information in a standard format. I decided on a two step process where the first step samples the output of a larger number of files to infer the current date format, with the second step applying the results to returning the info for the requested file. Here it is:
IF OBJECT_ID('dbo.p_FileInfo', 'P') IS NOT NULL DROP PROCEDURE dbo.p_FileInfo
go
CREATE PROCEDURE dbo.p_FileInfo
(@p_FileName varchar(250),
@p_FileDate varchar(20) = NULL OUTPUT, -- Format YYYYMMDD_HHMM
@p_FileSize int = NULL OUTPUT) -- Size in bytes
AS
SET NOCOUNT ON
-- Check if file exists, otherwise don't bother ...
declare @err int
exec master.dbo.xp_fileexist @p_FileName, @err OUTPUT;
if @err = 0 return(1);
-- The first part of this procedure tries to determine the current date format
-- used in "DIR" output, by analysing a sampling of the output.
CREATE TABLE #diroutput(line varchar(100), sDate varchar(20), sTime varchar(10),
FileSize int, sName varchar(100))
insert #diroutput(line) Exec master.dbo.xp_cmdshell
'dir /A-D /TW /-C /4 %SYSTEMROOT%\TEMP %TEMP% | findstr /R "^[0-9]"'
delete #diroutput where line is null
declare @line varchar(100);
declare @line1 varchar(100);
set @line = (select top 1 line from #diroutput);
declare @iDateStart smallint
declare @iDateEnd smallint
declare @iTimeStart smallint
declare @iTimeEnd smallint
declare @iSizeStart smallint
declare @iSizeEnd smallint
declare @iNameStart smallint
set @iDateEnd = CHARINDEX(' ', @line);
set @line1 = STUFF(@line, 1, @iDateEnd, REPLICATE(' ', @iDateEnd));
set @iTimeStart = @iDateEnd + 1;
set @iTimeEnd = CHARINDEX(' ', @line1, PATINDEX('%[0-9]%', @line1));
set @line1 = STUFF(@line, 1, @iTimeEnd, REPLICATE(' ', @iTimeEnd));
set @iSizeEnd = CHARINDEX(' ', @line1, PATINDEX('%[0-9]%', @line1));
set @line1 = STUFF(@line, 1, @iSizeEnd, REPLICATE(' ', @iSizeEnd));
set @iNameStart = @iSizeEnd + 1;
UPDATE #diroutput SET
sDate = left(line, @iDateEnd-1)
,sTime = LTRIM(substring(line, @iTimeStart, @iTimeEnd - @iTimeStart))
,FileSize = convert(int, LTRIM(substring(line, @iSizeStart,
@iSizeEnd - @iSizeStart)))
,sName = LTRIM(substring(line, @iNameStart, LEN(line) - @iNameStart + 1));
declare @sDateSample varchar(20)
declare @dateSep char(1)
set @sDateSample = (select top 1 sDate from #diroutput);
set @dateSep = SUBSTRING(@sDateSample, PATINDEX('%[^0-9]%', @sDateSample), 1);
declare @sTimeSample varchar(10)
declare @timeSep char(1)
set @sTimeSample = (select top 1 sTime from #diroutput);
set @timeSep = SUBSTRING(@sTimeSample, PATINDEX('%[^0-9]%', @sTimeSample), 1);
-- Three parts of a datestring
declare @DateParts
TABLE (
PartNo smallint,
PartStart smallint,
PartLen smallint,
PartName varchar(10));
declare @iPartStart smallint, @iPartLen smallint
-- First part
set @iPartStart = 1;
set @iPartLen = CHARINDEX(@dateSep, @sDateSample) - 1;
INSERT @DateParts(PartNo, PartStart, PartLen) SELECT 1, @iPartStart, @iPartLen;
-- Second part
set @iPartStart = @iPartStart + @iPartLen + 1;
set @iPartLen = CHARINDEX(@dateSep, @sDateSample, @iPartStart) - @iPartStart;
INSERT @DateParts(PartNo, PartStart, PartLen) SELECT 2, @iPartStart, @iPartLen;
-- Third part
set @iPartStart = @iPartStart + @iPartLen + 1;
set @iPartLen = LEN(@sDateSample) - @iPartStart + 1;
INSERT @DateParts(PartNo, PartStart, PartLen) SELECT 3, @iPartStart, @iPartLen;
UPDATE dp
set PartName = case
when dpMax.maxval <= 12 then 'MONTH'
when dpMax.maxval <= 31 then 'DAY'
else 'YEAR'
end
FROM @DateParts dp JOIN
(select max(PartNo) As PartNo, max(substring(sDate, PartStart, PartLen)) As maxval
from #diroutput o cross join @DateParts p group by p.PartNo) dpMax
on dp.PartNo = dpMax.PartNo;
-- In the second part we retrieve the information about the file
declare @cmd varchar(1000)
TRUNCATE TABLE #diroutput
set @cmd = 'dir /A-D /TW /-C /4 "' + @p_FileName + '" | findstr /R "^[0-9]"'
insert #diroutput(line) Exec master.dbo.xp_cmdshell @cmd
delete #diroutput where line is null
UPDATE #diroutput SET
sDate = left(line, @iDateEnd-1)
,sTime = LTRIM(substring(line, @iTimeStart, @iTimeEnd - @iTimeStart))
,FileSize = convert(int, LTRIM(substring(line, @iSizeStart,
@iSizeEnd - @iSizeStart)))
,sName = LTRIM(substring(line, @iNameStart, LEN(line) - @iNameStart + 1));
-- Perform AM/PM conversion to 24 hour clock (just in case)
UPDATE #diroutput SET
sTime = case
when sTime LIKE '12%a%' then
'00' + SUBSTRING(sTime, 4, 2)
when sTime LIKE '0%p%' then
convert(varchar(2), 12 + convert(int, left(sTime, 2)))
+ SUBSTRING(sTime, 4, 2)
when sTime LIKE '1[01]%p%' then
convert(varchar(2), 12 + convert(int, left(sTime, 2)))
+ SUBSTRING(sTime, 4, 2)
else left(sTime, 2) + SUBSTRING(sTime, 4, 2)
end;
SELECT @p_FileDate = SUBSTRING(sDate, Y.PartStart, Y.PartLen)
+ SUBSTRING(sDate, M.PartStart, M.PartLen)
+ SUBSTRING(sDate, D.PartStart, D.PartLen)
+ '_' + sTime,
@p_FileSize = FileSize
FROM #diroutput
CROSS JOIN (select PartStart, PartLen from @DateParts where PartName = 'YEAR') as Y
CROSS JOIN (select PartStart, PartLen from @DateParts where PartName = 'MONTH') as M
CROSS JOIN (select PartStart, PartLen from @DateParts where PartName = 'DAY') as D;
return(0);
go
go
CREATE PROCEDURE dbo.p_FileInfo
(@p_FileName varchar(250),
@p_FileDate varchar(20) = NULL OUTPUT, -- Format YYYYMMDD_HHMM
@p_FileSize int = NULL OUTPUT) -- Size in bytes
AS
SET NOCOUNT ON
-- Check if file exists, otherwise don't bother ...
declare @err int
exec master.dbo.xp_fileexist @p_FileName, @err OUTPUT;
if @err = 0 return(1);
-- The first part of this procedure tries to determine the current date format
-- used in "DIR" output, by analysing a sampling of the output.
CREATE TABLE #diroutput(line varchar(100), sDate varchar(20), sTime varchar(10),
FileSize int, sName varchar(100))
insert #diroutput(line) Exec master.dbo.xp_cmdshell
'dir /A-D /TW /-C /4 %SYSTEMROOT%\TEMP %TEMP% | findstr /R "^[0-9]"'
delete #diroutput where line is null
declare @line varchar(100);
declare @line1 varchar(100);
set @line = (select top 1 line from #diroutput);
declare @iDateStart smallint
declare @iDateEnd smallint
declare @iTimeStart smallint
declare @iTimeEnd smallint
declare @iSizeStart smallint
declare @iSizeEnd smallint
declare @iNameStart smallint
set @iDateEnd = CHARINDEX(' ', @line);
set @line1 = STUFF(@line, 1, @iDateEnd, REPLICATE(' ', @iDateEnd));
set @iTimeStart = @iDateEnd + 1;
set @iTimeEnd = CHARINDEX(' ', @line1, PATINDEX('%[0-9]%', @line1));
set @line1 = STUFF(@line, 1, @iTimeEnd, REPLICATE(' ', @iTimeEnd));
if LTRIM(@line1) LIKE '[AP]M %'
begin
set @iTimeEnd = CHARINDEX(' ', @line1, CHARINDEX('M', @line1));
set @line1 = STUFF(@line, 1, @iTimeEnd, REPLICATE(' ', @iTimeEnd));
end;
set @iSizeStart = @iTimeEnd + 1;begin
set @iTimeEnd = CHARINDEX(' ', @line1, CHARINDEX('M', @line1));
set @line1 = STUFF(@line, 1, @iTimeEnd, REPLICATE(' ', @iTimeEnd));
end;
set @iSizeEnd = CHARINDEX(' ', @line1, PATINDEX('%[0-9]%', @line1));
set @line1 = STUFF(@line, 1, @iSizeEnd, REPLICATE(' ', @iSizeEnd));
set @iNameStart = @iSizeEnd + 1;
UPDATE #diroutput SET
sDate = left(line, @iDateEnd-1)
,sTime = LTRIM(substring(line, @iTimeStart, @iTimeEnd - @iTimeStart))
,FileSize = convert(int, LTRIM(substring(line, @iSizeStart,
@iSizeEnd - @iSizeStart)))
,sName = LTRIM(substring(line, @iNameStart, LEN(line) - @iNameStart + 1));
declare @sDateSample varchar(20)
declare @dateSep char(1)
set @sDateSample = (select top 1 sDate from #diroutput);
set @dateSep = SUBSTRING(@sDateSample, PATINDEX('%[^0-9]%', @sDateSample), 1);
declare @sTimeSample varchar(10)
declare @timeSep char(1)
set @sTimeSample = (select top 1 sTime from #diroutput);
set @timeSep = SUBSTRING(@sTimeSample, PATINDEX('%[^0-9]%', @sTimeSample), 1);
-- Three parts of a datestring
declare @DateParts
TABLE (
PartNo smallint,
PartStart smallint,
PartLen smallint,
PartName varchar(10));
declare @iPartStart smallint, @iPartLen smallint
-- First part
set @iPartStart = 1;
set @iPartLen = CHARINDEX(@dateSep, @sDateSample) - 1;
INSERT @DateParts(PartNo, PartStart, PartLen) SELECT 1, @iPartStart, @iPartLen;
-- Second part
set @iPartStart = @iPartStart + @iPartLen + 1;
set @iPartLen = CHARINDEX(@dateSep, @sDateSample, @iPartStart) - @iPartStart;
INSERT @DateParts(PartNo, PartStart, PartLen) SELECT 2, @iPartStart, @iPartLen;
-- Third part
set @iPartStart = @iPartStart + @iPartLen + 1;
set @iPartLen = LEN(@sDateSample) - @iPartStart + 1;
INSERT @DateParts(PartNo, PartStart, PartLen) SELECT 3, @iPartStart, @iPartLen;
UPDATE dp
set PartName = case
when dpMax.maxval <= 12 then 'MONTH'
when dpMax.maxval <= 31 then 'DAY'
else 'YEAR'
end
FROM @DateParts dp JOIN
(select max(PartNo) As PartNo, max(substring(sDate, PartStart, PartLen)) As maxval
from #diroutput o cross join @DateParts p group by p.PartNo) dpMax
on dp.PartNo = dpMax.PartNo;
-- In the second part we retrieve the information about the file
declare @cmd varchar(1000)
TRUNCATE TABLE #diroutput
set @cmd = 'dir /A-D /TW /-C /4 "' + @p_FileName + '" | findstr /R "^[0-9]"'
insert #diroutput(line) Exec master.dbo.xp_cmdshell @cmd
delete #diroutput where line is null
UPDATE #diroutput SET
sDate = left(line, @iDateEnd-1)
,sTime = LTRIM(substring(line, @iTimeStart, @iTimeEnd - @iTimeStart))
,FileSize = convert(int, LTRIM(substring(line, @iSizeStart,
@iSizeEnd - @iSizeStart)))
,sName = LTRIM(substring(line, @iNameStart, LEN(line) - @iNameStart + 1));
-- Perform AM/PM conversion to 24 hour clock (just in case)
UPDATE #diroutput SET
sTime = case
when sTime LIKE '12%a%' then
'00' + SUBSTRING(sTime, 4, 2)
when sTime LIKE '0%p%' then
convert(varchar(2), 12 + convert(int, left(sTime, 2)))
+ SUBSTRING(sTime, 4, 2)
when sTime LIKE '1[01]%p%' then
convert(varchar(2), 12 + convert(int, left(sTime, 2)))
+ SUBSTRING(sTime, 4, 2)
else left(sTime, 2) + SUBSTRING(sTime, 4, 2)
end;
SELECT @p_FileDate = SUBSTRING(sDate, Y.PartStart, Y.PartLen)
+ SUBSTRING(sDate, M.PartStart, M.PartLen)
+ SUBSTRING(sDate, D.PartStart, D.PartLen)
+ '_' + sTime,
@p_FileSize = FileSize
FROM #diroutput
CROSS JOIN (select PartStart, PartLen from @DateParts where PartName = 'YEAR') as Y
CROSS JOIN (select PartStart, PartLen from @DateParts where PartName = 'MONTH') as M
CROSS JOIN (select PartStart, PartLen from @DateParts where PartName = 'DAY') as D;
return(0);
go
No comments:
Post a Comment