Wednesday, September 8, 2010

Advantage Web API

Currently, data stored in the Advantage Database Server can only be accessed via an Advantage client interface of some sort (Delphi components, .NET Data Provider, PHP driver, etc.). All of these interfaces are built on top of the Advantage Client Engine (ACE), and ACE only supports two platforms; Windows and Linux.

The easiest way to consume your data from other platforms is to expose that data via a web service. Depending on your experience and familiarity with web services, this can be a trivial task, or a pain point. There are many choices to be made at a high level (IIS, Apache, Ruby, Python, ASP.NET, PHP, etc.) and once those decisions are made there are even more at lower levels (framework decisions, REST vs RPC, etc.).

Once implemented, these technologies work well with Advantage, but have introduced a variety of moving parts that your team must now understand and maintain. In addition, the requirement for these technologies as a foundation has increased both your development time and the scope of your project. For many teams this is a manageable solution, but for others this entry barrier prevents projects from ever getting off the ground.


A Built-In Solution

We want Advantage users to have a turnkey option to retrieve their data via a web service. You can still obviously build your own, but for teams that would like a jump start, or would like to investigate web interfaces without the overhead or commitment, we will be distributing a pre-built web server and web service that provides access to your existing Advantage data. 


The Technology

The web service is implemented as an Apache module that will be loaded by a stand-alone instance of Apache. This web service can also be installed into an existing Apache installation, but by default will install it’s own minimal copy of Apache.

The web service is a RESTful API that returns data using a format very similar to the oData specification. The initial implementation will return JSON data. XML is a possibility for future implementations, but will not be part of the initial beta.

Every modern development environment I know of provides the ability to make an HTTP call and consume a web service. This means you will have access to your Advantage data from literally any programming language and on any device platform.

Configuration will be VERY simple. Add the path to the database you want to expose to a configuration file, and that’s it. You will immediately be able to securely (via HTTPS) request and update data from your Advantage server via web URI’s.


Consumption Examples

I’ve included a few examples below of this web service retrieving a set of records. These examples are by no means complete, but provide a quick glimpse at the consumption of this API on a variety of platforms and devices.


Web Browser

The easiest way to test the service is to simply enter a URI into your browser.

For example, entering the following URI into your browser after installing the server:


would return:




Consumption from the .NET framework is accomplished using the built in WebClient class in combination with the JSON.NET library for JSON serialization and de-serialization:

   1: WebClient w = new WebClient();
   3: w.Headers.Add( "Authorization", "Basic " + 
   4:    Convert.ToBase64String( Encoding.ASCII.GetBytes( "adssys:" ) ) );
   5: w.Encoding = Encoding.UTF8;
   6: string result = w.DownloadString( 
   7:    "https://myserver:6282/adsweb/v1/test_db/tables/orders" );
   9: sr = new StringReader( result );
  10: olist = (oDataResults<Orders>)serializer.Deserialize( new JsonTextReader( sr ), 
  11:                                                       typeof( oDataResults<Orders> ) );
  13: // Results are now ready to use in an object list
  14: if ( olist.d.results.Count > 0 )
  15:    MessageBox.Show( string.Format( "First order ID is {0}", 
  16:                     olist.d.results[0].id.ToString() ) );


iPhone/iPad (Objective-C)

Consumption in Objective-C is accomplished using the URLWithString constructor and the TouchJSON library:

   1: NSString *jsonString = [NSString stringWithContentsOfURL:
   2:    [NSURL URLWithString: @"https://myserver:6268/adsweb/v1/test_db/tables/orders"]
   3:           encoding: NSUTF8StringEncoding error:&error];
   5: // deserialize
   6: NSData *jsonData = [jsonString dataUsingEncoding:NSUTF32BigEndianStringEncoding];
   7: NSDictionary *dictionary = 
   8:    [[CJSONDeserializer deserializer] deserializeAsDictionary:jsonData error:&error];
  10: // save reference to the rows
  11: _rows = [[NSMutableArray alloc] initWithArray:[dictionary objectForKey:@"d"]];
  12: [_rows retain];


Android (Java)

Consumption in Java is accomplished using the WebClient class and the Gson library:

   1: WebClient ordersClient = 
   2:    new WebClient( "https://myserver:6282/adsweb/v1/test_db/tables/orders" );
   4: String jsonOrders = ordersClient.GetData( "total>50000" )
   6: Gson gson = new Gson();
   8: OrdersListWrapper ordersList = gson.fromJson( jsonOrders, OrdersListWrapper.class );
  10: // The deserialized Results are now contained in the "d" object array.
  11: if ( ordersList.d.Count > 0 ) {
  12:    TextView tvResults = (TextView)mainView.findViewById( );
  13:    tvResults.setText( "The First Order ID is " + 
  14:                       (new Integer( ordersList.d[0].id ).toString() ));
  15:    }



The following example consumes the API using Javascript in a browser, and uses the JQuery framework to quickly manipulate the JSON data and present a table:

   1: <script language="javascript">
   3:   $.getJSON('https://myserver:6282/adsweb/v1/test_db/tables/orders',
   4:   function(data, status){
   5:     $.each( data.d, function(i, item) {
   6:      $('#res').append( '<li>Order ' + item.OrderNo + ' : $' + item.AmountPaid + '</li>' );
   7:     } );
   9:  });
  10: </script>
  12: <ul>
  13:   <div id=res>
  14:   </div>
  15: </ul>



The following example consumes the API using PHP, and uses the CURL extension and built in json_decode function:

   1: $session = 
   2:   curl_init("https://localhost:6282/adsweb/pupdir/v1/query/select%20*%20from%20test1");
   4: // configure CURL to trust our server with self-signed cert
   5: curl_setopt($session, CURLOPT_SSL_VERIFYPEER, false);
   6: // set db password
   7: curl_setopt($session, CURLOPT_USERPWD, "adssys:");
   8: // setup curl_exec to return the data instead of printing it
   9: curl_setopt($session, CURLOPT_RETURNTRANSFER, true);
  10: // set headers
  11: curl_setopt($session, CURLOPT_HTTPHEADER, 
  12:   array ( "Accept: application/json; charset=utf-8" ) );
  14: // get the data
  15: $response = curl_exec($session);
  16: curl_close($session);
  18: // decode the json into an object
  19: if ( ! $dataset = json_decode( $response ) )
  20:   trigger_error( json_last_error() );
  22: // spit out a simple table (normally would use a view, but not in small example)
  23: print( "<table><tr><hr><th>Lastname</th><th>Firstname</th></tr>" );
  24: foreach ( $dataset->d->results as $row )
  25:   print( "<tr><td>$row->LASTNAME</td><td>$row->FIRSTNAME</td></tr>" );
  26: print( "</table>" );


Beta Testing and Discussion

The beta program is underway and we are ready for testers to try the new web API. If you would like to be included in the beta process, please register here and we will email you download and documentation links.

In addition, you can visit the Advantage.webapi newsgroup for Advantage Web API discussions and updates. If you can not access NNTP newsgroups, feel free to email me (Jeremy.Mullin at with any feedback you may have.


Anonymous said...

This sounds great! One question however comes to mind: if the data format is very similar to oData, wouldnt it be an advantage to be fully oData compatible, so that existing client libraries can be used?

J.D. Mullin said...

That was the original plan, and given enough time, energy, and resources, it may happen. For now we are going with a subset so we can quickly get people using it, instead of spending time making it implement the entire spec.

Also, if you check out the oData spec, it actually says it is designed to be implemented as portions: "OData is designed to be modular such that an OData implementation needs to implement only as much of an OData specification as required for its target scenario."

Unknown said...

Looks very interesting! Any idea when the beta release will be available?

I just registered for the beta and received the following message: "Thank you for registering. We will contact you when the beta is available..."

Thanks in advance.

J.D. Mullin said...

@rhazell, the beta has been available for a while, I sent you an e-mail this morning with download and documentation details. Thanks for your interest!

Anonymous said...

Looks very interesting - in your examples you do not have any code for VBScript. Is that because it is not available via VBScript?

J.D. Mullin said...

No reason, just couldn't include every language that can call a web service. :)

Post a Comment