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.

Today I use xp_read_file to see if a file exists or not. But on somtimes it return null even if a file exist?

if xp_read_file('\\\server10\\folder1\\test.log') is null then 0 else 1 endif;

I guess there's better way than do like this to check if a file exist or not?

asked 22 Jan '16, 10:25

Rolle's gravatar image

accept rate: 0%

edited 22 Jan '16, 10:28

When you do use xp_read_file() - say, because you are not running v16 or above - you can set the optional 2nd "lazy" parameter to 1 or the like to prevent the file contents from being read. That should give a performance benefit particularly for larger files.

(22 Jan '16, 16:49) Volker Barth

We use v12. What then is the best method to check if a file exists or not with that version. xp_read_file works fine, but many times it returns null even if it is a physical file on that path?

(23 Jan '16, 05:27) Rolle
Replies hidden

Sounds like a bug, unless perhaps it is returning null when the file is empty... " If the file does not exist or cannot be read, NULL is returned." - V16 Help

(23 Jan '16, 09:10) Breck Carter

Strange. Is there a bugfix made between v12 and v16?

(23 Jan '16, 09:37) Rolle

Note the "or cannot be read" part - possibly the according file is opened in exclusive mode?

E.g. trying to open the database's own translog will return NULL although it certainly exists at that time:

select db_property('LogName'), xp_read_file(db_property('LogName'));

(And according to your sample, the same will be true if you try to open the log of another database...)

(25 Jan '16, 03:00) Volker Barth

I do not quite understand what you mean. If I want to see if a file exists or not, whether it is open or not, what do I do? Your example only returns null for me.

(25 Jan '16, 15:12) Rolle
Replies hidden

If the files you are looking at may be open / locked you need to look at the other two solutions proposed below: ie a directory server, or simpler if you are using v16 / v17 sp_list_directory()

xp_read_file() does what it says - it reads the file. If the operating system won't let the database engine read the file, it can't read it and therefore can't return anything.

Also be aware that xp_read_file() by default reads the whole file, so if the files that you are interested in are very large, it could be slow and generate a lot of disk activity. It that case you can choose to read just one byte of the file with something like:

select byte_substr(xp_read_file(<filepath>,1),0,1)

which will return just the first byte of the file - enough to know if there is something there!

(25 Jan '16, 15:16) Justin Willey

Ok, I think the problem is that the operating system won't let the database engine read the file. There we have the answer! So, I use v12, is there any other way to look if a file exist or not?

(25 Jan '16, 15:37) Rolle
Replies hidden

Thiago's solution will work in v12 - see the docs on CREATE SERVER

If you don't want to do that for whatever reason I suggest you think about using a batch file with an construct like:

 IF EXIST filename ... 

that set an error level accordingly and call that with xp_cmdshell() and look at the return values.

(25 Jan '16, 16:03) Justin Willey

Big thanks! Something like this?

select xp_cmdshell('if not exist c:\test.txt goto error')

Returns 1 since goto error returns an error since error does not exist.

select xp_cmdshell('if exist c:\test.txt goto error')

Returns 0 since c:\test.txt exists.

(25 Jan '16, 16:13) Rolle

Regarding Thiago solution, I guess I should change this row to include subfolders

create server backup_tree class 'directory' using 'root=L:\LOG';subdirs=y
(25 Jan '16, 16:20) Rolle

Doesn't cmdshell work on a network path? Locally on the C:\ it works for me, but when I try on a mapped drive, it will not work. Ex:

 select xp_cmdshell('if not exist H:\\test.txt goto error', 'no_output')
(26 Jan '16, 15:56) Rolle

It depends on the rights of the user that the database engine runs under. The default system user won't have rights to network locations. You'll need to configure network rights appropriately whichever method you use.

(26 Jan '16, 16:05) Justin Willey

Thanks for fast response!

Can you describe a little more how I should tell our IT provider how to configure the database engine so it get network rights?

(26 Jan '16, 16:17) Rolle

You may have a look at that FAQ, both as to the background and possible solutions:

How do I execute a batch file using xp_cmdshell?

BTW: Is that the reason why xp_read_file() does fail for you - the engine is asked to read a file on a net share that it cannot access? Or is the file locked exclusively? What do you want to do with the file if it exists?

(27 Jan '16, 01:19) Volker Barth
More comments hidden
showing 5 of 15 show all flat view

Just for completeness I'd like to add a further solution (although certainly not an "easy one", so I would prefer the directory access server for v12):

You can also create an external (native) function that calls an OS-level function to check for an existing file, say for Windows to call the GetFileAttributes API.

Breck has explained in his (former?) blog how to call a different WinAPI function (GetOpenFileName()) here:

Calling GetOpenFileName() From SQL

permanent link

answered 25 Jan '16, 17:12

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%


Are you saying that doesn't pass the "something simpler" test? :)

(27 Jan '16, 08:10) Breck Carter

SELECT 1 FROM sp_list_directory( '\\\\server10\\folder1\\' ) where file_path = '\\\\server10\\folder1\\test.log'

permanent link

answered 22 Jan '16, 11:17

Thiago%20Reis's gravatar image

Thiago Reis
accept rate: 40%

converted 22 Jan '16, 16:41

Volker%20Barth's gravatar image

Volker Barth

Just to note: sp_list_directory() was introduced in v16.

(22 Jan '16, 16:44) Volker Barth

I have this example that I use to check my backup logs:

 create server backup_tree class 'directory' using 'root=L:\LOG';
 create externlogin dba to backup_tree;
 create existing table backup_files at 'backup_tree;;;.';

After that, to check a file, you can just run a simple select:

 select file_name from backup_files;

Your directory will work like a table. You can select, update and delete files. It works for me.

permanent link

answered 22 Jan '16, 11:06

Thiago%20Reis's gravatar image

Thiago Reis
accept rate: 40%

edited 22 Jan '16, 11:08

Thanks! But there must be something simpler way to do it?

(22 Jan '16, 11:10) Rolle

Can I use this to include subfolders? Or Can I load two different folders to same table?

(25 Jan '16, 15:14) Rolle
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: 22 Jan '16, 10:25

question was seen: 3,567 times

last updated: 27 Jan '16, 08:10