[NEWSboard IBMi Forum]

Hybrid View

  1. #1
    Registriert seit
    May 2002
    Beiträge
    1.121

    SQL UDF mit Tabel

    Liebe Gemeinde!
    Zurzeit beschäftige ich mich UDFs. Funktionen, welche mir einen Wert zurück geben, sind nicht das Problem.
    Jetzt taste ich mich aber an UDFs mit einer Table als Rückgabe ran.

    Wenn ich die Funktion in SQL schreibe, dann klappt das auch ganz gut.
    Nun habe ich aber eine bauen wollen, welche ein RPG-Programm aufruft, um zu einer übergebenen Nummer verschiedene Werte zurückzugeben.
    Funktion und Programm wurden ohne Fehler gewandelt. Wenn ich die Funktion aufrufe, und das Service-PGM debugge, sehe ich auch, dass die Werte vor dem RETURN im Programm gefüllt sind.
    Nur die Funktion zeigt mir bei dem Select nichts an.
    Jemand einen Tipp für mich, wo ich was falsch habe? Oder wie ich die Sache anders angehen könnte?
    Hatte das Ganze auch schon als Procedure geschrieben. Da hat es funktioniert. Nur möchte ich aber es aber lieber als Funktion haben, damit ich es in einem Select verwenden.
    zum Beispiel so:
    PHP-Code:
    Select NummerWert1Wert2Wert5
    from MyFile
    tablemyfunc(Nummer)) 
    Function:
    PHP-Code:
    create or replace function MyLib.MyFunc inNummer char(8) )
            
    Returns table
                
    (
                    
    Wert1   char(10),
                    
    Wert2   char(10),
                    
    Wert3   char(10),
                    
    Wert4   char(10),
                    
    Wert5   char(10)              
                )
         
    language RPGLE
         NO SQL
         
    FINAL CALL
         DISALLOW PARALLEL
         NOT FENCED
        external name 
    'MYLIB/MYRPGFUNC1(MYRPGFUNC1)'
        
    parameter style sql

    RPG
    PHP-Code:
    **free
    ctl
    -opt  nomain

    dcl-c TRUE *on;  
    dcl-c FALSE *off;

    dcl-proc  MYRPGFUNC1 export;       
      
    dcl-pi *n          ;            
        
    inNummer char(8)      ;
        
    outWert1 char(10)      ;      
        
    outWert2 char(10)      ;      
        
    outWert3 char(10)      ;      
        
    outWert4 char(10)      ;      
        
    outWert5 char(10)      ;      
        
    n_Nummer     int(5)    
        
    n_Wert1         int(5)      ;   
        
    n_Wert2         int(5)      ;    
        
    n_Wert3         int(5);          
        
    n_Wert4         int(5);          
        
    n_Wert5         int(5);          
        
    Status char(5);                
        Function 
    varchar(517)      ;  
        
    Specific varchar(128)      ;  
        
    errorMsg varchar(1000);       
        
    CallType int(10)      ;       
      
    end-pi;
      
    monitor;
        
    //...Hier jetzt noch diverser Code, welcher Wert1-Wert5 befüllt
      
    on-error;
      
    endmon;
      
    Status '02000';
      return;
    end-proc
    Das Ganze wird gewandelt mit:
    PHP-Code:
    CRTRPGMOD MODULE(MYLIB/MYRPGFUNC1SRCFILE(MYLIB/QRPGLESRCDBGVIEW(*ALL)

    CRTSRVPGM SRVPGM(MYLIB/MYRPGFUNC1EXPORT(*ALL
    Jemand einen Tipp für mich?

    Gruß
    Ronald

  2. #2
    Registriert seit
    Feb 2001
    Beiträge
    20.206
    Schau mal nach:
    exec sql set result sets with return array host-struct-array for variable rows;

    Host-Struct-Array ist dein Array (DIM) einer DS, die deiner Tabelle entspricht, Variable erklärt sich von selber.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  3. #3
    Registriert seit
    May 2002
    Beiträge
    1.121
    Hmm....

    stehe jetzt auf den Schlauch.

    Ich brauche das Ganze im STRSQl oder in QM-Abfragen.

    Die Rückgabe der Funktion sind auch mehr wie 5 Werte, und die Werte haben auch unterschiedliche Feldlängen. Habe ich eventuell hier im Beispiel ungllücklich gewähl, dass die Werte alle die gleiche Länge haben.

    "Dr.Google" hat mir bei der Suche nach "host-struct-array" icht wirklich weiter geholfen...

    Gruß
    Ronald

  4. #4
    Registriert seit
    Feb 2001
    Beiträge
    20.206
    Du definierst ganz normal eine Struktur (DS) mit den benötigten Feldern, allerdings als Tabelle:

    dcl-ds MyDs dim(100);
    F1 ...
    F2 ...
    end-ds;
    dcl-s CountMyDs int(5);

    Dann füllst du diese Tabelle mit den dir bekannten RPG-Methoden;-).
    Zum Schluss gibst du das Ganze dann als Tabelle (Resultset) an SQL:

    exec sql set result sets with return Array :
    MyDs
    for
    :
    CountMyDs
    rows;

    Somit hast du auch die Möglichkeit, eine variable Anzahl Zeilen zurückzugeben.

    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  5. #5
    Registriert seit
    Feb 2001
    Beiträge
    20.206
    Bitte entschuldige die unglückliche Darstellung. Ich kann den Beitrag von hier aus nicht editieren.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  6. #6
    Registriert seit
    May 2002
    Beiträge
    1.121
    Hmm...

    Problem bleibt.

    Habe jetzt das RPG noch erweiter/geändert um
    PHP-Code:
    dcl-ds ds_return  qualified dim(10);    
      
    Wert1 char(10);                       
      
    Wert2 char(10);                       
      
    Wert3 char(10);                       
      
    Wert4 char(10);                       
      
    Wert5 char(10);                       

    end-ds;                                 
    dcl-s Anzahl_Saetze int(5inz(1); 
    PHP-Code:
      endmon;
      
    Status '02000';
      
    ds_return(1).Wert1 'BlaBla';
      
    ds_return(1).Wert2 'b hsh';
      
    ds_return(1).Wert3 '1111';
      
    ds_return(1).Wert4 'gfs41';
      
    ds_return(1).Wert5 'kdjdj';

      
    exec sql set result sets with return array :ds_return            
                                           
    for :Anzahl_Saetze rows;  
      return; 
    Gruß
    Ronald

  7. #7
    Registriert seit
    Feb 2001
    Beiträge
    20.206

  8. #8
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Exeterne User Defined Table Functions sind ein bisschen tricky, da diese über ein Callback Processing aufgerufen werden.
    Schau Dir mal den folgenden Artikel an. Da ist beschrieben, wie externe User Defined Table Functions codiert weren müssen.
    The Power of User Defined Table Functions

    Birgitta
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  9. #9
    Registriert seit
    May 2002
    Beiträge
    1.121
    Den Artikel hatte ich auch schon mit am Wickel. Hatte ja mein Beispiel an Hand dem Artikel gebastelt. Nur dass mein RPG keine Datei ließt, sondern nur selber noch ein paar Programme aufruft, um meine Werte zu setzen..
    Habe immer noch den Stand. Parameter sind vor dem Return gefüllt, und das Select zeigt kein Ergebnis....

    Habe jetzt sogar mal die Feld-definiton von char auf varchar geändert... Hat nix gebracht
    Werde jetzt wohl für jeden Wert den ich brauch, eine eigene Function schreiben. Auch wenn ich dass vermeiden wollte.

    Gruß
    Ronald

  10. #10
    Registriert seit
    Feb 2001
    Beiträge
    20.206
    Birgitta hat da Recht, da die TABLE-Function nicht auf "set result sets" reagiert.
    Letzteres geht nur in Verbindung mit einem "CALL MYPROC (P1, …, Pn)" und einem Associate Result set:
    https://www.ibm.com/support/knowledg.../t0009168.html

    Per ODBC/JDBC geht das dann erheblich einfacher.

    Eine Table-Function muss, wie in Birgittas Beispiel, implementiert werden.
    Wichtig ist natürlich, dass die 3 Aufrufvarianten bedient werden (Open, Fetch, Close) und beachtet werden muss, dass alle Daten einer Table-Function abgerufen werden!
    Eine Where/Join-Klausel wird erst nach dem Ende der Bereitstellung des Resultsets ausgewertet.

    Hier ist also genau zu überlegen, ob eine solche Funktion Sinn macht, wenn das gesamte Ergebnis u.U. nicht gebraucht wird. Die Parameter sollten dann entsprechend gewählt werden, was allerdings die Verwendung als Join unmöglich macht. Als Lateral Table sollte es aber wiederum funktionieren.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  11. #11
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Eine (externe) UDTF wird mindestens 3x Aufgerufen:
    - OPEN Call
    - FETCH Call
    - CLOSE Call

    Diese 3 Schritte muss Du Programmieren.
    Wobei der FETCH call solange aufgeruen wird bis Du den Status 02000 setzt.
    Bei jedem FETCH call gibst Du einen Satz zurück. Dabei spielt es keine Rolle, ob Du den "Satz" aus einer Datenstruktur (die Du beim OPEN gefüllt hast) oder einer Tabelle/View, die du (satzweise) verarbeitest oder aus einem User Space (der z.B. durch ein LIST API) gefüllt wurde, liest.
    Habe ich nicht Beispiele zum Verarbeiten einer intern beschriebenen Tabelle bzw. eines List APIs in dem Artikel.

    ... und Du gibst KEIN Select zurück (das funktioniert nur bei SQL Funktionen!), sondern programmierst eine ganz normale Schleife aus der beim FETCH die Ausgabe-Felder gefüllt werden.

    Birgitta
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

Similar Threads

  1. SQL - Create Tabel - Objektberechtigung *PUBLIC auf *CHANGE
    By loisl in forum NEWSboard Programmierung
    Antworten: 6
    Letzter Beitrag: 23-05-16, 16:23
  2. create tabel ohne level check
    By DEVJO in forum NEWSboard Programmierung
    Antworten: 12
    Letzter Beitrag: 29-09-15, 15:07

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • You may not post attachments
  • You may not edit your posts
  •