How do I get sp_list_directory() to raise an exception when there's a problem?

[Breck_Carter]

If SQL Anywhere is run as a Windows service created with dbsvc -as, the following statement

select * FROM sp_list_directory ( '\\\\\\\\Inspiron\\\\c\\\\DATA\\\\InspironOFSSremote\\\\', 1 )

returns an empty result set rather than raising a SQLCODE -602 "Could not execute statement - Cannot access folder - Access is denied" because the LocalSystem Windows accound could not deal with the UNC filespec.

When the service is correctly created with dbsvc -a user -p password, sp_list_directory correctly returns a result set.

My problem is this: An "empty result set" does not indicate an error, because the directory could indeed be empty... I want to know about the problem so that an error message can be displayed.

[VolkerBarth]

Probably related to that FAQ?

[Breck_Carter]

Thanks for that link... I remembered reading it but couldn't find it.

Here's a restatement of your question: "How do I get sp_list_directory() to tell me when a directory exists?"

My question is: "How do I get sp_list_directory() to tell me when it can't access a directory at all?"

These sp_things are nice, but the Help is not exaggerating when it says "they are not as flexible nor as powerful as directory access proxy tables and servers".

[VolkerBarth]

Yes, I agree, it would be nice if both kind of errors would be flagged via error codes.

[Breck_Carter]

Here's a workaround demo that shows how a CLASS 'DIRECTORY' CREATE EXISTING TABLE statement can be used to check if a folder is accessible or not.

If not, as when dbsvc -as is used, it raises a SQLCODE -1066 File system error.

(Note: If all you want to do is check the validity of the folder, the SELECT * FROM check_file is unnecessary.)

BEGIN
DECLARE @sqlcode    INTEGER;
DECLARE @sqlstate   VARCHAR ( 5 );
DECLARE @errormsg   VARCHAR ( 32767 );
DECLARE @check_path LONG VARCHAR;

BEGIN
   DROP TABLE check_file;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP EXTERNLOGIN DBA TO check_folder;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER check_folder;
   EXCEPTION WHEN OTHERS THEN
END;

SET @check_path = '\\\\\\\\Inspiron\\\\c\\\\DATA\\\\InspironOFSSremote\\\\';

CREATE SERVER check_folder CLASS 'DIRECTORY' USING 'ROOT={@check_path};SUBDIRS=0';

CREATE EXTERNLOGIN DBA TO check_folder;

BEGIN
   CREATE EXISTING TABLE check_file AT 'check_folder;;;.';
   EXCEPTION WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
       INTO @sqlcode, @sqlstate, @errormsg;
      MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, 
         ' SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) TO CONSOLE;
      RESIGNAL;
END;

SELECT * FROM check_file;

BEGIN
   DROP TABLE check_file;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP EXTERNLOGIN DBA TO check_folder;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER check_folder;
   EXCEPTION WHEN OTHERS THEN
END;

END;

-- dbsvc -as doesn't allow access to the folder...

MESSAGE ... TO CONSOLE:
I. 08/01 08:39:03. DIAG 2019-08-01 08:39:03.131 SQLCODE = -1066, SQLSTATE = WO027, ERRORMSG() = File system error: \\\\Inspiron\\c\\DATA\\InspironOFSSremote

RESIGNAL:
Could not execute statement.
File system error: \\\\Inspiron\\c\\DATA\\InspironOFSSremote
SQLCODE=-1066, ODBC 3 State="HY000"
Line 1, column 1
(Continuing after error)

-- dbsvc -a -s does allow access...

permissions,size,access_date_time,modified_date_time,create_date_time,owner,file_name,contents
'-rw-rw-rw-',8,'2019-07-31 16:29:28.000','2019-07-31 16:29:28.000','2019-07-31 16:29:12.000','0','xxx.txt',