Dynamics NAV / Business Central mit einer PostgreSQL Datenbank verbinden

Von | 26. Juli 2020

Hallo zusammen,

wie kann eine PostgreSQL Datenbank in Dynamics NAV / Dynamics Business Central angebunden werden? Genau vor dieser Frage standen meine Kollegen und ich in einem Projekt.

Für mich lagen drei Lösungsansätze auf der Hand:

  • Option 1: Verbindung per .NET Integration (Service Tier verbindet sich zu der PostgreSQL Datenbank)
  • Option 2: Verbindung des Microsoft SQL Servers zu der PostgreSQL Datenbank
  • Option 3: Nutzung einer REST api um Daten per Web Service von der PostgreSQL Datenbank lesen zu können.

Auf Option 3 gehe ich in diesem Blog nicht ein, da es zum Thema REST mit Dynamics NAV / BC eigentlich schon genug Informationen gibt.

Für Option 1 und 2 wird als Basis ein separater Treiber benötigt. Microsoft SQL Server kann nicht direkt ohne Treiber mit einer PostgreSQL Datenbank verbunden werden. Ich habe mich für diesen Treiber entschieden: https://odbc.postgresql.org/

Ich empfehle die 64-bit Variante des Treibers. Die Installation des Treibers sollte als Administrator ausgeführt werden. Die Installation sollte dort installiert werden, wo dieser später auch benutzt wird. Beispiele:

  • Möchte ich eine Verbindung von SQL Server zu der PostgreSQL Datenbank aufbauen, dann muss der Treiber auf dem SQL Server installiert werden.
  • Möchte ich eine Verbindung über die Service Tier (beispielsweise per Task Scheduler) aufbauen, dann muss der Treiber auf der Maschine der Service Tier installiert werden.

Installation des ODBC Treibers

Die Installation ist einfach durchzuführen. Starte die Installation und klicke auf “Weiter” (Next):

Akzeptiere nach dem Lesen die “License Agreement”:

Anschließend können die Module ausgewählt werden. Die Dokumentation ist optional:

Die Installation wird anschließend ausgeführt und benötigt Adminrechte.

Nach der Installation können wir die Verbindung testen. Dafür können wir das Feature “System DNS” von Windows Server nutzen. Dazu muss auf dem Windows Server der “Server Manager” geöffnet werden.

Im Server Manager können wir über Tools die Option “ODBC Data Sources (64-bit)” auswählen:

In dem neuen Fenster wählen wir den Reiter System DSN aus.

Hier können wir nun einen neuen Eintrag hinzufügen. Dazu klicken wir auf “Add” (Hinzufügen). Wir wählen anschließend den Treiber “PostgreSQL Unicode (x64)” aus:

Die folgenden Werte sollten ausgefüllt werden:

  • Data Source: Gibt den generellen Namen an. Ich empfehle einen einfachen Namen, da dieser später wiederverwendet wird (in Linked Server des SQL Servers oder auch in Dynamics NAV).
  • Database: Muss den exakt korrekten Namen der PostgreSQL Datenbank beinhalten.
  • Server: Sollte den DNS Namen oder die IP des PostgreSQL Servers beinhalten.
  • Username: Benutzername des Logins
  • Description: Das ist optional, kann aber helfen eine bessere Übersicht über die Datenquellen zu schaffen, wenn Verbindungen zu vielen verschiedenen Datenbanken existieren.
  • SSL Mode: Das hängt stark von der Konfiguration der PostgreSQL Datenbank ab, allerdings sollte dies mindestens auf “require” stehen um die Verbindung sicher zu machen.
  • Port: Port des PostgreSQL Servers. Der Standardport ist 5432.
  • Password: Passwort für den Benutzernamen.

Anschließend sollten die Einstellungen gespeichert werden. Der Treiber kann innerhalb der Option “Datasource” / “Global” noch eingestellt werden. Dort werden auch immer wieder Optionen verbessert. Es lohnt sich daher von Zeit zu Zeit auch den Treiber zu aktualisieren, falls ein bestimmtes Problem besteht. Beispielsweise kann über den Treiber gesteuert werden, ob boolean Felder als integer (mit 0 oder 1) oder als Chars übertragen werden.

Bei Probleme mit dem Treiber lohnt sich ein Blick ins Wiki oder in dafür spezialisierten Foren (beispielsweise: https://www.pg-forum.de)

Nach der Einstellung der Verbindungsparameter kann der Treiber mit der Aktion “Test” geprüft werden:

Sobald die Verbindung mit System DNS funktioniert, sollte auch die Verbindung mit dem SQL Server oder mit Dynamics NAV funktionieren. Das ist die bereits die halbe Miete. Sollte die Verbindung nicht klappen, sollte generell geprüft werden, ob der PostgreSQL Server erreichbar ist. Wird dieser in der Cloud gehostet sind ggf. IP Freigaben notwendig um überhaupt eine Verbindung aufbauen zu können.

Zugriff auf die PostgreSQL Datenbank mit pgAdmin

Empfehlenswert ist ebenfalls ein Tool um auf die PostgreSQL Datenbank zugreifen zu können. Dafür habe ich pgadmin verwendet, da es übersichtlich und einfach ist.

https://www.pgadmin.org

Hinweis: Ich hatte Probleme mit pgAdmin in der Verwendung des Internet Explorers. Vermutlich müssen einige Skripte in den Internet Einstellung aktiviert werden. Mit anderen Browsern klappt es ohne Probleme.

Zugriff auf die PostgreSQL Datenbank per SQL Server

Im SQL Server Management Studio können wir eine Verbindung per “Linked Server” aufbauen.

Dazu erstellen wir einen neuen Linked Server und wählen die folgenden Einstellungen:

  • Linked server: Name des Linked Servers – kann frei vergeben werden. Keine Leerzeichen oder Sonderzeichen verwenden um spätere Probleme zu vermeiden.
  • Provider: Microsoft OLE OD Provider for ODBC Drivers
  • Product name: optional
  • Data source: Hier benutzen wir den exakt gleichen Namen, den wir in den System DNS Einstellungen angegeben haben.
  • Provider string: optional
  • Location: optional
  • Catalog: optional

Unter Sicherheit/Security sollte die Option “be made using this security context” ausgewählt werden. Benutzername und Password müssen hier erneut eingegeben werden.

Nach der Einrichtung des Linked Servers kann per Rechtsklick “Test Connection” ausgewählt werden. Die Verbindung sollte auch hier erfolgreich sein. Auch sollte man unter Catalogs die PostgreSQL Datenbank sehen und unter Tables die einzelnen PostgreSQL Tabellen. Es gibt auch die Möglichkeit per Connection String den SQL Server mit der PostgreSQL Datenbank zu verbinden. In diesem Fall ist keine Konfiguration in den System DNS Einstellungen notwendig.

Zum Lesen der PostgreSQL Tabellen wird immer ein Skript benötigt.

Beispiel:

 
SELECT
	number
	ISNULL(name), 100), '') as name, 
	ISNULL(email), 80), '') as email,
	ISNULL(CAST(active_customer as tinyint), 0) as active_customer,
	CONVERT(VARCHAR,ISNULL(updated_at, CAST('1753-01-01 00:00:00:000' as datetime2))) as updated_at
FROM OPENQUERY(NAVLinkedServer, 'SELECT number, name::varchar(255), email::varchar(1000), active_customer, updated_at FROM public.customers');
GO

Wir benötigen den Befehl OPENQUERY, da wir mit einem normalen Skript nicht alle Datentypen korrekt lesen können und höchstwahrscheinlich Probleme bekommen. Allerdings hat OPENQUERY auch den Nachteil, das das SELECT Statement nur statisch mit einer WHERE Klausel eingeschränkt werden kann. Erstellen wir hieraus eine View und verknüpfen diese mit einer Tabelle in NAV werden wir immer das gleiche SELECT Statement verwenden um aus der PostgreSQL Tabelle Daten zu lesen. Sollte man mit Tabellen arbeiten, die immer mehr Daten beinhalten, dann ist die Option möglicherweise nicht sinnvoll.

Außerdem muss hier der Transaction Type in NAV geändert werden. Der Standard ist ReadNoLocks. Das wird aber dazu führen, das in dem SQL SELECT ein “BEGIN DISTRIBUTED TRAN” ergänzt wird. Das wird von PostgreSQL nicht unterstützt. Demnach muss der TransactionType auf “Browse” umgestellt werden, wenn Daten von PostgreSQL gelesen werden sollen.

Mehr dazu kann in der Online Hilfe lesen: https://docs.microsoft.com/en-us/dynamics-nav/currenttransactiontype-function–database-

Die Integration innerhalb des SQL Servers ist also relativ unflexibel. Hier benötigt man allerdings keine Programmierung mit .NET in Dynamics NAV. Es reicht die Erstellung einer View in der NAV Datenbank, das Verknüpfen der View per LinkedObject in NAV und das Umstellen des Transaction Type in NAV.

Zugriff auf die PostgreSQL Datenbank per Service Tier

Für den Zugriff auf die PostgreSQL Datenbank benötigen .NET Variablen. Das sind die folgenden:

Als Text:

ODBCConnection – DotNet – System.Data.Odbc.OdbcConnection.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
ODBCCommand  – DotNet – System.Data.Odbc.OdbcCommand.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
ODBCDataReader – DotNet – System.Data.Odbc.OdbcDataReader.’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′

Folgende Hinweise:

  • RunOnClient sollte auf “No” stehen. Die Verbindung zur PostgreSQL Datenbank sollte nur von der Service Tier ausgeführt werden.
  • Baut ein Fehlerhandling ein, falls der PostgreSQL Server mal nicht erreichbar ist (TryFunction)

So könnte die Funktion zum Holen von Daten dann aussehen. Die Variable QueryText ist eine Text Variable und beinhaltet das SELECT Statement. Was ihr hier braucht hängt stark von euren Anforderungen ab. Ihr könnt eure SELECT Abfrage aber auch vorher in pgAdmin testen und dann in NAV übernehmen. Denkt daran bei großen Tabellen eure Abfrage mit einer WHERE Klausel korrekt einzuschränken. Hier bieten sich unterschiedliche Konzepte an:

  • Nur neue Daten lesen, die beispielsweise seit dem letzten Lesevorgang aktualisiert worden sind. In diesem Fall benötigt ihr ein Feld “updated_at” welches ohne Zeitzonen das Datum und die Uhrzeit der letzten Änderung speichert. Die Uhrzeit sollte in einem solchen Fall 6 Nachkommastellen pro Sekunde vorweisen.
  • Nur neue Daten lesen, die beispielsweise eine neue EntryNo vorweisen. In diesem Fall dürften Daten in der PostgreSQL Datenbank nicht modifiziert werden. Jeder Änderung sollte einen neuen Eintrag generieren.

Es gibt auch andere Varianten, aber ihr solltet diese Thematik in jeden Fall berücksichtigen.

Wurde die Funktion korrekt ausgeführt, dann könnt ihr mit “IF ODBCDataReader.HasRows THEN” prüfen, ob ihr Daten für eure Abfrage erhalten habt. Falls das nicht der Fall ist, könnt ihr damit die Durchführung von unnötigen Code vermeiden und die Codeausführung stoppen.

Mit dem Befehl “WHILE ODBCDataReader.Read DO BEGIN” könnt ihr durch jeden Eintrag eures Ergebnisses durchgehen. Die Schleife arbeitet die Daten in der korrekten Reihenfolge ab. Diese kann in dem SELECT Statement auch mit “ORDER BY” beeinflusst werden.

Zusätzlich gibt es dann diverse Befehle um die einzelnen Spalten korrekt zu lesen:

  • ODBCDataReader.IsDBNull(1)  – prüft ob das angegebene Feld einen Wert enthält. Ihr könnt es beispielsweise überspringen, wenn es NULL ist.//Der Parameter ist die Feldnummer innerhalb der Query (0 ist das erste Feld / 1 ist das zweite Feld / 2 ist das dritte Feld / …)
  • ODBCDataReader.GetInt32(0) – gibt den Wert als integer zurück – für NAV integer Felder geeignet
  • ODBCDataReader.GetString(2) – gibt den Wert als String zurück – für NAV text Felder geeignet
  • ODBCDataReader.GetBoolean(4) – gibt den Wert als boolean zurück – für NAV boolean Felder geeignet – solltet ihr hier Probleme beim Lesen der Daten bekommen, dann prüft den ODBC Treiber (System DNS) und versucht auch das Boolean Feld mit dem Befehl “COALESCE(Feld,FALSE)” im SELECT statement zu lesen. Damit wird Null in False umgewandelt.

Bei decimal Feldern muss ggf wie folgt vorgegangen werden:

  • Convert.ToDecimal(ODBCDataReader.GetValue(10)) – Convert ist System.Convert.’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′

Am Ende des Lesevorgangs sollte die Verbindung ordnungsgemäß geschlossen werden:

  • ODBCConnection.Close;

Ich hatte bei der Integration in NAV immer ausfüllende Leerzeichen erhalten. Anstatt “Gustav” stand beispielsweise “Gustav                 ” im Namen. Diese Leerzeichen habe ich per Funktion abgeschnitten:

Ich halte die .NET Integration für sehr flexibel und einfach erweiterbar. Ein Wechsel des TransactionsType ist hier nicht notwendig. Die .NET Variante wird vermutlich auch noch sehr lange nutzbar sein, sofern man die Service Tier selber betreibt. In der Microsoft Cloud ohne Zugriff auf die Server sollte man sich eher mit der REST api in AL befassen und die Daten per Web Service austauschen. Wer allerdings noch länger plant OnPremise auf einem lokalen oder einem eigenen Cloud Server zu nutzen, kann die .NET Integration mit Ausführung auf dem Server noch eine ganze Weile nutzen. Dies sollte problemlos mit C/AL oder AL funktionieren.

Ich hoffe das dieser Blog beim Einstieg in dieses Thema hilft.

Viel Spaß.

3 Gedanken zu „Dynamics NAV / Business Central mit einer PostgreSQL Datenbank verbinden

  1. Pingback: Dynamics NAV / Business Central mit einer PostgreSQL Datenbank verbinden - Robert's Dynamics NAV Entwickler Blog - Dynamics 365 Business Central/NAV User Group - Dynamics User Group

  2. Matthias Rabus

    Hallo Robert,

    interessanter Blogpost – wie immer.

    Nur das Ende ist mMn. sachlich nicht korrekt, da die .NET Integration auch in AL nicht nutzbar ist (wenn Du Deine Lizenz noch nicht auf BC umgestellt hast). Zukünftige BC Lizenzen müssen nämlich entweder “Universal Code” entsprechen oder zusätzliche Module schalten, die ziemlich teuer sind für keinen realen Benefit.
    Quelle z.B.: https://www.anaptis.com/universal-code-initiative-cloud-ready-oder-abstrafung/ oder https://www.1clickfactory.com/blog/business-central-universal-code-initiative/

    Der einzige wirklich zukunftssichere Weg ist der Datenaustausch per API oder Webservice.

    Viele Grüße und frohe Weihnachten,
    Matthias

    Antworten
    1. Robert Beitragsautor

      Hallo Matthias,

      der Blogeintrag ist von 2020 🙂

      Die Universal Code Initiative ist soweit ich weiß erst später veröffentlicht worden. Zu mindestens war es mir damals noch nicht bekannt.

      Antworten

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert