Thursday, September 14, 2017

Demo App for REST enabled SQL


Getting Started


 The new Oracle REST Data Services SQL over REST.  How to enable that is on my last blog post here: http://krisrice.blogspot.com/2017/09/ords-173-beta-introducing-rest-enabled.html

cURL Examples

The simplest way to test this new feature out is with a curl command sending over the SQL.


$ curl -X "POST" "http://localhost:9090/ords/hr/_/sql"  \
       -H "Content-Type: application/sql"               \
       -u HR:oracle                                     \
   -d $'select * from dual;'


There are a number of other curl based examples now in the example github project : https://github.com/oracle/oracle-db-tools/tree/master/ords/rest-sql . The examples try to cover the various type of output that can be returned.  This includes trying to use SPOOL which is a restricted command,  DDL, a full sql script, a SQLcl command "DDL" and others.




A better way


cURL is great but a web page is much more dynamic to show off such features.  Dermot who created this new feature created a demo page to show off many of the features as he showed off in this tweet ( hint follow him ) 

Starting from that example there a file in that same github folder.
demo-all-in-one.html
This is a full test page now based in Oracle JET , ORDS and Code Mirror that can be placed on your ORDS server ( for CORS reasons ) and served up.  There's a series of inputs on the left, output on the right hand side and finally some examples on the bottom of performing the call in cURL, JQuery , or SQLcl.




The most useful thing in this demo page is the Examples drop list.  There's everything from a trivial select from dual to doing POST of a fully form JSON document of the command to run such as a select with a bind of a VARRAY


{
  "statementText": "SELECT ? as col_ARRAY FROM dual",
  "offset": 0,
  "limit": 5,
  "binds":[
{"index":1,"data_type":"VARRAY", "type_name":"ADHOC_VARRAY_NUMBER","value":[1,5,3]}
]
}


The Output


The returning JSON can vary quite a lot depending on what is being sent. However the basic structure is quite simple. There is an array of "items" which is the statements processed. The variance comes in depending on what is being sent.

For a Query there will be a item[N].resultSet.  This will then have a child of metadata which is the columns, datatypes, json scrubbed name, real name. Peered to this is another "items" which has an array of the rows from the select.

For NON-Query there is an item[N].response which contains the text of what the command did.

And it can get more complicated from there.

{
    "env": {
        "defaultTimeZone": "America/New_York"
    },
    "items": [
        {
            "statementId": 1,
            ....
        },
.....

Here's a short example and it's corresponding output:
spool a
select 1 from dual;
DESC dual;
begin
 null;
end;
/
spool off




{
    "env": {
        "defaultTimeZone": "America/New_York"
    },
    "items": [
        {
            "statementId": 1,
            "statementType": "sqlplus",
            "statementPos": {
                "startLine": 1,
                "endLine": 1
            },
            "statementText": "spool a",
            "response": [
                "SP2-0738: Restricted command: \n\"spool a\"\nnot available",
                "\n"
            ],
            "result": 0
        },
        {
            "statementId": 2,
            "statementType": "query",
            "statementPos": {
                "startLine": 2,
                "endLine": 2
            },
            "statementText": "select 1 from dual",
            "response": [],
            "result": 0,
            "resultSet": {
                "metadata": [
                    {
                        "columnName": "1",
                        "jsonColumnName": "1",
                        "columnTypeName": "NUMBER",
                        "precision": 0,
                        "scale": -127,
                        "isNullable": 1
                    }
                ],
                "items": [
                    {
                        "1": 1
                    }
                ],
                "hasMore": false,
                "limit": 1500,
                "offset": 0,
                "count": 1
            }
        },
        {
            "statementId": 3,
            "statementType": "sqlplus",
            "statementPos": {
                "startLine": 3,
                "endLine": 3
            },
            "statementText": "DESC dual",
            "response": [
                "Name  Null? Type        \n----- ----- ----------- \nDUMMY       VARCHAR2(1) \n"
            ],
            "result": 0
        },
        {
            "statementId": 4,
            "statementType": "plsql",
            "statementPos": {
                "startLine": 4,
                "endLine": 7
            },
            "statementText": "begin\n null;\nend;",
            "response": [
                "\nPL/SQL procedure successfully completed.\n\n"
            ],
            "result": 0
        },
        {
            "statementId": 5,
            "statementType": "sqlplus",
            "statementPos": {
                "startLine": 8,
                "endLine": 8
            },
            "statementText": "spool off",
            "response": [
                "SP2-0738: Restricted command: \n\"spool off\"\nnot available",
                "\n"
            ],
            "result": 0
        }
    ]
}
Posted by at