Aufgabe 16 - ODBC

Exercise 16 - ODBC

In den bisherigen Übungen haben Sie SQL direkt im DB-Client verwendet. In realen Projekten greifen Anwendungen jedoch über standardisierte Schnittstellen auf Datenbanken zu. Eine der wichtigsten davon ist ODBC (Open Database Connectivity).

Ziel dieses Übungsblatts ist es, ODBC nicht nur theoretisch zu verstehen, sondern praktisch einzusetzen: von der Konfiguration über den Verbindungsaufbau bis zur sicheren, parametrisierten Abfrage.

---

Vorbereitung

Stellen Sie sicher, dass Ihre Datenbankumgebung läuft (PostgreSQL mit Northwind).

Verschaffen Sie sich außerdem einen Überblick über die Datenbankobjekte:

-- PostgreSQL
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

Dokumentieren Sie kurz:

• Welche Northwind-Tabellen für Kunden, Bestellungen und Produkte relevant sind.
• Welche Primär- und Fremdschlüssel in diesen Tabellen verwendet werden.

---

Auftrag 1 - ODBC verstehen

1.1 Theorie

Beantworten Sie schriftlich:

1. Welche Aufgabe hat ODBC und warum ist es als Standard sinnvoll?
2. Was ist der Unterschied zwischen:
   • ODBC Driver Manager
   • ODBC Driver
   • DSN (Data Source Name)
3. Wann würden Sie ODBC statt eines nativen Treibers (z. B. pg oder psycopg2) einsetzen?
4. Nennen Sie einen Vorteil und einen Nachteil von ODBC im Vergleich zu nativen Treibern.

1.2 Architektur skizzieren

Erstellen Sie eine Skizze der Kommunikationskette von der Anwendung bis zum DBMS Server.

Ergänzen Sie in Ihrer Skizze, wo Host, Port, Datenbankname, Benutzer und Passwort verwendet werden.

---

Auftrag 2 - DSN und Verbindung einrichten

2.1 DSN-Konfiguration

Richten Sie einen ODBC-DSN für Ihre Northwind-Datenbank ein (PostgreSQL).

Vorgehen nach Betriebssystem:

Windows

1. PostgreSQL-ODBC-Treiber herunterladen und installieren:
   • https://odbc.postgresql.org/
   • Nehmen Sie die neueste stabile Version und wählen Sie die Architektur passend zu Ihrer Office-/Tool-Installation (in der Regel x64).
   • Typische Datei für 64-Bit: psqlodbc_x64.msi.
   • Nur bei 32-Bit-Umgebungen: psqlodbc_x86.msi.
2. ODBC-Datenquellen (64-Bit) öffnen.
3. System-DSN oder Benutzer-DSN anlegen.

macOS

Hinweis: unixODBC funktioniert in diesem Kontext mit Excel unter macOS nicht zuverlässig. Deshalb wird auf macOS das Actual ODBC Pack verwendet; für Apple Silicon muss zusätzlich der iODBC Administrator installiert werden, um die Datenquelle für Excel bzw. Word zugänglich zu machen.

Wichtig: Die kostenlose Version des Actual ODBC Pack liefert pro Abfrage maximal 3 Datensätze zurück.

1. Actual ODBC Pack herunterladen und installieren:

   • https://actualtechnologies.cachefly.net/Actual_ODBC_Pack.dmg

2. Den iODBC Administrator (Apple Silicon) installieren.

   • Downloads: https://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads
   • Direktlink für aktuelle macOS-Versionen: https://github.com/openlink/iODBC/releases/download/v3.52.16/iODBC-SDK-3.52.16-macOS11.dmg

3. Den iODBC Administrator öffnen und prüfen, ob dort ein PostgreSQL-Treiber auswählbar ist.

4. Im iODBC Administrator einen System DSN für PostgreSQL anlegen. Dabei darauf achten, dass die Verbindungsparameter korrekt eingetragen werden (Host, Port, Datenbankname, Benutzer). Als Host statt localhost 127.0.0.1 verwenden, da localhost manchmal als IPv6-Adresse aufgelöst wird.

5. Referenzen:

   • Actual ODBC Pack: https://actualtechnologies.cachefly.net/Actual_ODBC_Pack.dmg
   • PostgreSQL-ODBC-Treiber: https://odbc.postgresql.org/
   • iODBC Downloads: https://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads

Linux (unixODBC)

1. Driver-Manager + Treiber installieren:

Linux (passende Distribution wählen):

Debian/Ubuntu:

sudo apt update
sudo apt install unixodbc odbc-postgresql

Arch Linux:

sudo pacman -Syu
sudo pacman -S unixodbc postgresql-libs

Optional (AUR, falls psqlodbc nicht im Standard-Repo vorhanden ist):

yay -S psqlodbc

2. Installation prüfen:

odbcinst -j
odbcinst -q -d

3. Falls kein PostgreSQL-Treiber gelistet ist (oder odbcinst.ini leer ist), Treiber manuell in der von odbcinst -j angezeigten Datei eintragen.

Beispiel für Linux (/etc/odbcinst.ini):

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode)
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/psqlodbcw.so
FileUsage=1

4. Danach erneut prüfen:

odbcinst -q -d

Falls die Datei psqlodbcw.so nicht existiert, echten Pfad suchen:

Linux:

find /usr -name 'psqlodbc*.so' 2>/dev/null

5. DSN in ~/.odbc.ini (User) oder /etc/odbc.ini (System) anlegen.
6. Referenzen:
   • PostgreSQL-ODBC-Treiber: https://odbc.postgresql.org/
   • unixODBC: https://www.unixodbc.org/
   • Arch Wiki ODBC (Linux): https://wiki.archlinux.org/title/ODBC

Ihr DSN soll mindestens enthalten:

• DSN-Name (z. B. northwind_odbc)
• Treiber
• Host
• Port
• Datenbankname
• Benutzer

Beispiel für Linux in ~/.odbc.ini (User-DSN):

[northwind_odbc]
Driver=PostgreSQL Unicode
Servername=localhost
Port=5432
Database=northwind
Username=student
Password=
SSLmode=prefer

Hinweise:

• Der Wert bei Driver muss exakt zu einem installierten Treibernamen passen (prüfen mit odbcinst -q -d).
• Lassen Sie Password nach Möglichkeit leer und geben Sie es erst beim Verbindungsaufbau ein.
• Für Linux kann dieselbe Struktur als System-DSN auch in /etc/odbc.ini verwendet werden.

2.2 Verbindung testen

Testen Sie die Verbindung mit einem ODBC-fähigen Tool Ihrer Wahl.

Windows

1. Wenn verfügbar, im ODBC Data Source Administrator “Test Connection” ausführen.
2. Danach mit Excel (Daten abrufen -> Aus ODBC) oder DBeaver testen.

macOS

1. Verbindung direkt in Excel testen:
   • Excel -> Daten abrufen -> Aus Datenbank
   • Danach im iODBC Data Source Chooser auf System DSN wechseln und den DSN auswählen.
2. Alternativ mit DBeaver testen.

Linux

1. Zuerst per Kommandozeile testen:

isql -v northwind_odbc

2. Danach mit GUI-Tool testen:
   • DBeaver oder LibreOffice Calc/Base

Führen Sie anschließend diese Abfragen aus und dokumentieren Sie das Ergebnis (z. B. Screenshot oder Protokoll):

SELECT COUNT(*) AS anzahl_kunden FROM customers;

SELECT COUNT(*) AS anzahl_bestellungen FROM orders;

Wenn eine Abfrage fehlschlägt:

• Notieren Sie die Fehlermeldung.
• Beschreiben Sie, ob das Problem am Treiber, am DSN oder an den Credentials lag.

---

Auftrag 3 - ODBC mit Office-Tools nutzen

Greifen Sie mit Excel/Word oder LibreOffice per ODBC auf die Northwind-Datenbank zu.

Motivation: In der Praxis wird ODBC oft genau in folgenden Situationen genutzt:

1. Excel-Szenario (Analyse): Die Vertriebsleitung möchte kurzfristig wissen, aus welchen Ländern die meisten aktiven Kundinnen und Kunden kommen. Statt Daten manuell zu exportieren, wird eine ODBC-Verbindung in Excel genutzt, um die Daten direkt aus PostgreSQL zu laden, zu filtern und als Pivot-Auswertung bereitzustellen.
2. Word-Szenario (Kommunikation): Das Backoffice soll personalisierte Informationsschreiben an ausgewählte Kundinnen und Kunden senden (z. B. neue Lieferbedingungen nach Region). Über eine ODBC-Datenquelle in Word werden Empfängerdaten direkt aus der Datenbank geladen und als Serienbrief erzeugt.

Die folgenden Teilaufgaben setzen genau diese beiden Szenarien um.

Hinweis zur Plattform:

• Windows: Excel und Word können direkt mit ODBC genutzt werden.
• macOS: Excel kann mit ODBC genutzt werden; wegen der Begrenzung der kostenlosen Actual-ODBC-Version auf 3 Datensätze pro Abfrage sollten Sie zum Testen bewusst kleine Ergebnismengen verwenden oder eine lizenzierte Version einsetzen. Für Serienbriefe nutzen Sie Word mit direkter ODBC-Datenquelle oder alternativ LibreOffice Writer mit ODBC.
• Linux: Nutzen Sie LibreOffice Calc/Base für ODBC-Zugriff; für Serienbriefe verwenden Sie LibreOffice Writer mit ODBC-Datenquelle.

3.1 Excel oder LibreOffice Calc: Datenimport per ODBC

Stellen Sie in Excel oder LibreOffice Calc eine ODBC-Verbindung zu Ihrem DSN her und importieren Sie Daten aus der Tabelle customers.

So binden Sie die Datenquelle in Excel ein (kurz):

1. Excel öffnen und zu Daten wechseln.
2. In der deutschen Excel-Version: Daten -> Daten abrufen -> Aus Datenbank wählen.
3. Im anschließend geöffneten iODBC Data Source Chooser auf System DSN wechseln und dort Ihren DSN (z. B. northwind_odbc) auswählen.
4. Falls abgefragt, Benutzername/Passwort eingeben.
5. Im Navigator die Tabelle customers wählen oder eine SQL-Abfrage angeben.
6. Mit Laden (oder Transformieren) bestätigen.
7. Die geladenen Daten mit Aktualisieren (Refresh) neu einlesen.

Hinweis für macOS mit kostenloser Actual-ODBC-Version:

• Pro Abfrage werden maximal 3 Datensätze zurückgegeben.
• Für vollständige Auswertungen (z. B. belastbare Pivot-Analysen) ist eine lizenzierte Version erforderlich.

Aufgaben:

1. Importieren Sie mindestens die Spalten customer_id, company_name, country.
2. Filtern Sie in Excel/Calc nach einem Land Ihrer Wahl.
3. Ermitteln Sie die Anzahl der Kunden pro Land (Pivot-Tabelle oder Gruppierung).
4. Aktualisieren Sie die Abfrage (Refresh) und dokumentieren Sie, was passiert.

3.2 Word oder LibreOffice Writer: ODBC als Datenquelle

Verwenden Sie Word oder LibreOffice Writer mit ODBC-Datenquelle (z. B. Serienbriefempfänger aus customers).

macOS-Hinweis: Wenn direkte ODBC-Anbindung in Word nicht möglich ist, ist der einfachste Weg über Excel: Daten in Excel per ODBC aus PostgreSQL laden und anschließend in Word als Datenquelle für den Serienbrief verwenden.

Linux-Alternative: Wenn Word nicht verfügbar ist, verwenden Sie LibreOffice Writer mit ODBC-Datenquelle.

Aufgaben:

1. Binden Sie die Tabelle customers als Empfängerliste ein.
2. Erstellen Sie ein kurzes Seriendokument mit mindestens drei Feldern (z. B. Firmenname, Kontaktperson, Land).
3. Filtern Sie auf ein Land oder eine Region.
4. Erzeugen Sie mindestens 3 individualisierte Ausgaben (Vorschau reicht).

3.3 Fehlerdiagnose und Reproduzierbarkeit

Untersuchen Sie typische ODBC-Probleme systematisch und dokumentieren Sie Ihre Lösungsschritte.

Aufgaben:

1. Erzeugen Sie gezielt einen Fehlerfall (z. B. falscher DSN-Name, falscher Port oder falscher Benutzer) und notieren Sie die genaue Fehlermeldung.
2. Beheben Sie den Fehler und dokumentieren Sie die Änderung, die zur erfolgreichen Verbindung geführt hat.
3. Erstellen Sie eine kurze Checkliste (5-7 Punkte), mit der ein Mitschüler die gleiche Verbindung reproduzierbar einrichten kann.

---

Auftrag 4 - Sicherheit und Robustheit im Office-Zugriff

4.1 Sichere Konfiguration

Setzen Sie für Ihren ODBC-Zugriff folgende Sicherheitsmaßnahmen um und dokumentieren Sie diese:

1. Verwenden Sie einen DB-Benutzer mit möglichst wenigen Rechten (nur read-only für diese Übung).
2. Speichern Sie keine Klartext-Passwörter in geteilten Dateien.
3. Dokumentieren Sie, welche Datenquelle verwendet wurde (DSN-Name), ohne sensible Zugangsdaten offenzulegen.

Beantworten Sie zusätzlich diese Frage schriftlich:

• Wo speichert ein DSN das Passwort auf Ihrem Betriebssystem (Windows, macOS oder Linux), und warum ist das sicherheitsrelevant?

---

Auftrag 5 - Vergleich und Reflexion

Beantworten Sie schriftlich:

1. Welche Unterschiede haben Sie zwischen ODBC und dem direkten Arbeiten im SQL-Client wahrgenommen?
2. Für welche Projektszenarien würden Sie ODBC empfehlen?
3. Für welche Szenarien wären native Treiber vermutlich die bessere Wahl?
4. Welche Rolle spielen Connection-Strings/DSNs in Bezug auf Portabilität und Wartbarkeit?

---

Abgabe

Geben Sie ab:

1. Ihre schriftlichen Antworten (Theorie + Reflexion)
2. DSN-Konfiguration in dokumentierter Form (ohne Klartext-Passwort)
3. Office-Artefakte:
   • Tabellen-Artefakt mit ODBC-Import und Auswertung (Excel oder LibreOffice Calc)
   • Serienbrief-Artefakt (Word oder LibreOffice Writer mit direkter ODBC-Datenquelle)
4. Kurze Ergebnisdokumentation Ihrer Testläufe inkl. Fehleranalyse