VB-SQL SERVER Question

hi,

i want to upload the images into the SQL SERVER database
using VB. I want images to be stored in the database and not
the path of the images.
how can i do it

 

I can't help you with VB, but speaking of SQL Server, you would need to store your images in a column defined as image. This datatype allows you to store up to 2 GB of data.

Keep in mind that storing images in the database will require additional maintenance on your part and will also put additional strain on your web server. For example, you will have to retrieve images from the DB first and then serve them with some custom HTTP headers (e.g. content-type). This may also prevent your web server from being able to use file compression, depending on your configuration.

J.D.

 

i'm assuming your means visual basic instead of vbulletin

the best way to do this is to use the stream object provided by ADO:
1. instantiate a new stream object - e.g. set obj = new adodb.stream
2. set the stream object's type to binary
3. load an image from disk into the stream using the loadfromfile method of the stream object
4. assign the image in the stream object into a sql server recordset and save or update the recordset

i'd use a blob datatype in sql server to save the image. there's also an image data type but i've never used it before.

personally, i prefer to save the images to directories on the file system and only save the file path to sql server... but that's my personal preference...

 

BLOB's are stored as image, text or ntext.

J.D.

 

The big question is WHY???!!!!

 

If your question is about my last comment, there's no such data type as BLOB in SQL Server. Binary large objects (BLOBs) may be stored in columns defined as image, text or ntext. image doesn't actually mean that it has to be a picture - it can be any binary data (usually greater than 8K - smaller data should be stored in a varbinary column).

J.D.

 

No JD, I mean why store images in the database. It produces a great deal of overhead, the images cannot be delievered to the browser efficiently and it's just a bad idea all the way around.

 

i do not want to use the above posted question for web browsers. this problem i am facing while developing software for a cosmetics company and saving the path is not a good idea.
that is why i want to upload image using VB into SQL SERVER
no scripting language is used

 

I'm not sure what you mean. Are you saying that these images won't be served by a web server, but instead you will use some client-side application to pull them out of the database and present in the non-browser UI?

J.D.

 

yes J.D, u r right
i am using VB and SQL SERVER to develop an application software.
i want to store images in the database
all the articles I have gone thru have only one answer.
they say, store images at some different place on the hard disk and place a pointer of that location in the database field.
that will slow down the application as the images will be stored somewhere else
I do not want that

I just want images to be stored in the database as any other data is stored
VJK

 

how will storing the images outside the database slow down the response? i don't think the difference (if any) will be noticeable. in fact, i would have thought that the response would be faster.

the only scenario i would store the images in the database is if i cannot get someone to code a service to retrieve the images to be delivered to the clients.

the use of a middle-tier service will remove the necessity of the clients to have a direct connection with the location where the images are stored - i.e. only one connection (by the service) is required to the location of the images. all the clients requests will be serviced by the service. the service can be coded using any winsock control.

 

hi daboss,

dont u think retrieving files again and again from a different location will slow down the process. consider a situation where u have more than 10,000 records and u have to fetch them all. it means control will be passed 10,000 times to the the database and then to the respective file(s) where images are stored. isn't it better to store all the images in the database which will fetch the images at the same time the other fields will be retrieved. moreover, we are not allowing clients to directly have any control over the database. only the administrator is to be given all the rights, and i hope he shud be given access. moreover it is the demand of the situation right now for me as our client wants us to do it this way only.

 

well, if your client is focing the method on you, that's that isn't it...

anyway, i've worked with imaging systems for a while now... and this has been a debate all along. my personal preference is still to have the file system take care of retrieving the image from disk and passing it to the requester.

any way you look at it, you still need to pass the indexes to the database and the images needs to be retrieved from disk (either by the file system or by the database engine). whether it's 10000 images retrieved in a single query or not, the individual records still have to be retrieved one at a time by the database engine.

so it boils down to the question of whether it's more efficient to have the file system or the database engine to maintain and upkeep the images on disk.

 

If the client is insisting, you gotto do what he/she/it says... blob would be the best option here (depending on size, again)..

Some reading to be done:
http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html

That`ll surely help you out...

 

Dim rstRecordset As ADODB.Recordset
Dim cnnConnection As ADODB.Connection
Dim strStream As ADODB.Stream

'** The field type in Sql Server must be "Image"
'** Everywhere you see "***" in the code is where you must enter
'** your own data.

Private Sub cmdClear_Click()
Image1.Picture = Nothing
End Sub

Private Sub cmdLoad_Click()
If Not LoadPictureFromDB(rstRecordset) Then
MsgBox "Invalid Data Or No Picture In DB"
End If
End Sub

Private Sub cmdSelectSave_Click()
'Open Dialog Box
With dlgDialog
.DialogTitle = "Open Image File...."
.Filter = "Image Files (*.gif; *.bmp)| *.gif;*.bmp"
.CancelError = True
procReOpen:
.ShowOpen

If .FileName = "" Then
MsgBox "Invalid filename or file not found.", _
vbOKOnly + vbExclamation, "Oops!"
GoTo procReOpen
Else
If Not SavePictureToDB(rstRecordset, .FileName) Then
MsgBox "Save was unsuccessful", vbOKOnly + _
vbExclamation, "Oops!"
Exit Sub
End If
End If

End With
End Sub

Private Sub Form_Load()
Set cnnConnection = New ADODB.Connection
Set rstRecordset = New ADODB.Recordset

cnnConnection.Open ("Provider=SQLOLEDB; " & _
"data Source=**YourServer**;" & _
"Initial Catalog=**YourDatabase**; " & _
"User Id=**YourUID**;Password=***YourPass***")
rstRecordset.Open "Select * from YourTable", cnnConnection, _
adOpenKeyset, adLockOptimistic


End Sub


Public Function LoadPictureFromDB(RS As ADODB.Recordset)

On Error GoTo procNoPicture

'If Recordset is Empty, Then Exit
If RS Is Nothing Then
GoTo procNoPicture
End If

Set strStream = New ADODB.Stream
strStream.Type = adTypeBinary
strStream.Open

strStream.Write RS.Fields("**YourImageField**").Value


strStream.SaveToFile "C:\Temp.bmp", adSaveCreateOverWrite
Image1.Picture = LoadPicture("C:\Temp.bmp")
Kill ("C:\Temp.bmp")
LoadPictureFromDB = True

procExitFunction:
Exit Function
procNoPicture:
LoadPictureFromDB = False
GoTo procExitFunction
End Function

Public Function SavePictureToDB(RS As ADODB.Recordset, _
sFileName As String)

On Error GoTo procNoPicture
Dim oPict As StdPicture

Set oPict = LoadPicture(sFileName)

'Exit Function if this is NOT a picture file
If oPict Is Nothing Then
MsgBox "Invalid Picture File!", vbOKOnly, "Oops!"
SavePictureToDB = False
GoTo procExitSub
End If

RS.AddNew


Set strStream = New ADODB.Stream
strStream.Type = adTypeBinary
strStream.Open
strStream.LoadFromFile sFileName
RS.Fields("***YourImageField***").Value = strStream.Read

Image1.Picture = LoadPicture(sFileName)
SavePictureToDB = True
procExitSub:
Exit Function
procNoPicture:
SavePictureToDB = False
GoTo procExitSub
End Function

 

Your DB will be HUGE