Letzten Wert Identitätsspalte zurückliefern per Stored Procedure

[Gutmann]

Hallo,
wir haben eine As400 mit V7R1 im Einsatz. Für ein Projekt wurde eine einfache Messtabelle mit Messkopf u. Messwerten erstellt. Für jede Tabelle existiert eine Stored Procedure zum hinzufügen der neuen Datensätze (Anforderung vom externen Dienstleister).

Ich habe die Tabellen u. SQL Prozeduren über das einfache IBM Tool "SQL Prozeduren ausführen..." erstellt. Allerdings liefert mir der Cursor für die Idenditätswerte immer NULL zurück. Führe ich ein Insert gefolgt von einem "VALUES IDENTITY_VAL_LOCAL()" aus erhalte ich die korrekte ID - die Tabelle scheint also korrekt zu arbeiten. Ich sehe das Problem eher in der Stored Procedure, entweder kann mir der Cursor diesen wert nicht zurückliefern (über einen OUT Parameter funktioniert es nämlich, damit kommt allerdings der externe Dienstleister nicht klar) oder ich habe einen Fehler (z.B. wegen Transaktionen?).
Ziel ist einfach, das ein 1x1 Zelliges-Resultset zurückkommt, das mir die ID des neuen Satzes liefert. Ich habe mir erstmal so beholfen, dass ich mir die "MAX(ID)" der Tabelle liefern lasse - ist nicht schön, funktioniert aber (ersteinmal) wie gewünscht. Das "fehlerhafte" Declare Cursor habe ich daher im Beispiel unten auskommentiert.

Vielleicht hat jemand ähnliche Probleme/Erfahrungen und einige Tips dazu.

DDL Mastertable

-- MASTER
DROP TABLE HGWBDE/MEASURE1P;
CREATE TABLE HGWBDE/MEASURE1P (
fMeas_ID FOR M1ID INT GENERATED ALWAYS AS IDENTITY(INCREMENT BY 1) PRIMARY KEY,
fRefFileName FOR M1RefFile VARCHAR(255),
fPartName FOR M1Part VARCHAR(255),
fRefDate FOR M1RefDate DATE,
fRefTime FOR M1RefTime TIME,
fRefTimeSt FOR M1RefTS TIMESTAMP,
fMesFileName FOR M1MesFile VARCHAR(255),
fMesDate FOR M1MesDate DATE,
fMesTime FOR M1MesTime TIME,
fMesTimeSt FOR M1MesTS TIMESTAMP,
fTotalState FOR M1State INT,
fInstance FOR M1Instance VARCHAR(255),
fFt0 FOR M1FT0 VARCHAR(255),
fFt1 FOR M1FT1 VARCHAR(255),
fFt2 FOR M1FT2 VARCHAR(255),
fFt3 FOR M1FT3 VARCHAR(255),
Created FOR M1DAT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
Application FOR M1PGM VARCHAR(50) NOT NULL DEFAULT '',
Autor FOR M1BEN VARCHAR(50) NOT NULL DEFAULT USER
)
RCDFMT MEASURE1F1
;

DDL Code StoredProcedure

CREATE OR REPLACE PROCEDURE HGWOBJ.AddMeasHead(IN iRefFileName varchar(255), IN iRefPartName varchar(255), IN iRefDate date, IN iRefTime time, IN iMesFileName varchar(255) ,IN iMesDate date, IN iMesTime time, IN iTotalState int, IN iInstance varchar(255), IN iFt0 varchar(255), IN iFt1 varchar(255), IN iFt2 varchar(255), IN iFt3 varchar(255))--, OUT Meas_Head_ID INT)
LANGUAGE SQL MODIFIES SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
--DECLARE Continue Handler for SQLEXCEPTION SET Meas_Head_ID = 0;
DECLARE C1 CURSOR WITH RETURN FOR Select MAX(fMeas_ID) MEAS_HEAD_ID FROM MEASURE1P;
-- Geht nicht
--DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR Select IDENTITY_VAL_LOCAL() MEAS_VALUE_ID FROM SYSIBM.SYSDUMMY1;
-- Satz hinzufügen
INSERT INTO MEASURE1P
(
fRefFileName, fPartName, fRefDate, fRefTime, fRefTimeSt, fMesFileName, fMesDate, fMesTime, fMesTimeSt, fTotalState, fInstance, fFt0, fFt1, fFt2, fFt3)
VALUES(
iRefFileName,iRefPartName,iRefDate,iRefTime,TIMEST AMP(iRefDate, iRefTime),iMesFileName,iMesDate,iMesTime, TIMESTAMP(iMesDate, iMesTime),iTotalState,iInstance,iFt0,iFt1,iFt2,iFt 3);
-- Liefert zuletzt eingefügte ID der Identitätsspalte
-- SET Meas_Head_ID = IDENTITY_VAL_LOCAL();
OPEN C1;
SET RESULT SETS WITH RETURN TO CLIENT CURSOR C1;
END;;

[Fuerchau]

Wie wärs damit?

SET Meas_Head_ID = IDENTITY_VAL_LOCAL();

DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR Select Meas_Head_ID MEAS_VALUE_ID FROM SYSIBM.SYSDUMMY1;

[Gutmann]

Oh man, danke!
Genau so etwas habe ich gesucht, dass ich eine Variable mittels Cursor im Resultset zurückliefern kann!

Hier die funktionierende Version:

/* ------------------------------------ */
/* Prozedur AddMeasHead erstellen */
/* ------------------------------------ */
CREATE OR REPLACE PROCEDURE HGWOBJ.AddMeasHead(IN iRefFileName varchar(255), IN iRefPartName varchar(255), IN iRefDate date, IN iRefTime time, IN iMesFileName varchar(255) ,IN iMesDate date, IN iMesTime time, IN iTotalState int, IN iInstance varchar(255), IN iFt0 varchar(255), IN iFt1 varchar(255), IN iFt2 varchar(255), IN iFt3 varchar(255))--, OUT Meas_Head_ID INT)
LANGUAGE SQL MODIFIES SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE Meas_Head_ID INT DEFAULT 0;
DECLARE C1 CURSOR FOR Select MEAS_HEAD_ID FROM SYSIBM.SYSDUMMY1;
-- Satz hinzufügen
INSERT INTO MEASURE1P
( fRefFileName, fPartName, fRefDate, fRefTime, fRefTimeSt, fMesFileName, fMesDate, fMesTime, fMesTimeSt,
fTotalState, fInstance, fFt0, fFt1, fFt2, fFt3
)
VALUES
(
iRefFileName, iRefPartName, iRefDate, iRefTime, TIMESTAMP(iRefDate, iRefTime), iMesFileName, iMesDate, iMesTime, TIMESTAMP(iMesDate, iMesTime), iTotalState, iInstance, iFt0, iFt1, iFt2, iFt3
);
-- Liefert zuletzt eingefügte ID der Identitätsspalte
SET Meas_Head_ID = IDENTITY_VAL_LOCAL();
OPEN C1;
SET RESULT SETS WITH RETURN TO CLIENT CURSOR C1;
END;;


Folgende Nachricht erscheint abschließend noch - ich denke eher informativ als eine Warnung:


SQL-Status: 0100C
Vendorencode: 466
Nachricht: [SQL0466] 1 Ergebnisgruppen sind aus Prozedur ADDMEASHEAD in HGWOBJ verfügbar.
Ursache . . . . : Prozedur ADDMEASHEAD in BIB wurde aufgerufen und gab eine oder mehrere Ergebnisgruppen zurück.
Fehlerbeseitigung: Keine.Anweisung wurde erfolgreich mit Warnungen ausgeführt (20 MS)

[B.Hauser]

Der SQL-Status 0100C bzw. der SQLCODE 466 ist wine Warnung und wird grundsätzlich ausgegeben, wenn ein Result Set zurückgegeben wird.

Warum machst Du das Ganze überhaupt über Result set, wenn Du nur einen einhzigen Datensätz einfügst?
Warum gibst Du nicht einfach eine Ausgabe-Variable zurück.
Bzw. der Befehl SET RESULT SETS ist nicht notwendig. Result Sets werden allein schon durch den OPEN (ohne anschließenden CLOSE) zurückgegeben.

Birgitta

[Fuerchau]

Ich nehme mal an deswegen:

"(über einen OUT Parameter funktioniert es nämlich, damit kommt allerdings der externe Dienstleister nicht klar)"

[Gutmann]

Das mit dem SQL State dachte ich mir bereits - da etwas von "Fehlerbeseitigung: Keine" erwähnt wird.

Das mit dem SET RESULT SETS wusste ich nicht 100% - hatte zwar auch festgestellt, dass ein Resultset im "SQL Prozedur ausführen" ohne den Befehl angezeigt wird aber hatte es in einigen anderen Prozeduren bereits problemlos verwendet. Aber gut zu wissen, dann lasse ich es in Zukunft weg u. arbeite nur noch mit OPEN CURSOR.

Die OUT Variable war leider nicht möglich für den externen Dienstleister, das hat aber funktioniert.

Danke für die Hilfe!