RPG Free mit SQL / Joins / mit QSYS2.USER_INFO

[dcdeal]

Hallo, mir wurde dieses Forum wärmstens ans Herz gelegt wenn es um Fragen rund um die as 400 geht. Ich bin noch relativ neu in der AS 400 Welt, SQL's programmiere ich aber schon "ewig".

Leider geht ein SQL in meinem Free RPG Programm nicht, Open funktioniert super, jedoch kein fetch. Fehlermeldungen findet Ihr in den Kommentaren direkt hinter dem SQL.

Ich habe den SQL einmal mit speziellen Feldern, und einmal mit * abgeschickt, beides mal unterschiedliche Fehlermeldungen.

Es geht hier hauptsächlich um die beiden Fehlermeldungen
a) Syntax von Datums-, Zeit- oder Zeitmarkenwert ungültig. SQLSTATE: 22007
b) Zeichenumsetzung zwischen CCSID 1208 und CCSID 65535. SQLSTATE: 57017

Wenn ihr den Code seht wisst ihr ev. schon was da schief geht. Es muss irgend eine Konvertierung im Fetch schiefgehen, da bin ich mir sicher, nur wie kann ich das beheben!?

Des weiteren Fehlt mir noch eine bessere Methode, Programmausgaben zu realisieren, beim dsply Befehl gibts nur 52 Zeichen und mann muss immer auf enter drücken. Aber das ist nicht mein Kernproblem.

Hinweis: im ACS läuft der SQL einwandfrei.

danke!

**free
  ctl-opt option(*nodebugio)  alwnull(*usrctl);


dcl-ds sysUserTable extname('QSYS2/USER_INFO') qualified;
END-DS;


dcl-s InsertErrSQLtext char(999);
dcl-s insertState char(6);
dcl-s testStr char(52);
dcl-s statSQL int(10);


//  exec sql DECLARE CUILoop CURSOR FOR
//        SELECT A.USER_NAME,
//         A.INITPGM,A.INITPGMLIB
//        FROM QSYS2.USER_INFO A
//        left join USRSTPR1 B on (A.USER_NAME = B.AUTHNAM)
//        where B.AUTHNAM is null Order by A.USER_NAME asc;
//  exec sql OPEN CUILoop;
  //Das ergebnis der Bildschirmausgabe:
//  DSPLY  Cursor CUILOOP eröffnet.
//  DSPLY
//  DSPLY  SQLSTATE: 00000
//  DSPLY  Syntax von Datums-, Zeit- oder Zeitmarkenwert ungült
//  DSPLY  ig.
//  DSPLY  SQLSTATE: 22007




  exec sql DECLARE CUILoop CURSOR FOR
        SELECT *
        FROM QSYS2.USER_INFO A
        left join USRSTPR1 B on (A.USER_NAME = B.AUTHNAM)
        where B.AUTHNAM is null Order by A.USER_NAME asc;
  exec sql OPEN CUILoop;
  //  Das ergebnis der Bildschirmausgabe:
//  DSPLY  Cursor CUILOOP eröffnet.
//  DSPLY
//  DSPLY  SQLSTATE: 00000
//  DSPLY  Zeichenumsetzung zwischen CCSID 1208 und CCSID 65535
//  DSPLY   ungültig.
//  DSPLY  SQLSTATE: 57017




  // SQL DEBUG START
  Exec SQL GET DIAGNOSTICS Condition 1 :InsertErrSQLtext = MESSAGE_TEXT;
  Exec SQL Get Diagnostics Condition 1 :insertState = RETURNED_SQLSTATE;
  statSQL = SQLCOD;
  testStr = %SUBST(InsertErrSQLtext:1:52);
  dsply testStr;
  testStr = %SUBST(InsertErrSQLtext:53:105);
  dsply testStr;
  testStr = %trim('SQLSTATE: '+insertState);
  dsply testStr;
  // SQL DEBUG END


  exec sql FETCH FROM CUILoop INTO :sysUserTable ;


  // SQL DEBUG START
  Exec SQL GET DIAGNOSTICS Condition 1 :InsertErrSQLtext = MESSAGE_TEXT;
  Exec SQL Get Diagnostics Condition 1 :insertState = RETURNED_SQLSTATE;
  statSQL = SQLCOD;
  testStr = %SUBST(InsertErrSQLtext:1:52);
  dsply testStr;
  testStr = %SUBST(InsertErrSQLtext:53:105);
  dsply testStr;
  testStr = %trim('SQLSTATE: '+insertState);
  dsply testStr;
  // SQL DEBUG END


  exec sql close CUILoop;


*INLR = *ON;

[Fuerchau]

Da der Code unvollständig ist, kann ich mit dem Fehler bzgl. Datumformat nichts anfangen, da eine Variable dieses Types nicht vorkommt.
Was die CCSID angeht, so läuft dein Job in CCSID 65535, die Daten haben UTF8 = CCSID 1208.
Dazwischen ist eine Umsetzung nicht definert.
Das liegt aber an der üblichen Systemeinstellung, dass alle Jobs im HEX-Modus laufen, was aber in SQL nicht geht.

Mache mal einen "CHGJOB CCSID(273)" vor Aufruf des Programmes.

[dcdeal]

Hallo und vielen Dank für die schnelle Hilfe.

Da der Code unvollständig ist, kann ich mit dem Fehler bzgl. Datumformat nichts anfangen, da eine Variable dieses Types nicht vorkommt.

-> leider ist die Fehlermeldung von IBM ist etwas irreführend, da die einzige Variable die ich hier habe eine Datenstruktur ist, in der ich die Sturkur meiner abzufragenden Tabelle vorgebe (s. Definition DCL-DS nach den Options ).
Demnach befindet sich die Variable in der QSYS2/USERINFO Datenstruktur (vermute ich mal, so lange bin ich noch nicht dabei im Thema AS/400)
Eins ist auf jeden Fall klar. Wenn ich auf dieselbe Art und weise ein Physical File abfrage geht alles wie am Schnürchen, z.b. ein einfaches select etc.
Hinweis: Weiter unten im code wird mit der Datensruktur leider nichts gemacht, es ist also schon irgendwie ein dummy programm, d.h. es gibt keine Datenverarbeitung mehr nach dem Fetch. Das soll natürlich noch dazu kommen, aber dafür muss mit der fetch erst mal konkret etwas in meine DS liefern.

Ich hoffe ich konnte das ordentlich klar stellen, was ich meine .

"CHGJOB CCSID(273)"

Interessanter Ansatz mit dem Befehl vor dem Programm. Was genau passiert denn da? Da ich noch relativ frisch im Thema AS 400 Bin kann ich mir noch keinen reim machen, was da genau in dem Befehl abgefahren wird. Nun bekomme ich nochmal eine andere Fehlermeldung bei meinen dsplys weiter unten:

Zeichen in CAST-Argument ungültig.
SQLSTATE: 22018

[Fuerchau]

Nun ja, wenn du so ahnungslos bist wie du behauptest, werden wir hier im Forum nicht weit kommen.
Es fehlen dir ja alle Grundinformationen rund um die AS/400 um überhaupt ein Programm zu schreiben.
Ich denke auch nicht, dass man im Selbststudium da wirklich weiterkommt.

Falls doch, dann findest du hier Literatur dazu:
https://www.ibm.com/support/knowledg...e/pdftable.htm

Da gibt es auch ILERPG- und SQL-Handbücher (Reference, Programmersguide) incl. Beispiele.

Zu deinem Programm nur soviel:
Der Fetch muss genau so viele Variablen als Ziel angeben, wie im Select definiert. Nur eine Struktur angeben passt da selten, da die Reihenfolge des Selects wohl kaum zur Struktur der DS passt.

Der Befehl CHGJOB CCSID(nnn) ändert die Codeumgebung, so dass Codewandlungen zwischen Programm und Datenbank funktioniert.
Dazu gibt es ein eigenes umfangreiches Handbuch "Globalisierung".

Das Forum sprengt deine Anfrage auf jeden Fall.

[dcdeal]

Hi Fuerchau , danke dir erstmal für die Ausführungen. Ja AS 400 ist schon gar nicht mal so einfach, zumal man viel wissen haben muss und auf viele Sachen Achten muss.
Ich habe vorher mit Delphi gearbeitet, Jahrelang, und bin recht fit im Thema programmieren. Leider läuft auf der AS 400 alles etwas anders

Ich habe die Datenstruktur direkt aus meiner Abzufragenden Tabelle "kopiert" mit dem Schlüsselwort qualified.
Daher stimmt die Datenstruktur exakt mit der Datenstruktur der QSYS2.USER_INFO überein.

Ich konkretisiere mal meine Frage:

Was sind die Anforderungen an eine Datenstruktur, die mit einem Select SQL harmonieren soll, der nur einige Felder abfragt.
Bedeutet das ich muss die Deklarationen exakt redundant mit meinem Physical File bzw. Tabelle gestalten, mit der exakt selben Reihenfolge? Dann könnte ich ev. mit meinem oberen select weiterkommen.
Hinweis dazu: Der "qualified" Trick funktionierte bisher bei allen abgefragten Tabellen einwandfrei, jedoch nciht mehr bei der qsys2.user_info

Hast und da Ev. ein passendes Beispiel?

Danke für die Mühe!

[dcdeal]

ich haben nun schon einen test gemacht. Wenn ich die genauen Feldtypen, Bezeichner etc. übernehme geht es!
SUPERCOOL. danke, aber für ein paar gute beispiele dazu wäre ich dennoch dankbar. Ich such immer das halbe Internet durch.

Offensichtlich steht qualified doch für was anderes... ?

[Fuerchau]

Ja.
Ohne qualified steht jede Variable einer DS einzeln zur Verfügung, mit qualified muss die Variable der DS mit ihrem Strukturnamen also "myds.myvar" angesprochen werden.

Steht aber alles in den o.a. Handbüchern;-).

[dcdeal]

Hallo, hiermit poste ich nun noch mein Lösung, der code funktioniert nun so.

Im weiteren hatte ich noch das Problem mit der Fehlermeldung "Anzeigervariable erforderlich SQLSTATE 22002"

Man beachte die Lösung für diese Fehlermeldung,
die Variable "myind", die die NULL STATI Für die Felder zurückgibt
(Die sonst als ersten Datensatz angefügt werden - wie clever gelöst, IBM! [ha-ha])

Ich hoffe das der Code jedem Hilft, der später nach der Lösung für solcherlei Probleme Googelt.

Ein speziellen Dank nochmal an Fuerchau, der so nett war und mir geholfen hat .

  
**free
ctl-opt option(*nodebugio:*srcstmt:*nounref)  alwnull(*inputonly)  main(main);

dcl-proc main;
  dcl-ds sysUserTable QUALIFIED;
    USER_NAME char(10);
    INITPGM char(10);
    INITPGMLIB char(10);
  END-DS;
  dcl-s myind int(5) dim(256);

  dcl-s InsertErrSQLtext char(999);
  dcl-s insertState char(6);
  dcl-s testStr char(52);
  dcl-s statSQL int(10);
  dcl-s CNT int(10);


  exec sql DECLARE CUILoop SCROLL CURSOR FOR
        SELECT
        USER_NAME,
        INITPGM ,
        INITPGMLIB
        FROM QSYS2.USER_INFO A
      ;
  exec sql OPEN CUILoop;

  exec sql FETCH FIRST FROM CUILoop INTO :sysUserTable  :myind;

  // SQL DEBUG START
  // Falls hier kommt: Anzeigervariable erforderlich 22002 .. dann brauche ich hinter dem sql
  // noch eine weitere Variable, so
  // dcl-s myind int(5) dim(256);
  // exec sql FETCH FROM CUILoop INTO :sysUserTable :myind;
  Exec SQL GET DIAGNOSTICS Condition 1 :InsertErrSQLtext = MESSAGE_TEXT;
  Exec SQL Get Diagnostics Condition 1 :insertState = RETURNED_SQLSTATE;
  Monitor;
    statSQL = %dec(SQLSTATE:7:2);   // Vorsicht! es können codes mit buchstaben vorkommen.
  On-Error  *all ;
  Endmon;
  testStr = %SUBST(InsertErrSQLtext:1:52);
  dsply testStr;
  testStr = %SUBST(InsertErrSQLtext:53:105);
  dsply testStr;
  testStr = %trim('SQLSTATE: '+insertState);
  dsply testStr;
  // SQL DEBUG END

  dow (statSQL < 2000) or (CNT > 3);
    dsply %trim(sysUserTable.USER_NAME+'-'+sysUserTable.INITPGM+'/'+sysUserTable.INITPGMLIB);
    exec sql FETCH NEXT FROM CUILoop INTO :sysUserTable  :myind;
    Monitor;
      statSQL = %dec(SQLSTATE:7:2);   // Vorsicht! es können codes mit buchstaben vorkommen.
    On-Error  *all ;
    Endmon;
    dsply %trim('SQLSTATE '+SQLSTATE);
    CNT += 1;
  enddo;
  exec sql close CUILoop;
end-proc;

Hinweis zur Lösung:
In der Lösung hier benutze ich keinen Join mehr, es ist eine einfache User_Name Abfrage, allerdings funktioniert auch die join Abfrage aus der ursprünglichen anfrage s.o.)

[Fuerchau]

Noch ein paar Hinweise:

Diagnostics ist nicht nötig, da der Compiler eine SQLCA-Struktur generiert, die alle Informationen nach einem SQL enthält.
Hierzu gibt es i.W. die Variable

SQLCODE

Der Inhalt ist grob folgender:
0 = alles OK
100 = Keine Daten mehr beim Fetch
< 0 = meist schwere Fehler die ein Weiterarbeiten unnötig machen

Auch ein ggf. vorhandener Fehlertext steht dann in der SQLCA.
https://www.ibm.com/support/knowledg...ddescsqlca.htm

Dein Code wäre also übersichtlicher:

exec sql open ....;
dow SQLCODE = *zero;
exec sql fetch ....
if SQLCODE <> *zero;
leave;
endif;
// to was
enddo;
exec sql close ...;

[B.Hauser]

SQLCODE

Der Inhalt ist grob folgender:
0 = alles OK
100 = Keine Daten mehr beim Fetch
< 0 = meist schwere Fehler die ein Weiterarbeiten unnötig machen

Auch ein ggf. vorhandener Fehlertext steht dann in der SQLCA.
https://www.ibm.com/support/knowledg...ddescsqlca.htm

Dein Code wäre also übersichtlicher:

exec sql open ....;
dow SQLCODE = *zero;
exec sql fetch ....
if SQLCODE <> *zero;
leave;
endif;
// to was
enddo;
exec sql close ...;

Das musst Du mir mal zeigen wo der Fehlertext in der SQLCA stehen soll.
Ich habe bislang noch keinen gefunden. In SQLERM stehen zwar die Variablen Message-Texte aber ohne die eigentliche Message, die man sich mühsam aus der Mesage-File QSQLMSG und dem SQL Code ermitteln muss bringt das nichts.
GET DIAGNOSTICS bringt den kompletten Message-Text incl. der eingesetzten variablen Message-Texte.
Mit Deiner Lösung hebelst Du das komplette Fehlerhandling!

Außerdem sollte man niemals im DOW oder DOU den SQLCode abfragen und schon gar nicht auf 0.
Weitere SQL-Statements könnten innerhalb der Verarbeitungsschleife ausgeführt werden, die ihrerseits den SQLCODE (und SQLSTATUS) setzten. Eine dieser SQL Abfragen könnte z.B. den SQLCODE 100 zurückbringen und dann schaut man bedeppert, weil die Verarbeitung mittendrin beendet ist.

Auch sollte man nie den SQLCODE nie direkt auf 0 abfragen sondern immer auf 100 oder < 0. Es gibt situationen in denen eine Warnung ausgegeben wird, die aber für die Verarbeitung als solche uninteressant ist. Bei einer Warnung wird ein positiver SQLCODE (<> 100) ausgegeben. Eine solche Warnung würde dann auch wieder bewirken, dass nur die Hälfte abgearbeitet wird.

Sofern man lieber den SQLSTATE prüft, sollte man die ersten beiden Ziffern (Status Gruppe) vorrangig prüfen:
00 = Alles Okay
01 = Warnung
02 = nicht gefunden
Alles andere ist Fehler

Man kann sich natürlich eine generische Fehlerbehandlung bauen, die nach jedem SQL-Statement aufgerufen wird. Dadurch spart man sich eine Menge Kopiererei und ggf. auch eine Menge Wartungsaufwand.

Birgitta

[Fuerchau]

Man kann alles auch übertreiben.
In 99,99% aller Fälle reicht die Fehlerbehandlung mit SQLCODE vollkommen aus.
Schließlich testet man ja auch seine Programme bevor sie in den Echteinsatz gehen.
Und den Fehlertext benötige ich i.d.R. zur Laufzeit überhaupt nicht.

Mit meinem Verfahren arbeite ich seit über 20 Jahren absolut problemlos;-).

Warnungen sind i.d.R. auch nicht einfach zu ignorieren. In der Doku steht auch nur irgendwas von > 0 und <> 100. Da ist es sicherer dann lieber nicht weiter zu arbeiten als sich in die sehr detailliertere SQLSTATE-Auswertung zu stürzen die man ja vorher programmieren muss.

Fehler wie fehlenden Null-Anzeiger treten ja nur zur Entwicklungszeit auf.
Wer die Datenbankstruktur nach der Fertigstellung so anpasst, dass Programme nicht mehr laufen, gehört sowieso verhaftet.

[BenderD]

Das musst Du mir mal zeigen wo der Fehlertext in der SQLCA stehen soll.
Ich habe bislang noch keinen gefunden. In SQLERM stehen zwar die Variablen Message-Texte aber ohne die eigentliche Message, die man sich mühsam aus der Mesage-File QSQLMSG und dem SQL Code ermitteln muss bringt das nichts.
GET DIAGNOSTICS bringt den kompletten Message-Text incl. der eingesetzten variablen Message-Texte.
Mit Deiner Lösung hebelst Du das komplette Fehlerhandling!

Außerdem sollte man niemals im DOW oder DOU den SQLCode abfragen und schon gar nicht auf 0.
Weitere SQL-Statements könnten innerhalb der Verarbeitungsschleife ausgeführt werden, die ihrerseits den SQLCODE (und SQLSTATUS) setzten. Eine dieser SQL Abfragen könnte z.B. den SQLCODE 100 zurückbringen und dann schaut man bedeppert, weil die Verarbeitung mittendrin beendet ist.

Auch sollte man nie den SQLCODE nie direkt auf 0 abfragen sondern immer auf 100 oder < 0. Es gibt situationen in denen eine Warnung ausgegeben wird, die aber für die Verarbeitung als solche uninteressant ist. Bei einer Warnung wird ein positiver SQLCODE (<> 100) ausgegeben. Eine solche Warnung würde dann auch wieder bewirken, dass nur die Hälfte abgearbeitet wird.

Sofern man lieber den SQLSTATE prüft, sollte man die ersten beiden Ziffern (Status Gruppe) vorrangig prüfen:
00 = Alles Okay
01 = Warnung
02 = nicht gefunden
Alles andere ist Fehler

Man kann sich natürlich eine generische Fehlerbehandlung bauen, die nach jedem SQL-Statement aufgerufen wird. Dadurch spart man sich eine Menge Kopiererei und ggf. auch eine Menge Wartungsaufwand.

Birgitta

... was soll denn diese Klugscheißerei? Foren leben davon, dass nette Menschen wie Baldur Arbeitshinweise geben, mit denen Fragende weiter arbeiten können. Produktionsreifer Code erfordert selbstredend ein wenig mehr Aufwand. Wenn Du Baldur schlicht nicht leiden kannst, setz ihn auf die Ignore Liste oder schick ihm eine grobe PM oder beiß in die Schreibtischkante, je nach Präferenz.

D*B