XML/JSON Server

The Hypertext Transfer Protocol (HTTP) is the most ubiquitous of the Internet protocols. Although seen primarily as a means to fetch pages of Hypertext Markup Language (HTML) content for display in a web browser, HTTP is really a general-purpose transport for any type of data. The OpenBase.cgi process makes use of HTTP to communicate with clients requesting data using XML or JSON.

There are three ways to provide remote access to your data through an HTTP server.

TYPE#1 - RESTRICTED QUERIES

The easiest and safest way to provide remote access to data is through a restricted access profile. This means that the profile itself will contain a restricted query as to what will be returned when called.

Here is an example of a simple query that returns authors from the pubs database:

access = restricted
use = pubs@127.0.0.1
login = admin
password = ""
table = authors
order = au_fname
constraint = au_lname like '*@[search]*' or au_fname like '*@[search]*'

The demo_pubs_simple should be installed by default as an example, so you should see this profile when you launch the XML Setup application. Note that the constraint uses the "@[key]" pattern for value replacement inside the search string. key/value pairs can be provided either in the URL or as part of an XML post.

Here is an example of performing a search using the "demo_pubs_simple" profile and returning the results as JSON:

http://127.0.0.1/cgi-bin/OpenBase.cgi?p=demo_pubs_simple&search=en

Here is the same call, but specifies that you want the results as xml:

http://127.0.0.1/cgi-bin/OpenBase.cgi?p=demo_pubs_simple&search=en&out=xml

Returning Deep Results

In some cases you may want to provide a tree of related data. The OpenBase JSON/XML interface also allows relationships to be specified so that a tree of data can be returned as either JSON or XML.

The example called "demo_pubs_deep" adds the following attribute to the simple example:

relationships = toTitleauthor|toTitleauthor.toTitles

This example shows two relationships separated by a vertical bar: Titleauthor and Titles. The second relationship specifies that you access the target table through a second relationship separated by a period ("toTitleauthor.toTitles"). This creates a tree of information surrounding each author in the example.

Here is an example as JSON:

http://127.0.0.1/cgi-bin/OpenBase.cgi?p=demo_pubs_deep&search=am&callback=foobar

This returns the following result:

foobar ({"name" : "demo_pubs_deep", "table" : "authors", "result" : [{"_rowid" : 100, "address" : "6223 Bateman St.", "au_fname" : "Abraham", "au_id" : "409-56-7008", "au_lname" : "Bennet", "city" : "Berkeley", "contract" : 1, "phone" : "415 658-9932", "state" : "CA", "zip" : "94705", "toTitleauthor" : [{"_rowid" : 100, "au_id" : "409-56-7008", "au_ord" : 1, "royaltyper" : 60, "title_id" : "BU1032", "toTitles" : {"_rowid" : 200, "advance" : "$5,000.00", "notes" : "An overview of available database systems with emphasis on common business applications. Illustrated.", "price" : "$19.99", "pub_id" : 1389, "pubdate" : "Jun 12, 1985", "royalty" : "$10.00", "title" : "The Busy Executive's Database Guide", "title_id" : "BU1032", "type" : "business", "ytd_sales" : 4095}}]
}]
})

"callback=foobar" is optional. However it is often used to return JSON data so it is easily interpreted on the client.

Here is an example as XML:

http://127.0.0.1/cgi-bin/OpenBase.cgi?p=demo_pubs_deep&search=am&out=xml
<?xml version="1.0" encoding="UTF-8"?>
<response xmlns:rest='REST'>
<sequence rest:revision='1'><item><array table ='authors'><record rest:id='authors|100'><_rowid rest:id='authors._rowid|100'>100</_rowid><address rest:id='authors.address|100'>6223 Bateman St.</address><au_fname rest:id='authors.au_fname|100'>Abraham</au_fname><au_id rest:id='authors.au_id|100'>409-56-7008</au_id><au_lname rest:id='authors.au_lname|100'>Bennet</au_lname><city rest:id='authors.city|100'>Berkeley</city><contract rest:id='authors.contract|100'>1</contract><phone rest:id='authors.phone|100'>415 658-9932</phone><state rest:id='authors.state|100'>CA</state><zip rest:id='authors.zip|100'>94705</zip><toTitleauthor rest:id='authors.toTitleauthor|100'><array table ='titleauthor'><record rest:id='titleauthor|100'><_rowid rest:id='titleauthor._rowid|100'>100</_rowid><au_id rest:id='titleauthor.au_id|100'>409-56-7008</au_id><au_ord rest:id='titleauthor.au_ord|100'>1</au_ord><royaltyper rest:id='titleauthor.royaltyper|100'>60</royaltyper><title_id rest:id='titleauthor.title_id|100'>BU1032</title_id><toTitles rest:id='titleauthor.toTitles|100'><array table ='titles'><record rest:id='titles|200'><_rowid rest:id='titles._rowid|200'>200</_rowid><advance rest:id='titles.advance|200'>$5,000.00</advance><notes rest:id='titles.notes|200'>An overview of available database systems with emphasis on common business applications.  Illustrated.</notes><price rest:id='titles.price|200'>$19.99</price><pub_id rest:id='titles.pub_id|200'>1389</pub_id><pubdate rest:id='titles.pubdate|200'>Jun 12, 1985</pubdate><royalty rest:id='titles.royalty|200'>$10.00</royalty><title rest:id='titles.title|200'>The Busy Executive's Database Guide</title><title_id rest:id='titles.title_id|200'>BU1032</title_id><type rest:id='titles.type|200'>business</type><ytd_sales rest:id='titles.ytd_sales|200'>4095</ytd_sales></record></array></toTitles></record></array></toTitleauthor></record></array></item></sequence></response>

Providing Custom Authentication

In many cases you will want to password protect the URLs without having clients specify a database password. For this we recommend adding a "verify" attribute to your configuration and specify a stored procedure call which will authenticate access. Here is an example:

verify = call JSON_checkPassword('@[account]','@[pw]')

This stored procedure returns "1" for a successful login and "0" if the call should be denied.
In this case the URL call would be modified to look like this:

http://127.0.0.1/cgi-bin/OpenBase.cgi?p=demo_pubs_deep&search=am&account=myaccount&pw=mypassword

These values can also be provided using an XML POST. Custom authentication can be used with all three types of HTTP access.

TYPE#2 - PROCEDURE ACCESS

Another type of access is "procedure" access. This is where you pass in parameters to a call to a stored procedure. It in turn can modify the database or perform a select and return back results. In this case the user is passing information to the database through a controlled mechanism to restrict what the caller is able to do and see. While stored procedures cannot return a deep result tree, they can return result sets as well as perform complex business logic. That makes it a good choice for some types of access.

Here is an example of a stored procedure call:

access = procedure
use = pubs@127.0.0.1
login = admin
password = ""
action = execute
sql = call myprocedure('@[value]')

This is defined using the XML Setup application.
The URL to call this might look like this:

http://127.0.0.1/cgi-bin/OpenBase.cgi?p=demo_pubs_procedure&value=myvalue&out=xml

TYPE#3 - OPEN ACCESS

By "open access" we mean that you can use your HTTP server as a relay to an OpenBase database. You can force the caller to provide all authentication information or you can provide some of it and force the user to provide a database password or custom authentication using a verify attribute.
Here is an example of an open configuration that relays requests to the pubs database:

access = open
use = pubs@127.0.0.1
login = admin
password = ""

The open configuration can either authenticate the users each time they make a request or it can assign a session ID which can be used for subsequent requests by the same client.
Here is an example of a one-time request which assumes that the database login credentials is included in the profile.

<?xml version="1.0" encoding="UTF-8"?>
<openbase>
   <action>execute</action>
   <sql>select * from authors</sql>
</openbase>

You can also include the credentials in the XML post. Here is an example:

<?xml version="1.0" encoding="UTF-8"?>
<openbase>
   <login>admin</login>
   <password></password>
   <action>execute</action>
   <sql>select * from authors</sql>
   <out>xml</out >
</openbase>

Authenticating a Session ID

With client-server applications it is sometimes best to authenticate a client to avoid passing login information which each request. This can be done with the "connect" action. Upon successful authentication, the OpenBase XML/JSON interface responds with a random sessionID which can be used to identify subsequent requests.

Here is an example of authenticating a new session:

<?xml version="1.0" encoding="UTF-8"?>
<openbase>
   <use>pubs@127.0.0.1</use>
   <login>admin</login>
   <password></password>
   <action>connect</action>
   <out>xml</out>
</openbase>

Here is an example of the result:

<?xml version="1.0" encoding="UTF-8"?>
<response xmlns:rest='REST'>
    <openbase>
        <result>success</result>
        <sessionID>TcwB4f9X1g5J87K</sessionID>
    </openbase>
</response>

Here is an example that uses a sessionID.

<?xml version="1.0" encoding="UTF-8"?>
<openbase>
   <sessionID>TcwB4f9X1g5J87K</sessionID >
   <action>execute</action>
   <sql>select * from authors</sql>
</openbase>

You can execute any type of SQL using an open connection. The sessionID can be included either in the URL or inside <openbase> tags as shown above.

Updating the Database Using XML REST

The XML REST interface supported by OpenBase uses URL inputs or XML posts to specify information.

URL:  http://127.0.0.1/cgi-bin/OpenBase.cgi?p=contact?out=xml

The first example updates a bunch on columns in a single record. Here the rest:id needs to be set a string containing the table name and _rowid value separated with a vertical bar (for example, <record rest:id="contact|10"> ). This tells the server which record we are updating.

<?xml version="1.0" encoding="UTF-8"?>
<openbase>
   <sessionID>TcwB4f9X1g5J87K</sessionID >
</openbase>
<xml xmlns:rest="REST">
  <record rest:id="contact|10">
    <company>Bazzini Company, Inc.</company>
    <name>John Smith</name>
    <city>Bronx</city>
  </record>
</xml>

You can also update multiple records the same way:

<xml xmlns:rest="REST">

  <record rest:id="contact|10">
    <company>Bazzini Company, Inc.</company>
    <name>John Smith</name>
  </record>

  <record rest:id="contact|20">
    <company>Smith Company, Inc.</company>
    <name> Scott Smith </name>
  </record>

</xml>

You can also update columns in several records individually. This is done by using an an extended rest:id that includes the table, column and _rowid in this format: "table.column:_rowid" (<record rest:id="contact.firstname|10"> ).

Here is an example of updating an individual record using column identifiers:

<xml xmlns:rest="REST">
     <company rest:id="contact.firstname|10">Bazzini Company, Inc.</company>
        <name rest:id="contact.firstname|10">John Smith</name>
</xml>

Here is an example of updating columns in records from two different tables:

<xml xmlns:rest="REST">
    <record>
        <company rest:id="contact.firstname|10">Bazzini Company, Inc.</company>
        <name rest:id="contact.firstname|10">John Smith</name>
    </record>
    <record>
        <name rest:id="contact.firstname|20">Scott Smith</name>
        <company rest:id="contact.firstname|20">Smith Company, Inc.</company>
    </record>
    <record>
        <name rest:id="order.number|20">112211</name>
    </record>
</xml>

All XML updates happen inside a transaction so they follow an all or nothing rule.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License