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.

792 Views
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 & "'"
About the Author

I completed my Computer Science degree at the University of Manitoba in 1976. I did two and a half years of programming in medical research followed by twenty-nine years at Manitoba Hydro (electric utility). Most of that was spent on doing development and maintenance on an AGC/SCADA (real-time programming/process control) system. The last ten years of that was spent doing application and infrastructure support and development. I have programmed in FORTRAN (mostly), APL, PL/1, COBOL, Lisp, SNOBOL, ALGOL, Assembler (several flavours), C, C++, Paradox, VB, vbScript and more recently, Python. I am married with two grown children of whom I am very proud, and a most beautiful wife. I am currently retired (and loving it).