OCT API zum Ausführen von Datenbank-Prozeduren mit Parameterübergabe

Gültig ab OCT Version 5.11

1. Vorwort

Die Funktionalität eine Datenbank-Prozedur mit Parameterübergabe über die OCT API auszuführen ist eine Erweiterung der OData API, die in folgendem Artikel beschrieben ist: Daten per OData API zur Verfügung stellen

2. 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.

3. Erstellen eines API Endpunkts

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

3.1. Grundsätzliche Bedingungen

die Prozedur muss im Schema “odata” erstellt sein
der Name der Prozedur darf keine Sonderzeichen enthalten
die Prozedur muss folgende Parameter enthalten:
- @Username vom Typ NVARCHAR(255)
- @POSTBody vom Typ NVARCHAR(MAX) enthält einen beliebigen Text in beliebigem Format, der beim API-Aufruf als Body mitgegeben wird und von der Prozedur verarbeitet werden kann
die Prozedur kann maximal eine Ergebnismenge zurückgeben

3.2. Einfache Beispielprozedur Mandant hinzufügen “odata.MandantNeu” (nur Text als Parameter)

JSON

@POSTBody = 'Firma 100';

SQL

DROP PROCEDURE IF EXISTS odata.MandantNeu;
GO

CREATE PROCEDURE odata.MandantNeu
    @Username         NVARCHAR(255)  = NULL,
    @POSTBody         NVARCHAR(MAX)  = 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, 2, @Username, @POSTBody;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;

        -- Datensatz in Tabelle einfügen
        INSERT INTO integration.tMandanten (MandantenID)
        VALUES (@POSTBody);
        
        -- Ergebnis zurückgeben
        SELECT @POSTBody AS NeuerMandant;

        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 26, in denen der im Parameter @POSTBody enthaltene Wert in die Tabelle integration.tMandanten in die Spalte MandantenID eingetragen wird und anschließend als Ergebnis zurückgegeben wird. Die Befehle davor und danach entsprechen dem Standard-Aufbau einer typischen OCT Prozedur. Grundsätzlich kann die Prozedur beliebig aufgebaut und angepasst werden.

Grundsätzlich lassen sich beliebige Operationen innerhalb der Prozedur ausführen, so dass auch deutlich komplexere Aufgabenstellungen gelöst werden können.

3.2. Komplexe Beispielprozedur Mandant hinzufügen “odata.MandantNeu” (JSON Text Parameter)

JSON

@POSTBody = '
{
    "Mandanten": [
        {
            "MandantenID": "100",
            "Name": "Firma 100"
        },
        {
            "MandantenID": "200",
            "Name": "Firma 200"
        }
    ]
}
';

SQL

DROP PROCEDURE IF EXISTS odata.MandantNeu;
GO

CREATE PROCEDURE odata.MandantNeu
    @Username         NVARCHAR(255)  = NULL,
    @POSTBody         NVARCHAR(MAX)  = 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, 2, @Username, @POSTBody;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
        
        -- JSON Syntax prüfen
        IF ISJSON(@POSTBody) = 0
        BEGIN
            EXEC system.spSEND_Message 'ERROR', 'JSON Syntax fehlerhaft'
        END
        
        -- jedes Element im JSON Array "Mandanten" in die Tabelle einfügen
        INSERT INTO integration.tMandanten (MandantenID, Name)
        SELECT 
            JSON_VALUE(m.value, '$.MandantenID') AS MandantenID,
            JSON_VALUE(m.value, '$.Name') AS Name
        FROM OPENJSON(@json, '$.Mandanten') AS m;
        
        -- Ergebnis zurückgeben
        SELECT 'Mandanten erstellt' AS Info;        
        
        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;

In diesem Beispiel wird das im @POSTBody übergebene JSON Objekt zuerst geprüft und anschließend wird jedes Element im Array “Mandanten” in die Zieltabelle integration.tMandanten eingefügt.

Es ist darüber hinaus auch möglich sämtliche OCT Prozeduren innerhalb dieser Prozedur aufzurufen und so z.B. das Erstellen von neuen Fabriken, Produktlinien oder Produkten über die API durchzuführen.

4. Aufruf eines API Endpunkts

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

CODE

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

Zum Ausführen der Prozedur mit Parameterübergabe wird die POST-Methode verwendet.

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

Der Parameter @POSTBody wird durch den in der HTTP-POST Anfrage übergebenen Body befüllt.

4.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.MandantNeu” Prozedur:

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

Body: “Firma 100”

4.2. Antwort

Die Antwort erfolgt im standardisierten OData Format.

JSON

{
  "@odata.context": "https://AppServer01/odata/octsql/octdb/MandantNeu/$metadata#OctOData",
  "value": [
    {
      "Id": 0,
      "NeuerMandant": "Firma 100"
    }
  ]
}

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.

4.3. Einschränkungen

4.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).

Excel Dialog “Daten aus OData-Datenfeed”

4.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.