Managing image data types

Question

Post reply

Managing image data types

Stefan Ardeleanu

Mr or Mrs. 500

Points: 501
More actions
April 26, 2004 at 2:40 am

#359431

I tried to import with DTS an Oracle database to a SQL Server database.
I realise this task with one single, but important, exception.
It exists a BLOB field in a table.
I consider that it's corespondent in a SQL Server database is image.
But when I tried to import it was imposible.
How can I manage image types because it seams to be the only type compatible with oracle blob types.
On the other side I notices that I can not use full text searvhes on image types. Is there anybody who can advice hoew can I manage these image data types. It seams that only the application can help because SQL Server is not very useful in managing image data types.
Thanks'

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 1

Stefan,

An Image data type does not store text so how would you do a full text search of it?

What are you trying to store? In Oracle is it a CLOB or LONG data type for the field you are trying to import?

If you are storing text in a field, you should use the TEXT data type.

Robert

Stefan Ardeleanu Mr or Mrs. 500 Points: 501 More actions · Answer 2

The application where I work store large documents in a binary format.

In Oracle is it posible to realise queries based on this kind of columns.

I found in BooksONline that this is posible in SQL Server too but I cannot get the fdata from the queries I wrote.

Is it posible to construct a full text index on column having an image type. That column must be asociated with another column (string) were it is specified the extension of the file (.doc etc).

But I cannot get the data from the queries (using contains and other similar sintax). Thank you very much.

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 3

In SQL Server, change it to a TEXT datatype and try that. In Oracle, you are not storing the document in its native format, you are only storing the text of the document. That is why you need to store it in SQL Server in the TEXT datatype column.

I would slo suggest that if you are wanting to store the document in native format, that you use the IMAGE datatype for that. But, you will still need the text of the document stored in the TEXT datatype column.

Stefan Ardeleanu Mr or Mrs. 500 Points: 501 More actions · Answer 4

But can I store a .doc file, for example, in a text field?

If the doc contains text and images can I store the whole file contain in a text field. Because this is the purpose of the image, to can save entire documents in a filed. And the document can be .doc, .txt. I read in Books Online that it is posible in image and it can be indexed but I can'i interogate the field.

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 5

No, you cannot store a .doc file in a TEXT column. You have to use an IMAGE column to do that. If a document contains both text and images, and you want to store both the text and the images, you will have to use an IMAGE column to store the actual document. The document can be of any type if you use the IMAGE column to store the document. A TEXT column will ONLY store text. I went to books online and I cannot find any reference to being able to index an IMAGE datatype column.

I have given you the how.

1. Create a TEXT column to store the text of any document you want to store.

2. Create an IMAGE column to hold the actual document.

3. Perform any searches on the TEXT column.

Since the IMAGE column stores data in a binary format, it is only logical that you would NOT be able to search for text in it.

Stefan Ardeleanu Mr or Mrs. 500 Points: 501 More actions · Answer 6

Is it posible to create full text index on image columns. Search the stored procedure sp_fulltext_column and you will see that exista a parameter named @type_colname used only for image indexes. You will also find in the chapter creating and mantaining databases, full text indexes, Filtering Supported File Types that image can be indexed.

I already indexed the column but I don't know what mistake I make because I cannot query the file using contains or other similar phrases. In fact, the problem is that any query I make return no rows.

I search the Microsoft site and they say it is a bug and that sometimes the index on the image column is not created properly and that you can see that in the log. But my log confirm me that the index was created succesfully, I populated him but I cannot get the proper results in the queries.

On the other side, let's say I have a .doc document with text and pictures too. I can save this document in an image column but is it posible to store in a text column just the text part of the document? How can I separe these two parts? Do you know any method for that?

I try and try and I don't know why it happens this.

Thank you again and if you find anything or if you know a method of separe the two components of a file please tell me.

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 7

Since you are trying to do something that has been identified as "buggy," I would stop trying to do it.

I have not tried this, but you should be able to just copy and paste the text into the TEXT column. I would think that the image portion of the copy would drop off. But, I do not think it is something that you could automate simply without using VB or VBA.

Stefan Ardeleanu Mr or Mrs. 500 Points: 501 More actions · Answer 8

Thanks for the support.

This contains simply doesn't want to work with images although the index exista and is populated too.