Image Insert

Question

Image Insert

pro2003

SSC Enthusiast

Points: 190
More actions
July 7, 2003 at 10:05 am

#80793

I want to insert Image into a SLQ Server Table. Can anyone tell me of an easy way to do that.
thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 1

You really shouldn't store images and other binary files in the SQL Server tables 'cause it REALLY slows things down. You should store a link (file path) to the file in the tables and let your applications directly access the files. For example, I point all my image links to the images folder of my Web site (i.e. images\ads\budweiser.gif). However, if you really want to store these type of files within SQL Server, use the text/ntext or image datatype columns.

You can also try:

1. Use a utility called textcopy.exe that comes with SQL 7.0 and 2000. This file is in the Binn folder located in your SQL Server installation. Run it from the command prompt. Here's what you would get if you type in textcopy /?:

Copies a single text or image value into or out of SQL Server. The value

is a specified text or image 'column' of a single row (specified by the

"where clause") of the specified 'table'.

If the direction is IN (/I) then the data from the specified 'file' is

copied into SQL Server, replacing the existing text or image value. If the

direction is OUT (/O) then the text or image value is copied from

SQL Server into the specified 'file', replacing any existing file.

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]

[/D [database]] [/T table] [/C column] [/W"where clause"]

[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

/S sqlserver The SQL Server to connect to. If 'sqlserver' is not

specified, the local SQL Server is used.

/U login The login to connect with. If 'login' is not specified,

a trusted connection will be used.

/P password The password for 'login'. If 'password' is not

specified, a NULL password will be used.

/D database The database that contains the table with the text or

image data. If 'database' is not specified, the default

database of 'login' is used.

/T table The table that contains the text or image value.

/C column The text or image column of 'table'.

/W "where clause" A complete where clause (including the WHERE keyword)

that specifies a single row of 'table'.

/F file The file name.

/I Copy text or image value into SQL Server from 'file'.

/O Copy text or image value out of SQL Server into 'file'.

/K chunksize Size of the data transfer buffer in bytes. Minimum

value is 1024 bytes, default value is 4096 bytes.

/Z Display debug information while running.

/? Display this usage information and exit.

----------------------------------------

Here's an sp for inserting an image with textcopy:

This is an example to copy an image into SQL Server database pubs, table pub_info, column name logo from picture.bmp file where pub_id='0736':

sp_textcopy @srvname = 'ServerName',

@login = 'Login',

@password = 'Password',

@dbname = 'pubs',

@tbname = 'pub_info',

@colname = 'logo',

@filename = 'c:\picture.bmp',

@whereclause = " WHERE pub_id='0736' ",

@direction = 'I'

-----------------------------------------

2. Use the GetChunk and AppendChunk methods of ADO Field object. Go to MSDN for examples.

3. Use the ADO Stream object.

4. Use the Bulk Insert Image utility (BII) that ships with SQL Server 2000 (Can be found at \Program Files\Microsoft SQL

Server\80\Tools\Devtools\Samples\Utils)

Edited by - dalec on 07/07/2003 1:08:10 PM

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

yan_aung SSC-Addicted Points: 480 More actions · Answer 2

I do agree with DALEC on the storing images in Database, it can be slow. A few GB Data is not a problem.

Step 1 : Create a table with Image Data Type

Step 2 : Make an ADO Call to SQL DB.

Set fldData = rsGetDocument.Fields("Data")

lBytes = fldData.ActualSize

aByteDocument = fldData.GetChunk(lBytes)

Step 3 : Stream the Data to a File and do what you want.

lge Old Hand Points: 334 More actions · Answer 3

We have 4 databases with fax images (data type image) constantly being loaded from incoming fax messages. The largest db is now 9 GB, but so far we have no performance problems whatsoever (with SQL 2000).

David.Poole SSC Guru Points: 76000 More actions · Answer 4

One of the content management systems I use is called OBTREE and all binary files are stored within the database. One client has over 8,000 binary files stored in the database.

When rendering the graphics files directly from the database there seems to be little performance problems.

The performance hits that I have noticed are:-

Initial upload into the database.
Export from the database back from the file system.
Replication of that table containing the images.

As these three events are not common there isn't a problem here. Storing data in the database allows multiple language and browser instances of a single object plus versioning as well.

LinkedIn Profile

lge Old Hand Points: 334 More actions · Answer 5

Oh my, only 8000 - we have 62 000 in the largest fax image db.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hi Dale,

quote:
You really shouldn't store images and other binary files in the SQL Server tables 'cause it REALLY slows things down.

this is an argument I hear quite often. Maybe I am blind, but I can't see any major performance issue (if at all). Can you explain this to me?

quote:
You should store a link (file path) to the file in the tables and let your applications directly access the files. For example, I point all my image links to the images folder of my Web site (i.e. images\ads\budweiser.gif). However, if you really want to store these type of files within SQL Server, use the text/ntext or image datatype columns.

I like to keep them centralized for better control on security. You also have not to deal with inconsistencies, when someone deletes the file from the file system. In addition, we have one central server which all employee can access and several group volumes. I can't place 3-4 GB on such a server, for it should only be used to exchange data between different departments. However, I also can't place the files on a single group volume, because not everyone who wants to access this data has access or will be granted access to this group volume. So this is the most convenient way for me.

Maybe I have to add that these binary files all reside in one single db that stores nothing else but binaries. It is only accessed when someone really wants to have further information (so there isn't really heavy traffic on these tables). And once they are stored they do not change at all.

BTW, if it is 'bad practice' to store binary data in a db, why does the image type exist?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 7

My experience is that BLOB will inflate the database excessively if the requirements are heavy. Therefore, I prefer using linking instead of embedding. It is a highly recommended approach for massive image requirements (which is the case where I work). Also, it makes it much easier for the Graphics Designer to design the image using a standard naming convention based on the Customer ID and saving it to a shared disk. Therefore, she does not have to access the database to insert the image.

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Hi Dale,

quote:
My experience is that BLOB will inflate the database excessively if the requirements are heavy. Therefore, I prefer using linking instead of embedding. It is a highly recommended approach for massive image requirements (which is the case where I work). Also, it makes it much easier for the Graphics Designer to design the image using a standard naming convention based on the Customer ID and saving it to a shared disk. Therefore, she does not have to access the database to insert the image.

I see!

That's a different world. My binaries hardly never change, so when you must do frequently updates to them, your approach should be easier to handle. But actually I was hoping on some explanation on what you call

quote:
inflate the database excessively if the requirements are heavy

Is it just because of the usually (bigger) size of BLOB's?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 9

Frank,

Microsoft has a really good "all about BLOBs" on the Technet Web site. It's very interesting. It explains size issues, etc. In my case, you can see where they validate my argument for the Photoshop production house.

quote:
Storing images in a file system would be a better choice if:
* The application in which the images will be used requires streaming performance, such as real-time video playback.
* BLOBs require frequentaccess by applications, such as Microsoft PhotoDraw® or Adobe Photoshop, which only know how to access files.
* You want to use some specific feature in the NTFS file system such as Remote Storage.

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part3/c1161.asp

You need to check out the Terraserver link they have. It is VERY cool. I played with that many years ago when they first brought it up.

Dale

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

Dale,

quote:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part3/c1161.asp

Thanks for this one!

This is the explanation I've been searching for.

quote:
You need to check out the Terraserver link they have. It is VERY cool. I played with that many years ago when they first brought it up.

Hm, many years ago mentioned along with M$ that reminds me of

http://www.klawitter.de/enhumor.html

Check out the topic

'What if IBM made toasters?'

the other stuff is also highly recommended for reading

Cheers,

Frank

Edited by - a5xo3z1 on 07/08/2003 07:38:06 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

pro2003 SSC Enthusiast Points: 190 More actions · Answer 11

I think I have some idea now. I will try all the approaches (To see which one works for me).

I would say that we are storing huge binary files in the database, which does seem to slow down the import or export problem within the SQL Server but the application itself works fine with it.

We have an external application that me and other programmers wrote and it is being used company wide as well as with some of our clients (All doing the same thing).

But its not continuous addition or use of binary files.

Thanks for your help and this is one forum where I have seen prompt and professional replies.

pro2003 SSC Enthusiast Points: 190 More actions · Answer 12

I tried to use the sp_textcopy and textcopy.exe utility but it doesnt let me insert a new row, it only works with a where clause and I want to insert without a where clause. Is there a way to do that. I also came to know about BCP Utility. Can some one explain that to me.

Thanks in advance

pro2003

pro2003 SSC Enthusiast Points: 190 More actions · Answer 13

I tired doign this

>bcp <DBName>.<TableName> in <FilePath>- S<ServerName> -U<UserName> -P<Password>

It came back with this error:

SQLState = S1000, NativeError = 0

Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP dat

a-file

0 rows copied.

Can anyone help.

Thanks,

Pro2003