JSON_TABLE in dynamischem SQL verwenden

[dschroeder]

Hallo,

ich glaube nicht, dass es geht, aber ich frage hier einfach mal:

Ich möchte die JSON_TABLE Funktion "variabler" nutzen. Ich würde gerne den path Parameter als Variable übergeben. Das scheint nicht zu klappen. Im meiner interaktiven SQL Oberfläche (dbVisualizer) will die Funktion unbedingt ein Literal dafür ('lax $.userprf') haben:


drop variable jsontest;
create variable jsontest varchar(1000);
set jsontest = '{ "userprf" : "TESTUSER", "ID" : 1000001234567 }';

select userprf from JSON_TABLE(jsontest, 'lax $'
columns( userprf varchar(10) path 'lax $.userprf')
) as json;





Alternativ habe ich versucht, die Funktion mit dynamischem SQL zu nutzen. Dabei ist mir aber ein Limit im Wege: Im embedded SQL kann ein SQL-Statement anscheinend nur 32K groß werden. Wenn ich mit Unicode arbeite, ist das nicht sehr viel.

Dieter

[andreaspr@aon.at]

Hallo Dieter,

Ab 7.2 und den entsprechenden PTFs kannst du das in RPG mit DATA-INTO machen.
Ist in manchen Fällen besser geeignet als SQL.
https://www.common.org/scotts-iland-blog/parsing-json-data-into/

lg Andreas

[Fuerchau]

Also 32K (Unicode-SQL ist das nur 16K) ist schon sehr viel für einen SQL-String.
Hostvariablen sind davon nämlich ausgenommen!
Auch dynamische SQL's reichen da vollkommen aus. Man muss nur seine Variableninhalte nicht einbetten sondern mit "?" und beim Execute/Open dann ein "Using : Hostvar1, : Hostvar2, .." verwenden.

[dschroeder]

Hallo Andreas,
vielen Dank. Das mit dem DATA-INTO habe ich nicht gewusst. Ich dachte, das geht nur für XML.

Ich schaue mir das auf jeden Fall mal an.

[dschroeder]

Also 32K (Unicode-SQL ist das nur 16K) ist schon sehr viel für einen SQL-String.
Hostvariablen sind davon nämlich ausgenommen!
Auch dynamische SQL's reichen da vollkommen aus. Man muss nur seine Variableninhalte nicht einbetten sondern mit "?" und beim Execute/Open dann ein "Using : Hostvar1, : Hostvar2, .." verwenden.

Hallo Baldur,
vielen Dank.

Das mit den Hostvariablen bei dynamischem SQL ist eigentlich genau, was ich gesucht habe. Das muss ich mir näher ansehen.

[Fuerchau]

Also ganz simpel:
Überall, wo du variable Werte benötigst, gibst du ein "?" als sog. Parametermarker ein.
Dann machst du deinen Prepare und beim Execute (oder auch execute immediate ohne Prepare) führst du dann deine Hostvariablen an.

Stmt = "insert into mytable (F1, F2, F3) values(?, ?, ?)"
prepare InsertMyTable from : Stmt;
execute InsertMyTable using : F1, : F2, : F3;

Stmt = "Select f1, f2, f3 from mytable where F1=? and F2=? and F3=?"
prepare SelectMyTable from : Stmt;
declare GetMyTable cursor for SelectMyTable;
open GetMyTable using : F1, : F2, : F3;
fetch GetMyTable into : DS.F1, : DS.F2, : DS.F3;

Alles kein Hexenwerk.
- volle Unicodeunterstützung der Variablen
- Typanpassung falls erforderlich
- NULL-Anzeiger wie immer ": V1 : N1"
- Fetch dann in benannte Variablen (mittels using SQLDA geht es auch absolut flexibel)
- keine Hochkomma-Probleme!

[dschroeder]

Super,

herzlichen Dank für die Info!

[andreaspr@aon.at]

Also 32K (Unicode-SQL ist das nur 16K) ist schon sehr viel für einen SQL-String.
Hostvariablen sind davon nämlich ausgenommen!
Auch dynamische SQL's reichen da vollkommen aus. Man muss nur seine Variableninhalte nicht einbetten sondern mit "?" und beim Execute/Open dann ein "Using : Hostvar1, : Hostvar2, .." verwenden.

Nur den PATH kann man leider nicht mit Hostvariablen abbilden :-(

[dschroeder]

Nur den PATH kann man leider nicht mit Hostvariablen abbilden :-(

Ja das ist schade. Aber es klappt trotzdem grundsätzlich. Der Pfad wird im dynamischen SQL-String angefügt. Mein Problem war dabei das Clob für den JSON-String. Das funktioniert ganz gut mit den Host-Variablen.

Allerdings habe ich leider immer noch ein Problem mit den Zeichensätzen. Ich habe den SQL-String als varucs2 deklariert. Das Parser normaler Zeichenketten klappt sehr gut. Aber sobald ich ein echtes Unicode Zeichen im JSON-String drinhabe, wird nichts mehr geparst. Irgendwie haut das mit den Clobs und Unicode noch nicht so hin.

Die Deklaration dcl-s clobDS sqltype(clob:200000) ccsid(1200); wird vom Compiler nicht akzeptiert. Ich musste den Clob ohne ccsid deklarieren. Ich fürchte, dass da irgendwo das Problem liegt.

Hier mal das ganze Programm, falls es jemanden interessiert:


//================================================== ========================================
// Funktion:
//
// Syntax zum Parsen von JSON mit der JSON_TABLE Funktion:
// $ – Start with the current object
// . – Look inside an object
// [ ] – Look inside an array
// – Reference the value associated with a key
//
//================================================== ========================================
dcl-proc um_JSON_getString export;
dcl-pi *n varucs2(200000);
json_in varucs2(200000) const;
path varucs2(1000) const;
end-pi;


dcl-s anw varucs2(16000);
dcl-s json_clobDS sqltype(clob:200000);
dcl-s clobDS sqltype(clob:200000);


json_clobDS_data = %trim(json_in);
json_clobDS_len = %len(%trimr(json_in));


//Jetzt das SQL-Statement zusammenbauen:
anw = 'select inhalt from JSON_TABLE(?, ' + um_hkUni('lax $')
+ ' columns( inhalt clob(200000) path ' + um_hkUni('lax $.' + path) + ')) as json';


exec sql prepare cmd from :anw;
exec sql declare csr1 cursor for cmd;


exec sql open csr1 using :json_clobDS;
UM_JoblogSQL(sqlcod:##PGMNAME:'open');


if sqlcod = 0;
exec sql fetch next from csr1 into :clobDS;
if sqlcod <> 0;
clear clobDS;
UM_JoblogSQL(sqlcod:##PGMNAME:'fetch');
endif;
endif;


exec sql close csr1;


return %ucs2(%trimr(%subst(clobDS_data:1:clobDS_len)));


end-proc;

[dschroeder]

Ich bin eben darauf gestoßen, dass es außer dem sqltype(clob) noch den sqltype(dbclob) also "Double Byte" Clob gibt. Bei diesem Typ kann man auch eine ccsid(1200) angeben. Ich probiere damit mal ein bisschen rum.