How can i store files in an SQL 2000 SERVER table? 1

[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 emails in an SQL table. My table has 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)?
The bottom line: How can I send a file from VB 6 to SQL Server 2000 and store it there in a table?
Any idea?

Thanks!

 

[rzs0502]

Most people use the AppendChunk and GetChunk functions in VB to store blobs in a table.
Using Streams is also a good option. I normally use that to store images in MySQL.
Depending on the amount of space you have, another option would be to save the attachment onto a public share and have the UNC path stored in your database, thereby keeping your database small and fast (and saves you on some coding)

 

[FoxyBOA]

Hi !

This extract code to load data from stream and save it to DB. If you wanna store non text file then change stream type to adTypeBinary.

Dim adoStream As ADODB.Stream
Set adoStream = New ADODB.Stream
'adoStream.Charset = "Windows-1251" ' I need it for correct load data from text file
adoStream.Type = adTypeText
adoStream.Open
adoStream.LoadFromFile strFileName

And then call method adoStream.Read which read all data from file and assign it to DBField or parameter SP.

 

[BogdanMBM]

Hi, FoxyBOA!
I can't declare adoStream As ADODB.Stream because "Stream" dazn't apear in ADODB. list. Do I vave to add to my VB 6 project some extra references to gain accsess to "Stream" ?
And another question: Once loaded into that adoStream, how can I pass it to SQL Server?

Thanks in advance!

Bogdan

 

[FoxyBOA]

You need ado version at least 2.5
I use it in such way:
adoCommand.Parameters.Item().Value = adoStream.Read

 

[BogdanMBM]

Great idea, rzs0502!

I found in MSDN docummentation (and an example, to!) about AppendChunk. I'll try it!
Thanks.

 

[BogdanMBM]

Well, I'm not used with "adoCommand.Parameters".
I generally use something like this to communicate with the server:
dim Conexiune as ADODB.Connection
Conexiune.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & txtUser & ";Password = " & txtParola & ";Initial Catalog=MyDatabase;Data Source=MyServer"
Conexiune.CursorLocation = adUseClient
Conexiune.Open
Conexiune.Execute "EXEC ProcedureName 'StringParameter', INT_Parameter " and so on...

But I'll read about it.
Can I use Execute instead?

 

[FoxyBOA]

Hello, BogdanMBM!

You have several possibilities to exec SP via ADO.

One of them via adoConnection (which you mention) another one via adoCommand.

dim Conexiune as ADODB.Connection
Conexiune.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & txtUser & ";Password = " & txtParola & ";Initial Catalog=MyDatabase;Data Source=MyServer"
Conexiune.CursorLocation = adUseClient
Conexiune.Open

Dim oCommand as ADODB.Command
SET oCommand =new ADODB.Command
oCommand.CommandType = adCmdStoredProc
oCommand.CommandText = pstrName
SET oCommand.ActiveConnection = Conexiune
oCommand.Parameters.Refresh 'Auto populate SP parameters
oCommand.Parameters.Item("@Par1&quot.Value = adoStream.Read
oCommand.Execute

 

[BogdanMBM]

Thanks, FoxyBOA!
I think this should do it.

 

[BogdanMBM]

Function TrimiteFisier(Fisier As String)
Flux.Type = adTypeBinary
Flux.Open
Flux.LoadFromFile Fisier
Comanda.CommandType = adCmdStoredProc
Comanda.CommandText = "InsertAttachements"
Set Comanda.ActiveConnection = Conexiune
Comanda.Parameters.Refresh
Comanda.Parameters("@argintID_Mesaj&quot.Value = 1
Comanda.Parameters("@argintArg_Atasament&quot.Value = 1
Comanda.Parameters("@argvchrNumeAtasament&quot.Value = Fisier
Comanda.Parameters("@argimgAtasament&quot.Value = Flux.Read
Comanda.Execute
End Function

Function AduFisier(ID_Mesaj As Integer, ID_Atasament As Integer)
Dim Tabel As New ADODB.Recordset, FisierLiber As Integer, x
Tabel.ActiveConnection = Conexiune
Tabel.Open "select * from Atasamente where ID_Mesaj = " & ID_Mesaj & " and ID_Atasament = " & ID_Atasament
DoEvents
x = Tabel.Fields("Atasament&quot
FisierLiber = FreeFile
Open App.Path & "\Transfer.pdf" For Binary As FisierLiber
Put FisierLiber, , x
Close FisierLiber
End Function

 

[BogdanMBM]

This works! It realy works!
Thanks, FoxyBOA.