SQL Anywhere: 2019

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

SQL Anywhere

Pages

Monday, December 30, 2019

Let's take that one step further

Sunday, December 29, 2019

Trigger Warning!

Thursday, December 26, 2019

It's fixed!

Tuesday, December 24, 2019

Merry Christmas

Sunday, December 22, 2019

Disaster Testing

Friday, December 20, 2019

No, never

Tuesday, December 17, 2019

You have to admit, they're motivated

Monday, December 16, 2019

It worked yesterday

Sunday, December 15, 2019

Release Candidate 47: Is Foxhound 5 Ready Yet?

Saturday, December 14, 2019

The older the code, or the coder?

Tuesday, December 10, 2019

But I didn't HAVE two days to spare!

Monday, January 21, 2019

Replace SERVER CLASS 'DIRECTORY' with sp_list_directory()

By Example

Categories