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;
