As I continue my look at integrating MobileFirst and Ionic, today I'm going to look at the SQL Adapter. MobileFirst Adapters are server-side components that connect your hybrid mobile application to other things. Those "things" being generally broken down into a few categories, and a few specific adapter types. The SQL adapter is, as you can guess, a connection to a database.
So at this point you may be thinking - isn't that what I'd use ColdFusion, or Node, or heck, even PHP for? Sure - this is traditionally something an application server would handle. But MobileFirst lets you skip installing a complete application server where your needs may be met by a simpler adapter. If you've ever written server-side code that literally takes in a HTTP request, calls a simple SQL statement, and then just spits out JSON, then you really don't need a complete separate application server for that. The SQL adapter will handle that for you out of the box - and much easier.
Creating an adapter is simple. Inside a MobileFirst project, simply type mfp add adapter. You'll be prompted to enter a name and then select the type:
Select the one you want (in our case, SQL), and then just accept the default for the next question.
This will create two files under your adapters folder: MyAdapterTest.xml and MyAdapterTest-impl.js. Both the directory these are created under and the names themselves are based on the name of the adapter you chose. Let's first look at the XML file.
<?xml version="1.0" encoding="UTF-8"?>
<!--
Licensed Materials - Property of IBM
5725-I43 (C) Copyright IBM Corp. 2011, 2013. All Rights Reserved.
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
-->
<wl:adapter name="MyAdapterTest"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:wl="http://www.ibm.com/mfp/integration"
xmlns:sql="http://www.ibm.com/mfp/integration/sql">
<displayName>MyAdapterTest</displayName>
<description>MyAdapterTest</description>
<connectivity>
<connectionPolicy xsi:type="sql:SQLConnectionPolicy">
<!-- Example for using a JNDI data source, replace with actual data source name -->
<!-- <dataSourceJNDIName>java:/data-source-jndi-name</dataSourceJNDIName> -->
<!-- Example for using MySQL connector, do not forget to put the MySQL connector library in the project's lib folder -->
<dataSourceDefinition>
<driverClass>com.mysql.jdbc.Driver</driverClass>
<url>jdbc:mysql://localhost:3306/mydb</url>
<user>myUsername</user>
<password>myPassword</password>
</dataSourceDefinition>
</connectionPolicy>
</connectivity>
<!-- Replace this with appropriate procedures -->
<procedure name="procedure1"/>
<procedure name="procedure2"/>
</wl:adapter>That's a lot of markup, but you really only need to care about two parts. Under dataSourceDefinition, you can see a class, url, user and password defined. If using MySQL, you can leave the class alone, but do not forget to grab a copy of the MySQL Jar and copy it to your project! Modify the URL to point to your MySQL server and database. Finally, set an appropriate username and password.
At the bottom, note the procedure list. This is where you define the various different calls your mobile application is going to make. What those calls are obviously depends on your needs. Now let's look at the JavaScript file.
/*
* Licensed Materials - Property of IBM
* 5725-I43 (C) Copyright IBM Corp. 2011, 2013. All Rights Reserved.
* US Government Users Restricted Rights - Use, duplication or
* disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
*/
/************************************************************************
* Implementation code for procedure - 'procedure1'
*
*
* @return - invocationResult
*/
var procedure1Statement = WL.Server.createSQLStatement("select COLUMN1, COLUMN2 from TABLE1 where COLUMN3 = ?");
function procedure1(param) {
return WL.Server.invokeSQLStatement({
preparedStatement : procedure1Statement,
parameters : [param]
});
}
/************************************************************************
* Implementation code for procedure - 'procedure2'
*
*
* @return - invocationResult
*/
function procedure2(param) {
return WL.Server.invokeSQLStoredProcedure({
procedure : "storedProcedure2",
parameters : [param]
});
}Yeah, the first time I saw this, and realized I could write my adapters in JavaScript, I was all like -
To be clear, this isn't a Node-engine, I'll be talking more about working with JavaScript and MobileFirst tomorrow, but if you can write JavaScript, you can probably handle this just fine. You can see two examples in the default - calling a simple SQL statement (and with a bound parameter) and calling a stored procedure.
And that's really it. You do need to remember to build/deploy when you work on your adapter. When I worked on my demo, I kept one tab open for my adapters and one more my common (HTML, CSS, JS) code so I could build/deploy at will when I needed to.
For my application I created a simple table called "content" that had text entries, a bit like a blog. I wanted one procedure to list all the content and one to get all the columns for one entry. I began by creating two procedures in the XML:
<procedure name="getContent"/>
<procedure name="getDetail"/>Looking at it now, getContent feels a bit vague since it can mean one or more things, but you get the idea. Now let's look at the code.
var getAllStmt =
WL.Server.createSQLStatement("select id, title from content order by created desc");
var getDetailStmt =
WL.Server.createSQLStatement("select id, title, body, created from content where id = ?");
function getContent() {
return WL.Server.invokeSQLStatement({
preparedStatement : getAllStmt
});
}
function getDetail(id) {
WL.Logger.info("getDetail, requesting id "+id);
return WL.Server.invokeSQLStatement({
preparedStatement : getDetailStmt,
parameters:[id]
});
}So I assume this probably makes sense as is. I'm using the MobileFirst server-side API to create two SQL statements. Notice that the second one has a bound parameter. I then build my two functions with names matching the procedures in the XML. The first simply executes, and returns, all the rows, while the second uses a parameter to return one row.
At this point, even before I try using it in Ionic, I can test it from the command line using mfp invoke. When run, it first asks you to select an adapter, and then a procedure:
Select the procedure, optionally enter parameters, and you can then see the result right in your command prompt:
You definitely want to make use of this tool as it will be much quicker to debug any possible issues with your adapters here. Alright, now let's turn to the client-side. I'm assuming you've read my earlier posts about using Ionic with MobileFirst. I created a new application with the blank template and set up two simple screens. The first just shows a list of articles:
Clicking an item takes you to the detail page:
Ok, so how do we use it? I created a service for my Ionic application and wrapped calls to WL.Client.invokeProcedure. This library exists for you automatically in a hybrid application running in the MobileFirst platform. Here is the complete service:
angular.module('starter.services', [])
.factory('ContentService', function($q) {
function getAll() {
var data = {
adapter:"DatabaseAdapter",
procedure:"getContent"
};
var deferred = $q.defer();
WL.Client.invokeProcedure(data).then(function(res) {
deferred.resolve(res.invocationResult.resultSet);
}, function(bad) {
console.log("bad");
console.dir(bad);
deferred.reject("um something here");
});
return deferred.promise;
};
function getOne(id) {
var data = {
adapter:"DatabaseAdapter",
procedure:"getDetail",
parameters:[id]
};
var deferred = $q.defer();
WL.Client.invokeProcedure(data).then(function(res) {
console.log("good");console.dir(res.invocationResult);
deferred.resolve(res.invocationResult.resultSet[0]);
}, function(bad) {
console.log("bad");
console.dir(bad);
deferred.reject("um something here");
});
return deferred.promise;
}
return {
getAll:getAll,
getOne:getOne
};
});The calls to invokeProcedure return promises so I simply use a deferred to handle the result. Here is my controller:
angular.module('starter.controllers', [])
.controller('ListCtrl', function($scope, ContentService) {
console.log('ListCtrl loaded');
ContentService.getAll().then(function(results) {
console.log(results);
$scope.content = results;
});
})
.controller('DetailCtrl', function($scope, $stateParams, ContentService, $timeout) {
console.log('DetailCtrl loaded');
$scope.detail = {};
ContentService.getOne($stateParams.itemId).then(function(result) {
console.log("i got one result");
console.dir(result);
$scope.detail = result;
});
});Pretty simple, right? The result data is simply an array of plain JavaScript objects, or in the case of the detail view, one object. Using them in the view layer then is pretty trivial. Here is the list view:
<ion-view view-title="List">
<ion-content class="padding">
<ion-list class="list list-inset">
<ion-item ng-repeat="item in content" href="#/item/{{item.id}}">
{{item.title}}
</ion-item>
</ion-list>
</ion-content>
</ion-view>And the detail view:
<ion-view view-title="Detail">
<ion-content class="padding">
<div class="card">
<div class="item item-divider">
{{detail.title}}
</div>
<div class="item item-text-wrap">
<p>
{{detail.body}}
</p>
</div>
<div class="item item-divider">
{{detail.created}}
</div>
</div>
</ion-content>
</ion-view>For a video of this process, watch the embed below!