output to file

[ximen]

CREATE TABLE test ( filename VARCHAR(255) , f_updatefiles long binary );

The column name is f_updatefiles, which is used to store some common upgrade files (such as D ll files) of LL. I want to use SQL statements to export them to the server directory, but if I use

'select f_updatefiles from' DBA '. Test'; output to 😧 \\ 1.dll'

this file will report an error

The out put function can't use binary export file. The default is TXT. How to solve this problem

[Baron]

You can try:

select f_updatefiles from DBA.Test ># D:\\1.dll

OR you can try:

unload select f_updatefiles from DBA.Test to 'D:\\1.dll'

In the second case you should be connected to DB as DBA, or you should start the DB with -gl all option

[Breck_Carter]

Have you tested either of those, to make sure that 1.dll is in fact a binary DLL file, or a text representation of one?

[Breck_Carter]

CREATE TABLE test ( filename VARCHAR(255) , f_updatefiles long binary );

INSERT test VALUES (
   'dbcapi.dll', 
   xp_read_file ( 'C:\\\\Program Files\\\\SQL Anywhere 17\\\\Bin64\\dbcapi.dll' ) );
COMMIT;

unload select f_updatefiles from DBA.Test to 'c:\\\\temp\\\\1.dll';

-- The 1.dll file contains a text string '0x4d5a90000300000004000000ffff0000b80000000...

fc "c:\\temp\\1.dll" "C:\\Program Files\\SQL Anywhere 17\\Bin64\\dbcapi.dll"
Comparing files C:\\TEMP\\1.dll and C:\\PROGRAM FILES\\SQL ANYWHERE 17\\BIN64\\DBCAPI.DLL
Resync Failed.  Files are too different.

select xp_write_file ( 'c:\\\\temp\\\\2.dll', f_updatefiles ) from test;

-- The 2.dll file is a binary dll that is identical to dbcapi.dll.

fc "c:\\temp\\2.dll" "C:\\Program Files\\SQL Anywhere 17\\Bin64\\dbcapi.dll"
Comparing files C:\\TEMP\\2.dll and C:\\PROGRAM FILES\\SQL ANYWHERE 17\\BIN64\\DBCAPI.DLL
FC: no differences encountered

[Baron]

Actually I did not test it, just thought about the statment itself, and not about the content of the file.

Thank you for the very nice explanation

[VolkerBarth]

I recommend the xp_write_file() function to export files, both for text and binary files.

The OUTPUT command and the UNLOAD statement are primarily meant to export result sets to files.