How to Query from JavaScript

RdbHost provides databases as a webservice. Accessing your RdbHost database, from JavaScript, can be accomplished via our jQuery.Rdbhost.js library. Read the Readme in the library for guidance on using the jquery plugin methods. This page describes the SQLEngine class; this class is used by the plugin, and can be used on its own.

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.

1 function success(json) { 2 // do something useful with data 3 var row = json.records.rows[0]; 4 getElementById('sum').innerHTML = row[0]; 5 } 6 7 var uid = 'r0000000002'; 8 var authcode = '239847~~~~~~~~~~~~~~~~~~~~~~9834'; 9 var rdb = new SQLEngine(uid,authcode,'rdbhost'); 10 var query = 'SELECT 1+1 as "sum"'; 11 var res = rdb.query( {'callback' : success, 12 '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:

JSON Result data

Now let's look at the result data:

1 { 2 "records": { 3 "header": [ 4 [ 5 23, 6 "sum" 7 ] 8 ], 9 "rows": [ 10 [ 11 2 12 ] 13 ] 14 }, 15 "row_count": [ 16 1, 17 "1 Rows Affected" 18 ], 19 "status": [ 20 "complete", 21 "OK" 22 ] 23 }
  • 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:

1 { 2 "error": [ 3 "None", 4 "FATAL: authentication failed \"r000004\"\n" 5 ], 6 "status": [ 7 "error", 8 "DatabaseNotOpened" 9 ] 10 }

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:

1 <form method="post" action="" id="rdbform" enctype="multipart/form-data" 2 onsubmit="onClick();"> 3 q:<textarea name="q" id="q" rows="8" cols="80" style="vertical-align:text-top"> 4 </textarea><br/> 5 arg000: <input type="text" name="arg000" id="arg000" size="30" /><br /> 6 <input type="submit" name="Do it!" value="Do it!" /><br/> 7 </form> 8 function success(json) { 9 // do something useful with data 10 } 11 function onClick() { 12 var uid = 'r0000000002'; 13 var authcode = '2398~~~~~~~~~~~~~~~~~~7219834'; 14 var rdb = new SQLEngine(uid,authcode,'rdbhost'); 15 var res = rdb.queryByForm('rdbform', success); 16 return res; 17 }
  • 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. The README.md page on github should be enough to get you started, with reference to various pages on the website for specific problems.

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

Working examples can be found, live, at: JavaScript Examples