SYSTOOLS.JSON2BSON

[rischer]

Hallo!

Da die IBM i nun endlich auch JSON über die DB2 verarbeiten kann (a la XML) und da wir JSON immer häufger verwenden, wollte ich das Thema angehen, da unsere iSeries nun V7.2 und den erforderlichen PTF-Stand hat.

Was mir um die Burg nicht gelingen will, ist das JSON-Format abzuspeichern (Vorlage sind Beispiele von der IBM-Seite):

Ich habe eine Tabelle erstellt:

CREATE TABLE LKWSQL.WS_JSON (
ID DECIMAL(9, 0) GENERATED ALWAYS AS IDENTITY (
START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER
CACHE 20 )
,
JSON_INFO BLOB(3M) NOT NULL ,
CONSTRAINT LKWSQL.Q_LKWSQL_WS_JSON_ID PRIMARY KEY( ID )) ;

Danach versuche ich, ein gültiges JSON-Format zu speichern:

INSERT INTO LKWSQL.WS_JSON (JSON_INFO)
VALUES ( SYSTOOLS.JSON2BSON('{"detail":"hilfe"}')) ;

Versuche ich es in unserer Standard-Jobumgebung bekomme ich einen Umsetzungs-Fehler, was ich gut verstehe, weil unsere Jobs standardmäßig 1141 gesetzt haben.
Setze ich die JOB-CCSID auf 65535, so sieht die Fehlermeldung so aus:

Fehler von Auslöserprogramm oder externer Routine erkannt.
"Der zugeordnete Text ist JSON parsing error code: 4 for: C........z........."
...Fehler in Auslöserprogramm Tabelle QSQJSON in Schema QSYS".

Die Prozedur JSON2BSON gibt es 2 x im Schema QSYSTOOLS, eine die JSON im Format UTF-8 empfängt und eine die JSON mit CCSID 65535 empfängt.

Und nun weiß ich nicht mehr weiter.
Hat irgendjemand eine Idee oder schon dasselbe Problem gehabt?

Danke für Eure Hilfe..

[Fuerchau]

Da JSON nun mal Text ist, solltest du einen CLOB statt BLOB mit einer CCSID für UCS2 (13488) nehmen.
Vielleicht gibt's ja schon einen NLOB (national large ...).
Bei CCSID 65535 stimmt dann wohl das Anführungszeichen nicht da dieses CCSID-Abhängig ist.
Die DB könnte dann ggf. 037 annehmen, sicher ist das aber nicht.

[rischer]

Hallo!

Danke für Deine Antwort.

BLOB deswegen, weil dies die Vorgaben von IBM waren und ja die Prozedur SYSTOOLS.JSON2BSON ein BLOB voraussetzt. Anführungszeichen weglassen funkt natürlich auch nicht.

Infos dazu entnahm ich von:
http://www.mcpressonline.com/databas...db2-for-i.html

[Fuerchau]

Das ist natürlich ein Problem.
Das Anführungszeichen und die Klammern sind CCSID-Spezifisch und gehören zur Syntax von JSON.
XML nimmt daher keine Codespezifischen Zeichen für die Tags.
Nun ist die Frage, in welcher CCSID die Funktionen erstellt sind.
Somit sind die Daten als BLOB aber in der korrekten CCSID zu übergeben.
Ebenso ist auch das Auslesen der Daten entsprechend zu sehen.

Probiere es am besten mal mit einem 037-Job aus.
D.h., das Terminal in 037, den Job in 037 und die PF in 037.

Wenn das klappt oder auch nicht, kannst du deine Daten auch casten:
cast(cast('Json-Text' as varchar(nn) ccsid 037) as blob(3m))

Auf dem selben Weg ist dann ggf. auch UTF-8 umzusetzen, wobei bei 037 UTF-8 unsinnig wird.

[rischer]

Das ist natürlich ein Problem.
Das Anführungszeichen und die Klammern sind CCSID-Spezifisch und gehören zur Syntax von JSON.
XML nimmt daher keine Codespezifischen Zeichen für die Tags.
Nun ist die Frage, in welcher CCSID die Funktionen erstellt sind.
Somit sind die Daten als BLOB aber in der korrekten CCSID zu übergeben.
Ebenso ist auch das Auslesen der Daten entsprechend zu sehen.

Probiere es am besten mal mit einem 037-Job aus.
D.h., das Terminal in 037, den Job in 037 und die PF in 037.

Wenn das klappt oder auch nicht, kannst du deine Daten auch casten:
cast(cast('Json-Text' as varchar(nn) ccsid 037) as blob(3m))

Auf dem selben Weg ist dann ggf. auch UTF-8 umzusetzen, wobei bei 037 UTF-8 unsinnig wird.

Das ist ja echt verzwickt.
Stelle ich den Job auf CCSID 037 und setze ich das SQL so ab:
INSERT INTO LKWSQL.WS_JSON (JSON_INFO)
VALUES ( SYSTOOLS.JSON2BSON(cast('{"detail":"hilfe"}' as varchar(50) ccsid
037)) )
So kommt zwar selbiger Fehler wie oben wieder, jedoch kann ich die Fehlermeldung lesen:
..
Der zugeordnete Text ist JSON parsing error code: 4 for:
.."detail":"hilfe"... Trat der Fehler in einem Auslöserprogramm auf, ist der....

Offenbar kann jetzt wirklich nicht das { und ' umgesetzt werden, womit der Parser in einen Fehler läuft.

[Fuerchau]

Schau dir die Programme und Module in den Programmen an (sollte wohl ILE sein).
Die CCSID der Quelle wird zumindest vermerkt.

Ansonsten reicht es nicht, nur den Job auf 037 zu setzen. Das Terminal muss auch in 037 sonst kommen die Klammern im falschen Code an.

Alternativ versuche es nicht per STRSQL sondern mit RUNSQLSTM.
Wenn die SRC mit 273 im Job erfasst wird und das RUNSQLSTM dann im Job mit 037 läuft, wird beim Lesen der SRC von 273 in 037 gewandelt.

Ganz schön bescheuert vom JSON-Tool, nicht mit unterschiedlichen CCSID's arbeiten zu können!
Da ist Java in soweit besser als das Java grundsätzlich bei Strings in Unicode arbeitet.

Fehlermeldung an den Lieferanten, warum die Software nur auf amerikanischen Systemen läuft.

[rischer]

Hallo!

Danke für Deine Worte.

Ich stimme Dir völlig zu, echt bescheuert die Geschichte - ich schätze, das ganze Thema um JSON ist implementierungstechnisch noch in den Kinderschuhen auf er i.
Vor allem, ich kann die CCSID meiner Terminals nicht ändern, weil firmenweit alle Terminals 273 haben und alle Jobs unter 1141 laufen.
Egal, was ich hier rumprobiere, verwenden kann ich das dann nicht.

[Fuerchau]

Hast du es denn dann schon mal mit obigen cast's probiert?

JSON sollte vom Grundsatz unicodebasiert sein um alle Formen abdecken zu können.
Schließlich handelt es sich ausschließlich um Texte in allen Sprachen.
Binärdaten (z.B. Bilder) können nur über den Umweg von MIME64 gespeichert werden.

D.h., in der PF(Tabelle) grundsätzlich NVARCHAR bzw. falls vorhanden NLOB oder eben CLOB mit CCSID 13488. Alles andere macht überhaupt keinen Sinn.

Per Cast in Unicode (wenn das System das nicht sogar automatisch macht) für die Funktion käme man mit jeder CCSID zurecht.

[rischer]

Hi!

Ja, habe ich alles probiert beim Create Table.
NLOB nicht gültig, BLOB mit CCSID 13488 versucht, CCSID nicht gültig.
Bei CLOBS natürlich keine CCSID möglich, die haben standardmäßig 65535 in Tabelle.

Wenn ich das bereitgestellte Tool SYSTOOLS.JSON2BSON verstehe, müßte ich schon UTF-8 reinschicken, damit das binäre JSON in das BLOB der Tabelle gespeichert wird.
Das habe ich bis jetzt noch nicht geschafft, obwohl ich ja von einem HTTP-Rest-Webservice UTF-8 reinbekomme und ich via API iConv nach 1141 konvertiere um die Daten weiterverwenden zu können.
Habe versucht, die UTF-8 Daten, die ich in einem ILE-RPG aus dem QtmhRdStin auslese gleich direkt in das SQL (embedded) zu stellen. Das will auch nicht recht funken, da das embedded SQL Probleme mit den UTF-8 Daten hat.

Ich habe jetzt schon ein paar Haare weniger ;-).

[Fuerchau]

OK, UTF-8 macht ja Sinn, das ist auch CCSID-Neutral.
Deine Daten kommen als UTF-8 rein, dann solltest du diese nicht nach 1141 sondern nach Unicode 13488 konvertieren.
Dies geht auch mit iConv.
Diese Unicode-Daten stellst du in eine Zeichenvariable vom Typ "C" (USC2).
Beim Aufruf der SQL-Funktion, die nun leider ein BLOB erwartet, castest du wieder zurück nach UTF-8 (1208) und anschließend in einen BLOB.

Ähnlich sollte es auch in STRSQL gehen.
Deine Eingabe ist 273/1141, die must du nach 1208 und dann in BLOB casten.

Wenn du die Daten aus der Funktion zurück erhältst, brauchst du wohl nichts zu tun da die Daten ja bereits in UTF-8 sind. Benötigst du diese als Zeichendaten, dann per iConv von 1208 in 13488 (Unicode) wandeln.

Ansonsten:
CLOB kann natürlich eine CCSID (beim Create) enthalten.
Und das Ergebnis von CLOB(xxx) als Funktion entscheidet sich an Hand der Daten von XXX.

[Fuerchau]

Nachtrag:

Statt iConv zu bemühen kannst du natürlich auch SQL verwenden:

exec SQL set : MyVar = cast(: Myvar.....);

[Rainer Ross]

Hallo Rischer,

ich mache relativ viel mit IBM i, Webanwendungen, AJAX und JSON.

Die Maschine hat 7.2 mit dem neuesten PTF-Stand und CCSID 1141 im Systemwert QCCSID.
Mit folgenden Schritten läuft das Beispiel auf meiner Maschine:

1. JSON-SQL-Funktionen einrichten

qsh
/QIBM/ProdData/OS/SQLLIB/bin/db2nosql -setup enable

2. Datei erstellen

create or replace table tsto.wrkjson
(jsid int generated always as identity (start with 1 increment by 1),
json_info blob(3M) not null,
primary key(jsid));

Label on Column wrkjson (jsid is 'Id');
Label on Column wrkjson (json_info is 'JSON-Info');


3. JSON-Daten einfügen

INSERT INTO tsto.wrkjson (JSON_INFO)
VALUES ( SYSTOOLS.JSON2BSON('{"detail":"hilfe"}'))
INSERT INTO tsto.wrkjson (JSON_INFO)
VALUES ( SYSTOOLS.JSON2BSON('{"detail":"hilfe2"}'))

4. JSON-Daten auslesen

select jsid,json_val(json_info,'detail','s') as info
from tsto.wrkjson


Anfang auf Zeile . . . . . .
....+....1....+....2....+....3....+....4....
Id INFO
1 hilfe
2 hilfe2
******** Datenende ********

Jetzt mit einem komplexeren Beispiel

INSERT INTO tsto.wrkjson (JSON_INFO)  
VALUES ( SYSTOOLS.JSON2BSON('{        
 "kunde": [                           
  {                                   
   "id"  :123456,                     
   "name":"Testname",                 
   "adresse": {                       
       "plz":86916,                   
       "ort":"Kaufering"              
   }                                  
  }]                                  
 }')) 

select jsid,                                                        
 json_val(json_info,'kunde.id','i') as Id,                          
 substr(json_val(json_info,'kunde.name','s') , 1 , 20) as Name,     
 json_val(json_info,'kunde.adresse.plz','i') as PLZ,                
 substr(json_val(json_info,'kunde.adresse.ort','s') , 1 , 20) as Ort
from tsto.wrkjson                                                

....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
           Id              ID   NAME                            PLZ   ORT       
            1         123.456   Testname                     86.916   Kaufering 
********   Datenende   ******** 

Herzliche Grüße
Rainer

Folgende Anwendungen arbeiten mit AJAX, JSON und IBM i
www.myhofi.com - Hotels finden - leicht gemacht
www.myhofi.com/devhtm/spoolsorter.htm - wrkoutq in Javascript mit nur 75 Zeilen Code