User Defined Function in SQL, Datumsübergabe an Serviceprogramm

[dschroeder]

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]

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]

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]

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]

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

[B.Hauser]

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

[Fuerchau]

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]

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

[dschroeder]

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]

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

Ich habe das eben nochmal durchprobiert:
1. Wenn RPG das Datumsfeld als date(*iso) empfängt und im SQL das Feld als date deklariert ist, klappt es.
2. Wenn RPG das Datum als date(*eur) empfängt und im SQL das Feld als char(10) (oder auch varchar(10) ) deklariert ist, klappt es nicht. Dann kommt der Fehler, dass die SQL-Funktion das Serviceprogramm gar nicht finden kann.

Dieter

[Fuerchau]

Für SQL muss das Feld als DATE definiert sein, da eben an die Prozedur ein DATE übergeben wird!
Genauso muss auch ein Returnwert als DATE definiert sein.
Nun arbeitet halt eine SQL-Function/Prozedur grundsätzlich mit *ISO.

Unabhängig von der H-Bestimmung *EUR lässt sich das Format eines D-Feldes individuell festlegen.
Die Parameter der SQL RPGLE-Prozedur können also mit DATFMT(*ISO) arbeiten. Bei der Übertagung zwischen D-Feldern erfolgt eine Anpassung.

Der Aufruf via SQL ist für alle Sprachen gleich, egal ob STRSQl, embedded SQL, ODBC/JDBC (Java, OpsNav, u.v.m).

Was ich halt meinte, dass man die Aufrufart einer RPG-Prozedur nicht mischen sollte.
Durch "parameter style General" kann ich die Prozedur via SQL oder eben auch native via CALLP verwenden was eben zu verhindern gilt.

Schreibe also eine RPGLE-Prozedur mit *ISO als Datumsformat. Alle RPGLE-Programme mit *EUR als Datumsformat übergeben an SQL ein Datum immer in *ISO!
Schau dir mal nämlich den Spool an. Die SQLnnnn-Variable ist mit DATFMT(*ISO) definiert und bei der Zuweisung von/nach konvertiert die Runtime zwischen *ISO und *EUR.
Deshalb gibt es ja häufig Laufzeitfehler bei falschen Datenformat von der RPG-Runtime und nicht von SQL wenn die Formate nicht konvertierbar sind.

[dschroeder]

Danke für deine Ausführungen. Bisher verfolgen wir das Ziel, ein Serviceprogramm von beiden Umgebungen (SQL und RPG) aufrufen zu können. Wenn wir nicht gerade diese Datumsproblematik haben, klappt das ja auch.

Ich danke nochmals allen für Ihre Antworten.
Dieter.