Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.


I am looking for a solution in ASA 9 to list the contents of a folder or just report back whether the folder exists and/or contains files would be acceptable.

Is this possible?

asked 05 Mar '18, 10:22

Jongee's gravatar image

accept rate: 0%

AFAIK there's no functionality in ASA9 to browse folder contents. Directory access servers were introduced with the following version, SA10.

Slightly OT: it would be possible to check for existence of a file using xp_read_file(<filepath>) and checking the result (NOT NULL if file exists and has size > 0).

(05 Mar '18, 11:13) Reimer Pods
Replies hidden

I found reference to the xp_read_file function but as the documents are stored on a network, I am struggling to get this to work.

(05 Mar '18, 11:14) Jongee

Not with built-in facilities, AFAIK. Starting with version 10, you could use "directory access servers" (a special kind of proxy tables) to do so, and version 16 has introduced a bunch of file-related procedures.

If you do know the possible file names, you could try to use xp_read_file to read the contents. However, in my tests that does not work to "read" directories.

permanent link

answered 05 Mar '18, 11:21

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Ah, Breck was faster...

Note that xp_read_file is server-based, i.e. the database engine needs to be allowed to access the files, which often requires particular permissions within a network.

(05 Mar '18, 11:23) Volker Barth
Replies hidden
Comment Text Removed

> Breck was faster

who? :)

(05 Mar '18, 12:09) Breck Carter

Sigh, I should not use a mobile to answer questions... Reimer was the one I meant...

(05 Mar '18, 12:21) Volker Barth

Once upon a time, there was Calling GetOpenFileName() From SQL

permanent link

answered 05 Mar '18, 14:38

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

I took a look at this option but decided against it after I read the 'Libertine answer'.

(06 Mar '18, 03:24) Jongee
Replies hidden

Hm, IMHO Breck's point was not the particular Windows API function to open a file but to show how you can call API functions via the external call interface. That way you could also use C based POSIX functions like opendir() or FindFirstFile() from the WINAPI. Not easy, if you are not familiar with C, I guess, but doable...

(06 Mar '18, 06:13) Volker Barth

Of course you can also use xp_cmdshell with an OS-level command to list files, here for Windows...

-- Code snippet to use xp_cmdshell to list directory contents on Windows via DIR /b
-- with output redirection for the file ("> ...") list or an error ("2> ...").
   declare ret_code int;
   declare dir_list long varchar;
   declare err_desc long varchar;
   ' Hard coded pathes within the current users dir tree - list according PDF files
   ret_code = call xp_cmdshell('DIR /b C:\\Users\\MyName\\Documents*.pdf > C:\\Users\\MyName\\MyDirList.txt 2> C:\\Users\\MyName\\MyDirError.txt', 'no_output');
   ' xp_cmdshell returns 0 for success and <> 0 otherwise
   if ret_code = 0 then
      set dir_list = xp_read_file('C:\\Users\\MyName\\MyDirList.txt');
   ' one possible "error": no file is found
      set err_desc = xp_read_file('C:\\Users\\MyName\\MyDirError.txt');
    end if;
    select ret_code, dir_list, err_desc;

There are more samples in this forum for xp_cmdshell...

Note that the DIR command will both return an error for non-existing and empty directories. The error text will depend on the OS language. Of course you should delete the result files afterwards, probably by another use of xp_cmdshell...  

permanent link

answered 07 Mar '18, 03:57

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 05 Mar '18, 10:22

question was seen: 1,870 times

last updated: 07 Mar '18, 03:57