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. |
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', |
Probably related to that FAQ?
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".
Yes, I agree, it would be nice if both kind of errors would be flagged via error codes.