Creating database mobile application with ColdFusion Splendor

In my previous post, Simplify Mobile Application Development Using ColdFusion, I posted a video that showed how easy it is to create mobile application with CFMobile features of ColdFusion Splendor (Server) and ColdFusion Thunder (IDE). In that video I created a simple app called ‘Simple Expense Tracker’. But the code was not optimal, because the main purpose of the video was to show you features of CFMoible. I also mentioned that I will post a better example of the same app.

So In this post I will show you how to create a database application, where user interface code is separated from the data access code. I will also use JQuery for DOM access and Bootstrap for UI.

The application is called ‘Expense Tracker’. Here are a couple of screenshots of the application –

This application uses following features of CFMobile. Though tags and code may look familiar to CFML developers, notes that all the features work on the client side and once packaged, CF server does not come into play (because CFML is translated to JavaScript code).

Easy data access using cfquery/queryExecute
Client side OOP using cfcomponent
CFML Custom tags
Error handling using try-catch
Interoperability between cfclient code and JavaScript

Let’s see the main page, index.cfm –

<!DOCTYPE html>
<html>
	<head>
	    <meta name="viewport" content="width=device-width, initial-scale=1.0">
	    <link href="css/bootstrap.min.css" rel="stylesheet" ></link>
		<script src="js/jquery-2.0.3.min.js" ></script>
		<script src="js/bootstrap.min.js" ></script>

		<script src="js/app.js" ></script>
	</head>

	<body style="margin-left:5px;margin-right:5px">

		<nav class="navbar navbar-default navbar-static-top" role="navigation" style="vertical-align:central">
			<h4 style="float:left">Expense Tracker</h4>
			<button type="button" class="btn btn-default navbar-btn" id="deleteAllBtn" style="float:right">Delete All</button>
			<button type="button" class="btn btn-default navbar-btn" id="addBtn" style="float:right">Add</button>
		</nav>

		<div id="expenseListDiv">
		</div>

		<div class="modal fade" id="addDlg" tabindex="-1" role="dialog" aria-hidden="true">
			<div class="modal-dialog">
		    	<div class="modal-content">
		      		<div class="modal-header">
		        		<button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
		        		<h4 class="modal-title">Add Expense</h4>
		      		</div>
		      		<div class="modal-body">
		      			<table width="100%">
		      				<tr>
		      					<td>Date:</td>
		      					<td><input type="date" id="dateTxt"></td>
		      				</tr>
		      				<tr>
		      					<td>Amount:</td>
		      					<td><input type="text" id="amtTxt"></td>
		      				</tr>
		      				<tr>
		      					<td>Desc:</td>
		      					<td><input type="text" id="descTxt"></td>
		      				</tr>
		      			</table>
		      		</div>
		      		<div class="modal-footer">
		        		<button type="button" class="btn btn-default" data-dismiss="modal">Cancel</button>
		        		<button type="button" class="btn btn-primary" id="saveBtn">Save</button>
		      		</div>
		   		</div>
		  	</div><!-- /.modal-dialog -->
		</div>
	</body>
</html>

<cfclient>

	<cftry>

		<cfset expMgr = new cfc.ExpenseManager()>
		<cfset expenses = expMgr.getExpenses()>

		<cfcatch type="any" name="e">
			<cfset alert(e.message)>
		</cfcatch>
	</cftry>

	 <cf_expenseList parentDiv="expenseListDiv" expenses=#expenses# action="displayAll">

	<cffunction name="displayAddExpenseDlg" >
		<cfset $("##addDlg").modal() >
	</cffunction>

	<cffunction name="saveExpense" >

		<cfscript>
			var dateStr = trim($("##dateTxt").val());
			var amtStr = trim($("##amtTxt").val());

			if (dateStr == "" || amtStr == "")
			{
				alert("Date and amount are required");
				return;
			}

			if (!isNumeric(amtStr))
			{
				alert("Invalid amount");
				return;
			}

			var amt = Number(amtStr);
			var tmpDate = new Date(dateStr);
			var desc = trim($("##descTxt").val());

			var expVO = new cfc.ExpenseVO(tmpDate.getTime(),amt,desc);
			var expAdded = false;

			try
			{
				expMgr.addExpense(expVO);
				expAdded = true;
			}
			catch (any e)
			{
				alert("Error : " + e.message);
				return;
			}
		</cfscript>

		<cfset $("##addDlg").modal("hide") >

		<cfif expAdded eq true>
	 		<cf_expenseList parentDiv="expenseListDiv" expenses=#expVO# action="append">
		</cfif>

	</cffunction>

	<cffunction name="deleteAll" >

		<cfscript>
			if (!confirm("Are you sure you want to delete all?"))
				return;

			try
			{
				expMgr.deleteAllExpenses();
			}
			catch (any e)
			{
				alert("Error : " + e.message);
				return;
			}
		</cfscript>

 		<cf_expenseList parentDiv="expenseListDiv" action="removeAll">

	</cffunction>
</cfclient>

From line# 5-7 we include Bootstrap and JQuery files. We also include our application specific JS file (app.js) on line# 9. Then from line# 12-54 we create HTML user interface, which include top navigation bar, div to contain expense list and modal dialog for getting inputs for adding new expense item.

<cfclient> block starts from line# 56 till the end of the file. Note that you could move this content to another cfm file and use cfinclude (in cfclient) in index.cfm to include that content.

On line# 60 we create an instance of ExpenseManager CFC and then call getExpenses method on it. This method returns array of ExpenseVO CFCs. On line# 68, we call a custom tag <cf_expenseList> and pass a few attributes, including id of the div in which to show expense list and array of ExpenseVO objects that we got above. Then we have UI handler functions – displayAddExpenseDlg, saveExpense and deleteAll.

Here is the code in ExpenseManager.cfc –

component client="true"
{
	this.dsn = "expense_db";

	function init()
	{
		var dbCreated = localStorage.dbCreated;
		if (!isDefined("dbCreated"))
			localStorage.dbCreated = false;

		if (!dbCreated)
		{
			try
			{
				createTable();
				localStorage.dbCreated = true;
			}
			catch (any e)
			{
				alert("Error : " + e.message);
			}
		}
	}

	function createTable()
	{
		queryExecute(
			"create table if not exists expense (id integer primary key, 
			expense_date integer, amount real, desc text)",
			[],
			{"datasource":this.dsn});
	}

	function addExpense (expVO)
	{
		queryExecute(
			"insert into expense (expense_date,amount,desc) values(?,?,?)",
			[expVO.expenseDate,expVO.amount,expVO.description],
			{"datasource":this.dsn}
		);

		//get auto generated id
		queryExecute(
			"select max(id) maxId from expense",
			[],
			{"datasource":this.dsn, "name":"rs"}
		);

		expVO.id = rs.maxId[1];
	}

	function getExpenses()
	{
		queryExecute("select * from expense order by expense_date desc",
			[],
			{"datasource":this.dsn, "name":"rs"});

		var result = [];
		if (rs.length == 0)
			return result;

		for (i = 1; i <= rs.length; i++)
		{
			var expVO = new ExpenseVO();
			expVO.id = rs.id[i];
			expVO.expenseDate = rs.expense_date[i];
			expVO.amount = rs.amount[i];
			expVO.description = rs.desc[i];
			result.append(expVO);
		}

		return result;
	}

	function deleteAllExpenses()
	{
		queryExecute("delete from expense",[],{"datasource":this.dsn});
	}
}

CFC is written completely in CFScript syntax. Note that this CFC is marked as client side CFC with attribute client=”true”.
In the constructor of this CFC (init method), we create database table, if it is not already created. Notice new query function, queryExecute, in the createTable function on line#27. This function takes sql statement as the first argument, array of parameter values as the second argument and then key-value pairs of attributes, that you would use in cfquery tag, as the third argument. getExpenses function returns array of ExpenseVO objects. This is how ExpneseVO CFC looks –

component client="true"
{
	this.id;
	this.expenseDate;
	this.amount;
	this.description;

	function init(expDate, amt, desc)
	{
		this.expenseDate = expDate;
		this.amount = amt;
		this.description = desc;
	}
}

Now let’s look at the custom tag expenseList.cfm, remember this custom tag is used in index.cfm to display list of expenses –

<cfclient>
	<!--- Handle only tag start --->
	<cfif thisTag.executionMode eq "start">
		<cfset processTagStart()>
	</cfif>

	<cffunction name="processTagStart" >

		<cfset variables.action = "">

		<cfif not structKeyExists(attributes,"parentDiv") or attributes.parentDiv eq "">
			<cfreturn >
		</cfif>

		<cfif structKeyExists(attributes,"action")>
			<cfset variables.action = attributes.action>
		</cfif>

		<cfif structKeyExists(attributes,"expenses")>
			<!--- 
				Displays list of expenses in a given div element
				If attribute.expenses is an array then create HTML table and display in the parent DIV
				If attribute.expenses is not an array then assume it to be ExpenseVO and append to 
					the existing table
			 --->

			<cfif variables.action eq "displayAll" and isArray(attributes.expenses)>
				<cfset displayExpensesArray()>
				<cfreturn >
			<cfelseif variables.action eq "append" >
				<cfset appendExpense()>
				<cfreturn >
			</cfif>
		</cfif>

		<cfif variables.action eq "removeAll">
			<cfset removeAllExpenses()>
		</cfif>
	</cffunction>

	<!--- Display array of expenseVO objects --->
	<cffunction name="displayExpensesArray" >
		<cfset var expenses = attributes.expenses>
		<cfset var parentDiv = attributes.parentDiv>

		<cfif expenses.length eq 0>
			<!--- No expneses added to database yet --->
			<cfset $("##" + parentDiv).append("<b>No expenses found</b>")>
			<cfreturn >
		</cfif>

		<!--- Create table --->
		<cfset $("##" + parentDiv).children().remove()>
		<cfset tblEml = $(createHTMLtable()).appendTo("##" + parentDiv)>

		<!--- Add table rows --->
		<cfloop array="#expenses#" index="expense">
			<cfset $(tblEml).append(createTableRow(expense))>
		</cfloop>

	</cffunction>

	<!--- Append one expense item to the existing list --->
	<cffunction name="appendExpense" >

		<cfset var expenseVO = attributes.expenses>
		<cfset var parentDiv = attributes.parentDiv>

		<!---First check if we need to create the table --->
		<cfset tblElm = $("##" + parentDiv + " table")>
		<cfif tblElm.length eq 0>
			<!--- Table does not exist. Create one --->
			<cfset $("##" + parentDiv).children().remove()>
			<cfset tblElm = $(createHTMLtable()).appendTo("##" + parentDiv)>
		</cfif>

		<!--- append expenseVO --->
		<cfset $(tblElm).append(createTableRow(expenseVO))>
	</cffunction>

	<!--- Create HTML text for displaying expnese in a table row --->
	<cffunction name="createTableRow" >
		<cfargument name="expenseVO" >

		<cfoutput >
			<cfsavecontent variable="tmpStr" >
				<tr>
					<td>#dateToStr(expenseVO.expenseDate)#</td>
					<td>#expenseVO.amount#</td>
					<td>#expenseVO.description#</td>
				</tr>
			</cfsavecontent>
		</cfoutput>

		<cfreturn tmpStr>
	</cffunction>

	<!--- removes all expense rows from the table --->
	<cffunction name="removeAllExpenses" >
		<cfset var parentDiv = attributes.parentDiv>
		<cfset $("##" + parentDiv).children().remove()>
		<cfset $("##" + parentDiv).append("<b>No expenses found</b>")>
	</cffunction>

	<!--- Creates HTML table to display expenses --->
	<cffunction name="createHTMLtable" >
		<cfsavecontent variable="tmpStr">
			<table width="100%">
				<tr>
					<th>Date</th>
					<th>Amount</th>
					<th>Description</th>
				</tr>
			</table>
		</cfsavecontent>

		<cfreturn tmpStr>
	</cffunction>

	<!--- Converts date in milliseconds to local string --->
	<cffunction name="dateToStr" >
		<cfargument name="dateAsNumber" type="numeric" required="true" >
		<cfset tmpDate = new Date(dateAsNumber)>
		<cfreturn dateFormat(tmpDate,"mm/dd/yyyy")>
	</cffunction>
</cfclient>

This custom tag takes three attributes – 1. parentDiv : is the id of div element in which you want to display list of expenses. 2. action : valid values are displayAll, append and removeAll. displayAll action removed all expenses in the div before adding new expenses. append just adds to existing list. 3. expenses : is an array of ExpneseVO objects to display in the list. Notice use of cfsaveconent tag on line# 86 and 107. This tag makes creating HTML content with dynamic values very easy, otherwise you would create the content by appending strings. Another advantage of using cfsavecontent is that you get code assist for all the tags within cfsaveconent in the ColdFusion Builder.

Lastly, here is our app specific JS file app.js. It really does not have much content-

$(document).ready(function(){
	$(document).on("click","#addBtn", displayAddExpenseDlg);
	$(document).on("click","#saveBtn", saveExpense);
	$(document).on("click", "#deleteAllBtn", deleteAll);
});

Note that handler functions displayAddExpenseDlg, saveExpense and deleteAll are declared in index.cfm in cfclient block.

You can download the entire project . You can also download the Android APK file, install it and see how it works.

-Ram Kulkarni

31 Replies to “Creating database mobile application with ColdFusion Splendor”

Andrew Scott says:

February 25, 2014 at 1:14 pm

The only thing apart from showing this example, is the term ValueObject is actually not the correct term. Java defines objects that contain many value objects or objects to be classed as TransferObjects.

Why does the ColdFusion world still consider a TransferObject as a ValueObject?

Reply
1. Ram Kulkarni says:
  
  February 25, 2014 at 1:21 pm
  
  When I called ExpenseVO CFC a ValueObject, I was not speaking for the “ColdFusion world”. But you could be right about TransferObject being more appropriate in this case.
  
  Reply
  1. Andrew Scott says:
    
    February 25, 2014 at 2:04 pm
    
    Cheers Ram, I am not having a go, except to point out that there is enough confusion in the ColdFusion world with objects without adding more. Hope you understand.
    
    Reply
    1. Ram Kulkarni says:
      
      February 25, 2014 at 3:08 pm
      
      Sure, I do undersand you point.
      
      Reply
Andrew Scott says:

February 25, 2014 at 2:08 pm

How does the SQL work? Is it compiled run and delivered to the client or is a RPC made to the server to retrieve this data. If the latter is true, I think as a community we will need to make the developers aware of the amount of data that is going over the pipe. Which for Mobile users is going to be a huge bottleneck for rouge applications.

Reply
1. Ram Kulkarni says:
  
  February 25, 2014 at 3:07 pm
  
  For this particular app, there is no interaction with the server once it is packaged and running on a device. SQL statements in cfclient block are executed on the client side. in browser. It uses browser’s SQLite database. So to be very clear, in this app SQL statements are not executed on the server and there is no client-server communication.
  However you can build client-server applications using cfclient very easily and I would probably cover that in a future blog post.
  
  Reply
  1. Andrew Scott says:
    
    February 25, 2014 at 3:18 pm
    
    Cheers for the clarification, I thought that this was the case. I only hope future developers, look at the data being transmitted and try to keep that to a minimum, but otherwise I am interested to see what you have done here with cfclient. Still yet to be convinced this is the right approach…
    
    Reply
Sean Coyne says:

February 25, 2014 at 7:19 pm

Things like and still just seem very wrong to me. I cannot see how this is a good idea.

Reply
1. Sean Coyne says:
  
  February 25, 2014 at 7:22 pm
  
  Damn. stripped out the code. It was the alert() and $(…).hide() calls in a cfset to be clear.
  
  Reply
  1. Ram Kulkarni says:
    
    February 25, 2014 at 7:41 pm
    
    You don’t have to use these statements in cfclient, you can still have then in a separate JavaScript block. But since everything finally gets translated to JavaScript, it hardly makes any difference.
    
    Reply
Sean Coyne says:

February 25, 2014 at 7:54 pm

Ram, those were just examples. The fact that ANY javascript is written in a cfset is just… bad. The fact that it cross compiles to Javascript is bad too.

It is a wrong solution to a problem that doesn’t exist. There is already a perfect solution for this “problem”. Just write it in Javascript. I know you are going to reply by saying “async javascript is difficult, etc”. That is not a valid argument. If you are web developer you need to learn Javascript. Period. It is no longer an option. If you find learning the asynchronous nature of javascript difficult to learn, especially with the assistance of libraries like jQuery then you probably shouldn’t be a web developer.

Given the continual flow of questions to StackOverflow and other places where people are profoundly confused about server side code vs client side code I see this as only furthering that confusion.

Catering to the lowest common denominator should not be a goal of the ColdFusion product. I realize that is how CF started. When CF first came about web development was a new field. Now, however, it is a robust, mature environment. Cater to this new market. Provide better tooling, more developer friendly features, etc. Don’t focus on solving problems that don’t exist.

Reply
1. Ram Kulkarni says:
  
  February 25, 2014 at 8:02 pm
  
  No, I am not going to justify anything about this feature now. I tried to do that In my post ColdFusion mobile features are not just about cfclient, but it is necessary, before CFMobile features where public. Now that they are available publicly, users can try it and decide if they want to use it. And if you don’t want to try it just by looking at method call, that is also fine. After all not all features of a product are liked by all its users.
  But I will keep posing examples of how these features make mobile development easy.
  
  Reply
Raymond Camden says:

February 25, 2014 at 8:55 pm

Where is the deviceready listener? All PG/Cordova apps (for the most part) should be using it as opposed to document.ready.

Reply
1. Ram Kulkarni says:
  
  February 25, 2014 at 9:31 pm
  
  In this example it is not necessary to wait for deviceready event because client side storage APIs are not implemented by Cordova but made available by the Webkit browser.
  In any case you don’t need to handle deviceready event in cfclient because it does it for you when you call –
  
  All cfclient code below this will be executed only after device is ready. My next post is going to be about taking a picture from your mobile and uploading to CF server, in which I will be using this setting.
  
  Reply
jasonnokes says:

March 4, 2014 at 8:01 am

When I have cfclient and cfclientsettings on the same page, I get the error: Uncaught cfclient is not initialized properly. When I comment out cfclientsettings, the code inside cfclient will work. And, I had to change the reference to “cordova.js” in dnfimain.js to “cordova-2.0.0.js” to get cfclient to work.

Reply
1. Ram Kulkarni says:
  
  March 4, 2014 at 8:31 am
  
  cfclientsettings should be before cfclient block. I am assuming you are testing this in Shell app or desktop browser. If yes, then make sure you have Application.cfm in the root of the project and you include /CFIDE/cfclient/useragent.cfm in it.
  You need not make any changes to dnfimain.js. You might see error in JS console about loading cordova.js but you can ignore it.
  Once cfclient framework knows if client is Android or iOS it loads correct cordova.js. And useragent.cfm in Application.cfm helps to determine type of mobile OS. You do not need to include Appliction.cfm when packaging the application.
  
  Reply
jasonnokes says:

March 5, 2014 at 9:27 am

When I add application.cfm with the useragent.cfm include and uncomment cfclientsettings, my data call fails. I’m trying a simple remote cfc call that selects data from SQL Server. When I comment out cfclientsettings again, the data call works.

Reply
1. Ram Kulkarni says:
  
  March 5, 2014 at 9:59 am
  
  It would be difficult to tell why data call does not work unless I see the code. But I would like to clarify a few things (you may already know about them, though) –
  1. The example in this post creates/accesses local database on the mobile. Data access APIs in cfclient are not meant to access remote databases
  2. Data access APIs in cfclient do not need cfclientsettings, unless you use other device APIs in the same application like Camera, Contact etc.
  
  I do have an example of how to get data from a remote server in a mobile application using cfclient. I will blog about it soon.
  
  Reply
jasonnokes says:

March 6, 2014 at 4:51 am

I’ve got number 1 working without the call to cfclientsettings. Once I put that into the main file I do not get data back from the database. This is the same for both a browser and a packaged apk file. I plan on using both remote CFC calls and device APIs so I’d like to get this figured out.

Reply
1. Ram Kulkarni says:
  
  March 6, 2014 at 7:46 am
  
  When you enable device apis in cfclientsettings, the page will not work in a browser because PhoneGap events are not fired. You will have to use Ripple emulator (Chrome extension) if you want to test device APIs in a browser. Make sure you have Application.cfm with useragent.cfm included in it when you test in Ripple. Use cordova version 2.0.0 in Ripple.
  Packaged APK should work. Do not include application.cfm when packaging.
  I have posted two examples that use device APIs in CFMobile Example – Taking picture and uploading to ColdFusion server and CFMobile Example – Using Geolocation APIs in ColdFusion Splendor.
  
  Reply
shigemii says:

March 26, 2014 at 1:47 pm

I’m also facing the same issue. It seems that the problem is related to the phonegap version.

By default, ColdFusion Thunder beta is set to 3.1.0. (> Properties for mobile project > ColdFusion Mobile Project > PhoneGap > preference > phonegap-version ).

I changed it to 2.9.0 , it works correctly.

Reply
Jason Nokes says:

July 3, 2014 at 7:58 am

I installed the production version of CF11 and Builder 3 and I cannot get the apps that worked on the beta 11 version to work. I’ve tried everything I can think of and the app works fine via the URL. Do you have any suggestions?

Reply
1. Ram Kulkarni says:
  
  July 3, 2014 at 8:49 am
  
  Are you saying the same demo app that I explained in the post does not work as packaged app? If you are working on a different app that uses some of the device APIs then I would suggest that you make sure you have selected required device features in ColdFusion Builder project preferences. Go to ‘ColdFusion Mobile Project’ preference page->PhoneGap->New (button)->Feature. Check features that you have used in you app in this page.
  
  Reply
  1. Jason Nokes says:
    
    July 4, 2014 at 12:14 am
    
    No, it was a simple test app I wrote that did a read/write from the database. It will build the app and display static text, but the call to the database fails.
    
    Reply
    1. Ram Kulkarni says:
      
      July 4, 2014 at 9:09 am
      
      If you are accessing remote database then you need to update cfclient_main.js file on your server. See comments in this post.
      
      Reply
      1. Jason Nokes says:
        
        July 4, 2014 at 8:03 pm
        
        I downloaded the cfclient_main.js file, restarted CF11, rebuilt my app, but still get the same result: no data from database. Anything else I can try?
      2. Ram Kulkarni says:
        
        July 7, 2014 at 9:53 am
        
        If you are accessing remote database, did you set Application Base URL in the project preferences->ColdFusion Mobile Project-> Miscellaneous? Example of application base URL is http://domain_name:port/%5Bcontext_root%5D. Specify context_root only if your server is set up with a context root.
      3. Jason Nokes says:
        
        July 7, 2014 at 7:22 pm
        
        I had a context root in the application base URL, so I took it out and now the test app works. I will test the real app tonight. Thanks for your help!
shirish68 says:

March 31, 2015 at 3:57 pm

Hi Ram, The Attache Receipt function throws an error. Any Suggestion?

Reply
1. Ram Kulkarni says:
  
  March 31, 2015 at 4:11 pm
  
  My guess is that the latest CF 11 updates might have broken the application. I don’t work for Adobe anymore and have not kept track of what has changed in CF 11. So hard to tell how to fix the error.
  
  Reply
epal says:

October 5, 2018 at 4:24 am

hi, when i run db_scripts.cfm, it get an error ” Error Executing Database Query.
Failed to create database ‘C:/ColdFusionBuilder2018/ColdFusion/cfusion/db/EmployeeDS’, see the next exception for details. ”
i don’t understand. why this happen?

Reply