Using Rhino to Write Stored Procedures in JavaScript

Using Rhino to Write Stored Procedures in JavaScript – Part 4

July 16th, 2009 · No Comments

Throughout this series we have been incrementally building an external JavaScript stored procedure to parse, and insert JSON-encoded data into a SQL Anywhere database. In this final post, we will modify our procedure to accept JSON strings with more complex embedded objects and arrays. We will also make the procedure available directly through a SQL Anywhere web service.

To make our contact objects more complex we will expand them to include an arbitrary number of phone numbers, segmented into named groups. For example, a sample contact object including phone numbers is:

{
 "Surname"      : "John",
 "Given Name"   : "Doe",
 "Date of Birth": "1983-04-04",
 "Phone": {
   "work": [
     "(555) 555-5555", 
     "(666) 666-6666"],
   "home": [
     "(777) 777-7777"]}
}

To facilitate storing the phone groups (i.e. "work", "home", etc) and the individual phone numbers, we will add a couple of tables.

CREATE TABLE "DBA"."PhoneGroups" (
    "id"                             integer NOT NULL DEFAULT autoincrement
   ,"contact_id"                     integer NULL
   ,"name"                           long varchar NULL
   ,PRIMARY KEY ("id") 
);
 
ALTER TABLE "DBA"."PhoneGroups"
ADD FOREIGN KEY "Contacts" ("contact_id")
REFERENCES "DBA"."Contacts" ("id");
 
 
CREATE TABLE "DBA"."PhoneNumbers" (
    "id"                             integer NOT NULL DEFAULT autoincrement
   ,"phonegroup_id"                  integer NULL
   ,"number"                         long varchar NULL
   ,PRIMARY KEY ("id") 
);
 
ALTER TABLE "DBA"."PhoneNumbers"
ADD FOREIGN KEY "PhoneGroups" ("phonegroup_id")
REFERENCES "DBA"."PhoneGroups" ("id");

Since we are still only passing a single JSON document (albeit a more complex one) into our JavaScript procedure, we don’t need to modify JsWrapper.java at all. But we do need to modify Contact.js to handle the new phone number fields.

// this is the equivalent to: import java.sql.*
importPackage(java.sql)
 
// *****************
// getIdentity(conn)
// *****************
// generic function to return the @@IDENTITY of the last 
// autoincremented integer 
var getIdentity = function(conn) {
  var stmt, retval, rs;
  stmt = conn.createStatement();
  rs = stmt.executeQuery("SELECT @@IDENTITY");
  if (rs.next()) {
    retval = rs.getInt(1);
  }
  stmt.close();
  return retval;
};
 
// *************************************************
// addPhoneNumber(phonenumbers, phonegroup_id, conn)
// *************************************************
// 
// accepts an array of phone numbers to insert
//  ex. phonenumbers = [
//        "(555) 555-5555", 
//        "(666) 666-6666"];
//
var addPhoneNumber = function (phonenumbers, phonegroup_id, conn) {
  var stmt, i;
  stmt = conn.prepareStatement(
    'INSERT INTO PhoneNumbers("phonegroup_id", "number") VALUES (?, ?)');
  for (i = 0; i < phonenumbers.length; i++) {
    stmt.setInt(1, phonegroup_id);
    stmt.setString(2, phonenumbers[i]);
    stmt.executeUpdate();
  }
  stmt.close();
};
 
// ********************************************
// addPhoneGroup(phonegroups, contact_id, conn)
// ********************************************     
// accepts a hash of phone groups to insert
//  ex. phonegroups = {
//        "work": [
//          "(555) 555-5555", 
//          "(666) 666-6666"],
//        "home": [
//          "(777) 777-7777", 
//          "(888) 888-8888"]};
//
var addPhoneGroup = function (phonegroups, contact_id, conn) {
  var stmt, group; 
  stmt = conn.prepareStatement(
    'INSERT INTO PhoneGroups("contact_id", "name") VALUES (?, ?)');
  for (group in phonegroups) {
    stmt.setInt(1, contact_id);
    stmt.setString(2, group);
    stmt.executeUpdate();
    addPhoneNumber(phonegroups[group], getIdentity(conn), conn);
  }  
  stmt.close();
};
 
// *************************
// addContact(contact, conn)
// *************************
// accepts an array of contact object to insert
// ex. contact = {
//       "Surname": "Washington",
//       "Given Name": "George",
//       "Date of Birth": "1732-02-22",
//       "Phone": {
//         "work": [
//           "(555) 555-5555", 
//           "(666) 666-6666"],
//         "home": [
//           "(777) 777-7777", 
//           "(888) 888-8888"]}};
//
var addContact = function (contact, conn) {
  var stmt, i;
  // create a prepared statement for inserting contacts
  stmt = conn.prepareStatement(
    'INSERT INTO Contacts("surname", "givenName",' +
    '"birthDate") VALUES (?, ?, ?)');
 
  for (i in contact) {
    // iterate through contact list and insert contacts
    stmt.setString(1, contact[i]["Surname"]);
    stmt.setString(2, contact[i]["Given Name"]);
    stmt.setString(3, contact[i]["Date of Birth"]);
    stmt.executeUpdate();
    addPhoneGroup(contact[i].Phone, getIdentity(conn), conn);
  }
  stmt.close();
};
 
// get handle to the default JDBC connection
var jdbc_conn = DriverManager.getConnection("jdbc:default:connection");
 
// add all the contacts from the passed JSON contact array
addContact(JSON.parse(arguments[0]), jdbc_conn);
 
// commit the transaction
jdbc_conn.commit();

Compile Contact.js (as explained in a previous post), and update the resultant .JAR file in the database.

1	INSTALL JAVA UPDATE FROM FILE 'Contact.class';

The last thing we need to do is create a web service to wrap our JavaScript procedure. (You will need to have the SQL Anywhere HTTP running in order for this to work. Read this post for information on starting the HTTP server). The following code defines a web service called /contact that accepts POST requests. The POST body should be a JSON-encoded list of contacts.

CREATE SERVICE "contact" 
TYPE 'RAW'
AUTHORIZATION OFF 
SECURE OFF 
URL PATH OFF 
USER "DBA" 
METHODS "POST"
AS CALL InsertContactList(HTTP_BODY());

To test this out we will need to send a POST request to our newly-created web service. The easiest way I have found to do this is using the cURL command-line tool.

curl http://localhost:8080/contact -X POST -d "
[{
 \"Surname\"      : \"John\",
 \"Given Name\"   : \"Doe\",
 \"Date of Birth\": \"1983-04-04\",
 \"Phone\": {
   \"work\": [
     \"(555) 555-5555\", 
     \"(666) 666-6666\"],
   \"home\": [
     \"(777) 777-7777\"]}
}]"

Lastly, check that everything was inserted properly using this query.

SELECT givenName, surName, name, number 
FROM Contacts, PhoneGroups, PhoneNumbers
WHERE Contacts.id = PhoneGroups.contact_id AND
      PhoneGroups.id = PhoneNumbers.phonegroup_id;