Return aus SQL Procedure

[Fuerchau]

@Andreas
Lies doch noch mal den Text:
If a RETURN statement with a specified return value is used to return from a
procedure then the SQLCODE, SQLSTATE, and message length in the SQLCA or
diagnostics area are initialized to zeros, and message text is set to blanks. An
error is not returned to the caller.

Also entweder RETURN weglassen oder den Return-Wert weglassen!
Ein RETURN -1 kann nur per GET DIAGNOSTIC ausgelesen werden da kein SQLCODE zurückgegeben wird.
Das ist aber nicht "State of the art" da man normalerweise den SQLCODE abfragt (ggf. noch den SQLSTATE).

[dabeda]

Habe es jetzt auch gänzlich ohne RETURN versucht, aber im RPG ist der sqlcode immer noch 0.
Wenn ich die Procedure debugge, dann ist der sqlcode darin -803.
Ein get diagnostics liefert ebenfalls 0 zurück.
Im Joblog steht aber:

Bestehender Datenbereichseintrag für einmaligen Schlüsselindex enthält
doppelte Schlüssel.
COMMIT-Operation schlug aufgrund der Verletzung einer Integritätsbedingung
fehl.
ODP nicht gelöscht.
ODP nicht gelöscht.
Doppelter Schlüsselwert angegeben.
Anweisung CALL beendet.
Anweisung GET DIAGNOSTICS beendet.

[mk]

Hallo,

als Lösung bieten sich auch Ausgabeparameter an.

z.B:

CREATE  PROCEDURE xyz 
(
  in     pAktion        varchar ( 50 )  ,
  in     pxxx           dec ( 5, 0 )    ,
  out    pOutValue      varchar ( 256 ) ,
  out    pStatus        varchar ( 10 )  ,
  out    pStatusCode    varchar ( 256 )
)          

...
...
...

 GET DIAGNOSTICS EXCEPTION 1 pStatusCode  = MESSAGE_TEXT ,
                                pStatus      = RETURNED_SQLSTATE ; 

Die Werte kann man dann im Aufrufer auswerten

Gruß
Michael

[B.Hauser]

Du must den Fehler natürlich mit SIGNAL ausgeben.
Ein negativer SQLCODE in einem embedded SQL Programm bewirkt schließlich auch keinen Abbruch.
Tritt der Fehler innerhalb der Prozedur auf muss er wie Andreas gezeigt hat mit SIGNAL ausgegeben werden!

Birgitta

[dabeda]

Wenn ich den SQLSTATE mit SIGNAL zurückgeben, dann hab ich das im Joblog stehen, aber eben nicht beim GET DIAGNOSTICS.
Was mach ich da noch falsch??

p1: begin


declare v_message_text varchar(128) default '';
declare v_sqlCode integer default 0;
declare v_sqlState char(5) default '00000';


declare exit handler for sqlexception
begin
get diagnostics exception 1 v_message_text = message_text,
v_sqlCode = db2_returned_sqlcode,
v_sqlState = returned_sqlstate;
rollback;
signal sqlstate v_sqlState set message_text = v_message_text;
end;


delete from table
where sonderartikel = 0;


insert into table
select felder from view


end p1;

Im RPG mach ich:

exec sql
call arupd();

exec sql
get diagnostics condition 1
:state = returned_sqlstate,
:code = db2_returned_sqlcode,
:msg = message_text;

In msg steht "Anweisung CALL beendet", state = '00000' und code = 0.

Im Joblog steht:

Doppelter Schlüsselwert angegeben.
Nachricht Doppelter Schlüsselwert angegeben. wurde von SIGNAL, RESIGNAL
oder RAISE_ERROR zurückgegeben.

PS.: Wie fügt man denn hier so ein schönes Codefensterchen ein?

[Fuerchau]

Schalte um auf "Erweitert", da gibt's das "#" für Code-einfügen:

  Das ist nun der Code

Was haben denn deine aktuelle SQLCA und SQLCOD für einen Inhalt?
Normalerweise benötigst du dafür keine Get Diagnostics.

Und was passiert, wenn du den Exit-Handler weglässt?
In deinem Beispiel benötigst du ihn nämlich nicht.

Prozeduren und Funktionen sollten keinen COMMIT/ROLLBACK enthalten da damit das Transaktionshandling der Anwendung unterbrochen wird.
Schließlich machst du ja auch keinen COMMIT im Erfolgsfall.
Beides ist Fatal, da die Anwendung sich auf verschiedenes verlassen können muss.

[dabeda]

Diese Procedure ist rein zu Testzwecken, ein COMMIT wird nicht benötigt, da sie immer einen Fehler produziert.

Ich glaube ich weiß jetzt woran es liegt.
Die Procedure wurde mit langem Namen erstellt und als specific dann ein kurzer Systemname.

create procedure sp_artikelupdate_old()
  lanuage sql
  specific arupdold

Wenn ich call arupdold() mache, bekomme ich als Rückmeldung "Anweisung CALL beendet",
mache ich das call sp_artikelupdate_old(), bekomme ich "Doppelter Schlüsselwert angegeben".
Hab die Procedure nochmal gelöscht, mit dem i Navigator und PDM nach Überbleibsel gesucht und alles neu erstellt, das Ergebnis ist dasselbe.
Dazu fällt mir nur ein.

[Fuerchau]

Ja das ist der Fluch der Automatismen.
Der Specific-Name dient zur Benennung des generierten Objektes und zu sonst nichts.
Bei langen Namen generiert SQL ansonsten den Systemnamen selber.
Im Gegensatz zu Feldern und Tabellen ist der Specific-Name nicht im SQL-Kontext verwendbar.
D.h., dadurch dass du in SQL jedes vorhandene Programm per CALL aufrufen kannst, wird deine SQL-Prozedur mittels "CALL PROGRAM" aufgerufen.
Dies ist das selbe, als wenn du das Programm per CALL aus RPG aufrufst.
SQL spielt da nicht mit.
Eine SQL-Prozedur muss mit ihrem originären Namen aufgerufen werden und zwar so wie sie in SYSPROCS unter ROUTINE_NAME eingetragen ist.
Wird sie dort nämlich nicht gefunden, wird ein Programm-Call daraus.
Nun führt dies nicht zu Fehlern, da die SQL-Prozeduren in "C" erstellt werden und daher automatisch mit 0-N Parametern aufgerufen werden (Ähnlich wie halt RPGLE).
Erst beim Zugriff auf die fehlenden Variablen würde ein MCH ausgelöst.

Wenn du eine externe Prozedur im SQL-Stil erstellst, erhältst du zusätzliche Übergabeparameter für z.B. eine SQLCODE-Rückgabe. Greifst du nicht darauf zu gibt's auch keinen Laufzeitfehler.
Machst du die Prozedur im Stil "General" sind nur die Funktionsparameter deklariert und dann merkst du keinen Unterschied zwischen SQL-Prozedur-Aufruf und SQL-Programm-Aufruf.

[dabeda]

Ja das ist der Fluch der Automatismen.
Der Specific-Name dient zur Benennung des generierten Objektes und zu sonst nichts.

Danke für die Erklärung, jetzt wirds mir klar.
Weiß nicht warum ich das gemacht hab, beim Überladen mach ich das ja auch nicht.
Ich halte ja nichts von so langen Namen, aber die werden für die PC-Welt erstellt, vielleicht hat sich da etwas in mir gesträubt.
Naja, eine Woche an der falschen Stelle gesucht, aber viel dabei gelernt.

[andreaspr@aon.at]

Ich habe es bei mir jetzt auch nochmal getestet und funktioniert so wie erwartet. (7.1)
Einmal mit "declare exit handler for sqlexception" und einmal ohne.
In beiden Fällen bekomme ich den SQL Fehler:

• In der standard SQLSTATE variable vom RPG
• Oder mit der Get Diagnostics Funktion

CREATE or REPLACE PROCEDURE PRANLIB.MYPROC2 ( 
	IN PNAME VARCHAR(10) ) 
	LANGUAGE SQL 
	SPECIFIC PRANLIB.MYPROC2 
	NOT DETERMINISTIC 
	MODIFIES SQL DATA  
	SET OPTION  COMMIT = *CHG 
	
	BEGIN 
DECLARE SQLCODE INT ; 
DECLARE SQLSTATE CHAR ( 5 ) ; 

insert into pranlib.tab1 values (1, PNAME);

END;

Exec Sql Call PRANLIB.MYPROC2('asdf');
dsply sqlstate;
Exec Sql Get Diagnostics CONDITION 1 :vsqlmsg = MESSAGE_TEXT;
dsply (%subst(vsqlmsg : 1: 20 ) );

Bei den langen SQL und kurzen SYS Namen muss man etwas aufpassen.
Da kann es passieren, dass z.B. beim CREATE OR REPLACE die alte Prozedur nicht gelöscht sondern einfach eine Neue angelegt wird.
Produktiv verwenden wir gar nicht die REPLACE Syntax sondern Löschen alle Prozeduren mit:
DROP SPECIFIC ROUTINE ...
und
DROP ROUTINE ...
... um ganz sicher zu gehen dass da nicht was vergessen wurde.

lg Andreas