View Full Version : User Defined Function in SQL, Datumsübergabe an Serviceprogramm
dschroeder
27-11-14, 09:32
Hallo,
wir schreiben häufig User Defined Functions in SQL, um damit Serviceprogramme aufzurufen. Manchmal erwarten die Serviceprogramme ein Datumsfeld als Input-Parameter. Datumsfelder sind bei uns grundsätzlich im *EUR Format definiert.
Ein Datumsfeld in SQL scheint aber nicht gleich einem Datumsfeld in RPG zu sein. Deshalb behelfen wir uns immer damit, ein zusätzliches RPG-Serviceprogramm zu erstellen, das als Adapter zwischen die SQL-Funktion und das eigentliche RPG-Serviceprogramm eingefügt wird. Das Adapterprogramm empfängt das Datum von SQL dann als Varchar(10), wandelt es in ein echtes RPG-Date um und übergibt es an das eigentliche Serviceprogramm.
Das ist natürlich sehr umständlich.
Hier mal ein Beispiel für eine SQL-Funktion, die ein Datum (i#Stand) als Varchar übernimmt.
create function EDPGMLIB/BVS9VS_ermIndexfaktor(
i#Code varchar(3),
i#_vs_vbnr decimal(15, 0),
i#_vs_nach decimal(9, 0),
i#stand varchar(10))
returns decimal(15, 7)
language RPGLE
specific EDPGMLIB/BVS9VS_ermIndexfaktor
deterministic
reads sql data
called on null input
disallow parallel
external name 'EDPGMLIB/BVS9VSF15(BVS9VS_ERMINDEXFAKTOR)'
parameter style general;
Hat vielleicht jemand eine bessere Idee?
Dieter
andreaspr@aon.at
27-11-14, 13:19
Hallo Dieter,
Wenn ihr das Datum direkt als Datums Feld übergebt konvertiert das System automatisch zwischen SQL und RPG.
Beispiel:
H DATFMT(*ISO)
D getDate PR 10A
D _Date D
PgetDate B export
D PI 10A
D _Date D
return %Char (_Date);
PgetDate E
create or replace function mylib.dateToChar(p_Date date)
returns char(10)
language rpgle
external name 'MYLIB/MYSRVPGM(GETDATE)'
parameter style general;
Aufruf:
values (mylib.dateToChar(current date) );
lg Andreas
dschroeder
27-11-14, 13:35
Du hast da eine H-Bestimmung mit DATFMT(*ISO). In unserer H-Bestimmung steht *EUR. Liegt das vielleicht daran?
So wie du es beschrieben hast, würden wir es natürlich gerne machen. Aber unsere Datumsfelder sind grundsätzlich als *EUR deklariert.
Dieter
andreaspr@aon.at
27-11-14, 14:43
Stimmt, ich habe es genau umgekehrt getestet. RPG mit *ISO und in SQL *EUR.
Da zumindest hat es richtig funktioniert.
Ab 7.2 kann beim CREATE FUNCTION auch noch das SET OPTION (mit DATFMT) mitgegeben werden, hat aber auch nichts gebracht.
Sieht mir ehrlich gesagt mehr nach einem Bug als ein Feature aus.
dschroeder
27-11-14, 15:00
Vielen Dank für deine Mühe. Ich habe den Eindruck, als ob SQL ein Datum immer im ISO-Format handelt. Ich könnte das RPG-Programm das Datum ja einfach im ISO-Format empfangen lassen. Dann ginge es wahrscheinlich von SQL aus. Aber alle anderen RPG-Programme, die das Programm dann direkt (also ohne die SQL-Funktion) nutzen wollten, müssten ihr Datum dann immer erst in ISO konvertieren (da muss man dann immer dran denken).
Ich werde das alles nochmal ausprobieren. Jetzt haben wir erstmal wieder einen Alpha-Adapter geschrieben.
Dieter
SQL interessiert das Datums-Format überhaupt nicht, sondern arbeitet immer mit der scaliger no.
Das Datums-Format wird nur dazu verwendet, das Datum lesbar zu machen.
Das Problem ist die Parameter-Übergabe von/an RPG. In RPG wird ein Datum immer in eine alphanumerische Darstellung konvertiert. SQL kann auf der anderen Seite alphanumerische Strings im Format JJJJ-MM-TT, TT.MM.JJJJ und MM/TT/JJJJ als Datum identifizieren und problemlos umsetzen. Wird aus einer SQL-Funktion ein Datum an RPG übergeben, wird dieses in eine alphanumerische Darstellung im *ISO-Format (JJJJ-MM-TT) konvertiert und übergeben.
... und an dieser Stelle kracht RPG.
Was passiert eigentlich, wenn Du die Original-RPG-Funktion, die ein echtes Datum erwartet registrierst, den Datum-Parameter jedoch als CHAR(10) definierst?
Dann überlädst Du die SQL-Funktion und definierst den Datums-Parameter als echtes Datum. In dieser Funktion, konvertierst Du das Datum in eine alphanumerische Darstellung im *Europäischen Format (CHAR(Datum, EUR) und rufst die Origianl-Funktion mit diesem Parameter auf.
Zu Deiner Idee mit dem ISO-Format. Habt Ihr bei den Datums-Feldern im Prototypen ein Datums-Format hinterlegt?
Wenn nein solltet Ihr das tun (DATFMT(*ISO)). Die vorhandenen RPG-Programme sollten dann die Funktion ohne Umstellung des Formats aufrufen können. Die Runtime konvertiert das Datum in das erwartete Format.
Birgitta
Birgitta
Das Hauptproblem ist doch eigentlich, dass man SQL-Prozeduren/Funktionen mit "parameter style general" erstellt. "parameter style SQL" wäre der bessere Weg, da man hier auch vernünftig mit NULL-Values und Fehlercodes arbeiten kann. In diesem fall kann man dann auch mit ISO-Datum arbeiten.
Zusätzlich verhindert man damit, dass SQL-Funktionen so einfach "native" aufgerufen werden, da man keine Lust hat alle SQL-Felder immer mit zu definieren!
Eine SQL-Funktion bleibt eine SQL-Funktion, eine RPGLE-Funktion bleibt eine RPGLE-Funktion.
Dieses halte ich für wichtig. Sollten diese SQL-Funktionen native benötigt werden, kann ich diese ja auch mit "exec SQL set : MyDate = MyFunction(...);" verwenden. Somit ist die einheitliche Verwendung gewährleistet und man zerbricht sich nicht den Kopf für unnötige Lösungsansätze..
dschroeder
28-11-14, 08:33
Danke, Birgitta.
Ich werde das mit SQL char und RPG Date mal probieren.
Wir haben bei allen Datumsfeldern immer *EUR angegeben. Das lässt sich bei mehreren tausend Programmen jetzt nicht mehr ändern. Für uns war bis jetzt immer wichtig, dass alle Programme dasselbe Datumsformat verwenden. Und da haben wir uns von vielen Jahren für *EUR entschieden.
Dieter
dschroeder
28-11-14, 08:35
Das Hauptproblem ist doch eigentlich, dass man SQL-Prozeduren/Funktionen mit "parameter style general" erstellt. "parameter style SQL" wäre der bessere Weg, da man hier auch vernünftig mit NULL-Values und Fehlercodes arbeiten kann. In diesem fall kann man dann auch mit ISO-Datum arbeiten.
Zusätzlich verhindert man damit, dass SQL-Funktionen so einfach "native" aufgerufen werden, da man keine Lust hat alle SQL-Felder immer mit zu definieren!
Eine SQL-Funktion bleibt eine SQL-Funktion, eine RPGLE-Funktion bleibt eine RPGLE-Funktion.
Dieses halte ich für wichtig. Sollten diese SQL-Funktionen native benötigt werden, kann ich diese ja auch mit "exec SQL set : MyDate = MyFunction(...);" verwenden. Somit ist die einheitliche Verwendung gewährleistet und man zerbricht sich nicht den Kopf für unnötige Lösungsansätze..
Danke Baldur.
Leider verstehe ich deine Ausführungen nicht. Was meinst du mit verhindern von native Aufrufen? Wir möchten eine SQL-Funktion von 3 verschiedenen Umgebungen aus aufrufen können:
1. Aus Java (vom PC aus)
2. Aus embedded SQL im RPG
3. Aus einer interaktiven SQL Oberfläche (dbVisualizer, STRSQL, Navigator, ...)
Dieter
... einfach eine 2. procedure mit auf iso angepasster Schnittstelle aus demselben modul exportieren, die den Aufruf an die bereits implementierte durchreicht und fertig ist. Da RPG (leider) nicht überladen kann, muss die halt einen anderen Namen haben (suffix iso).
Ansonsten gehören die Aufrufe der function ohnehin im vie layer gekapselt, sodass ein cast von date nach alfa im Aufruf auch kein drama wäre.
D*B