output to file

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 D: \ 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

sa-12

asked 08 Dec '19, 05:10

mfkpie8
273●66●70●75
accept rate: 12%

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

(09 Dec '19, 09:27) Baron

Replies hidden

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?

(09 Dec '19, 13:41) 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

(09 Dec '19, 13:52) Breck Carter

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

(10 Dec '19, 03:22) Baron

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 08 Dec '19, 06:37

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 09 Dec '19, 03:17

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

sa-12 ×438

question asked: 08 Dec '19, 05:10

question was seen: 1,198 times

last updated: 10 Dec '19, 03:22

SAP SQL Anywhere

output to file

Follow this question

Related questions