Geek Answers Handbook

How to store and retrieve images using SQL Server (Server Management Studio)

I'm having difficulty trying to insert files into a SQL Server database. I will try to break this as best as possible:

  • What type of data should I use to store image files (jpeg / png / gif / etc)? My table now uses the image data type, but I'm curious if varbinary would be varbinary better option.

  • How can I insert an image into a database? Does Microsoft SQL Server Management Studio have built-in functions that allow you to insert files into tables? If so, how is this done? Also, how can this be done using an HTML form with PHP that processes the input and puts it in a table?

  • How to get the image from the table and display it on the page? I understand how to select the contents of the cells, but how can I translate this into an image. Should I have a title (Content Type: image / jpeg)?

I have no problem with any of these things with MySQL, but the SQL Server environment is still new to me and I am working on a project for my work that requires using stored procedures to capture various data.

Any help is appreciated.

+7
sql php sql-server image
source share
2 answers

The image type is deprecated, so do not use it.

The short answer is to use the type FILESTREAM if it is SQL 2008, or varbinary(max) if it is 2005 or earlier.

It is better to respond to using the FILESTREAM type if it is SQL 2008, rather than storing images in a database. Databases are not built for this type of thing, and throwing blob files can significantly affect performance. FILESTREAM bypasses the problem by storing the actual data in the file system; everything else is a sub-standard solution.

As for reading and writing to the varbinary column, if you decide to use this imperfect approach, it will be presented as a stream in PHP. This is what you get from the query as a column value, and I believe that you should insert as a parameter.

+5
source share

The command line uses bulk insert, available from the bcp.exe command line. Read this one for more information on how to insert binary data into MS SQL via t-sql.

0
source share

More articles:


All Articles