How to use READ_CLIENT_FILE() in real life ?

[Former Member]

I write a procedure that read an xml file on a client computer, parse with OPENXML() and insert the data. The database option allow_read_client_file is set to On for the user. The READCLIENTFILE authority is set for the group. The server start with -sf -read_client_file parameter.

All work fine in Interactive SQL but in the client application with a standard user, not a DBA, I get a message : "the client application don't allow the transfer".

I have see that the client application have to register a callback function but I don't found any informations on this function.

We use ADO.NET and ODBC client with SQLA 11 & 12.

[Breck_Carter]

Please show us your code that is failing, and tell us the exact error message and SQLCODE your are getting.

[VolkerBarth]

When you are using indirect file transfer (i.e. by calling that function from within stored functions, procedures and the like) within ODBC, you'll have to consider that doc page on SA_REGISTER_VALIDATE_FILE_TRANSFER_CALLBACK

[Breck_Carter]

Volker, is that doc topic written in German? ...it certainly isn't English, but it doesn't look like German either 🙂

[Former Member]

The error code is :
Client application does not allow transfert of data ('C:CS3.XML')
SqlState = S1000
SQLCODE = -1171

C:CS3.XML is the path to the file I want read

The procedure code is :
ALTER PROCEDURE "EASYCOM"."EASYCAT_PARSING"(IN _FILE CHAR(254 CHAR))
BEGIN ATOMIC
DECLARE _CAT CHAR(6 CHAR);
DECLARE _XML XML;
IF _FILE IS NOT NULL THEN
SELECT CAST(READ_CLIENT_FILE(_FILE) AS XML) INTO _XML;
IF _XML IS NOT NULL THEN
...
parsing and INSERT Code
...
ENDIF
ENDIF
END

[Former Member]

I think it is the good entry point for my problem. Thank's

But it is complex for me.

Actually the parsing is done on the client side. The cost is 105 seconds for parsing and inserting a file of 12 Mo in 11 tables.

With the procedure above the cost is less than 40 seconds for the same task in Interactive SQL.

[Former Member]

I have read the documentation pointed by Volker and I have four questions :

=> the solution can work for ODBC but how do for the ADO.NET interface ?

=> if some malicious guy have some controls on the database server is the file transfer the primary problem for security ?

=> I do not understand why a batch with abitrary instructions is more secure than execute a procedure with limited instructions, a direct call is more secure than a procedure call ?

=> I do not understand why ISQL that have the code that validate the transfer is more secure than a client application with a far more limited acces ?

[VolkerBarth]

It's ODBCEnglish:)

[Former Member]

With the doc topic pointed by Volker I have found a workaround that can work for all client interface :

1) I Create a variable at the connection level @SIFXML with the long binary Data type.

2) First populate the Variable with a SELECT READ_CLIENT_FILE(ClientFilePath) INTO @SIFXML;
This is a direct call not supported in the procedure call.

3) Call the parsing and loading procedure with a little change :

CREATE PROCEDURE "EASYCOM"."EASYCAT_PARSING"()
BEGIN ATOMIC
DECLARE _CAT CHAR(6 CHAR);
DECLARE _XML XML;
IF @SIFXML IS NOT NULL THEN
SELECT CAST(@SIFXML AS XML) INTO _XML;
IF _XML IS NOT NULL THEN
SET @SIFXML = NULL;
...
parsing ans loading code ...
ENDIF;
ENDIF;
ENDIF;
END;