Link Adobe Flex 3.5 Chart to ColdFusion webservice

As promised, here is a (not very) short blog about how to get Adobe Flex 3.5 to consume a coldfusion cfc based webservice and display it as a chart.

As mentioned in my previous post it's EPIC that Adobe decided to put charting in the SDK. I use the SDK (coz I'm poor) and with it and a little patience you can achieve some amazing Flex apps.

So to do this you'll need the Flex 3.5 SDK, you'll need the Flex data visualization pack and obviously you'll need coldFusion.

I'm sorry for the code here, Google blogger doesn't make posting code easy.

1)The cfc:

<cfcomponent>

 <cffunction name="getPersonByDept" access="remote" returntype="query" output="false">
  <cfargument name="deptname1" type="string" required="true" />
  <cfargument name="deptname2" type="string" required="true" />
  <cfargument name="deptname3" type="string" required="true" />
  <cfset var myQuery = queryNew('counttt,deptName') />
  
  <cfquery name="myQuery" datasource="jayLocal">
   select count(personid) * 100  as [counttt], [deptName]
   from [person]
   group by [deptName]
   having 
    [deptName] = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.deptname1#" />
    or [deptName] = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.deptname2#" />
    or [deptName] = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.deptname3#" />
  </cfquery>

  <cfreturn myQuery />
 </cffunction>

</cfcomponent>

I know, I know, the function isn't great, i don't need to pass in 3 separate params, but this is just for illustration purposes. Take this cfc and save it as latestone.cfc in your coldfusion webroot or in a webservices folder. Whatever you fancy. You're going to want to be careful of Application.cfc because it can eat your webservices. You should be able to then open up the wsdl in your browser.
http://localhost/webserrvices/latestone.cfc?wsdl
You should see a bunch of xml data. This is coldfusion doing the hard work for you and describing the webservices offered at this wsdl.
The datasource and query are linked to a ms sql server, a single table called person. It should all be pretty straightforward.



2)The test.cfm:

OK so far so good, lets create a quick test.cfm and make sure the webservice works so far. Create the cfm file and paste in the following code:

<cfobject type="JAVA"
 action="Create"
 name="factory"
 class="coldfusion.server.ServiceFactory">
 <cfset RpcService = factory.XmlRpcService />
 <cfset RpcService.refreshWebService("http://localhost/webserrvices/latestone.cfc?wsdl")>

<cfinvoke webservice="http://localhost/webserrvices/latestone.cfc?wsdl" method="getPersonByDept" returnvariable="hello2">
 <cfinvokeargument name="deptname1" value="development" />
 <cfinvokeargument name="deptname2" value="sales" />
 <cfinvokeargument name="deptname3" value="marketing" />
</cfinvoke>

<cfdump var="#hello2#" />

OK quick bit of explaining. First and foremost the first paragraph, the JAVA stuff may be the single most useful piece of code when working with webservices in CF. CF does some aggressive caching when it comes to webservices. So if you're in dev mode this code forces CF to keep re-looking. DO NOT leave this code on a production server as it'll slow it right down. But for now it's useful. The second bit of code is a simple cfinvoke based on a webservice consumption.


3)The mxml:

Finally the Flex part. OK So assuming you've downloaded Flex, and tried a helloWorld already you should be able to take the following code and paste it into a .mxml file. Then compile it using the mxmlc command as before. Here's the mxml Flex code:

<?xml version="1.0" encoding="utf-8"?>
<mx:Application 
 xmlns:mx="http://www.adobe.com/2006/mxml" 
 viewSourceURL="src/HelloWorld/index.html"
 horizontalAlign="center" verticalAlign="middle" initialize="initApp()" backgroundColor="white">
 <mx:Script>
<![CDATA[
 import mx.collections.ArrayCollection;
 import mx.rpc.events.ResultEvent;
 import mx.controls.Alert;
 import mx.rpc.events.FaultEvent;
 import mx.utils.ObjectUtil;
 import mx.core.Application;
 import mx.validators.Validator;
 import mx.managers.CursorManager;
 import mx.events.ListEvent;
 import mx.controls.DataGrid;
 import mx.controls.dataGridClasses.DataGridListData;
 import mx.utils.ArrayUtil;

 [Bindable]
 private var myDebpts: ArrayCollection;

 public function initApp():void {
 //onload send webservice
 WSgetTasks.getPersonByDept.send();
 }
 private function server_fault(event:FaultEvent):void{
 //on error, show...an error message!
 Alert.show(ObjectUtil.toString(event.fault));
 }
 private function returnLoadTasks(evt:ResultEvent):void {
 //when webservice returns, we grab the results and stick it in the bindable myDebpts
 myDebpts = WSgetTasks.getPersonByDept.lastResult;
 }

 ]]>
</mx:Script>
 
//This is the crus of the app, the webservice call. the operation tells it what method to call and the request stuff is the parameters, that simple!
<mx:WebService id="WSgetTasks" wsdl="http://localhost/webserrvices/latestone.cfc?wsdl" fault="server_fault(event);" result="returnLoadTasks(event)">
<mx:operation name="getPersonByDept">
<mx:request>
 <deptname1>development</deptname1>
 <deptname2>sales</deptname2>
 <deptname3>marketing</deptname3>
</mx:request>
</mx:operation>
</mx:WebService>
 <mx:Panel title="Users By Department" height="300" width="400">
 //the chart
<mx:ColumnChart id="myChart" 
 dataProvider="{myDebpts}" 
 height="100%"
 width="100%"
 showDataTips="true">
 //this bit is confusing but it works!
<mx:horizontalAxis>
<mx:CategoryAxis dataProvider="{myDebpts}" categoryField="DEPTNAME" />
</mx:horizontalAxis>
 
<mx:series>
<mx:ColumnSeries yField="COUNTTT" xField="DEPTNAME" displayName="Department Name" />
</mx:series>
</mx:ColumnChart>

 <mx:Legend dataProvider="{myChart}"/>
</mx:Panel>

</mx:Application>

There we go. Hopefully the comments provide some clarity, again I'm sorry Google blogger does such a poor job of showing the code properly. To talk you through it briefly, on app initialize the initApp() is called, this triggers the webservice. The mx:WebService calls the cfc which we defined as a webservice. It uses the mx:operation and the mx:request as parameters, obviously you can bind these to a text field or something. Then on completion the result runs the returnLoadTasks() function which loads the lastresult into the bindable arraycollection. This arraycollection is bound to the chart with the appropriate x and y axis names.

4)Display the generated flash file:

Done. Compile this and flex will spit out a lovely flash file. Copy this to your web directory somewhere and add the following to output the flash file:

<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,29,0" width="100%" height="100%">
<param name="movie" value="wschart.swf">
<param name="quality" value="high">
<embed src="wschart.swf" quality="high" pluginspage="http://www.macromedia.com/go/getflashplayer" type="application/x-shockwave-flash" width="100%" height="100%"></embed>
</object>

Update: Charting available in Flex

A while ago i posted an entry saying charting was not available to Flex users of the SDK. While i was upset (charting is cool) I understood that Adobe wanted to attract users to their Flex Builder (purchased) product over the free SDK. Well in the latest version i discovered to my surprise the SDK can now support charting. This is awesome, hats of to Adobe, it works great and looks amazing, and all for free, grata, no money!

Here's a very brief example:

1) Download the SDK and the data visualization pack (currently flex version 3.5):
http://www.adobe.com/cfusion/entitlement/index.cfm?e=flex3sdk

2) Unzip install, etc, rtfm.

3) Create a blank text file with the extension .mxml

4) type the following code:



5) type something along the lines of this in a cmd window

C:\Adobe\Flex3\bin>mxmlc --strict=true --file-specs C:\flexie\chartTest.mxml

**should be obvious the first bit is cmd window telling me what dir i'm in, then the mxmlc is the executable to use for compiling and the rest is jarg, then the last bit is the location of my mxml code file.

6) This should deploy a .swf file to your code location. The swf file is then your flex app with some pretty little charts available!

I'll blog later with how to link these charts to something useful like a cf webservice.

Congrats Adobe on putting charts in the sdk, nice one.

Javascript display time in browser local format

This is something i found useful and I'm sure I will again in the future.

Use coldfusion to set the UTC date and time. Then we'll have JS convert that time to the users local time.

Obviously it's preferable to have the user create an account and have them select their own, but in this case that wasn't an option.

function getDstOffset(myInputDate)
{
   //get clients timezone offset so we can display in local format (60000 forces to use milliseconds)
   var clientOffset = -myInputDate.getTimezoneOffset() * 60000;
   return clientOffset;
}
function displayMyLocalTime(divname)
{
   //take the passed in datetime(handled by server) and convert it to browser local
   //this function takes the name of the dov to update with the datetime
   var bigBenTime = new Date();
  
   //set the js time to match that given to us by server
   bigBenTime.setDate(#day(futuredate)#);
   bigBenTime.setMonth(#month(futuredate)-1#);
   bigBenTime.setYear(#year(futuredate)#);
   bigBenTime.setHours(#hour(futuredate)#);
   bigBenTime.setMinutes(#minute(futuredate)#);
   bigBenTime.setSeconds(#second(futuredate)#);

   cfif LEN(futureTime)
        //time is defined, set it
        bigBenTime.setHours(#hour(futuretime)#);
        bigBenTime.setMinutes(#minute(futuretime)#);
        bigBenTime.setSeconds(#second(futuretime)#);
    /cfif

   ///get milliseconds since epoc and milliseconds of timezoneoffset
   localBenTime = bigBenTime.getTime() + (getDstOffset(bigBenTime));
  
   //put milliseconds back into useful format
   benTime = new Date(localBenTime);
  
   //set div to time
   $(divname).innerHTML = benTime;
}

Odd bug in Query of Queries

Not sure if this is a known bug or if it will really help anyone but it stumped me for a bit. As you can see ordering of uppercase values is handled completely differently than lowercase values! Frustrating.

cfscript
 hello = queryNew('lastname,firstname');
 queryAddRow(hello);
 querySetcell(hello,'lastname','YANKEE');
 querySetcell(hello,'firstname','DOODLE');
 queryAddRow(hello);
 querySetcell(hello,'lastname','ableson');
 querySetcell(hello,'firstname','abe');
/cfscript
cfdump var="#hello#"



cfquery name="qhello" dbtype="query"
 select lastname, firstname from hello 
 order by lastname,firstname
/cfquery
cfdump var="#qhello#"

Obviously the solution is "select lower(lastname), lower(firstname) from"....

I'd love to see CFML improve query of queries, it is a useful tool.

SQL "list" Functionality

Hey

I recently had to take a FullName field in sql and split it into first and last names. To complicate matters further the fullname had an initial in it and the fields were backwards:

Adams, Douglas B
Fox, Michael J
Spears, Britney M
Rose, Axel

Obviously in cfml this would be easy-peasy using list functions. I'm sure aspx, jsp and php make it fairly trivial too.

SQL However, wasn't built for this, but alas, that was the way it had to be. If it helps anyone the lastname was fairly simple:

left(column_1,charindex(',',column_1)-1) as lastName

Of course firstname become infinitely more complicated as you have to get everything after the comma and THEN strip everything after the space. PLUS not every user has a middle name (eg Axel Rose). Sounds easy..it's not.
Now subqueries make this a little more straightforward but in this case i didn't have that luxury.
If you need to do that try something like:

select fullString, lastname, calculateFirstName(otherName) as firstname from
(
    select fullstring, lastname, calculateWhatsLeft(lastname) as otherName from
    (
        select fullString, calculateLastName() as lastname from tablename
    )
)

(excuse the functions in there, it's just shorthand / pseudocode, you actually need to do the left(...charindex(...)) type stuff i just don't wanna retype.

Anyway as i said i couldn't use subqueries so onward with my plight. I tried coalesce but it needs nulls...urg! I ended up with some hideous case statement.
....back to the drawing board.

i stumbled accross this excellent idea by someone i've never met Daryl Banttari:
http://cfprimer.blogspot.com/2009/01/listfirst-listrest-in-sql-server.html

User functions, not normally a big fan of using custom functions in sql, but this was desperation. I modified the functions to take in a delimiter parameter:

CREATE FUNCTION [dbo].[listFirst] (@list nvarchar(4000),@delim nvarchar(2))
RETURNS nvarchar(4000)
AS BEGIN
 IF(@delim is null) SET @delim = ','
   DECLARE @pos int
   DECLARE @ret nvarchar(4000)
   SET @pos = charindex(@delim, @list)
   IF @pos > 0
       SET @ret = left(@list, @pos-1)
   ELSE
       set @ret = @list
   RETURN @ret
END
GO

CREATE FUNCTION [dbo].[listRest] (@list nvarchar(4000),@delim nvarchar(2))
RETURNS nvarchar(4000)
AS BEGIN
   IF(@delim is null) SET @delim = ','
   DECLARE @pos int
   DECLARE @ret nvarchar(4000)
   SET @pos = charindex(@delim, @list)
   IF @pos > 0
       SET @ret = substring(@list, @pos+1, len(@list)-@pos)
   ELSE
       SET @ret = ''
   RETURN @ret
END

and hey presto:

select column_1, 
dbo.listFirst(column_1) as lastname,
dbo.[listFirst2](ltrim(dbo.[listRest](column_1)),' ') as firstname
from tableName

Open Blue Draggon and Google App Engine

Hey Guys,

Anyone following cloud computing and coldFusion should absolutely check out some amazing work by a friend of mine Paul Kukiel:

http://blog.kukiel.net/2009/09/coldfusion-on-google-app-engine-with.html

He's built on some great work Google / Blue Dragon have been doing with porting a basic cloud computing style java cf instance (open Blue Dragon) onto Google app engine. It looks like it's work in progress but it's super exciting to see.

You can even try it yourself, go on it's easier than you think!

Using If exists drop table in Apache Derby and CF8

All credit to Giancarlo Gomez for this blog post, awesome:

http://fusecf.blogspot.com/2007/08/coldfusion-8-and-derby-db-create-and.html

Using apache derby as a testing database is really useful and this article describes a great method of checking if a table exists and dropping it or re-creating it.

I find it very useful when testing and developing a new site to create a cfm page that just drops all tables and re-creates them with new data in. This saves a lot of time fiddling when you suddenly decide you need a new column or something.