Monday, January 21, 2019

Replace SERVER CLASS 'DIRECTORY' with sp_list_directory()

Question: How do I fix these unexplained intermittent errors when using CREATE SERVER CLASS 'DIRECTORY' to get a list of the file names in a folder?

  • From a SELECT statement even though the CREATE EXISTING TABLE works OK:
    SQLCODE = -141, SQLSTATE = 42W33, ERRORMSG() = Table '...' not found
    

  • From a CREATE EXISTING TABLE statement even though the CREATE EXTERNLOGIN is correct:
    SQLCODE = -656, SQLSTATE = HY000, ERRORMSG() = Unable to connect to server '...': DirectoryAccess servers require an externlogin
    
Answer: Switch to using sp_list_directory(); here's how:
  • Set the max_depth parameter to 1 in the sp_list_directory() call if you are using SUBDIRS=0 in the CREATE SERVER statement.

  • Use SELECT TOP 1 and sa_split_list() to get the file name out of the file_path column returned by sp_list_directory().

  • Use LATERAL to join the sp_list_directory() and sa_split_list() calls.

By Example

After several years of intermittent errors, the SERVER CLASS 'DIRECTORY' method was recently judged too fragile for further use in Foxhound. Here is the before-and-after code for the four sections that were changed:

Example 1 BEFORE: SERVER CLASS 'DIRECTORY'
CREATE SERVER local_folder CLASS 'DIRECTORY' USING 'ROOT={@foxhound_folder};SUBDIRS=0';

CREATE EXTERNLOGIN DBA TO local_folder;

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

SELECT TOP 1 
       local_file.size,
       local_file.file_name
  INTO @size,
       @file_name
  FROM local_file 
 WHERE local_file.file_name LIKE @patch_file_name_like_pattern
   AND NOT EXISTS ( SELECT *
                      FROM rroad_applied_patch
                     WHERE rroad_applied_patch.file_name = local_file.file_name )
 ORDER BY local_file.file_name ASC; 
Example 1 AFTER: sp_list_directory()
SELECT TOP 1 
       sp_list_directory.file_size    AS size,
       local_file.file_name           AS file_name
  INTO @size,
       @file_name
  FROM sp_list_directory ( @foxhound_folder, 1 ),
       LATERAL ( SELECT TOP 1 
                        sa_split_list.row_value AS file_name
                   FROM sa_split_list ( sp_list_directory.file_path, '\\' )
                  ORDER BY sa_split_list.line_num DESC 
               ) AS local_file 
 WHERE sp_list_directory.file_type = 'F'
   AND local_file.file_name        LIKE @patch_file_name_like_pattern
   AND NOT EXISTS ( SELECT *
                      FROM rroad_applied_patch
                     WHERE rroad_applied_patch.file_name = local_file.file_name )
 ORDER BY local_file.file_name ASC;
Example 2 BEFORE: SERVER CLASS 'DIRECTORY'
CREATE SERVER dynamic_source_help_folder
   CLASS 'DIRECTORY' USING 'ROOT=C:/projects/foxhound5/help/dynamic_source;SUBDIRS=0';

CREATE EXTERNLOGIN DBA TO dynamic_source_help_folder;

CREATE EXISTING TABLE dynamic_source_help_folder AT 'dynamic_source_help_folder;;;.';

INSERT temp_help
SELECT dynamic_source_help_folder.file_name ,
       dbo.xp_read_file ( STRING ( 'C:/projects/foxhound5/help/dynamic_source/', dynamic_source_help_folder.file_name ) )
  FROM dynamic_source_help_folder 
 WHERE dynamic_source_help_folder.file_name LIKE '%.html'
 ORDER BY dynamic_source_help_folder.file_name;
Example 2 AFTER: sp_list_directory()
INSERT temp_help
SELECT dynamic_source_help_folder.file_name ,
       dbo.xp_read_file ( STRING ( 'C:/projects/foxhound5/help/dynamic_source/', dynamic_source_help_folder.file_name ) )
  FROM sp_list_directory ( 'C:/projects/foxhound5/help/dynamic_source', 1 ),
       LATERAL ( SELECT TOP 1 
                        sa_split_list.row_value AS file_name
                   FROM sa_split_list ( sp_list_directory.file_path, '\\' )
                  ORDER BY sa_split_list.line_num DESC 
              ) AS dynamic_source_help_folder  
 WHERE sp_list_directory.file_type          = 'F'
   AND dynamic_source_help_folder.file_name LIKE '%.html'
 ORDER BY dynamic_source_help_folder.file_name;
Example 3 BEFORE: SERVER CLASS 'DIRECTORY'
CREATE SERVER backup_folder CLASS 'DIRECTORY' USING 'ROOT={@backup_folder};SUBDIRS=0';
      
CREATE EXTERNLOGIN DBA_activation TO backup_folder;
      
CREATE EXISTING TABLE backup_file AT 'backup_folder;;;.';

INSERT backup_file_drop_down_list_entry
SELECT ROW_NUMBER() OVER ( ORDER BY backup_file.file_name DESC )    AS row_number,
       backup_file.file_name                                         AS file_name
  FROM backup_file 
 WHERE backup_file.file_name LIKE STRING ( 'foxhound5-', @build_number, '-mini-backup-%.txt' )
 ORDER BY backup_file.file_name DESC;
Example 3 AFTER: sp_list_directory()
INSERT backup_file_drop_down_list_entry
SELECT ROW_NUMBER() OVER ( ORDER BY file_name.file_name DESC )    AS row_number,
       file_name.file_name                                         AS file_name
  FROM sp_list_directory ( @backup_folder, 1 ),
       LATERAL ( SELECT TOP 1 
                        sa_split_list.row_value AS file_name
                   FROM sa_split_list ( sp_list_directory.file_path, '\\' )
                  ORDER BY sa_split_list.line_num DESC 
               ) AS file_name  
 WHERE sp_list_directory.file_type = 'F'
   AND file_name.file_name         LIKE STRING ( 'foxhound5-', @build_number, '-mini-backup-%.txt' )
 ORDER BY file_name.file_name DESC;
Example 4 BEFORE: SERVER CLASS 'DIRECTORY'
CREATE SERVER backup_folder CLASS 'DIRECTORY' USING 'ROOT={@backup_folder};SUBDIRS=0';

CREATE EXTERNLOGIN DBA TO backup_folder;

CREATE EXISTING TABLE backup_file AT 'backup_folder;;;.';

FOR f_backup_file AS c_backup_file INSENSITIVE CURSOR FOR
SELECT TOP ALL START AT ( @mini_backup_retention_count + 1 )
       backup_file.file_name AS @file_name
  FROM backup_file
 WHERE backup_file.file_name LIKE STRING ( 'foxhound5-', @build_number, '-mini-backup-%.txt' )
 ORDER BY backup_file.file_name DESC
FOR UPDATE
DO
Example 4 AFTER: sp_list_directory()
FOR f_backup_file AS c_backup_file INSENSITIVE CURSOR FOR
SELECT TOP ALL START AT ( @mini_backup_retention_count + 1 )
       backup_file.file_name AS @file_name
  FROM sp_list_directory ( @backup_folder, 1 ),
       LATERAL ( SELECT TOP 1 
                        sa_split_list.row_value AS file_name
                   FROM sa_split_list ( sp_list_directory.file_path, '\\' )
                  ORDER BY sa_split_list.line_num DESC 
              ) AS backup_file 
 WHERE sp_list_directory.file_type = 'F'
   AND backup_file.file_name       LIKE STRING ( 'foxhound5-', @build_number, '-mini-backup-%.txt' )
 ORDER BY backup_file.file_name DESC
FOR UPDATE
DO