Obtaining the name of the workstation the current connection is on

[Former Member]

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?

[Former Member]

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?

[Former Member]

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.

[Former Member]

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?

[VolkerBarth]

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.)

[Former Member]

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

[VolkerBarth]

Have you specified the path relative to the client machine?