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 '19, 16:03

Breck%20Carter's gravatar image

Breck Carter
30.8k496680992
accept rate: 20%

Probably related to that FAQ?

(31 Jul '19, 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 '19, 07:45) Breck Carter

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

(01 Aug '19, 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 '19, 08:49

Breck%20Carter's gravatar image

Breck Carter
30.8k496680992
accept rate: 20%

edited 01 Aug '19, 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:

×2

question asked: 31 Jul '19, 16:03

question was seen: 269 times

last updated: 01 Aug '19, 08:54