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

asked 08 Dec '19, 05:10

mfkpie8's gravatar image

mfkpie8
184475564
accept rate: 14%

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) Sako
Replies hidden
1

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
1
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) Sako

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%20Barth's gravatar image

Volker Barth
36.1k342501749
accept rate: 34%

edited 09 Dec '19, 03:17

Your answer
toggle preview

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

Question tags:

×432

question asked: 08 Dec '19, 05:10

question was seen: 214 times

last updated: 10 Dec '19, 03:22