1

Here is the query I am using to create and insert binary data in SQL Server 2008
Create query:

CREATE TABLE Employees (
Id int,
Photo varbinary(max) not null,
Name varchar(50) not null,
Sound varbinary(max) not null
)

Insert query:

INSERT INTO Employees SELECT '10',
(SELECT BulkColumn AS E FROM OPENROWSET ( BULK 'd:\1.jpg', Single_Blob) bc), 'John', (SELECT BulkColumn AS E FROM OPENROWSET ( BULK 'd:\2.wav', Single_Blob) bc)

One of the files is .jpg and the other is .wav
How can i know the extension of these files while retrieving?
Do i have to use a query for finding the extension?
OR
Do i have to see content-type after i get the resultset in jsp?

user646093
  • 1,495
  • 3
  • 15
  • 20

1 Answers1

0

You need to add another column for the content type or file name/extension to the DB table. This way you can just insert it along the binary data and retrieve back later.

In a Servlet, you can get the content type based on the file name/extension as follows:

String contentType = getServletContext().getMimeType(filename);
// ...

If you do this before insert, then you can store the content type along. If you do this after insert, then you should store the filename along.

The default mime types from the servlet context are definied in servletcontainer's web.xml, which is in case of for example Tomcat located in its /conf/web.xml. You can add another mime types to your own /WEB-INF/web.xml as well, e.g.

<mime-mapping>
    <extension>svg</extension>
    <mime-type>image/svg-xml</mime-type>
</mime-mapping>

See also:

Community
  • 1
  • 1
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • I am developing a database management tool similar to [phpmyadmin] (http://www.phpmyadmin.net/home_page/index.php). So inserting an extra column would be tedious. Also that column should be hidden will retrieving!!. So that would be very complex. What if I store just the url of the file(uploaded on the server) in the varbinary column. While retrieving the varbinary column i will display a link to the file so that it can be downloaded!!. Will there be any problems in this?? – user646093 Apr 10 '11 at 05:42
  • If I go by the above the method then I have to convert the filename (eg. C:\temp\1.jpg) into binary so that it can be inserted into varbinary column and while retrieving i have to convert binary data back to string. AM I RIGHT??? – user646093 Apr 10 '11 at 15:33
  • 1
    You *need* the filename or content type somewhere so that you know what kind of file it is. An alternative is to store the binary data on disk and store *only* the path/name in the DB. – BalusC Apr 10 '11 at 15:58
  • Yup. Thats what I want to do. The above the method I stated uses the same idea. But the trick over here is to store the filename in the varbinary column (since i dont want an extra column)!! Hence the conversion from string to binary(while storing) and binary to string(while retrieving). So will there be any difficulties/problems doing the conversion ?? – user646093 Apr 10 '11 at 16:04
  • Can't you just change the column type to varchar/text? Use the right type for the data. – BalusC Apr 10 '11 at 16:05
  • This would create problems while storing and retrieving as i am working on a database management tool. Logic while storing: if datatype is varchar then i should give a textbox to get input, if it is varbinary then user should upload a file. Logic while retrieving: if datatype is varchar simply print the value in the resultset, if varbinary then provide a link to download the file. – user646093 Apr 10 '11 at 16:21
  • 1
    I see. Well, I think I'd stick to varbinary with real binary content and use for example JMimeMagic to *guess* the content type. If it's unknown, consider providing it as `application/octet-stream` with `.bin` extension and/or showing some warning message. There's a JMimeMagic code snippet in the *See also* link. – BalusC Apr 10 '11 at 22:09
  • Do I **still** have to store filename or content type somewhere in the DB if I am using **JMimeMagic**? – user646093 Apr 11 '11 at 08:32
  • No, just the binary content is enough. JMimeMagic guesses it based on an `InputStream`. – BalusC Apr 11 '11 at 11:26