In this blog I am going to describe how to create a new Data Service using Bindaas. For the purpose of this tutorial I will assume that Bindaas is running locally on port 9099 in unsecured mode.

Identify Data Source and create a Profile

Formulate a Query to fetch data

Identify attributes from the Query that could be queried upon and create a Query Template

Assign the Query Template to a RESTful URL

Execute the RESTful URL corresponding to the query by supplying parameters in the request

Identify Data Source and create a Profile

The first step in creating any Data Service is identifying the source of Data . Your data could be anywhere :

Relational database like MySql , Oracle , DB2, Postgres , etc.

XML database like DB2 , Derby , etc .

NoSQL or document based databases like MongoDB , CouchDB , etc

Spreadsheets

Other webservices , etc.

How you fetch your data really depends on the data format , data source and the query language. Lets assume our data is stored in a IBM DB2 Database in the EMPLOYEE table described below

Employee ID

Employee Name

Annual Salary

Title

Phone Number

1

Joe

45,000

Sales Executive

233-234-3345

2

Anna

101,000

Manager

233-344-4445

3

Graham

56,000

Sales Executive

233-344-4444

4

William

76,000

HR Admin

233-344-4443

5

Smith

50,000

Technician

233-344-4442

6

Sally

80,000

Manager

233-344-4492

7

Duve

78,000

Technician

233-344-4432

8

Anand

45,000

Sales Executive

233-344-4462

9

Jake

60,000

HR Admin

233-344-4422

Having identified the data source , we need to create a Profile on Bindaas that will allow it connect to the DB2 database and establish an association. The REST API described in this blog is documented in the following format

HTTP [METHOD] [URL]

[FORM PARAMETER NAME] = [FORM PARAMETER VALUE]

The JSON object describes all the ingredients that make up a Profile

Property

Description

profileName

Unique name for this profile. This name will be referenced by the Query Template

dataSrcName

Describes the type of database we are using as our data source. The name is case-sensitive. The list of all supported data sources can be found on project homepage

dataSrcConfig.url

This is the JDBC URL used to connect to the DB2 instance

dataSrcConfig.username

DB username

dataSrcConfig.password

DB password

Formulate a Query to fetch you data

Next , we want to extract the data from Employee Table . Let's say we have the following use cases

Fetch all records , filtering data on Title having Salary more than some user specified value

Here $TITLE$ and $SALARY$ are user supplied values. They will become part of the RESTful requests in form of query parameters.

Fetch the Name of Employee having highest Salary

In this example there are no query attributes.

Fetch all records , filtering data on Columns specified by the user . If no filter specified then select all data

Identify attributes from the Query that could be queried upon and create a Query Template

A Query Template is as the name indicates , template to describe a Query along with all query parameters , optionality and description. Following illustrates Query Template created for each use case that we described in the previous section

Fetch all records , filtering data on Title having Salary more than some user specified value

Query Template

Fetch the Name of Employee having highest Salary

Query Template

Notice there are no bindVariables for this Query Template.

Fetch all records , filtering data on Columns specified by the user . If no filter specified then select all data

Query Template

Notice that bindVariables.required = "N" and bindVariables.defaultValue = "" . When the bindVariable is optional , a default value must be specified. When the RESTful call is made , if the user does not specify an optional query parameter , default value specified here will be used to fulfill the request

Query Template property description

Property

Description

queryName

Unique name for this query template. This name will become part of the RESTful URL

profile

Refers to the Profile we created in Step 1

outputFormat

Describes the format in which we would like to format the results of the query. For relational databases output formats like CSV is one option. Each data source has its own list of compatible output formats. In this example the output format we choose is CSV (db2csv)

List of available data sources and supported output formats is available on project homepage.

queryTemplate

This is a template that describes the query to be executed except , place holders qualified by '$' are specified to describe the variables in the query. These variables are called 'bindVariables' . A Query Template is independent of the underlying query language of the data source.

Thus a Query Template can be constructed from a SQL , XPath , XQuery or any other query language in pretty much the same way. All bindVariables in the query must be prefixed and suffixed by '$' . For example :

$EMPLOYEE_NAME$

bindVariables.name

The name of bind variable. This name MUST match with the name used in the Query Template.

bindVariables.required

Allowed values are 'Y' or 'N' .

bindVariables.description

Description of this bind variable

bindVariables.defaultValue

In case when required='N' , defaultValue is used to fulfill the request

Assign the Query Template to a RESTful URL

So far we have seen how to create a Query Template JSON request object . We need to send this information over to Bindaas

Bindaas will do all necessary pluming to make this query accessible via a RESTful URL .

Execute the RESTful URL corresponding to the Query

Fetch all records , filtering data on Title having Salary more than some user specified value