How to use the SQL statement to save the RAR or EXE file in binary mode, and also can use the SQL statement to export and save the local file

asked 25 Aug '15, 11:01

mfkpie8's gravatar image

mfkpie8
80434853
accept rate: 9%


If you plan on reading a .RAR/.EXE from the file system and store/retrieve those into/from a table you can use the XP_READ_FILE( ) and XP_WRITE_FILE( ) system procedures.

You can also just access the files directly using a Directory Access Server and leaving the files on the file system but still have access to them via SQL.

HTH

permanent link

answered 25 Aug '15, 13:41

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

edited 25 Aug '15, 13:41

1 how to convert update.exe binary save to the database, because the use of the xp_read_file function will save is garbled!

2 the client uses any statement to convert back to EXE file pls see picture

3.Does ASA have FILESTREAM concept for user MSSQL: insert into s1(data) SELECT * FROM OPENROWSET(BULK N'd:\19991030172400.rar', SINGLE_BLOB) AS Document

(26 Aug '15, 00:42) mfkpie8
Replies hidden

xp_read_file() and xp_write_file() work with binary data (i.e. a LONG BINARY data type), so they should not garble anything at all. May it be that you use a LONG VARCHAR (or LONG NVARCHAR) variable/column instead to set/receive the data or to the store that in the according table? Then I guess the usual charset/collation issues could take place between database engine and client...


As the the FILESTREAM question: See that according question:

Does SQL Anywhere have an equivalent feature as compared to Microsoft SQL Filestream?

(26 Aug '15, 03:19) Volker Barth

thanks ,I changed the data type to LONG BINARY

When I put the PNG file in the rar file to upload the database can be, but if use EXE file is not possible,pls

(26 Aug '15, 03:57) mfkpie8
Replies hidden

Sorry, I just don't understand the exact problem: What do you mean by "upload"?

xp_read_file() will treat an EXE file exactly like any other file.

(26 Aug '15, 04:13) Volker Barth

but if use EXE file insert to table,When using xp_wirte_file output exe, found that exe can not be run

(26 Aug '15, 04:24) mfkpie8

I have two windows 2008 R2 server: A server b server Use A Computer: 1.

insert into aaa (updateexe) select xp_read_file ('c: \ windows \ system32 \ notepad.exe')

Upload --read notepad.exe notepad.exe get insert statements 2.

The next generation insert statement  INSERT INTO "DBA". "Aaa" ("updateexe") VALUES (0x4d5a90000300000004000000ffff0000b800000000000000400000000000000000000000000000000000000000000000000000000000000000000000e00000000e1fba0e00b409cd21b8014ccd21546869732070726f6772616d2063616e6e6f742062652072756e20696e20444f53206d6f64652e0d0d0a2400000000000000b2bec262f6dfac31f6dfac31f6dfac31ffa73931f5dfac31ffa73f31ebdfac31f6dfad3100dfac31ffa72f31e9dfac31ffa72831f4dfac31ffa73831f7dfac31ffa73d31f7dfac3152696368f6dfac3100000000000000000000000000000000504500004c0104000fc65b4a0000000000000000e00002010b01090000a80000)

  1. Using ISQL (interactive SQL) connection B Computer ASA Insert binary data

  2. already connected to the computer B ASA execution:

select xp_write_file ('d: \ Nnotepad.exe', updateexe) from aaa --save as d: \ Nnotepad.exe Save the notepad.exe to D:

Problem: D: \ notepad.exe not run

(27 Aug '15, 01:37) mfkpie8
showing 3 of 6 show all flat view

alt text

permanent link

answered 26 Aug '15, 00:43

mfkpie8's gravatar image

mfkpie8
80434853
accept rate: 9%

1

It looks like it worked... what's the problem?

Posting an image with no explanation is rude behavior... everyone here has better things to do than try to read your mind.

(26 Aug '15, 04:11) Breck Carter

but if use EXE file insert to table,When using xp_wirte_file output exe, found that exe can not be run

(26 Aug '15, 04:24) mfkpie8
Replies hidden
Comment Text Removed

> exe can not be run

Provide proof.

(26 Aug '15, 06:05) Breck Carter

Is it possible you just have the rows reversed? (ie. your are calling xp_write_file to write out your PK RAR file as the .EXE?)

TIP: Opening up the produced .EXE should show the MZ header and not the PK header (aka file type).

(26 Aug '15, 17:03) Nick Elson S...
Replies hidden
'I have two windows 2008 R2 server: A server b server
Use A Computer:

 CREATE TABLE aaa( 
  updateexe   LONG BINARY  COMPRESSED
);

1.

insert into aaa (updateexe)
select xp_read_file ('c: \\ windows \ system32 \\ notepad.exe')

Upload --read notepad.exe notepad.exe get insert statements
2.

  The next generation insert statement
 INSERT INTO "DBA". "Aaa" ("updateexe") VALUES (0x4d5a90000300000004000000ffff0000b8000000000000004000000000000000
00000000000000000000000000000000000000000000000000000000e00000000e1
fba0e00b409cd21b8014ccd21546869732070726f6772616d2063616e6e6f742062
652072756e20696e20444f53206d6f64652e0d0d0a2400000000000000b2bec262f
6dfac31f6dfac31f6dfac31ffa73931f5dfac31ffa73f31ebdfac31f6dfad3100df
ac31ffa72f31e9dfac31ffa72831f4dfac31ffa73831f7dfac31ffa73d31f7dfac3
152696368f6dfac3100000000000000000000000000000000504500004c0104000f
c65b4a0000000000000000e00002010b01090000a80000)


3. Using ISQL (interactive SQL) connection B Computer ASA
Insert binary data

4. already connected to the computer B ASA execution:

select xp_write_file ('d: \\ Nnotepad.exe', updateexe) from aaa
--save as d: \ Nnotepad.exe
Save the notepad.exe to D:

Problem:
D: \ notepad.exe not run
'
(27 Aug '15, 01:38) mfkpie8

Your answer was correct, I just started using var char type, but found a mistake now the type to long LONG BINARY, but found preserved exe file can not run

(27 Aug '15, 02:06) mfkpie8

Hm, such long lines just ruin the forum GUI...

Have you made sure that the files (the source exe and the one extracted from the database) are binary identical (say, with the CMD tool "fc /b")? If they are identical (and they should), make sure the file permissions are correct.

(27 Aug '15, 04:03) Volker Barth
> 'd: \\ Nnotepad.exe'

Try running Nnotepad.exe because that is the name you gave it.
(27 Aug '15, 05:53) Breck Carter

> long lines just ruin the forum GUI

Volker, you have the power to fix that! (plus a few pre tags can clear up a lot questions :)

(27 Aug '15, 05:56) Breck Carter

Yep, you are right, and it's possible even without "magical" statistics:) - Hm, although reputation points may count as stats, too:)

(27 Aug '15, 06:26) Volker Barth
showing 3 of 10 show all flat view
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:

×23

question asked: 25 Aug '15, 11:01

question was seen: 483 times

last updated: 27 Aug '15, 06:27