Search and Log using SQL

Good evening. The following is an SQL-based version of the Search and Log script.

Here the search is done with the '.Global' parameter turned to false, and so the SQL also takes care of finding the number of instances per file. The logging starts after the search is done, and non-matching folders aren't listed.

SQL usage details: The script creates a temporary table called 'search_log'. My default database is the Northwind sample database, and the connection string is suited to windows authentication mode.

' Search and Log using SQL ' (c) Oren Hadar, 2009 Option Explicit Dim fso, wshell, f, mainFolder, mySearch, sfInclude, logFileName, logFile, _ cnn, rs, fc, f1, fileSearched, fileMatch, sfc, sf1, _ i, rs2, i2, most Sub Define Set fso = CreateObject("Scripting.FileSystemObject") Set wshell = createobject("wscript.shell") On Error Resume Next Set f = fso.GetFolder(InputBox("Enter subfolder or full path:", , wshell.currentdirectory)) If f = False Then MsgBox "The entered path does not exist, " _ & "or Cancel was pressed.", vbExclamation WScript.Quit End If mainFolder = f Set mySearch = New RegExp With mySearch .Pattern = InputBox("Enter search string:") .Global = False .IgnoreCase = True End With If f.subfolders.Count > 0 Then sfInclude = MsgBox("Include SubFolders?", vbYesNo) End If logFileName = "~" & f.name & " - find '" & mySearch.Pattern & "'.tmp" Set logFile = fso.CreateTextFile(logFileName , True) logFile.Write("Searching for '" & mySearch.Pattern & "' in " & Chr(34) & mainFolder & Chr(34)) If sfInclude = vbYes Then logFile.Write(" and its' subfolders") logFile.Write(":" & vbCrLf & vbCrLf) Set cnn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cnn.ConnectionString = "driver={SQL Server};" & _ "server=USER-PC\SQLEXPRESS;database=Northwind;Trusted_Connection=Yes" cnn.Open cnn.Execute("Create Table search_log (Path varchar(255), " _ & "FileName varchar(255), Instances Int, FileType varchar(255))") End Sub Sub Search Set fc = f.Files For Each f1 in fc If mySearch.Execute(f1.name).Count => 1 Then cnn.Execute("Insert Into search_log (Path, FileName, FileType) " _ & "Values ('" & Replace(Replace(f1.ParentFolder, mainFolder, ".."), "'", "_") _ & "\', '" & Replace(f1.name, "'", "_") & "', '" & Replace(f1.type, "'", "_") & "')") End If Next If sfInclude = vbYes Then Set sfc = f.Subfolders For Each sf1 In sfc Set f = fso.GetFolder(sf1) Call Search Next End If End Sub Sub Log If cnn.Execute("Select Count(*) From search_log")(0) = 0 Then logFile.Write(vbCrLf & "No matches were found.") Exit Sub End If cnn.Execute("Update search_log Set Instances = " _ & "(LEN(FileName) - LEN(REPLACE(FileName, '" & mySearch.Pattern & "', ''))) " _ & " / LEN('" & mySearch.Pattern & "')") Set rs = cnn.Execute("Select Path, Count(FileName), Sum(Instances) From search_log Group By Path") rs.MoveFirst For i = 1 To cnn.Execute("Select Count(Distinct Path) From search_log")(0) logFile.Write(vbCrLf & vbCrLf & rs(0) & " [" & rs(2) & " instance") If rs(2) > 1 Then logFile.Write("s") logFile.Write(" in " & rs(1) & " file") If rs(1) > 1 Then logFile.Write("s") logFile.Write("]" & vbCrLf) Set rs2 = cnn.Execute("Select FileName, Instances From search_log Where Path = '" _ & rs(0) & "'") rs2.MoveFirst For i2 = 1 To cnn.Execute("Select Count(*) From search_log Where Path = '" _ & rs(0) & "'")(0) logFile.Write(rs2(0) & " [" & rs2(1) & " instance") If rs2(1) > 1 Then logFile.Write("s") logFile.Write("]" & vbCrLF) rs2.MoveNext Next rs.MoveNext Next rs.Close rs2.Close End Sub Sub Summary logFile.Write(vbCrLf & vbCrLf & vbCrLf & "* * * * * * * *" _ & vbCrLf & "Search Summary" & vbCrLf _ & "* * * * * * * *" & vbCrLf & vbCrLf & vbCrLf) logFile.Write(cnn.Execute("Select SUM(Instances) From search_log")(0) _ & " instances of '" & mySearch.Pattern & "' were found in " _ & cnn.Execute("Select Count(FileName) From search_log")(0) & " files, inside " _ & cnn.Execute("Select Count(Distinct Path) From search_log")(0) & " folders." _ & vbCrLf & vbCrLf & vbCrLf & vbCrLf) Set rs = cnn.Execute("Select Path, Count(FileName), SUM(Instances) From search_log " _ & "Group by Path Order by Count(FileName) DESC") rs.MoveFirst most = rs(1) logFile.Write("Folder(s) with most matching files [" & most & " files]" & vbCrLf) Do logFile.Write(rs(0) & " [" & rs(2) & " instances]" & vbCrLf) On Error Resume Next rs.MoveNext Loop While rs(1) = most rs.Close logFile.Write(vbCrLf & vbCrLf & vbCrLF) Set rs = cnn.Execute("Select Path, SUM(Instances), Count(FileName) From search_log " _ & "Group by Path Order by SUM(Instances) DESC") rs.MoveFirst most = rs(1) logFile.Write("Folder(s) with most instances [" & most & " instances]" & vbCrLf) Do logFile.Write(rs(0) & " [" & rs(2) & " files]" & vbCrLf) On Error Resume Next rs.MoveNext Loop While rs(1) = most rs.Close logFile.Write(vbCrLf & vbCrLf & vbCrLF) Set rs = cnn.Execute("Select FileType, Count(FileName) From search_log " _ & "Group by FileType Order by Count(FileName) DESC") rs.MoveFirst logFile.Write("The matching files are of the following " _ & cnn.Execute("Select Count(Distinct FileType) From search_log")(0) _ & " file types, in descending order:" & vbCrLf) Do logFile.Write(rs(0) & " [" & rs(1) & " files]" & vbCrLf) On Error Resume Next rs.MoveNext Loop While rs(1) > 0 End Sub Call Define Call Search Call Log If sfInclude = vbYes And _ cnn.Execute("Select Count(*) From search_log")(0) > 0 Then Call Summary cnn.Execute("Drop Table search_log") cnn.Close logFile.Close fso.MoveFile logFileName, Mid(Replace(logFileName,".tmp" , ".log"), 2)

Multi-Rectangle \ Spiral [T-SQL]

I wrote a script which draws multiple rectangle-inside-rectangle in T-SQL. The number of rectangles to be drawn can be set ... to a certain extent ;)

A change of a parameter at the end of the script produces a spiral.

-- Multi-Rectangle \ Spiral -- by Oren Hadar, 2009 DECLARE @total INT DECLARE @count INT DECLARE @horiz INT DECLARE @vert INT DECLARE @max_vert INT DECLARE @h_draw varchar(128) DECLARE @v_draw varchar(128) SET @total = 7 -- @total sets to number of rectangles SET @count = @total SET @max_vert = @total * 3 SET @horiz = @total*6-3 SET @vert = @total * 3 - 1 SET @h_draw = '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' SET @v_draw = '+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +' WHILE @vert > -@total * 3 - 3 BEGIN PRINT LEFT(@v_draw, @max_vert-ABS(@vert)) + SUBSTRING(@h_draw, 1, ABS(@horiz)) + RIGHT(@v_draw, @max_vert-ABS(@vert)) PRINT LEFT(@v_draw, @max_vert-ABS(@vert)) + REPLACE(SUBSTRING(@h_draw, 1, ABS(@horiz)), '+', ' ') + RIGHT(@v_draw, @max_vert-ABS(@vert)) SELECT @horiz = @horiz - 6 SELECT @vert = @vert - 3 SELECT @count = @count-1 IF @count=0 BEGIN -- In the next line, change the '-2' to '+1' to get a spiral! SELECT @horiz=@horiz-2 SELECT @vert=@vert-2 END END