RdbHost: How to query from Javascript

How to Query from Javascript

RdbHost provides databases as a webservice. Accessing your RdbHost database, from Javascript, can be accomplished via our Rdb.js library.

Unfortunately, there is some preparatory work that needs to be done before this library will completely work. Due to javascript security constraints, you can only make requests across different subdomains of the same domain. If you own your domain, and your domain registrar has a web interface for managing domains, you can readily setup an 'rdhost' subdomain in addition to whatever subdomains you have setup already.

Once the subdomain is setup, you can start using the Rdbhost database via the Rdb.js library. A code sample is below, and a line-by-line explanation follows:

Example: Calculator

Here is a simple example that uses the server as a simple calculator, adding 1 and 1. This will work with any valid role on any database, as no specific privileges are required on any resource within the database.

 function success(json) { 
   // do something useful with data 
   var row = json.records.rows[0];
   getElementById('sum').innerHTML = row[0]; 
 } 
    6
 var uid = 'r0000000002'; 
 var authcode = '239847~~~~~~~~~~~~~~~~~~~~~~9834'; 
 var rdb = new SQLEngine(uid,authcode,'rdbhost'); 
var query = 'SELECT 1+1 as "sum"'; 
var res = rdb.query( {'callback' : success, 
                      'q' : query } ); 
					

The example above is an incomplete script that executes the addition query on the server and incorporates the data in JSON format into a <div> for display.

Lines 1 to 5 define the 'success' function, which fills a selected html element with a field value. Line 3 gets the first record into a variable.
Lines 7 and 8 define authentication parameters for a specific Rdbhost account. These values are from the account's role_manager page.
Line 9 creates the SQLEngine object, initialized with authentication parameters, and the subdomain value 'rdbhost'. If we were hosting on www.example.com, we would have created a subdomain 'rdbhost.example.com' which pointed at the rdbhost.com server.
Line 10 is the SQL query itself, requesting the sum of 1 and 1.
Line 11 is the heavy-lifter of the clip, actually sending the query to the server, parsing the response data, and calling the callback 'success' with the data as a parameter.

SQLEngine.query Options

The SQLEngine.query method takes an object as its one parameter, and that object can contain these options:

callback: a function to call when data successfully retrieved.
errback: a function to call when an error has occurred, either in the http connection or on the server.
q: the query itself, a string; It may contain '%s' tokens to be replaced with parameter values, or '%{cookie-name}' tokens to be replaced with cookie values, or '%[var-name]' tokens to be replaced with CGI variable values.
args: an array of query parameters. Each value must correspond to a '%s' substitution token in the query string. optional.
argtypes: an array of type-names, one value per args element; each element must be one of 'STRING', 'NUMBER', 'NONE', 'DATETIME', 'ROWID','BINARY','DATE', or 'TIME'. optional.
plainTextJson: true if results are desired as plain-text, rather than javascript data. default false;

JSON Result data

Now let's look at the result data:

  {
      "records": {
          "header": [
              [
                  23,
                  "sum"
              ]
         ],
          "rows": [
             [
                 2
             ]
         ]
     },
     "row_count": [
         1,
         "1 Rows Affected"
     ],
     "status": [
         "complete",
         "OK"
     ]
 }
					

Line 21 contains the status. That value will be 'complete', 'incomplete', or 'error'.
The rowcount, on line 16, showing how many affected rows the server reported.
Since this query returned one result, there is a 'records' element, containing a header and a row. Line 5 is the field data type, "23", which is PostgreSQL's identifier for array-of-int. Line 6 is the name of the field.

If there was an error, the page returned would resemble this:

    {
      "error": [
        "None",
        "FATAL:  authentication failed \"r000004\"\n"
      ],
      "status": [
        "error",
        "DatabaseNotOpened"
       ]
   }
	    

The error code (line 3) for rdbhost's own errors is always '-'. Other error codes and error messages (line 4) are pass-throughs from the relevant subsystem, such as PostgreSQL or the XML library.

SQLEngine.queryByForm

There is another method for querying, queryByForm. This alternate method is useful when the query data is already in a form, or if you wish to include files (binary data) as part of the query submission.

Here is an example of queryByForm:

<form method="post" action="" id="rdbform" enctype="multipart/form-data"
                      onsubmit="onClick();">
  q:<textarea name="q" id="q" rows="8" cols="80" tyle="vertical-align:text-top">     
  </textarea><br/>
  arg000: <input type="text" name="arg000" id="arg000" size="30" /><br />
  <input type="submit" name="Do it!" value="Do it!" /><br/>
</form>
 function success(json) { 
   // do something useful with data 
} 
function onClick() {
  var uid = 'r0000000002'; 
  var authcode = '2398~~~~~~~~~~~~~~~~~~7219834'; 
  var rdb = new SQLEngine(uid,authcode,'rdbhost'); 
  var res = rdb.queryByForm('rdbform', success);
  return false;
}
			

Lines 1-7 create the form, with fields for q and arg000.
Lines 8-10 define the success callback as described above.
Lines 11-17 define a click handler that complets the form, submits it, and calls success with the data recieved.

Sources

The code for this library can be found on github. There is little documentation other than this page and a blog post, but that will improve.

http://github.com/rdbhost/Rdb.Js

Examples can be found at: