Question Re: SQL Anywhere 17 build 6933 missing write_client_file() function in Technology Q&A

SQL Anywhere 17 build 6933 missing write_client_file() function

fvestjens — Fri, 07 Apr 2023 03:47:47 GMT

We want to write a downloaded file with scripting to a folder on the client machine. But the procedure write_client_file() seems to be missing. Has it been replaced? Has it something to do with having the proper rights. The function is mentioned in the help files and I would expect that the function would be available. The error we get:

Could not execute statement.
Procedure 'write_client_file' not found
SQLCODE=-265, ODBC 3 State="42S02"

Re: SQL Anywhere 17 build 6933 missing write_client_file() function

MarkCulp — Fri, 07 Apr 2023 07:38:30 GMT

Check the documentation for write_client_file here. You need to ensure a number of privileges have been enabled/set before using this function.

Also, you must be using a client API that uses the CmdSeq protocol (ODBC, dblib). If you are using any of the other APIs then this function cannot be used. What client API are you using?

Re: SQL Anywhere 17 build 6933 missing write_client_file() function

fvestjens — Fri, 07 Apr 2023 08:31:10 GMT

We're trying to run the function from within a script in the database.

But according to your comment this does not seem to be possible. This can only be achieved by creating a share on the client that is accessible from the server the database is running and then us xp_Write_file()

Re: SQL Anywhere 17 build 6933 missing write_client_file() function

chris_keating — Fri, 07 Apr 2023 09:30:25 GMT

That function should not be 'CALL'ed. It should be executed either as SELECT or via a SET var =. Here is an example:

set temporary option allow_write_client_file = 'on'; // permit write_client_file for current connection
select write_client_file( 'c:\\temp\\write_client_file.txt', 'Testing write_client_file with SELECT' );
set retval = write_client_file( 'c:\\temp\\write_client_file2.txt', 'Testing write_client_file with SET' );

-- but this will report Procedure 'write_client_file' not found
call write_client_file( 'c:\\temp\\write_client_file3.txt', 'Testing write_client_file with CALL'

It is also possible to further secure the feature with -sf.

Re: SQL Anywhere 17 build 6933 missing write_client_file() function

VolkerBarth — Fri, 07 Apr 2023 12:51:49 GMT

How and where do you execute that script? For example, it certainly is doable to run a script within a CmdSeq-based client on a user machine that accesses files on the client machine or on net shares that client can access... We do so.

Re: SQL Anywhere 17 build 6933 missing write_client_file() function

fvestjens — Sat, 08 Apr 2023 05:57:58 GMT

From a client application we call a stored procedure where we want to pass the location where to store the file on the client. The procedure first downloads a binary file using a web service and then needs to store it on the passed location on the client. The client application is a windows application that uses the ODBC driver to connect to the database.

Re: SQL Anywhere 17 build 6933 missing write_client_file() function

VolkerBarth — Sat, 08 Apr 2023 12:05:48 GMT

That should work but when the client file read/write is issued within a stored procedure (in contrast to via directly from the client code), it requires a particular ODBC callback, see here, in addition to the required privilege.