SYSTOOLS.JSON2BSON

[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

[rischer]

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

Hallo!

Danke für die vielen Antworten und Hilfe.
Im Prinzip funktioniert alles wie beschriebeh in den Tech Tips und auch wie Rainer Ross beschrieben hat.
Ich hatte mich nur am Anfang mit den CCSID's verzettelt, es funkt tatsächlich wirklich alles selbst mit CCSID 1141.

@Rainer Ross
MIr mag eine variable Abfrage aller Elemente und aller Subfelder eines Arrays einfach nicht gelingen. Sprich lt. Deinem Beispiel eine Variable abfrage all Deiner Partner Kunden im Array (wenn Du mehrere hättest)....

[Rainer Ross]

Hallo Rischer,

anbei ein einfaches Beispiel für ein Array mit zwei Kunden

INSERT INTO tsto.wrkjson (JSON_INFO) 
VALUES ( SYSTOOLS.JSON2BSON('{       
 "kunde": [                          
  {"id"  :123456,"name":"Testname"}, 
  {"id"  :234567,"name":"Testname2"} 
  ]                                  
 }'))

select jsid,                                                      
 json_val(json_info,'kunde.0.id','i') as Kunde1,                  
 substr(json_val(json_info,'kunde.0.name','s') , 1 , 15) as Name1,
 json_val(json_info,'kunde.1.id','i') as Kunde2,                  
 substr(json_val(json_info,'kunde.1.name','s') , 1 , 15) as Name2 
from tsto.wrkjson                                                 
                               
....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
           Id          KUNDE1   NAME1                   KUNDE2   NAME2          
            1         123.456   Testname               234.567   Testname2      
********   Datenende   ******** 

Anzahl der Kunden im Array

select                                 
 systools.json_len(json_info,'kunde')  
from tsto.wrkjson                      

....+....1.... 
     JSON_LEN  
            2 

In JSON fangen die Arrays mit Null an. Die erste Kunden-id ist
kunde.0.id

Es gibt auch die Funktion JSON_TABLE, aber damit habe ich noch keine Erfahrung.
http://www.mcpressonline.com/databas...db2-for-i.html

Schönes Wochenende

Rainer

[Rainer Ross]

Hallo Rischer,

ich hab mit den Arrays etwas rumprobiert:

Kundendaten im Array:

select                                                
 value from tsto.wrkjson,                                  
 table(systools.json_table(json_info,'kunde','s:32')) as x

Kunden-Ids im Array:

select                                                
 value from tsto.wrkjson,                                  
 table(systools.json_table(json_info,'kunde.id','i')) as x 

Quelle: http://www.volubis.fr/news/liens/courshtm/XML/JSON.HTM

Herzliche Grüße

Rainer

[rischer]

Hallo Rischer,

ich hab mit den Arrays etwas rumprobiert:

Kundendaten im Array:

select                                                
 value from tsto.wrkjson,                                  
 table(systools.json_table(json_info,'kunde','s:32')) as x

Kunden-Ids im Array:

select                                                
 value from tsto.wrkjson,                                  
 table(systools.json_table(json_info,'kunde.id','i')) as x 

Quelle: http://www.volubis.fr/news/liens/courshtm/XML/JSON.HTM

Herzliche Grüße

Rainer

Interessant, will aber bei mir nicht funken.

Ich habe ein JSON-Array in meiner Datenbank das so aussieht:

{
"displayinformation": {"detail": [{"name":"ZZESCD","contentdecimal":null,"contentstring":"", "type":"string", "colour":"GRN", "visible": true, "protected": false, "underlined": false, "highlighted": false, "haserror": false, "desciption": false},{"name":"ZZIITX","contentdecimal":null,"contentstring":"ÄNDERN", "type":"string", "colour":"GRN", "visible": true, "protected": false, "underlined": false, "highlighted": false, "haserror": false, "desciption": false},{"name":"Z1ZXSB","contentdecimal":null,"contentstring":"", "type":"string", "colour":"GRN", "visible": true, "protected": false, "underlined": false, "highlighted": false, "haserror": false, "desciption": false}]}} 

Setze ich ein Select darauf ab a la:

select
value from tsto.wrkjson,
table(systools.json_table(json_info,'displayinform ation.detail','s:32')) as x

bekomme ich kein Ergebnis angezeigt (aber auch keinen Fehler).

Mache ich es so:

select
cast(data1.value as varchar(20) ccsid 1141) as name,
from
tsto.wrkjson j, table
(systools.json_table(j.JSON_INFO,'displayinformati on.detail.name','s
:20')) data1

Dann bekomme ich x (Anzahl der Elemente im Array) Records mit dem Inhalt von Name.
Ohne Cast geht es bei mir übrigends nicht (obwohl ich CCSID 1141 im Job habe), sonst bekomme ich kryptische Zeichen angezeigt.

Was ich bräuchte wäre die dynamische Auslesung aller Inhalte eines Array-elements also name, string, colour etc. auf einmal. Das hab ich noch nicht geschafft.....

[rischer]

Interessant, will aber bei mir nicht funken.

Ich habe ein JSON-Array in meiner Datenbank das so aussieht:

{
"displayinformation": {"detail": [{"name":"ZZESCD","contentdecimal":null,"contentstring":"", "type":"string", "colour":"GRN", "visible": true, "protected": false, "underlined": false, "highlighted": false, "haserror": false, "desciption": false},{"name":"ZZIITX","contentdecimal":null,"contentstring":"ÄNDERN", "type":"string", "colour":"GRN", "visible": true, "protected": false, "underlined": false, "highlighted": false, "haserror": false, "desciption": false},{"name":"Z1ZXSB","contentdecimal":null,"contentstring":"", "type":"string", "colour":"GRN", "visible": true, "protected": false, "underlined": false, "highlighted": false, "haserror": false, "desciption": false}]}} 

Setze ich ein Select darauf ab a la:

select
value from tsto.wrkjson,
table(systools.json_table(json_info,'displayinform ation.detail','s:32')) as x

bekomme ich kein Ergebnis angezeigt (aber auch keinen Fehler).

Mache ich es so:

select
cast(data1.value as varchar(20) ccsid 1141) as name,
from
tsto.wrkjson j, table
(systools.json_table(j.JSON_INFO,'displayinformati on.detail.name','s
:20')) data1

Dann bekomme ich x (Anzahl der Elemente im Array) Records mit dem Inhalt von Name.
Ohne Cast geht es bei mir übrigends nicht (obwohl ich CCSID 1141 im Job habe), sonst bekomme ich kryptische Zeichen angezeigt.

Was ich bräuchte wäre die dynamische Auslesung aller Inhalte eines Array-elements also name, string, colour etc. auf einmal. Das hab ich noch nicht geschafft.....

So funkts zumindest jetzt bei mir zufriedenstellend:

select
cast(value as varchar(200) ccsid 1141) from tsto.wrkjsopn,
table(systools.json_table(json_info,'displayinform ation.detail',
's:200')) as x

das ist so halbwegs zufriedenstellend aber folgendes ist besser:

select
substr(JSON_VAL(x.value,'name','s:10'), 1, 10) as Name,
substr(JSON_VAL(x.value,'type','s:5'), 1, 7) as Typ,
substr(JSON_VAL(x.value,'colour','s:3'), 1, 3) as Colour
from tsto.wrkjson,
table(systools.json_table_binary
(json_info,'displayinformation.detail',
's:200')) as x

Wobei das 's:200' die maximale Grösse eines Elements im Array abdecken muß, sonst gehts wieder nicht... ob dann aber aus diesem Element auch andere Datentypen extrahieren kann wird sich weisen...

[Fuerchau]

Ich denke mal, das Ergebnis kommt wieder als UTF-8 an (das sind ASCII-Zeichen).
Für die Lesbarkeit am Terminal brauchst du natürlich EBCDIC, daher der Cast.
Eine Weitergabe als Webservice o.ä. benötigt natürlich keine weitere Umwandlung da dort ja meist UTF-8 ausgegeben werden muss.

[rischer]

Danke an alle die mich mit hilfreichen Tipps unterstützt haben.
Funkt alles zur Zeit bestens wie gewollt.

Eine Frage hätt ich dann doch noch ;-):
Ich bekomme vom Webservice über das Api QtmhRdStin ja den Browserstream (as JSON) in mein ILERPG rein, übersetze diesen von 1208 nach 1141 und speichere diesen via embedded SQL in meiner JSON-Datenbank via SYSTOOLS.JSON2BSON .
Der QtmhRdStin-In-Stream kann mit einer beliebig grossen Variable definiert werden, jedoch das Embedded SQL erlaubt nur eine Host-Variable bis zu einer Höhe von ca. 32700.
Was, wenn über den Browser mal mehr als 32700 Zeichen reinflattern - hat jemand dafür bereits eine Lösung oder eine Idee?

Danke im Voraus!