Store entire files in an SQL table.

[BogdanMBM]

I have an MS SQL Server 2000 and an application written in VB 6.0. I use this one for user interface in an intranet mailing system i've designed.
I whant to be able to store the attachements of the email in an SQL table. My table haz a column of "Image" data type. I know I can store in this column up to 2 Gb of data but i don't know how.
What varriable in VB can accomodate such a large amount of data to be used for sending to the SQL Server.
How can I write a INSERT statement in my stored procedure to insert the "Image" data (my file)?

Any idea?

 

[NoCoolHandle]

There are two main methods for this (i am not sitting a a system that has vb installed on it, so can't help too much here) the main one is the ado addchunk method..

SEarch google for addchuck and image.. You should get some hits..

The other is better, and from memory uses the stream object and an openfromfile method or something like that.

I have reciently been 100% .Net and can say it is almost too easy there..

Good luck

Rob

 

[Digimon02]

Sample of Storing and retrieving image

'BeginAppendChunkVB
Public Sub AppendChunkX()

Dim cnn1 As ADODB.Connection
Dim rstPubInfo As ADODB.Recordset
Dim strCnn As String
Dim strPubID As String
Dim strPRInfo As String
Dim lngOffset As Long
Dim lngLogoSize As Long
Dim varLogo As Variant
Dim varChunk As Variant

Const conChunkSize = 100

' Open a connection.
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
cnn1.Open strCnn

' Open the pub_info table.
Set rstPubInfo = New ADODB.Recordset
rstPubInfo.CursorType = adOpenKeyset
rstPubInfo.LockType = adLockOptimistic
rstPubInfpen "pub_info", cnn1, , , adCmdTable

' Prompt for a logo to copy.
Dim strMsg As String
strMsg = "Available logos are : " & vbCr & vbCr
Do While Not rstPubInfo.EOF
strMsg = strMsg & rstPubInfo!pub_id & vbCr & _
Left(rstPubInfo!pr_info, InStr(rstPubInfo!pr_info, ",&quot - 1) & _
vbCr & vbCr
rstPubInfo.MoveNext
Loop
strMsg = strMsg & "Enter the ID of a logo to copy:"
strPubID = InputBox(strMsg)

' Copy the logo to a variable in chunks.
rstPubInfo.Filter = "pub_id = '" & strPubID & "'"
lngLogoSize = rstPubInfo!logo.ActualSize
Do While lngOffset < lngLogoSize
varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
varLogo = varLogo & varChunk
lngOffset = lngOffset + conChunkSize
Loop

' Get data from the user.
strPubID = Trim(InputBox(&quot;Enter a new pub ID&quot; & _
&quot; [must be > 9899 & < 9999]:&quot)
strPRInfo = Trim(InputBox(&quot;Enter descriptive text:&quot)

' Add the new publisher to the publishers table to avoid
' getting an error due to foreign key constraint.
cnn1.Execute &quot;INSERT publishers(pub_id, pub_name) VALUES('&quot; & _
strPubID & &quot;','Your Test Publisher')&quot;

' Add a new record, copying the logo in chunks.
rstPubInfo.AddNew
rstPubInfo!pub_id = strPubID
rstPubInfo!pr_info = strPRInfo

lngOffset = 0 ' Reset offset.
Do While lngOffset < lngLogoSize
varChunk = LeftB(RightB(varLogo, lngLogoSize - lngOffset), _
conChunkSize)
rstPubInfo!logo.AppendChunk varChunk
lngOffset = lngOffset + conChunkSize
Loop
rstPubInfo.Update

' Show the newly added data.
MsgBox &quot;New record: &quot; & rstPubInfo!pub_id & vbCr & _
&quot;Description: &quot; & rstPubInfo!pr_info & vbCr & _
&quot;Logo size: &quot; & rstPubInfo!logo.ActualSize

' Delete new records because this is a demonstration.
rstPubInfo.Requery
cnn1.Execute &quot;DELETE FROM pub_info &quot; & _
&quot;WHERE pub_id = '&quot; & strPubID & &quot;'&quot;

cnn1.Execute &quot;DELETE FROM publishers &quot; & _
&quot;WHERE pub_id = '&quot; & strPubID & &quot;'&quot;

rstPubInfo.Close
cnn1.Close

End Sub
'EndAppendChunkVB

 

[BogdanMBM]

Thanks for your sugestions, guys!
In the meen time I found the answer to my question.
Here it is:

Public Conexiune As New ADODB.Connection
Public Comanda As New ADODB.Command
Public Flux As New ADODB.Stream

Function Conectare(User As String, Parola As String) As Boolean ' Connects to my server.
On Error GoTo e:
Conectare = True
Conexiune.ConnectionString = &quot;Provider=SQLOLEDB.1;Persist Security Info=False;User ID=&quot; & User & &quot;;Password = &quot; & Parola & &quot;;Initial Catalog=MyDatabase;Data Source=MyServer&quot;
Conexiune.CursorLocation = adUseClient
Conexiune.Open
Exit Function
e:
Conectare = False
MsgBox Err.Description, vbCritical + vbOKOnly
End Function

Function SendFile(Fisier As String) 'Fisier includes the full path to the file.
Flux.Type = adTypeBinary
Flux.Open
Flux.LoadFromFile Fisier
Comanda.CommandType = adCmdStoredProc
Comanda.CommandText = &quot;InsertAttachements&quot;
Set Comanda.ActiveConnection = Conexiune
Comanda.Parameters.Refresh
Comanda.Parameters(&quot;@argintID_Mesaj&quot.Value = 1
Comanda.Parameters(&quot;@argintArg_Atasament&quot.Value = 1
Comanda.Parameters(&quot;@argvchrNumeAtasament&quot.Value = Fisier
Comanda.Parameters(&quot;@argimgAtasament&quot.Value = Flux.Read
Comanda.Execute
End Function

Function RetriveFile(ID_Mesaj As Integer, ID_Atasament As Integer)
Dim Tabel As New ADODB.Recordset, FisierLiber As Integer, x
Tabel.ActiveConnection = Conexiune
Tabel.Open &quot;select * from Atasamente where ID_Mesaj = &quot; & ID_Mesaj & &quot; and ID_Atasament = &quot; & ID_Atasament
DoEvents
x = Tabel.Fields(&quot;Atasament&quot
FisierLiber = FreeFile
Open App.Path & &quot;\Transfer.pdf&quot; For Binary As FisierLiber
Put FisierLiber, , x
Close FisierLiber
' Now the file is on my hard disk. Cool, isn't it!?!
End Function