SQL Server: PDFs in varbinary(max)-Feld speichern

Rechnungen, Angebote und Lieferscheine liegen als PDF-Dateien auf einem Netzlaufwerk, während in der Datenbank nur der Pfad zur Datei steht. Wird eine Datei verschoben, umbenannt oder gelöscht, verweist der Pfad auf keine Datei mehr. Auch ein Backup der Datenbank enthält die Dokumente nicht. Der SQL Server kann die Dateien dagegen in der Datenbank speichern. Dafür nimmt der Datentyp varbinary(max) Binärdaten bis zu einer Größe von knapp 2 Gigabyte auf, auch ein PDF-Dokument. In diesem Beitrag zeigen wir, wie wir ein PDF-Dokument mit ADODB in ein Feld dieses Datentyps schreiben, wieder in eine Datei auf der Festplatte speichern und in einem Access-Formular im Edge-Steuerelement anzeigen.

Szenario und Beispieldatenbank

Zu den Datensätzen einer kleinen Dokumentenverwaltung soll jeweils ein PDF-Dokument gehören, zum Beispiel eine Rechnung, ein Angebot oder ein Lieferschein.

Die Beispieldatenbank PDFInVarCharMax enthält dazu eine Tabelle mit drei Feldern:

• PDFID: der Primärschlüssel vom Datentyp int mit der Eigenschaft IDENTITY, die dem AutoWert in Access entspricht
• Dateiname: der Name der Datei vom Datentyp nvarchar(255)
• Dateiinhalt: der Inhalt der Datei vom Datentyp varbinary(max)

Die Tabelle legen wir mit der folgenden Anweisung an, wobei dbo das Standardschema des SQL Servers ist. Das Skript PDFInVarCharMax.sql aus dem Download legt zusätzlich die Datenbank an.

Wir führen es im SQL Server Management Studio (SSMS) aus. Das Skript prüft vor jeder Anweisung, ob das Objekt schon existiert. Deshalb können wir es mehrmals ausführen.

CREATE TABLE dbo.tblPDFs (
    PDFID int IDENTITY(1, 1) NOT NULL
        CONSTRAINT PK_tblPDFs PRIMARY KEY,
    Dateiname nvarchar(255) NOT NULL,
    Dateiinhalt varbinary(max) NULL
);

Das Feld Dateiinhalt erlaubt den Wert NULL. Damit können wir auch Datensätze anlegen, die kein PDF-Dokument enthalten. Diesen Fall behandeln wir im Formular zur Anzeige der PDF-Dokumente gesondert.

Der Datentyp varbinary(max)

Der Datentyp varbinary(n) speichert höchstens 8.000 Byte. Geben wir statt n den Wert max an, nimmt das Feld bis zu 2.147.483.647 Byte auf, was knapp 2 Gigabyte entspricht.

Der SQL Server speichert die Datei als Folge von Bytes, ohne ihre Struktur zu kennen. Dass es sich um ein PDF-Dokument handelt, erkennen wir in der Tabelle nur am Dateinamen.

Die Länge eines Werts in Byte liefert die Funktion DATALENGTH(). Enthält das Feld den Wert NULL, liefert sie ebenfalls NULL. Die folgende Abfrage zeigt die Größe der gespeicherten Dateien:

SELECT PDFID, Dateiname,
       DATALENGTH(Dateiinhalt) AS Groesse
FROM dbo.tblPDFs;

Ein Feld mit der Länge 0 Byte, geschrieben als 0x, unterscheidet sich vom Wert NULL, der für einen fehlenden Wert steht.

Für die Anzeige im Formular behandeln wir beide Fälle gleich. Enthält das Feld keine Bytes, haben wir kein PDF-Dokument gespeichert.

Die Verbindung zum SQL Server

Die Beispiele verwenden ADODB. Dafür braucht das Projekt einen Verweis auf die Microsoft ActiveX Data Objects 6.1 Library. Die Funktionen und Prozeduren dieses Beitrags stehen im Modul mdlPDFInVarBinaryMax.

Am Anfang des Moduls legen wir den Server und die Datenbank als Konstanten fest. Hier tragen wir den Namen des eigenen Servers ein. In den Beispielen heißt er amvDesktop2023:

Private Const cstrServer As String = "amvDesktop2023"
Private Const cstrDatenbank _
    As String = "PDFInVarCharMax"

Die Funktion Verbindung setzt daraus die Verbindungszeichenfolge für den ODBC-Treiber zusammen (siehe Listing 1).

Public Function Verbindung(Optional ByVal strDatenbank As String = cstrDatenbank) As String
    Verbindung = "Driver={ODBC Driver 18 for SQL Server};Server=" & cstrServer & _
        ";Database=" & strDatenbank & _
        ";Trusted_Connection=Yes;Encrypt=yes;TrustServerCertificate=yes;"
End Function

Listing 1: Verbindung liefert die Verbindungszeichenfolge

Mit Trusted_Connection=Yes verwenden wir die Windows-Anmeldung.

Der ODBC-Treiber 18 verschlüsselt die Verbindung standardmäßig, wobei TrustServerCertificate=yes die Prüfung des Serverzertifikats unterdrückt. Das genügt für die Beispiele. Auf einem Produktivserver installieren wir ein vertrauenswürdiges Zertifikat. Übergeben wir der Funktion keinen Datenbanknamen, verwendet sie die Konstante cstrDatenbank.

Die Datenbank und die Tabelle per VBA-Prozedur anlegen

Wer das Skript nicht im SSMS ausführen möchte, legt die Datenbank und die Tabelle per VBA-Prozedur an. Die Prozedur DatenbankAnlegen verbindet sich dazu mit der Systemdatenbank master und legt die Datenbank an (siehe Listing 2).

Public Sub DatenbankAnlegen(Optional ByVal strDatenbank As String = cstrDatenbank)
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.Open Verbindung("master")
    cnn.Execute "IF DB_ID(N'" & strDatenbank & "') IS NULL " & _
        "CREATE DATABASE [" & strDatenbank & "]"
    cnn.Close
    cnn.Open Verbindung(strDatenbank)
    cnn.Execute "IF OBJECT_ID(N'dbo.tblPDFs', N'U') IS NULL " & _
        "CREATE TABLE dbo.tblPDFs (" & _
        "PDFID int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_tblPDFs PRIMARY KEY, " & _
        "Dateiname nvarchar(255) NOT NULL, " & _
        "Dateiinhalt varbinary(max) NULL)"
    cnn.Close
End Sub

Listing 2: DatenbankAnlegen legt die Datenbank und die Tabelle an

Anschließend öffnet sie eine Verbindung zu dieser Datenbank und legt die Tabelle an. Beide Anweisungen führt sie nur aus, falls das Objekt noch nicht existiert. Für die Datenbank braucht das Konto, mit dem wir uns anmelden, das Recht CREATE DATABASE. Die Prozedur setzt den Datenbanknamen unverändert in die Anweisung ein. Daher übergeben wir nur eigene, feste Namen.

Im Direktbereich rufen wir die Prozedur auf. Fehlen die Datenbank und die Tabelle, legt der Aufruf beide an. Existieren sie bereits, ändert er nichts:

DatenbankAnlegen

Ein PDF-Dokument speichern

Die Funktion PDFInSQLSpeichern liest eine Datei und schreibt sie in die Tabelle (siehe Listing 3). Zuerst prüft sie mit der Methode FileExists des Objekts FileSystemObject, ob die Datei existiert. Falls nicht, löst sie einen benutzerdefinierten Fehler aus (vbObjectError + 1000).

Public Function PDFInSQLSpeichern(ByVal strDateipfad As String, Optional ByVal strConnection As String) As Long
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim stm As ADODB.Stream
    Dim fso As Object
    Dim varData As Variant
    Dim strDateiname As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FileExists(strDateipfad) Then
        Err.Raise vbObjectError + 1000, "PDFInSQLSpeichern", "Die Datei " & strDateipfad & " existiert nicht."
    End If
    If Len(strConnection) = 0 Then strConnection = Verbindung()
    Set stm = New ADODB.Stream
    With stm
        .Type = adTypeBinary
        .Open
        .LoadFromFile strDateipfad
        varData = .Read
        .Close
    End With
    If IsNull(varData) Then
        Err.Raise vbObjectError + 1001, "PDFInSQLSpeichern", "Die Datei " & strDateipfad & " ist leer."
    End If
    strDateiname = fso.GetFileName(strDateipfad)
    Set cnn = New ADODB.Connection
    cnn.Open strConnection
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cnn
        .CommandType = adCmdText
        .CommandText = "SET NOCOUNT ON; " _
            & "INSERT INTO dbo.tblPDFs (Dateiname, Dateiinhalt) VALUES (?, ?); " _
            & "SELECT CAST(SCOPE_IDENTITY() AS int);"
        .Parameters.Append .CreateParameter("pDateiname", adVarWChar, adParamInput, 255, strDateiname)
        .Parameters.Append .CreateParameter("pDateiinhalt", adLongVarBinary, adParamInput, UBound(varData) + 1, varData)
        Set rst = .Execute
    End With
    PDFInSQLSpeichern = rst.Fields(0).Value
    rst.Close
    cnn.Close
End Function

Listing 3: PDFInSQLSpeichern schreibt eine Datei in die Tabelle

Danach öffnet sie einen ADODB.Stream vom Typ adTypeBinary und lädt die Datei mit LoadFromFile. Den gesamten Inhalt liest sie mit Read in eine Variant-Variable. Bei einer leeren Datei liefert Read den Wert Null. Dann löst die Funktion den benutzerdefinierten Fehler vbObjectError + 1001 aus.

Den Dateinamen ermittelt die Funktion mit der Methode GetFileName aus dem Pfad. Die Anweisung INSERT führt sie über ein Objekt vom Typ ADODB.Command mit zwei Parametern aus. Der Parameter für den Dateinamen hat den Typ adVarWChar, der Parameter für den Inhalt den Typ adLongVarBinary mit der Länge des Byte-Arrays. Den Inhalt übergeben wir als Parameter, weil ein Hexadezimalliteral in der SQL-Anweisung bei großen Dateien unpraktisch wäre.

Die Anweisung beginnt mit SET NOCOUNT ON. Damit sendet der Server keine Meldung über die Zahl der geänderten Zeilen, und das Recordset enthält das Ergebnis des SELECT. Dieses SELECT gibt mit SCOPE_IDENTITY() die neue PDFID zurück. Die Funktion liefert sie als Ergebnis.

Mit dem optionalen Parameter strConnection können wir eine andere Verbindungszeichenfolge übergeben.

Übergeben wir keine, verwendet die Funktion die Zeichenfolge, die Verbindung liefert.

Im Direktbereich speichern wir die Datei test.pdf aus dem Ordner der Datenbank:

PDFInSQLSpeichern(CurrentProject.Path & "\test.pdf")
 1

In einer neuen Tabelle erhält das Dokument die PDFID 1. Die Abfrage mit DATALENGTH() liefert für diesen Datensatz den Wert 155.264, die Größe der Datei in Byte. Für die Bilder speichern wir auf demselben Weg die Datei vbe_517.pdf als zweiten Datensatz. Den dritten Datensatz legen wir ohne PDF-Dokument an. Das Feld Dateiinhalt bleibt dabei leer:

INSERT INTO dbo.tblPDFs (Dateiname)
VALUES (N'ohne_Inhalt.pdf');

Ein PDF-Dokument auf die Festplatte schreiben

Die Funktion PDFAusSQLSpeichern schreibt den Inhalt zurück in eine Datei (siehe Listing 4). Sie liest das Feld Dateiinhalt des Datensatzes mit der übergebenen PDFID mit einem Objekt vom Typ ADODB.Recordset. Da wir die Daten nur einmal lesen, genügen die Cursorart adOpenForwardOnly und die Sperrart adLockReadOnly.

Public Function PDFAusSQLSpeichern(ByVal lngPDFID As Long, _
        ByVal strZieldatei As String, _
        Optional ByVal strConnection As String) As Boolean
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stm As ADODB.Stream
    If Len(strConnection) = 0 Then strConnection = Verbindung()
    Set cnn = New ADODB.Connection
    cnn.Open strConnection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT Dateiinhalt FROM dbo.tblPDFs WHERE PDFID = " & lngPDFID, _
        cnn, adOpenForwardOnly, adLockReadOnly
    If Not rst.EOF Then
        If rst.Fields("Dateiinhalt").ActualSize > 0 Then
            Set stm = New ADODB.Stream
            With stm
                .Type = adTypeBinary
                .Open
                .Write rst.Fields("Dateiinhalt").Value
                .SaveToFile strZieldatei, adSaveCreateOverWrite
                .Close
            End With
            PDFAusSQLSpeichern = True
        End If
    End If
    rst.Close
    cnn.Close
End Function

Listing 4: PDFAusSQLSpeichern schreibt den Inhalt in eine Datei

Ob das Feld ein PDF-Dokument enthält, entscheidet die Eigenschaft ActualSize des Felds. Sie liefert die Länge in Byte. In unseren Tests lieferte sie sowohl beim Wert NULL als auch bei einem Feld mit 0 Byte den Wert 0.

Nur wenn sie größer als 0 ist, schreibt die Funktion den Inhalt in die Zieldatei. Dazu verwendet sie einen ADODB.Stream und ruft SaveToFile mit der Option adSaveCreateOverWrite auf. Eine vorhandene Datei überschreibt sie dabei.

Die Funktion liefert True, wenn sie die Datei geschrieben hat. Andernfalls liefert sie False. Der Direktbereich zeigt die Werte in der deutschen Fassung als Wahr und Falsch an.

Auch hier können wir mit dem Parameter strConnection eine andere Verbindungszeichenfolge übergeben.

Im Direktbereich geben wir den Aufruf in einer Zeile ein. Zur besseren Lesbarkeit haben wir ihn hier umgebrochen:

Debug.Print PDFAusSQLSpeichern(1, CurrentProject.Path _
    & "\export.pdf")
Wahr

In unseren Tests war die Datei export.pdf danach genauso groß wie test.pdf, und ein Vergleich der Bytes ergab keinen Unterschied. Existiert zur PDFID kein Datensatz oder enthält das Feld Dateiinhalt keine Bytes, liefert der Aufruf Falsch. In diesem Fall legt die Funktion die Zieldatei nicht an und ändert sie nicht.

Beide Funktionen halten die Datei vollständig im Arbeitsspeicher. In unseren Tests haben wir Dateien bis 25 Megabyte fehlerfrei gespeichert und gelesen.