The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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.

asked 24 Apr '13, 10:21

Costa's gravatar image

Costa
106118
accept rate: 0%

edited 25 Apr '13, 12:43

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264

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

(24 Apr '13, 11:10) Breck Carter
Replies hidden

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

(24 Apr '13, 11:41) Volker Barth
Replies hidden

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

(24 Apr '13, 13:04) Breck Carter

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

(24 Apr '13, 16:16) Costa

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.

(24 Apr '13, 16:49) Costa

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 ?

(25 Apr '13, 02:22) Costa

It's ODBCEnglish:)

(25 Apr '13, 06:10) Volker Barth
showing 3 of 7 show all flat view

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;

permanent link

answered 25 Apr '13, 05:27

Costa's gravatar image

Costa
106118
accept rate: 0%

edited 25 Apr '13, 05:32

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:

×124
×35
×21
×1

question asked: 24 Apr '13, 10:21

question was seen: 1,095 times

last updated: 25 Apr '13, 12:43