I'm writing some code to export pdf files stored as blobs in databases. An issue occurs when the user is connected to a server database, it doesn't use the right path to place the file. Is there a way to query the workstation name for this user?

asked 06 Oct '16, 11:01

timoccci's gravatar image

timoccci
86159
accept rate: 0%

I am using SQL Anywhere 10 databases here. I see functions such as WRITE_CLIENT_FILE that appear in later versions, but don't look like they exist in V10. Anyone know another way?

(06 Oct '16, 11:28) timoccci

Is there a way to query the workstation name for this user?

You can get that from the AppInfo connection property.
[Hint parse that for Host= part of the string that returns]

This may or may not help you though. If you database server is running as a service, and it is logged in as the local 'SYSTEM' account, it may not sufficient network and remote system rights to write to a remote file system.

permanent link

answered 06 Oct '16, 12:30

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

I didn't think of that! What I need is some way to tell that this needs local execution and not on the server machine. The WRITE_CLIENT_FILE function in later builds seems to be able to do that from what it looks like in the documentation.

Anyone else know of a way to achieve this with V10?

(06 Oct '16, 12:53) timoccci

I guess I have never tried that with BLOBs but you might try:

In case the export needs not be part of a stored procedure or the like but can be done with a singel statement or a sequence of statements, the ISQL OUTPUT command allows a client-side export.

I guess for binary data you need to choose the according format options, possibly something like

 -- a query that should return exactly one single binary value
select MyPfdFile from MyTable where MyDocId = xyz;
output to 'MyLocalPath' format ascii escapes off quote '' hexadecimal asis;

As stated, I'm not sure whether this exports the binary data exactly as it is stored, if not, you might try to specify a particular (binary) encoding within the OUTPUT statement...

----

UPDATE: This does not seem to work in my tests with binary data.

I simply don't find a way to prevent the output from containing substitution characters, and there is no "binary" encoding (at least for v12.0.1.4403, I have no v10 setup available).

I have tested with the SQL Anywhere demo database and the products table which has a photo column with JPG images, such as:

select Photo from products where ID = 300;
output to 'C:\\Data\\TestOutputHexAsis.jpg' format ascii escapes off quote '' hexadecimal asis;

and the exported file is not identical to the output via xp_write_file():

begin
   declare binPhoto long binary;
   set binPhoto = (select Photo from products where ID = 300);
   call xp_write_file('C:\\Data\\TestWrite.jpg', binPhoto);
end;

because it replaces values which are not allowed in the (default) encoding with substitution chars such as 0x3F.

(The same procedure should however work with character data.)

permanent link

answered 06 Oct '16, 17:00

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 10 Oct '16, 02:45

Thanks for the suggestion. It gives an access denied error for me so far.

(06 Oct '16, 17:17) timoccci
Replies hidden

Have you specified the path relative to the client machine?

(06 Oct '16, 17:26) Volker Barth
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:

×20
×15
×14

question asked: 06 Oct '16, 11:01

question was seen: 188 times

last updated: 10 Oct '16, 02:45