Daten per OData API zur Verfügung stellen

Gültig ab OCT Version 5.10

Beschreibt die Bereitstellung von Daten aus der OCT Datenbank im OData Format. Das OData Format kann von vielen Drittprogrammen standardmäßig gelesen und verarbeitet werden (z.B. Excel, PowerBI, Tagetik).

1. Voraussetzungen

In einer OCT Datenbank unter “Administration” → “Einstellungen” → “OData” muss ein Benutzername und ein Passwort für den Zugriff auf die OData API festgelegt werden.

2. Erstellen eines OData Endpunkts zum Datenabruf

Für jede SQL Server Prozedur im “odata” Schema der OCT Datenbank wird automatisch ein OData Endpunkt erzeugt.

Dementsprechend muss man zum Erstellen eines neuen OData Endpunkts nur eine passende Prozedur erstellen.

2.1. Grundsätzliche Bedingungen

• die Prozedur muss im Schema “odata” erstellt sein

• der Name der Prozedur darf keine Sonderzeichen enthalten

• die Prozedur muss den Parameter @Username vom Typ NVARCHAR enthalten

• die Prozedur muss genau eine Ergebnismenge zurückgeben

2.2. Beispielprozedur Liste der Mandanten “odata.Mandanten”

DROP PROCEDURE IF EXISTS odata.Mandanten;
GO

CREATE PROCEDURE odata.Mandanten
    @Username         NVARCHAR(255)  = NULL
AS
BEGIN
    BEGIN TRY
        -- Logging
        DECLARE @TimestampCall DATETIME = GETUTCDATE();
        DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
        DECLARE @AffectedRows INT = 0;
        DECLARE @ResultCode INT = 501;
        DECLARE @Comment NVARCHAR(4000) = N'';
        DECLARE @ParameterString NVARCHAR(MAX) = N'';
        DECLARE @TransactUsername NVARCHAR(255) = N'';
        EXEC system.spGET_ParameterString @ParameterString OUTPUT, 1, @Username;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;

        -- Return data
        SELECT
            MandantenID                AS MandantenID
          , MandantenName              AS MandantenName
          , COALESCE(Beschreibung, '') AS Beschreibung
          , FinanzjahrBeginn           AS FinanzjahrBeginn
          , COALESCE(Kontorahmen, '')  AS Kontorahmen
          , Kontenlaenge               AS Kontenlaenge
          , COALESCE(Waehrung, '')     AS Waehrung
        FROM result.tFIN_Mandanten;

        SET @AffectedRows = @@ROWCOUNT;
        SET @ResultCode = 200;
    END TRY
    BEGIN CATCH
        SET @ResultCode = 500;
        SET @Comment = ERROR_MESSAGE();
    END CATCH;

    EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment;

    IF @ResultCode >= 500
    BEGIN
        EXEC system.spSEND_Message 'ERROR', @Comment;
    END

    RETURN @ResultCode;
END;

Der wichtigste Teil der Abfrage sind die Zeilen 21 bis 29, in denen die gewünschten Daten abgefragt werden. Die Befehle davor und danach entsprechen dem Standard-Aufbau einer typischen OCT Prozedur. Grundsätzlich kann die Prozedur beliebig aufgebaut und angepasst werden.

3. Aufruf eines OData Endpunkts

Ein OData Endpunkt wird dynamisch anhand der eingespielten Prozeduren im “odata” Schema verfügbar gemacht. Der Aufbau der URL ist folgendermaßen:

http(s)://<Servername>/odata/<Datenbankserver-ID>/<Datenbankname>/<Prozedurname>/octodata

Zum Abruf der Daten wird die GET-Methode verwendet.

Als Authentifizierungsart wird die “Basic” Authentifizierung verwendet. Der Benutzername und das Passwort wurde unter “1. Voraussetzungen” festgelegt.

3.1. Beispiel

Die normale Startseite der OCT Datenbank wird über folgende URL aufgerufen:

https://AppServer01/octsql/octdb/start

Daraus ergibt sich folgender Endpunkt für die Ergebnisse der “odata.Mandanten” Prozedur:

https://AppServer01/odata/octsql/octdb/Mandanten/octodata

3.2. Antwort

Die Antwort erfolgt im standardisierten OData Format.

{
  "@odata.context": "https://appserver01/odata/octsql/octdb/Mandanten/$metadata#OctOData",
  "value": [
    {
      "Id": 0,
      "MandantenID": "1",
      "MandantenName": "Projektbau Leipzig GmbH",
      "Beschreibung": "",
      "FinanzjahrBeginn": 1,
      "Kontorahmen": "",
      "Kontenlaenge": 4,
      "Waehrung": ""
    },
    {
      "Id": 1,
      "MandantenID": "2",
      "MandantenName": "Projektbau GmbH & Co.KG",
      "Beschreibung": "",
      "FinanzjahrBeginn": 1,
      "Kontorahmen": "",
      "Kontenlaenge": 4,
      "Waehrung": ""
    },
    {
      "Id": 2,
      "MandantenID": "3",
      "MandantenName": "Projektbau Hochbau Leipzig KG",
      "Beschreibung": "",
      "FinanzjahrBeginn": 1,
      "Kontorahmen": "",
      "Kontenlaenge": 4,
      "Waehrung": ""
    }
  ]
}

Zusätzlich zu den von der Prozedur zurückgegebenen Feldern wird automatisch das Feld “Id” hinzugefügt, das die Datensätze durchgehend nummeriert und einen eindeutigen Schlüssel darstellt.

3.3. Einschränkungen

3.3.1. OpenType Spalten

Aufgrund des dynamischen Aufbaus der OCT OData Schnittstelle werden sämtliche Felder als “OpenType”-Spalten übergeben. Das erfordert bei Drittprogrammen wie z.B. Excel oder PowerBI zusätzliche Einstellungen beim Datenimport (z.B. in Excel die Option “OpenType-Spalten einschließen” aktivieren).

3.3.2. Berechtigungssteuerung

Sämtliche über OData zur Verfügung gestellten Daten sind für alle Benutzer, die Zugriff auf den Benutzernamen und das Passwort haben, zugänglich. Es ist nicht möglich den Umfang der Daten einzuschränken. Daher sollte darauf geachtet werden, dass nur Daten zur Verfügung gestellt werden, auf die alle OData Endanwender Zugriff haben sollten.