i have table with 2 column ID , Image(save file path) but i need save image as Blob in new column. i am using SQLAnywhere

asked 24 Jul '14, 03:32

eraqaty's gravatar image

accept rate: 0%

edited 24 Jul '14, 03:46

If the file exists on the same computer that is running the SQL Anywhere 12 engine, then see xp_read_file system procedure.

If the file exists on a different computer (say, on a client computer that is connected over the network to dbsrv12.exe running on a server computer) then see READ_CLIENT_FILE function.

permanent link

answered 24 Jul '14, 07:32

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

i will try to use xp_read_file as "update test set blob=xp_read_file( image);"

permanent link

answered 24 Jul '14, 04:01

eraqaty's gravatar image

accept rate: 0%

edited 24 Jul '14, 04:02

OK, you have been faster:)

(24 Jul '14, 04:06) Volker Barth

done using xp_read_file ,but now i have other problem ,there are image too large and i need to reduce its size before save it as blob.

(24 Jul '14, 07:52) eraqaty
Replies hidden

image too large

Too large for what? What datatype has the according column?

(24 Jul '14, 07:58) Volker Barth

some image file with size 3 ,4 MB and i need to reduce its size before save in database for performance

(24 Jul '14, 08:19) eraqaty
Replies hidden

Hm, then you might read that article on what SQL Anywhere can - and cannot - do to compress data:

Compressed Columns - where's the squeeze - client or server?

(24 Jul '14, 08:52) Volker Barth

What kind of files are they? JPGs are already compressed so you won't be able to do much. BMPs will benefit greatly from compression, so will PNGs but to a lesser extent.

SQL Anywhere splits blob data into separate storage so it does a pretty efficient job of handling them... don't assume they will cause a performance problem, do some testing to prove it.

Are you using MobiLink synchronization? If so, there are techniques that can be used so blobs don't kill sync times.

(24 Jul '14, 09:32) Breck Carter
showing 3 of 6 show all flat view

If I understand your situation correctly, you have already a list of image file pathes in the according table but want to store the actual images there, too. So you will need to use xp_write_file() to read the file contents. (Note: That would require the database engine can access these files, i.e. they are not stored on a client computer.)

Here's a good starting point from the docs:

Insertion of documents and images

To go on further, you might use a cursor to loop over each row from the table and access the according file and insert its data via an UPDATE statement. There are also ways to do to this in one UPDATE statement, such as

UPDATE MyTable SET MyImage = xp_read_file(MyImagePath);

However, that may be a heavy burden in case you have lots of images to store, so a one-by-one approach might be easier.

permanent link

answered 24 Jul '14, 04:05

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



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:


question asked: 24 Jul '14, 03:32

question was seen: 5,112 times

last updated: 24 Jul '14, 09:32