|
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.