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
You can get that from the AppInfo connection property.
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.
answered 06 Oct '16, 12:30
Nick Elson S...
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 v220.127.116.1103, 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.)