Why Larry did it?

cloud-question-markIt is Very interesting to see al these theories and musings on the subject of Oracle (aka Larry) and Microsoft (aka Steve) forming what seems to be a “friendship”. I think very few (if any) of them commentators is pragmatic (read: they are all wrong). (The other few new recent “friendships” of Larry are somewhat irrelevant). So here goes my version (which is right, of course). Continue reading “Why Larry did it?”

SQL to JSON: SELECT * FROM persons AS JSON

[Update 2015 July 09]

At last 10 years after XML native support, Microsoft is announcing native JSON support in its next version of SQL Server 2016. Which is nice. It is shame PostgreSQL already has pretty decent native JSON since 2010. Ah sorry … I just can’t resist “snarky” comments.

[Update 2015 Mar 18]

Found today an interesting link. Apparently ORACLE does JSON.

[Started on 2008 Nov 4-th.]

This text originates from me, Dusan B. Jovanovic. Any similarities found elsewhere, are purely accidental. It, and any attachments, do contain confidential information and are subject to copyright and other intellectual property rights. It is only for the use of you, the reader. You may not copy, forward, disclose, save or otherwise use it in any way if you are not the the intended reader or responsible for delivery. If you receive this text by mistake, please advise me, and erase it immediately. I (Dusan B. Jovanovic) may monitor the content of texts and emails within my network to ensure compliance with the  policies and procedures. Any email or text is susceptible to alteration and its integrity cannot be assured. I (Dusan B. Jovanovic) shall not be liable if the text or message is altered, modified, falsified, or edited.

DBJSON™

Release 1.5

©2008-2009-2016 by Dusan B. Jovanovic

First of all: Why?

DBJSON™: Provides syntax and protocol, for the two-way communication, with the database in the form of a stream of JSON objects. DBJSON is JSON “formatted” Database Interface. For talking to the RDBMS, DBJSON replaces SQL. DBJSON is JSON interface to the data based online services. DBJSON is not SQL native DB extension to return (or index) JSON strings and data types.

You already forgot XML, now forget SQL. Think JSON.

SQL is the scripting language of talking to databases. To the caller (user), SQL enabled DB server does not return SQL. Instead it returns a pointer into the binary result structure (aka: cursor). In essence an memory block of text. The SQL based communication is a text based protocol. Ok then, what seems to be the problem?

Here we have one key issue: Transformations. Transformation chain, is always required to transform to/from SQL and Result pointer, and applications using the RDBMS.

Worse. In case of web app’s a whole sequence of transformations is required. Here are the two common implementations and their Run-Time transformations “From the data to the HTML”:

DB –> SQL –>T1–> ADORecordSet –>T2–> JavaScript == HTML
DB –> SQL –>T3–> ServiceXML –>T4–> AJAX == HTML

Flow from left to right is the data flow. SQL text queries are issued from either ADO (that was “the way” long time ago) or Service, both on the server side. Somewhere in the “middle tier”. We have two chains: request to the DB and reply from the DB. Thus each transformation chain requires (at least 2 transformations).

  • T1 — transform from SQL to ADO (or simillar) recordset (or .NET dataset)
  • T2 — transform from recodset/dataset to the javascript
  • T3 — transform from SQL to XML (sometimes can use native SQL ‘AS XML’ mechanism)
  • T4 — transform from XML to javascript

That is a lot of code to go wrong. Now imagine for example, an SQL query result delivered as a set of DBJSON™ objects. Where DBJSON (JavaScript) is an inbuilt (aka: native) RDBMS etension, to return the result as a set of JSON objects. In a simple string.

DB –> DBJSON –> ServiceDBJSON –> AJAX == HTML

This is just a output from the DB. In this imaginary world , DB + DBJSON + AJAX , chain would be extremely simple compared to current Result Pointer to HTML juggling through sequence of transformations. While there are no transformations present on the diagram above. AJAX is consuming DBJSON as any other JSON formated strings: instantly. And since DB returns DBJSON formated text, Service is just passing it to he waiting caller. In the shape of the AJAX components on the client side. Of course all the security issues are solved at the point of Service.

RDBMS result pointer is this special binary entity. Which is essentialy in-memory block of text lines. Thus DB result “cursor” (standard binary DB output structure), DB can not return full set of related DB entities all together with relations described. This is why (as an known example) .NET DataSet was invented.  Opposit to this, DBJSON™  full DB SET (relations description included) would be (is?) natural and easy to implement.

It should be possible to  add DBJSON™ facade/mediator component, to any legacy RDBMS. Thus to bypass DB rezult “cursor” transformations completely.

It has to be said, this is exactly why countless OO wrap-ups have been invented for, like ADO, OLEDB,  JDBC, etc … DBJSON is not very far from the same aim. But it is fundamentally different. It replaces SQL , it is completely different paradigm of communicating with data base. And last but not the least it is JavaScript.