cancel
Showing results for 
Search instead for 
Did you mean: 

output to file

ximen
Participant
1,571

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
Participant
0 Kudos

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
Participant

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
Participant
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
Participant
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

VolkerBarth
Contributor

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.