Several people have asked about storing binary data in a database. I coded up an example today that uses an ADODB.Stream to copy the binary data but after some fiddling I found a more direct way. If you are using MS SQL Server, the easiest way I have found is to let the DBMS do all the dirty work. This example uses SqlClient but as long as the DBMS supports the operation, the same query should work under ADO or OleDB on other platforms. The example uses a database with two columns.

Edited 1 Month Ago by Reverend Jim

Dim filename As String = "D:\test.pdf"

Dim con As New SqlConnection("Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
Dim cmd As New SqlCommand("", con)

cmd.CommandText = "INSERT INTO PDFStore (filename,Contents) " _
                & "SELECT '" & filename & "', * FROM " _
                & "OPENROWSET(BULK '" & filename & "', SINGLE_BLOB) SOMEALIAS"

con.Open()
cmd.ExecuteNonQuery()
con.Close()


The example database was created by

USE [mydb]

CREATE TABLE [dbo].[PDFStore](
	[FileName] [varchar](500) NOT NULL,
	[Contents] [varbinary](max) NULL,
 CONSTRAINT [PK_PDFStore] PRIMARY KEY CLUSTERED 
(
	[FileName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

An update to an existing record can be similarly done by

    cmd.CommandText = "UPDATE PDFStore " _
                    & "   SET Contents = (SELECT * FROM " _
                    & "       OPENROWSET(BULK '" & filename & "', SINGLE_BLOB) SOMEALIAS) " _
                    & " WHERE FileName = '" & filename & "'"

Computer languages in which I have developed applications

Assembler (DEC, Data General, 8080, GE, SEL, IBM 360)
WATFOR (Waterloo FORTRAN)
FORTRAN (SEL)
APL (IBM 360, IBM VSAPL)
PL/1
C/C++
Borland Paradox
VB.net
vbScript

The article starter has earned a lot of community kudos, and such articles offer a bounty for quality replies.