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.

asked 31 Jul, 16:03

Breck%20Carter's gravatar image

Breck Carter
29.3k486650959
accept rate: 20%

Probably related to that FAQ?

(31 Jul, 16:09) Volker Barth
Replies hidden

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

(01 Aug, 07:45) Breck Carter

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

(01 Aug, 08:50) Volker Barth

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',
permanent link

answered 01 Aug, 08:49

Breck%20Carter's gravatar image

Breck Carter
29.3k486650959
accept rate: 20%

edited 01 Aug, 08:54

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×1

question asked: 31 Jul, 16:03

question was seen: 123 times

last updated: 01 Aug, 08:54

Related questions