24. September 2012

JSON generieren: In der Datenbank mit einer SQL-Abfrage

Generating JSON in the database
Mehr und mehr kommt die Anforderung, aus Datenbankinhalten JSON zu generieren. JSON steht für JavaScript Object Notation; kann also von einer JavaScript Engine direkt interpretiert und als Objekt im Hauptspeicher aufgebaut werden. JSON wird gerne als "leichtgewichtiges" Datenaustauschformat verwendet - insbesondere dann, wenn es um moderne Webanwendungen geht und die Daten im Browser verarbeitet werden sollen.
Dieses Blog-Posting zeigt, wie man JSON anhand einer SQL-Abfrage generieren kann. APEX-Entwickler kennen das PL/SQL-Paket APEX_UTIL - darin sind einige Prozeduren zum Generieren von JSON enthalten. Mit denen möchte ich mich hier aber nicht beschäftigen - und das aus folgenden Gründen:
  • Nicht überall ist APEX installiert - und wo es kein APEX gibt, da gibt es auch kein APEX_UTIL.
  • Die JSON-Funktion ist in Form von Prozeduren enthalten; das Ergebnis wird demnach in den HTP Buffer geschrieben (HTP.P). Besser wäre es aber, wenn man das Ergebnis als Rückgabewert einer Funktion und dann als VARCHAR2 oder CLOB erhalten könnte.
  • Und schließlich möchte ich einen Weg vorstellen, mit dem man jede beliebige Objektstruktur generieren kann - und nicht nur einfach das "flache" Äquivalent zu einer SQL-Abfrage.
JSON hat starke Ähnlichkeit zu XML. Und in der Tat wird es teilweise aus den gleichen Gründen verwendet, aus denen man vor 10 Jahren auf XML umgestiegen ist: Sehr flexibel und leicht von Anwendungen konsumierbar. Wie XML bildet auch JSON eine hierarchische Objektstruktur ab - jedes XML kann als Objekt aufgefasst werden und umgekehrt. Na ja - und dann kann man auch jedes XML in ein JSON übersetzen und umgekehrt. Und genau aus diesem Grund werden die XML-Funktionen der Datenbank beim Erzeugen des JSON helfen.
Funktionen zum Erzeugen von XML haben wir in der Datenbank reichlich: Die SQL/XML-Funktionen sind in diesen Blog Postings beschrieben:
Wir nehmen einer der SQL/XML-Abfragen dieses Tutorials als Ausgangspunkt - damit haben wir schon mal das XML, welches nun "nur" noch in ein JSON umgewandelt werden muss. Und das Umwandeln von XML in etwas anderes wird meistens mit XSLT-Stylesheets gemacht. Und so findet man im Internet auch einige fertige Stylesheets; bei code.google.com gleich zwei. Zuerst habe ich xml2json-xslt ausprobiert; das hatte allerdings die Nachteile, dass es XML-Attribute nicht verarbeiten und mit 1:n-Beziehungen, also der Hierarchie nicht richtig umgehen kann. Also habe ich mit xmltojson von Keith Chadwick weitergearbeitet.
Wichtig ist, dass das XML, welches wir in JSON umwandeln wollen, keine leeren Elemente, also keine leeren Tags enthält - andernfalls wird fehlerhaftes JSON generiert. Das kann insbesondere bei SQL NULL Values auftreten - denn wenn die Tabellenspalte, die mit XMLElement() in ein Tag umgewandelt wird, SQL NULL enthält, macht die Funktion ein leeres Tag. Hier sollte man also sicherstellen, dass mit der NVL-Funktion gearbeitet wird. Als Ausgangspunkt dient also diese SQL-Abfrage, welche aus den Tabellen EMP und DEPT ein XML-Dokument generiert. Dieses XML werden wir dann mit dem Stylesheet xmltojson in JSON umwandeln.
select
   xmlelement(  
    "department",
    xmlelement("name", d.dname),
    (
     select
      XMLAgg(
       xmlelement(
        "employee",
         XMLAttributes(
          e.empno as "id",
          e.hiredate as "hire-date"
         ),
         xmlelement("name", e.ename),
         xmlelement("salary", e.sal),
         xmlelement("commission", nvl(e.comm,0)),
         XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
       )
      )
     from scott.emp e where e.deptno = d.deptno
    )
   )
from scott.dept d
/
Dann laden wir von https://code.google.com/p/xmltojson/ die Dateien xmltojsonv2.xsl und config.xml herunter ...
 Directory of D:\

14.09.2012  11:13             5.898 config.xml
14.09.2012  11:14            20.282 xmltojsonv1.xsl
Dann müssen die Dateien in die Datenbank gebracht werden, damit die datenbankeigene XSLT-Transformation sie benutzen kann. Dabei muss beachtet werden, dass das XSLT-Stylesheet xmltojsonv1.xsl die Datei config.xml referenziert. Damit das auch in der Datenbank einwandfrei funktioniert, brauchen wir das "virtuelle Dateisystem" der XML DB. Dorthin werden wir die Dateien mit FTP laden - und zwar ins Verzeichnis /public. Und dieser Pfad  /public/config.xml muss als absoluter Pfad in die Datei xmltojsonv1.xsl eingetragen werden.
  <!--
  ///////////////////////////////////////////////////////////////////////////////////////////
  Define the configuration document along with configuration parameters
  and switches
  ///////////////////////////////////////////////////////////////////////////////////////////
-->

  <xsl:param name="doc">/public/config.xml</xsl:param>
  <xsl:param name="config" select="document($doc)"/>

  <xsl:param name="encaseObject" select="$config/xmltojson/settings/encase/objectNames" />
Nun ladet Ihr die Dateien per FTP in die Datenbank (damit FTP funktioniert, muss der Protokollserver ggfs. vorher mit DBMS_XDB.SETFTPPORT(2100) freigeschaltet werden. Der Upload geht danach wie folgt:
D:\xml2json>ftp -n
ftp> open sccloud030 2100 
Connected to sccloud030.de.oracle.com.
220- sccloud030.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 sccloud030.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user scott **** 
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public 
250 CWD Command successful
ftp> put D:\config.xml 
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 5898 bytes sent in 0,00Seconds 5898000,00Kbytes/sec.
ftp> put D:\xmltojsonv1.xsl 
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 20282 bytes sent in 0,00Seconds 20282000,00Kbytes/sec.
ftp> bye 
Mit der SQL-Funktion XDBURITYPE bekommt Ihr nun sehr einfach Zugang zum Stylesheet ...
SQL> select xdburitype('/public/xmltojsonv1.xsl').getxml() XSL from dual;

XSL
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850"?>
<xsl:stylesheet version="1.0" xmlns:xsl="

1 Zeile wurde ausgewählt.
Und damit können wir loslegen. Nehmt einfach die XML-Abfrage von vorhin. Das XML-Dokument, welches sie produziert, muss nun noch "durch" die Funktion XMLTRANSFORM (welche eine Stylesheet-Transformation macht) geleitet werden. Das sieht dann so aus:
select
 xmltransform(
  xmlelement(  
   "department",
   xmlelement("name", d.dname),
   (
    select
     XMLAgg(
      xmlelement(
       "employee",
        XMLAttributes(
         e.empno as "id",
         e.hiredate as "hire-date"
        ),
        xmlelement("name", e.ename),
        xmlelement("salary", e.sal),
        xmlelement("commission", nvl(e.comm,0)),
        XMLComment('Dieses XML-Dokument wurde am '||to_char(sysdate, 'DD.MM.YYYY HH24:MI') ||' generiert.')
      )
     )
    from scott.emp e where e.deptno = d.deptno
   )
  ),
  xdburitype('/public/xmltojsonv1.xsl').getxml()
 ).getclobval() as json
from scott.dept d
/
Das Ergebnis sieht dann etwa so aus ...
JSON
--------------------------------------------------------------------------------
[{"department":{"name":"ACCOUNTING","employee":[{"id":7782,"hire-date":"1981-06-
09","name":"CLARK","salary":2450,"commission":0},{"id":7839,"hire-date":"1981-11
-17","name":"KING","salary":5000,"commission":0},{"id":7934,"hire-date":"1982-01
-23","name":"MILLER","salary":1300,"commission":0}]}}]

:

[{"department":{"name":"SALES","employee":[{"id":7499,"hire-date":"1981-02-20","
name":"ALLEN","salary":1600,"commission":300},{"id":7521,"hire-date":"1981-02-22
","name":"WARD","salary":1250,"commission":500},{"id":7654,"hire-date":"1981-09-
28","name":"MARTIN","salary":1250,"commission":1400},{"id":7698,"hire-date":"198
1-05-01","name":"BLAKE","salary":2850,"commission":0},{"id":7844,"hire-date":"19
81-09-08","name":"TURNER","salary":1500,"commission":0},{"id":7900,"hire-date":"
1981-12-03","name":"JAMES","salary":950,"commission":0}]}}]

[{"department":{"name":"OPERATIONS"}}]
Im Browser könnt Ihr das mit dem "Firebug-Plugin" recht einfach testen. Nehmt das generierte JSON (Zeilenumbrüche entfernen!) und setzt es in folgenden JavaScript-Code ein: window.JSON.parse('<<JSON-CODE hier>>').
Viel Spaß beim Ausprobieren ...
Generating JSON from SQL queries is a requirement which occurs more and more often. JSON is the abbreviation for JavaScript Object Notation; JavaScript engines are able to consume it directly in order to build an in-memory object structure. JSON is therefore very well suited to provide data for AJAX based web applications: the data can be directly consumed by the browsers' javascript engine.
In this blog posting I'll show an approach to generate JSON from SQL queries. Most APEX developers know the PL/SQL package APEX_UTIL - it contains some procedures to generate JSON from the reesults of a SQL query. But I'll not elaborate on these and show another approach - for these reasons:
  • APEX is not installed in every database - some databases don't have APEX, so no APEX_UTIL.
  • APEX_UTIL provides the functionality as procedures - the JSON output is written to the HTP buffer, which is appropriate for APEX applications. But I would like a more generic approach: The JSON should be returned from a PL/SQL function as a CLOB or VARCHAR2.
  • And last, but not least: The JSON returned by the APEX_UTIL procedures is flat - similar to the results of a SQL query - I'd like an approach which is able to generate nested object structures as well.
JSON is very similar to XML - some guys even say that JSON is the more flexible and lightweight alternative to XML (it's interesting that one of reasons for XML - 10 years ago - was that XML is "a lightweight and flexible alternative to ..."). As XML, JSON represents a hierarchical object structure - any object can be expressed as JSON or XML - and any XML or JSON can be interpreted as an programming languages' object. And that fact allows us to use the XML functionality of Oracle (the SQL/XML functions) in order to generate JSON. I have described the SQL/XML functions in another blog posting some time ago ...
We'll use one of the SQL/XML queries in that tutorial as a starting point for JSON generation. The SQL/XML query constructs the data as a nested XML document. When it's about converting XML to something different (HTML, TEXT, etc.), we can use XSLT-Stylesheets for that. So we need an XSLT Stylesheet, which converts XML to JSON. After googling a bit around, I found two implemetations on code.google.com. I started with xml2json-xslt, but this one had some severe disadvantages: It was not able to process XML attributes and a 1:n hierarchy in the XML (like DEPT -> EMP) led to errornous JSON. So I continued my work with xmltojson from Keith Chadwick.
The second stylesheet also has a limitation: It cannot work with empty XML tags. So our SQL/XML query must ensure, that the resulting XML document has no empty tag. Empty tags can occur, when the XMLElement() function is being used with a SQL NULL value - it then generates an empty tag. So we need to have a look at the SQL query and add some NVL() functions in those cases where a column value might be SQL NULL. This results in the following query, based on EMP and DEPT. This query produces XML output which we will then convert to JSON.
select
   xmlelement(  
    "department",
    xmlelement("name", d.dname),
    (
     select
      XMLAgg(
       xmlelement(
        "employee",
         XMLAttributes(
          e.empno as "id",
          e.hiredate as "hire-date"
         ),
         xmlelement("name", e.ename),
         xmlelement("salary", e.sal),
         xmlelement("commission", nvl(e.comm,0)),
         XMLComment('This XML document is generated as of '||to_char(sysdate, 'DD/MM/YYYY HH24:MI'))
       )
      )
     from scott.emp e where e.deptno = d.deptno
    )
   )
from scott.dept d
/
Now download the files xmltojsonv2.xsl and config.xml from https://code.google.com/p/xmltojson/.
 Directory of D:\

14.09.2012  11:13             5.898 config.xml
14.09.2012  11:14            20.282 xmltojsonv1.xsl
Next, both files must be brought into the database. And this very XSLT stylesheet has a specific "feature": It stores its configuration in the second file: config.xml. So the Stylesheet must be able to access the config.xml - also in the database. For that reason we cannot just store the XSLT in an ordinary table, we need to use the "virtual file system" of the XML DB Repository. We'll store them in the "folder" public - so we will then have two paths for two files: /public/xmltojsonv1.xml and /public/config.xml. Since the database does not support relative paths here, we now make sure that the path to config.xml within the stylesheet is absolute. Open xmltojsonv1.xsl with the editor of your choice and change the highlighted entry from config.xml to /public/config.xml.
  <!--
  ///////////////////////////////////////////////////////////////////////////////////////////
  Define the configuration document along with configuration parameters
  and switches
  ///////////////////////////////////////////////////////////////////////////////////////////
-->

  <xsl:param name="doc">/public/config.xml</xsl:param>
  <xsl:param name="config" select="document($doc)"/>

  <xsl:param name="encaseObject" select="$config/xmltojson/settings/encase/objectNames" />
Having done that we can bring the files into the database. This can be achieved using the package DBMS_XDB, another (and simpler) approach is to use the database-embedded FTP Server. This allows us to upload the files directly to the XML DB Repository. If your FTP Server does not work, activate it by executing DBMS_XDB.SETFTPPORT(2100) (with DBA privileges). Then you can upload the files as follows ...
D:\xml2json>ftp -n
ftp> open sccloud030 2100 
Connected to sccloud030.de.oracle.com.
220- sccloud030.de.oracle.com
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 sccloud030.de.oracle.com FTP Server (Oracle XML DB/Oracle Database) ready.
ftp> user scott **** 
331 pass required for SCOTT
230 SCOTT logged in
ftp> cd /public 
250 CWD Command successful
ftp> put D:\config.xml 
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 5898 bytes sent in 0,00Seconds 5898000,00Kbytes/sec.
ftp> put D:\xmltojsonv1.xsl 
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp: 20282 bytes sent in 0,00Seconds 20282000,00Kbytes/sec.
ftp> bye 
Now let's do a quick test. Try to access the stylesheet using the function XDBURITYPE:
SQL> select xdburitype('/public/xmltojsonv1.xsl').getxml() XSL from dual;

XSL
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850"?>
<xsl:stylesheet version="1.0" xmlns:xsl="

1 row selected.
Now we can do the converion of our XML to JSON. Just take the above SQL query and add the XMLTRANSFORM function. XMLTRANSFORM performs a stylesheet transformation - in the database. The query should look like this:
select
 xmltransform(
  xmlelement(  
   "department",
   xmlelement("name", d.dname),
   (
    select
     XMLAgg(
      xmlelement(
       "employee",
        XMLAttributes(
         e.empno as "id",
         e.hiredate as "hire-date"
        ),
        xmlelement("name", e.ename),
        xmlelement("salary", e.sal),
        xmlelement("commission", nvl(e.comm,0)),
         XMLComment('This XML document is generated as of '||to_char(sysdate, 'DD/MM/YYYY HH24:MI'))
      )
     )
    from scott.emp e where e.deptno = d.deptno
   )
  ),
  xdburitype('/public/xmltojsonv1.xsl').getxml()
 ).getclobval() as json
from scott.dept d
/
And the result looks like this:
JSON
--------------------------------------------------------------------------------
[{"department":{"name":"ACCOUNTING","employee":[{"id":7782,"hire-date":"1981-06-
09","name":"CLARK","salary":2450,"commission":0},{"id":7839,"hire-date":"1981-11
-17","name":"KING","salary":5000,"commission":0},{"id":7934,"hire-date":"1982-01
-23","name":"MILLER","salary":1300,"commission":0}]}}]

:

[{"department":{"name":"SALES","employee":[{"id":7499,"hire-date":"1981-02-20","
name":"ALLEN","salary":1600,"commission":300},{"id":7521,"hire-date":"1981-02-22
","name":"WARD","salary":1250,"commission":500},{"id":7654,"hire-date":"1981-09-
28","name":"MARTIN","salary":1250,"commission":1400},{"id":7698,"hire-date":"198
1-05-01","name":"BLAKE","salary":2850,"commission":0},{"id":7844,"hire-date":"19
81-09-08","name":"TURNER","salary":1500,"commission":0},{"id":7900,"hire-date":"
1981-12-03","name":"JAMES","salary":950,"commission":0}]}}]

[{"department":{"name":"OPERATIONS"}}]
To test the JSON, just open the Firefox, Chrome or Safari browser. The following screenshot was taken from Firefox with the Firebug plugin. I opened the console and executed the following JavaScript-Code window.JSON.parse('<<YOUR JSON-CODE GOES HERE>>)'. When you copy and paste the JSON from SQL*Plus, make sure that you remove the line breaks.
Have fun!

5. September 2012

DELETE und UPDATE: Welche(!) Zeilen waren betroffen?

DELETE and UPDATE: Which(!) rows were affected?
In diesem Blog Posting stelle ich die RETURNING-Klausel für SQL DML-Anweisungen nochmals vor. Für SQL INSERT Kommandos hat die sicherlich fast jeder schonmal gesehen (dennoch erkläre ich es kurz) und danach zeige ich, wie man mit der RETURNING-Klausel feststellen kann, welche Zeilen von einem DELETE oder UPDATE erfasst wurden.
create sequence seq_id 
/

create table test (id number, name varchar2(200))
/

insert into test (id, name) values (seq_id.nextval, 'Max Mustermann')
/
Nun stellt sich die Frage, wie man an die während dem INSERT generierte ID wieder herankommt. Nachträgliches Abfragen funktioniert nicht, da der einzige, eindeutige Schlüssel (eben die ID) noch nicht bekannt ist. Eine Variante wäre ein ...
select seq_id.currval from dual
/

CURRVAL
----------
         1
Aber das bedeutet das Absetzen einer neuen SQL-Abfrage. Viel eleganter wäre es doch, wenn man die ID direkt beim INSERT zurückbekommen könnte - und das geht mit der RETURNING-Klausel ganz einfach.
declare
  l_id test.id%type;
begin
  insert into test (id, name) values (seq_id.nextval, 'Fritz') returning id into l_id;
  dbms_output.put_line(l_id);
end;
/
Weniger bekannt ist, dass man RETURNING auch bei UPDATE und DELETE-Kommandos einsetzen kann. Da hier auch mal mehr als eine Zeile zurückkommen kann, muss man aber noch mit der BULK COLLECT-Klausel arbeiten. Ein Beispiel für DELETE.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
begin
  delete from emp where deptno = 20 
  returning empno bulk collect into la_empno;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('deleted row with EMPNO '|| la_empno(i));
  end loop;
end;
/
sho err
Die Ausgabe ist dann wie folgt:
SQL> @returning
deleted row with EMPNO 7369
deleted row with EMPNO 7566
deleted row with EMPNO 7788
deleted row with EMPNO 7876
deleted row with EMPNO 7902

PL/SQL-Prozedur erfolgreich abgeschlossen.
Auch bei einem Update kann die RETURNING-Klausel eingesetzt werden, dabei werden aber stets die neuen Werte zurückgegeben, nicht die alten. Aber die Primärschlüsselspalten dürften ohnehin die interessanteren sein. Hier also das Beispiel für ein UPDATE mit RETURNING-Klausel.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
  la_sal   t_numlist;
begin
  update emp set sal = sal * 2 where deptno = 20 
  returning empno, sal bulk collect into la_empno, la_sal;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('updated row with EMPNO '|| la_empno(i)||' - SAL: '||la_sal(i));
  end loop;
end;
/
sho err
Ich arbeite nun seit mehr als 10 Jahren mit Oracle - und RETURNING nutze ich regelmäßig. Aber auf die Kombination mit DELETE oder UPDATE wurde ich erst vor zwei Wochen aufmerksam.
In this blog posting I'll introduce the RETURNING clause for SQL DML commands. Some of you might have seen a RETURNING clause as part of a SQL INSERT statement. I'll show this as well but the more interesting bit is an UPDATE or DELETE command with a RETURNING clause, since this allows you to retrieve the affected rows. Let's start with a typical, simple example with a SQL INSERT and a sequence.
create sequence seq_id 
/

create table test (id number, name varchar2(200))
/

insert into test (id, name) values (seq_id.nextval, 'Max Mustermann')
/
Now, the question is: How can we get the ID number generated by the sequence. We cannot select the table since we don't know the unique ID - and all other columns might be non-unique. A working approach is ...
select seq_id.currval from dual
/

CURRVAL
----------
         1
But this required another SQL Query - it would be much more elegant to get the generated number directly from the SQL INSERT statement. And the returning clause makes it easy.
declare
  l_id test.id%type;
begin
  insert into test (id, name) values (seq_id.nextval, 'Fritz') returning id into l_id;
  dbms_output.put_line(l_id);
end;
/
You can use the RETURNING clause for DELETE statements as well. But since these typically affect more than one row, you'll also need the BULK COLLECT clause.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
begin
  delete from emp where deptno = 20 
  returning empno bulk collect into la_empno;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('deleted row with EMPNO '|| la_empno(i));
  end loop;
end;
/
sho err
This PL/SQL block produces the following output.
SQL> @returning
deleted row with EMPNO 7369
deleted row with EMPNO 7566
deleted row with EMPNO 7788
deleted row with EMPNO 7876
deleted row with EMPNO 7902

PL/SQL procedure successfully completed.
RETURNING does also work for UPDATE statements. In this case the new column values will be returned, not the "old" ones. But in practice, RETURNING will most often be used the get the non-changing primary key values back. Here is the example for a SQL UPDATE statement with a RETURNING clause.
declare
  type t_numlist is table of number index by binary_integer;
  la_empno t_numlist;
  la_sal   t_numlist;
begin
  update emp set sal = sal * 2 where deptno = 20 
  returning empno, sal bulk collect into la_empno, la_sal;

  for i in la_empno.first..la_empno.last loop
    dbms_output.put_line('updated row with EMPNO '|| la_empno(i)||' - SAL: '||la_sal(i));
  end loop;
end;
/
sho err
I'm working with Oracle for more than 10 years now and I frequently work with RETURNING. But I got aware of the combination with DELETE about three weeks ago.

Beliebte Postings