You Can't Do That In SQL!

Windows 7 Search sucks!

No, seriously...

...the Search feature in Windows 7 really sucks.

At least, it sucks for developers who work with ASCII text files and frequently want to search their code for some fixed string of text, like

---

Show me all the SQL scripts that contain the string literal 'Sampling Stopped'.

---

That's how the Windows XP Search used to work: WYTIWILF... what you typed is what it looked for.

So, after several months of increasing frustration, I decided to do something.

But what?

1. Drink the Unix Kool-Aid and learn to love grep for Windows?
   

2. switch from Wordpad to something fancy like Notepad++?
   

3. go big with something like Visual Studio for SQL?
   

4. or . . . build it using SQL Anywhere?
   

If you are familiar with this blog, especially if you've read Everything Looks Like a Database, the choice shouldn't come as any surprise:

Door Number Four: Build It Using SQL Anywhere

Here's what the SQL Anywhere solution looks like...

• A simple browser display that lets you pick a search string, a file type and a folder:
  

  
  

• and then lets you doubleclick on one of the files to open it in your editor of choice:
  

  
  

Here are the SQL Anywhere features that make it possible:

• A Directory Access Server to implement a SELECT on the file names in the folder,
  

• the xp_read_file() system procedure to read each file,
  

• the LOCATE() function to search inside each file,
  

• the xp_cmdshell() system procedure to launch the editor on a selected file, and
  

• the CREATE SERVICE statement to run it all from within a browser using SQL Anywhere's built-in HTTP server.
  

Here's the code:

CREATE TABLE search_spec (
   one_row_only  INTEGER NOT NULL PRIMARY KEY CHECK ( one_row_only = 1 ),
   folder_spec   VARCHAR ( 200 ) NOT NULL,
   file_ext      VARCHAR ( 10 ) NOT NULL,
   search_for    VARCHAR ( 100 ) NOT NULL );

INSERT search_spec VALUES ( 1, '.\\', '.sql', '' );
COMMIT;

CREATE SERVER local_folder CLASS 'DIRECTORY' USING 'ROOT=C:\\;SUBDIRS=0';

CREATE EXTERNLOGIN DBA TO local_folder;

CREATE EXISTING TABLE local_file AT 'local_folder;;;.';

CREATE SERVICE search TYPE 'RAW' AUTHORIZATION OFF USER DBA 
   AS CALL search();

CREATE PROCEDURE search()
RESULT ( html_string LONG VARCHAR )
BEGIN

DECLARE @folder_spec              VARCHAR ( 200 );
DECLARE @file_ext                 VARCHAR ( 10 );
DECLARE @search_for               VARCHAR ( 100 );
DECLARE @action                   VARCHAR ( 10 );
DECLARE @file_name                VARCHAR ( 200 );
DECLARE @variable_name            LONG VARCHAR;
DECLARE @alter_server_statement   LONG VARCHAR;

DECLARE LOCAL TEMPORARY TABLE local_file_match (
   local_file_name    VARCHAR ( 260 ) NOT NULL PRIMARY KEY )
   NOT TRANSACTIONAL;

SELECT folder_spec,
       file_ext,
       search_for
  INTO @folder_spec,
       @file_ext,
       @search_for
  FROM search_spec;

SET @variable_name = NEXT_HTTP_VARIABLE ( NULL );
WHILE @variable_name IS NOT NULL LOOP
   CASE @variable_name 
      WHEN 'folder_spec' THEN SET @folder_spec = HTTP_VARIABLE ( @variable_name );
      WHEN 'file_ext'    THEN SET @file_ext    = HTTP_VARIABLE ( @variable_name );
      WHEN 'search_for'  THEN SET @search_for  = HTTP_VARIABLE ( @variable_name );
      WHEN 'action'      THEN SET @action      = HTTP_VARIABLE ( @variable_name );
      WHEN 'file_name'   THEN SET @file_name   = HTTP_VARIABLE ( @variable_name );
   END CASE;
   SET @variable_name = NEXT_HTTP_VARIABLE ( @variable_name );
END LOOP;

SET @folder_spec = TRIM ( @folder_spec );
SET @file_ext    = TRIM ( @file_ext );
SET @search_for  = TRIM ( @search_for );

IF RIGHT ( @folder_spec, 1 ) <> '\\' THEN
   SET @folder_spec = STRING ( @folder_spec, '\\' );
END IF;

IF  LENGTH ( @file_ext )  >= 1
AND LEFT ( @file_ext, 1 ) <> '.' THEN
   SET @file_ext = STRING ( '.', @file_ext );
END IF;

SET @action = COALESCE ( @action, 'Search' );

CASE @action

   WHEN 'Search' THEN

      UPDATE search_spec
         SET folder_spec = @folder_spec,
             file_ext    = @file_ext,
             search_for  = @search_for;

      COMMIT;

      SET @alter_server_statement = STRING ( 
         'ALTER SERVER local_folder USING ''ROOT=',
         @folder_spec,
         ';SUBDIRS=0''' );

      EXECUTE IMMEDIATE @alter_server_statement;

   WHEN 'Open' THEN

      CALL xp_cmdshell ( STRING ( 
                            'start "C:\\Program Files (x86)\\Windows NT\\Accessories\\wordpadx.exe" "',
                            @folder_spec, 
                            @file_name,
                            '"' ),
                         'no_output' );

END CASE;

FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR
SELECT local_file.file_name AS @local_file_name
  FROM local_file 
 WHERE local_file.file_name LIKE STRING ( '%', @file_ext )  
 ORDER BY local_file.file_name 
FOR READ ONLY
DO
   IF TRIM ( @search_for ) = '' THEN
      INSERT local_file_match VALUES ( @local_file_name );
   ELSE
      IF LOCATE ( xp_read_file ( STRING ( @folder_spec, @local_file_name ) ), @search_for ) > 0 THEN
         INSERT local_file_match VALUES ( @local_file_name );
      END IF;
   END IF;
END FOR;

CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );

SELECT STRING (
   '<HTML><HEAD>\x0d\x0a',
   '<TITLE>Search</TITLE>\x0d\x0a',
   '<META HTTP-EQUIV="Pragma" CONTENT="no-cache">\x0d\x0a',
   '<script Language="JavaScript">\x0d\x0a',
   'function searchF() {\x0d\x0a',
      'document.f.action = "search?action=Search";\x0d\x0a',
      'document.f.submit();\x0d\x0a',
      'return false; }\x0d\x0a',
   'function openF ( file_name ) {\x0d\x0a',
      'document.f.action = "search?action=Open&file_name=" + file_name;\x0d\x0a',
      'document.f.submit();\x0d\x0a',
      'return false; }\x0d\x0a',
   '</script>\x0d\x0a',
   '<STYLE> P { font-family: COURIER NEW; font-size: 8pt; margin-bottom: 0; margin-top: 0; } </STYLE>\x0d\x0a',
   '</HEAD>\x0d\x0a',
   '<BODY BGCOLOR=#FFFFFF>\x0d\x0a',

   '<FORM NAME="f" METHOD="POST"><P>\x0d\x0a',

   'Search for this exact string: <INPUT TYPE="TEXT" NAME="search_for" VALUE="',
   @search_for,
   '" SIZE=40 STYLE="font-family: COURIER NEW;"><P> \x0d\x0a',

   '...in all files with this extension: <INPUT TYPE="TEXT" NAME="file_ext" VALUE="',
   @file_ext,
   '" SIZE=10 STYLE="font-family: COURIER NEW;"><P> \x0d\x0a',

   '...in this folder: <INPUT TYPE="TEXT" NAME="folder_spec" VALUE="',
   @folder_spec,
   '" SIZE=50 STYLE="font-family: COURIER NEW;"><P>\x0d\x0a',

   '<INPUT TYPE="SUBMIT" ONCLICK="searchF();" VALUE="Search"><P>\x0d\x0a',

   LIST ( STRING (
             '<P ONDBLCLICK="openF ( ''',
             local_file_match.local_file_name,
             ''' );"> ',
             local_file_match.local_file_name,
             '\x0d\x0a' ),
          ''
          ORDER BY local_file_match.local_file_name ), 

   '</FORM></BODY></HTML> ' )
   FROM local_file_match; 

END;

Here are some notes on the code:

• Lines 1 through 8: The search_spec table is the program's memory, allowing it to pass the most recent search parameters from session to session.
  

• 10: The CREATE SERVER statement creates the local_folder Directory Access Server which initially points at the root folder C:\ (but will be changed later via ALTER SERVER).
  

• 12: For security purposes, the Directory Access Server feature requires an EXTERNLOGIN to be created for each SQL user id that will be using it.
  

• 14: The local_file proxy table allows the search procedure to SELECT file information from the local_folder Directory Access Server.
  

• 16 and 17: The CREATE SERVICE statement names the search procedure as being responsible for responding to browser requests with raw HTTP.
  

• 43 through 53: This loop analyzes the HTTP parameters passed from the browser and stores the values in the appropriate local variables. You can also pass named parameters via the CREATE SERVICE and CREATE PROCEDURE statments, but in the long run it's easier to maintain a simple NEXT_HTTP_VARIABLE() loop like this one.
  

• 55 through 68: This code performs some cleanup and editing of the input paramenters.
  

• 70 through 97: This CASE statement processes the two possible actions: 'Search' when the Search button is pressed, and 'Open' when a file name is double-clicked.
  

• 81 through 86: The ALTER SERVER statement changes the 'ROOT=' option to point to the input folder.
  

• 90 through 95: The xp_cmdshell system procedure call opens the selected file using "Wordpad Classic". This exact code probably won't work for you unless you have installed the original Windows XP Wordpad utility as "wordpadx.exe" in this particular location. However, it will work if you change this line

  'start "C:\\Program Files (x86)\\Windows NT\\Accessories\\wordpadx.exe" "',

  to this

  'notepad "',

• 99 through 113: This FOR loop stores the matching file names in the local temporary table local_file_match.
  

• 102: The LIKE predicate looks for the @file_ext string in the file names.
  

• 109: The LOCATE() function and xp_read_file system procedure look for the @search_for string inside the files themselves.
  

• 115: The sa_set_http_header system procedure call tells the browser that HTML text is coming next.
  

• 117 through 161: The SELECT returns a single HTML string to the browser. The \x0d\x0a characters are all unnecessary; they have been included here to make the HTML text more readable.
  

• 122 through 126: The JavaScript function searchF() and openF() allow the "Search" and "Open" actions to be customized. This may not be required in the case of searchF(), but with openF() the file_name parameter depends on where openF() was called from.
  

• 137 through 147: These INPUT TEXT tags define the three HTTP input parameters search_for, file_ext and folder_spec.
  

• 149: This INPUT SUBMIT tag calls searchF() to pass the action HTTP input parameter to the search service.
  

• 151 through 158: The SQL LIST() and STRING() functions builds a series of active, double-clickable P paragraph tags to display all the matching file names. Each ONDBLCLICK attribute calls openF() to pass the action and file_name HTTP input parameters to the search service.
  

Here are the Windows commands to create the SQL Anywhere database, start it with the HTTP server running on port 12345, compile the code using dbisql and then launch it in your browser:

"%SQLANY12%\bin32\dbinit.exe" ddd.db

"%SQLANY12%\bin32\dbspawn.exe" -f "%SQLANY12%\bin32\dbeng12.exe" -xs http(port=12345) ddd.db 

"%SQLANY12%\bin32\dbisql.com" -c "ENG=ddd;DBN=ddd;UID=dba;PWD=sql" READ ENCODING Cp1252 search.sql

START "" "http://localhost:12345/search"

Here's what the HTML looks like:

<HTML><HEAD>
<TITLE>Search</TITLE>
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<script Language="JavaScript">
function searchF() {
document.f.action = "search?action=Search";
document.f.submit();
return false; }
function openF ( file_name ) {
document.f.action = "search?action=Open&file_name=" + file_name;
document.f.submit();
return false; }
</script>
<STYLE> P { font-family: COURIER NEW; font-size: 8pt; margin-bottom: 0; margin-top: 0; } </STYLE>
</HEAD>
<BODY BGCOLOR=#FFFFFF>
<FORM NAME="f" METHOD="POST"><P>
Search for this exact string: <INPUT TYPE="TEXT" NAME="search_for" VALUE="'Sampling Stopped'" SIZE=40 STYLE="font-family: COURIER NEW;"><P> 
...in all files with this extension: <INPUT TYPE="TEXT" NAME="file_ext" VALUE=".sql" SIZE=10 STYLE="font-family: COURIER NEW;"><P> 
...in this folder: <INPUT TYPE="TEXT" NAME="folder_spec" VALUE="C:\projects\foxhound\" SIZE=50 STYLE="font-family: COURIER NEW;"><P>
<INPUT TYPE="SUBMIT" ONCLICK="searchF();" VALUE="Search"><P>
<P ONDBLCLICK="openF ( '015b_rroad_other_startup.sql' );"> 015b_rroad_other_startup.sql
<P ONDBLCLICK="openF ( '019_rroad_setup_help.sql' );"> 019_rroad_setup_help.sql
<P ONDBLCLICK="openF ( '023_rroad_start_sampling.sql' );"> 023_rroad_start_sampling.sql
<P ONDBLCLICK="openF ( '203_rroad_monitor_sample_loop.sql' );"> 203_rroad_monitor_sample_loop.sql
</FORM></BODY></HTML> 

It Could Be Simpler

Here's what the proxy table local_file looks like:

CREATE EXISTING TABLE DBA.local_file ( 
   permissions                          VARCHAR ( 10 ) NOT NULL,
   size                                 BIGINT NOT NULL,
   access_date_time                     TIMESTAMP NOT NULL,
   modified_date_time                   TIMESTAMP NOT NULL,
   create_date_time                     TIMESTAMP NOT NULL,
   owner                                VARCHAR ( 20 ) NOT NULL,
   file_name                            VARCHAR ( 260 ) NOT NULL,
   contents                             LONG BINARY NOT NULL 
) AT 'local_folder;;;.';

Not only does local_file contain the file_name, it contains the actual file contents as well!

That means instead of calling xp_read_file, the FOR loop on line 99 could simply SELECT the local_file.contents column and use that to LOCATE the search string.

---