How to store/retrieve image/file in sql db

Hi Guys,
I want to employee details in one table along with his photograph and resume
.
How can I achieve this? I'm fresher and I don't have any basic idea about th
is.
Can somebody lead me to a worked out example.
If appropriate how to use WRITETEXT/READTEXT to achieve this task?
thanks in advance.
M.L.SrinivasYou can start here:
http://support.microsoft.com/kb/308042
or
You can use TextCopy.exe in the \bin folder.
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"M.L.Srinivas" <mlsrinivas@.indiatimes.com> wrote in message
news:9fa72100.0502102147.1a18787@.posting.google.com...
> Hi Guys,
> I want to employee details in one table along with his photograph and
> resume.
> How can I achieve this? I'm fresher and I don't have any basic idea about
> this.
> Can somebody lead me to a worked out example.
> If appropriate how to use WRITETEXT/READTEXT to achieve this task?
> thanks in advance.
> M.L.Srinivas|||I have gone threw this tutorial. But when I retrieve the image from the DB.
The image is only 1B, and I cannot open it. The filename is correct.
Whats wrong here?
-- Kenneth
"oj" wrote:

> You can start here:
> http://support.microsoft.com/kb/308042
> or
> You can use TextCopy.exe in the \bin folder.
> -- OJ: TEXTCOPY example
> -- Loading files into db &
> -- exporting files out to folder
> --
> --
> --TEXTCOPY IN
> --
> --create tb to hold data
> create table tmp(fname varchar(100),img image default '0x0')
> go
> declare @.sql varchar(255),
> @.fname varchar(100),
> @.path varchar(50),
> @.user sysname,
> @.pass sysname
> set @.user='myuser'
> set @.pass='mypass'
> --specify desired folder
> set @.path='c:\winnt'
> set @.sql='dir ' + @.path + '*.bmp /c /b'
> --insert filenames into tb
> insert tmp(fname)
> exec master..xp_cmdshell @.sql
> --loop through and insert file contents into tb
> declare cc cursor
> for select fname from tmp
> open cc
> fetch next from cc into @.fname
> while @.@.fetch_status=0
> begin
> set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
> /d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
> set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
> print @.sql
> exec master..xp_cmdshell @.sql ,no_output
> fetch next from cc into @.fname
> end
> close cc
> deallocate cc
> go
> select * from tmp
> go
> --
> --TEXTCOPY OUT
> --
> declare @.sql varchar(255),
> @.fname varchar(100),
> @.path varchar(50),
> @.user sysname,
> @.pass sysname
> set @.user='myuser'
> set @.pass='mypass,'
> --specify desired output folder
> set @.path='c:\tmp'
> set @.sql='md ' + @.path
> --create output folder
> exec master..xp_cmdshell @.sql
> --loop through and insert file contents into tb
> declare cc cursor
> for select fname from tmp
> open cc
> fetch next from cc into @.fname
> while @.@.fetch_status=0
> begin
> set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
> /d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
> set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
> print @.sql
> exec master..xp_cmdshell @.sql ,no_output
> fetch next from cc into @.fname
> end
> close cc
> deallocate cc
> set @.sql='dir ' + @.path + '*.bmp /c /b'
> exec master..xp_cmdshell @.sql
> go
> drop table tmp
> go
>
> --
> -oj
>
> "M.L.Srinivas" <mlsrinivas@.indiatimes.com> wrote in message
> news:9fa72100.0502102147.1a18787@.posting.google.com...
>
>

How to store very large document data?

Dear all,

I am facing a problem while storing Very large document data to a single image field in SQL Server 2000. It takes very much time for documents in size upto 10 MB. And its not possible to store data more than 10 MB. If I try it gives "Connection Write (Wrapper Write())" message with state 01000 and native 4.

I have used SQLPutData and SQLParamData for this purpose.

Is there any efficient way to do this? My concern is for less time.

Thanks in advance,
AvneeshI need urgent help regarding above problem. Would somebody please send some suggestions ASAP.

Thanks in advnace,
Avneesh

How to store large chunks for binary data into the DB?

From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?

If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?

TIA

Pat

That hardly makes sense to store 100M of binary data in the database even without 4GB database size limit. Consider storing file name instead.

|||ok but say i need to store an arbitrary large amount of data in a database and it needs to be only one file, what would you suggest|||

OK, say you have huge files in the DB. Now, how you going to work with them? Run DataReader and load them into memory? How you going to update them? Say, you need to change one bit in a huge file. With file system you open a file, seek to position and change this bit. With database you have to load entire file into memory, change one bit and save it back. Don’t you think it will be a bit slow if file is huge?

Why not simply keep big files where they belong – on a file system so you can work with them easily?

As to one file equipment, I don't quite understand it. Are you working on some special file system which only allows for one file?

|||Ilya: That is what "structured storage" is supposed to be for. I.e. ILockBytes|||

That needs support from the database and I don't believe it's supported by SQL Ev. No problems with files on the other hand. I also don't believe it's available on devices at all (SQL Ev used to be SQL CE), but memory mapped files API is available.

|||

SQL Server CE/Mobile 3.0 supports ILockBytes in OLEDB Provider. I dont know if ILockBytes exists in managed code!

Thanks,

Laxmi

How to store large chunks for binary data into the DB?

From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?

If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?

TIA

Pat

That hardly makes sense to store 100M of binary data in the database even without 4GB database size limit. Consider storing file name instead.

|||ok but say i need to store an arbitrary large amount of data in a database and it needs to be only one file, what would you suggest|||

OK, say you have huge files in the DB. Now, how you going to work with them? Run DataReader and load them into memory? How you going to update them? Say, you need to change one bit in a huge file. With file system you open a file, seek to position and change this bit. With database you have to load entire file into memory, change one bit and save it back. Don’t you think it will be a bit slow if file is huge?

Why not simply keep big files where they belong – on a file system so you can work with them easily?

As to one file equipment, I don't quite understand it. Are you working on some special file system which only allows for one file?

|||Ilya: That is what "structured storage" is supposed to be for. I.e. ILockBytes|||

That needs support from the database and I don't believe it's supported by SQL Ev. No problems with files on the other hand. I also don't believe it's available on devices at all (SQL Ev used to be SQL CE), but memory mapped files API is available.

|||

SQL Server CE/Mobile 3.0 supports ILockBytes in OLEDB Provider. I dont know if ILockBytes exists in managed code!

Thanks,

Laxmi

How to store large chunks for binary data into the DB?

From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?

If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?

TIA

Pat

That hardly makes sense to store 100M of binary data in the database even without 4GB database size limit. Consider storing file name instead.

|||ok but say i need to store an arbitrary large amount of data in a database and it needs to be only one file, what would you suggest|||

OK, say you have huge files in the DB. Now, how you going to work with them? Run DataReader and load them into memory? How you going to update them? Say, you need to change one bit in a huge file. With file system you open a file, seek to position and change this bit. With database you have to load entire file into memory, change one bit and save it back. Don’t you think it will be a bit slow if file is huge?

Why not simply keep big files where they belong – on a file system so you can work with them easily?

As to one file equipment, I don't quite understand it. Are you working on some special file system which only allows for one file?

|||Ilya: That is what "structured storage" is supposed to be for. I.e. ILockBytes|||

That needs support from the database and I don't believe it's supported by SQL Ev. No problems with files on the other hand. I also don't believe it's available on devices at all (SQL Ev used to be SQL CE), but memory mapped files API is available.

|||

SQL Server CE/Mobile 3.0 supports ILockBytes in OLEDB Provider. I dont know if ILockBytes exists in managed code!

Thanks,

Laxmi

How to store jpg's in an image column

Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-H
Some ideas here: http://vyaskn.tripod.com/programming_faq.htm#q5
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:98c001c433c2$5da25c10$a501280a@.phx.gbl...
Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-H

How to store jpg's in an image column

Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-HSome ideas here: http://vyaskn.tripod.com/programming_faq.htm#q5
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:98c001c433c2$5da25c10$a501280a@.phx.gbl...
Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-H

How to store jpg's in an image column

Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-HSome ideas here: http://vyaskn.tripod.com/programming_faq.htm#q5
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:98c001c433c2$5da25c10$a501280a@.phx.gbl...
Hi
I want to store an image (not just the pointer to the
image, but the entire image) in an image data type column
in my database.
How do I do this? The images will be small (7kb) so I'd
like to do this rather than just store the pointer to an
image on the file system.
Thanks in advance !!
-H

how to store image in image field in sql server 2000

hi all,

i have created a table with image field in it. Now i just want to store a jpeg file in it but not finding any way to do so.

how can i store any image ? what are the steps?

thanx in advance

Actually, the following statement will only work in sql2005. Sorry.

Try something like this:

Code Snippet

create table #bob

(id int, jpeg image)

insert into #bob

select 1, bulkcolumn

from openrowset(BULK 'c:\Winter.jpg', SINGLE_BLOB) AS table

HTH

|||

how to add using enterprise manager. I don't want to create a stored procedure for that or any sql statement.

Can i directly insert image as i can enter text into any text field?

|||

I don't think you're able to add them in via EM.

I think you can use textcopy (see Books Online) and there shouild an example of how this works in the pubimage.bat file in your install directory c:\program files\microsoft sql server\mssql\install\

HTH.

|||No way to do this in SSSM or EM.

If you favour doing this in SSMS in the next version of SQL Server, feel free to post a connect bug / feature request in the connect forums at www.connect.microsoft.com.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to store image datatype value to a variable?

I have image type col.
I'm trying to do the following,
DECLARE @.Data varbinary(16)
SET @.Data = (select imageCol from Table1 where id=3)
As image datatype returns varbinary value, so I want to store image col value to a varbinary variable(or any other type variable, eg., varchar). But getting following error,
========================================
Server: Msg 279, Level 16, State 3, Line 2
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
========================================
Is there anyway to store image datatype value to a variable?
Cheers.

Try this:
select @.Data=imageCol from Table1 where id=3

|||

mbanavige wrote:

Try this:
select @.Data=imageCol from Table1 where id=3

Thanks a lot.

How to store image (in the folder) that linked by staffno?

Hi..
I have a folder which store staff picture in my C drive. Is it possible for me to link that staff picture into my report? It linked by staffno..something like this..
"C:\picture\" + {tblStaff.StaffNo} + ".jpg"
but..where do i put this code? any idea? thanx in advance..;)Try looking in Crystal's Help under Images (dynamic)...

how to store and retrive .DOC file in sql

Hi frds,

My question is how should i store a .DOC file in sql 2005 as image field and how to retrive the Doc stored in Image format

Thanks & regards

Sithender.s

Have a look at the technique for storing images in http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp (Storing and retrieving images from SQL Server using Microsoft .NET.)
http://www.codeproject.com/aspnet/PicManager.asp (How to upload files to Web pages in ASP.NET? How to read an image from a database using ADO.NET and display it in a Web page?)
http://www.codeproject.com/aspnet/NetPix.asp (This article explains an ASP.NET application to view and share photos online.)

This should give the basis for your intended document storage and retrieval.

|||

My question to you is why you want to store the .doc in Image data type. This data type is basically for backward compatibility with SQL Server 2000 and will not be available in next version of SQL Server. Use Varbinary data type instead of Image. However by using the following methods you can upload it to image data type as well. I have used file upload control of ASP.Net 2.0 to upload files in following example.

protectedvoid btnFileUpload_Click(object sender,EventArgs e) { if (FileUpload1.FileName.Length > 0) { int len = FileUpload1.PostedFile.ContentLength; String strDes = txtDescription.Text; byte[] file =newbyte[len]; FileUpload1.PostedFile.InputStream.Read(file, 0, len); clsDAL objDal =newclsDAL(); objDal.UploadFile(file,strDes); } } publicvoid UploadFile(Byte[] pFile,String pDesc) { SqlConnection cnnConn; SqlCommand cmmCommand; cnnConn =newSqlConnection(ConfigurationManager.ConnectionStrings["Connection String"].ConnectionString); cnnConn.Open(); cmmCommand =newSqlCommand(); cmmCommand.Connection = cnnConn; cmmCommand.CommandType =CommandType.StoredProcedure; cmmCommand.CommandText ="sp_UploadFile"; cmmCommand.Parameters.AddWithValue("@.pFile", pFile); cmmCommand.Parameters.AddWithValue("@.pDesc", pDesc); cmmCommand.ExecuteNonQuery(); }|||

Sorry, i am newbie in upload file.

May i know that where do i replace my sql statement and where do i should fill in the user is, password and server path like 192.xx.xxxx.xxx??

thanks.........

how to store and retrieve image files in sql server

hi like to knw the steps for storing 1000+ image files in an sql server.....
i/p: 1000+ image files

o/p: should be displayed in an vb6 environment in an pic box.....I would recommend you storing full path to picture file and not file itself then get path from a database and load picture from file into a picture box.
It is extremely slow and complicated to do it on the database side. You should be able to convert picture file into a binary stream to store it on the database and then be able to decode this binary stream back to picture.

Good Luck.

How to store an image on to my SQL database?

How to store an image on to my SQL database from the web form? Please guide me through. Thank you.http://www.dotnetspider.com/Question117.aspx