AJ Mercer
Blogging at http://webonix.net/
webonix.net :: Exploring the wonderful world of CFML
Railo running JAVA version of less.js
January 24, 2012
Railo running JAVA version of less.js
At Work, we are giving one of our sites a make over which was done by an external company. They have delivered some working HTML, but very quickly that I discovered that I am going to have to tweak the CSS - lots of hard coded height and widths for mega menu; not so good for a CMS.
After a bit of a look around it seems to me that it was generated with less.js. So I have asked if they could provide me with the less files.
While waiting for that I did a bit of Googling and found that Railo can run a JAVA version of less.js. A bit of downloading some JARS and cut and paste coding I had it working - first time!
Next I tweaked it a bit forther to create something that would output CSS content with something like:
<link rel='stylesheet' href='/less/index.cfm?src=/css/global-styles.less' />
Code:
<cfsetting enablecfoutputonly="true">
<cfparam name="URL.src" type="string">
<cfset variables.javajs = createObject("java","org.mozilla.javascript.tools.shell.Main",
"/labs/less-js/cfelements/jars/js.jar")>
<cfset variables.javaless = createObject("java","com.asual.lesscss.LessEngine",
"/labs/less-js/cfelements/jars/lesscss-engine-1.1.4.jar")>
<cfcontent type="text/css" reset="true">
<cfoutput>#variables.javaless.compile(fileRead(URL.src))#</cfoutput>
<cfsetting enablecfoutputonly="false">
Thanks to Cfelement, Nathan Strutz and Asual - Oh, and those wonderful folks over at Railo ;-)
CFML Study Group - Perth AU
January 05, 2012
CFML Study Group - Perth AU
At the start of last year, Chris Bauer and I got together to take a look at the CFWheels framework in what we refered to as a 'study group'. We are going to kick this off again in the new year.
We have also been discussing running CFML classes. The recently released Railo 3 Beginners Guide book would make an excellent text book. I am sure I could get a good price for bulk order.
So if you would like to learn the ColdFusion Markup Language, or would like to extend your skills, please leave a comment.
Railo 3 Beginner’s Guide
December 20, 2011
Railo 3 Beginner’s Guide
Back in March Mark Drew approach the Railo Team looking for contributors for a Railo book for beginners. Whilst I have been using Railo for a long time and am very comfortable with it, I did not think I was up to writing any chapters of the book. Instead I put my hand up to be a Technical Reviewer. I am not ashamed to admit that I learnt a few new things along the way. The Railo 3 Beginner’s Guide is now avaible from Packt Publishing.
Aimed at beginners, this book guilds you through installing and setting up a Railo Server, then covers many aspects on building Web Applications with Railo. Even though it is called a beginners guild, the book covers many advanced features of CFML such as Caching, AJAX, ORM and Multi Media to name a few. Check out the table of contents to see everything covered in the book. For those that like to learn by doing, there are lots of working coding examples so you can play along at home and do your own experimenting. There is also a fully working application for you to build (but you can cheat and download from the Packt Publishing website).
All of the authors, and many other talented people, can be found on the Railo Mail List and are very keen to help you continue your Railo adventure.
Joel and the rest of the team at Packt Publishing were a delight to work with and I thank them for their patients as we worked though our first book. If you are in a User Group, ask them about the discounts and book review offers they have.
CFML Image Place holder Custom Tag
September 26, 2011
CFML Image Place holder Custom Tag
I saw a tweet today from @modius about an Image Placeholder service and I thought this should be pretty easy to do with CFML - and it was.
Here is the code for a custom tag
<cfsetting enablecfoutputonly="true">
<cfparam name="attributes.width" default="350">
<cfparam name="attributes.height" default="150">
<cfparam name="attributes.backgroundcolour" default="black">
<cfparam name="attributes.text" default="">
<cfparam name="attributes.fontSize" default="36">
<cfparam name="attributes.fontColour" default="white">
<cfparam name="attributes.returnType" default="image" > <!--- hint="image|tag" --->
<!--- font hard-coded to make calculating centre easier ;-) --->
<cfset attr = { font="Courier New", size=attributes.fontSize, style="normal"}>
<cfset top = Round((attributes.height + attr.size) / 2) >
<cfset left = Round(((attributes.width - (Len(attributes.text) * attr.size*0.6)) /2) ) />
<cfset imgPlaceHolder = imageNew("",attributes.width,attributes.height,"rgb",attributes.backgroundcolour)>
<cfset ImageSetDrawingColor(imgPlaceHolder,attributes.fontColour)>
<cfset imageDrawText(imgPlaceHolder, attributes.text, left, top, attr)>
<cfif attributes.returnType EQ "Tag">
<cfoutput><img src=""><img src="data:image/*;base64,#toBase64( imgPlaceHolder )#"/> </cfoutput>
<cfelse>
<cfoutput><cfimage action="writeTobrowser" source="#imgPlaceHolder#"></cfoutput>
</cfif>
<cfsetting enablecfoutputonly="false">
And to use it to return image to browser
eg /index.cfm?width=600&height=400&fontSize=60&text=hello,+Webonix!
<cfparam name="URL.width" default="350">
<cfparam name="URL.height" default="150">
<cfparam name="URL.backgroundcolour" default="black">
<cfparam name="URL.text" default="">
<cfparam name="URL.fontSize" default="36">
<cfparam name="URL.fontColour" default="white">
<cf_imagePlaceHolder
width="#URL.width#"
height="#URL.height#"
backgroundcolour="#URL.backgroundcolour#"
fontColour="#URL.fontColour#"
fontSize="#URL.fontSize#"
text="#URL.text#"
>
Or as an image tag in you code
<cf_imagePlaceHolder
width="200"
height="200"
backgroundcolour="##e0e0e0"
fontColour="black"
fontSize="12"
text="image goes here"
>
To make the text positioning calculations easiy, i have hard-coded a mono-space font. But using Ben Nadel's ImageUtils you could pimp this out some more.
CFML has not hit critical mass
August 30, 2011
CFML has not hit critical mass
I have been having a (email) chat with Hal Helms about how CFML frameworks stack up against Ruby on Rails. Hal has worked in both environments and after taking a quick look at what RoR has to offer I was wondering if the CFML frameworks had a lot of catching up to do. And as a side note, CFML does not have anything that touches WordPress and a few other PHP applications.
The thing that struck me from Hal's comments was when he mention the size of the community that is behind RoR (and I assume PHP), but more to the point, the lack of people on the CFML projects. For the most part, the CFML projects I work with, there is the person that started the project and maybe a couple of people that are 'committers'. And there are projects that have been picked up by another person when some one else no longer has the time/montivation to keep working on it.
So this got me to thinking - why is this the case? Is it because the people from Allaire/Macromedia/Adobe, and now Railo and openBD, have spoilt us and now we expect everything to be given to us? Does CFML attract people that want easy answers and quick fixes? Is it someone else job to make our lives easier?
For me, I think is is because I don't think I am as smart as the people that work on the projects and frameworks I use. I am happy enough to submit bugs when I find them. If I am able to get it working for myself, I will submit it is a 'hack' as I would not presume it a 'solution'. I wonder if that is a common feeling for a lot of CFML developers?
So for all of those that have stepped up to work on CFML projects - congratualtions and thank you. As wonderful as the CFML engines are, I am sure it is the projects and frameworks that entice people to use them.
Customised URLs for CFEclipse
May 25, 2011
Customised URLs for CFEclipse
CFEclipse has the ability to view web pages right in the IDE itself. This is fine if the page you are working on is in the webroot, but if you are working with a MVC framework like CFWheels or FW/1, then things are not so great - until now.
Mark Drew has just closed a ticket that adds this functionality, which at the time of writting this blog is avaliable from http://cfeclipse.org/update-dev.
For a CFWheels project, the view templates are stored in the views directory. Right-click this folder and navigate your way to CFElipse -> Edit URL
Now this is where the enhancement has been added - snippet constants. There are two that help us out here: $${CURRENTPRJPATH} and $${CURRENTFILENOEXT}. As CFWheels uses a conventions base stucture we are able to tell CFEclipse what the URL will look like
NOTE: your web site needs to have rewrite rules set up already for this URL.
Now after making a change to a view file, click on the brower icon in the tool bar and admire your work.
See the URL - pretty damn sweet :-)
Friendly URLs for Railo in Cloudbees
May 09, 2011
Friendly URLs for Railo in Cloudbees
Railo was so easy to deploy to the 'cloud' using Cloudbees RUN@Cloud. Mark Drew has written up how to deploy Railo to Cloudbees.
I have been messing around with this just to see what 'cloud' computing is all about and just for fun uploaded CFWheels to http://cloudbees.webonix.org (http://cfwheels.webonix.cloudbees.net/ cloudbees lets you add your own domains). But for a wheels site to look sexy it needs friendly URLs.
This is achievable using Tuckey URL Rewrite Filter which I discovered on Aaron Greenlee's website. Extract to the WEB-INF in the root of your website. Edit the WEB-INF/web.xml and add this before the servet tag
<filter>
<filter-name>UrlRewriteFilter</filter-name>
<filter-class>org.tuckey.web.filters.urlrewrite.UrlRewriteFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>UrlRewriteFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
While you are in this file, also add the following with the rest of the CFML mappings. Tomcat does not allow for multiple wildcards
<servlet-mapping>
<servlet-name>CFMLServlet</servlet-name>
<url-pattern>/rewrite.cfm/*</url-pattern>
</servlet-mapping>
Next Create urlrewrite.xml in WEB-INF. This is what I have based on comments in CFWheels doco
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE urlrewrite PUBLIC "-//tuckey.org//DTD UrlRewrite 3.2//EN" "http://tuckey.org/res/dtds/urlrewrite3.2.dtd">
<!--
Configuration file for UrlRewriteFilter
http://tuckey.org/urlrewrite/
-->
<urlrewrite>
<rule>
<note>
Allow TomCat to have SES links.
</note>
<!--
If our URI ends with one of these extentions we will not perform
a URL rewrite.
-->
<condition type="request-uri" operator="notequal">\.(bmp|gif|jpe?g|png|css|js|txt|pdf|doc|xls|xml|cfc|ico|php|asp)$</condition>
<!--
If any URI begins with one of these directories, we will not
perform a rewrite. Helpful for running your unit tests or
"blocking" rewrite for some directories.
-->
<condition type="request-uri" operator="notequal">^/$</condition>
<condition type="request-uri" operator="notequal">^$</condition>
<condition type="request-uri" operator="notequal">^(.*)/railo-context(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/flex2gateway(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/jrunscripts(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/cfide(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/cfformgateway(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/files(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/images(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/javascripts(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/miscellaneous(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/stylesheets(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/robots.txt(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/sitemap.xml(.*)$</condition>
<condition type="request-uri" operator="notequal">^(.*)/rewrite.cfm(.*)$</condition>
<from>^/(.*)$</from>
<to>/rewrite.cfm/$1</to>
</rule>
</urlrewrite>
Now you can start cloudbees for your site and you should be ready for creating freindly URLS. Which for CFWheels means editing config/settings.cfm and adding
<cfset set(URLRewriting="On")>
And builing links using linkTo(). E.g.
<h1>Page One</h1>
<cfoutput>
#linkTo(text="Page Two", controller="ses", action="pageTwo")#
</cfoutput>
Now you are ready to deploy to the cloud and end up with something like this http://cloudbees.webonix.org/ses/page-one
webDU 2011 review
April 18, 2011
webDU 2011 review
I went to webDU 2011 with an aim to see what is happening in the mobile space as that is an area Patersons is looking at getting in to. I have built a prototype / proof of concept with jQuery Mobile so was happy to see that this was getting a lot of attention at the conference.
The conference had sessions on using jQuery to build web applications for mobile devices, and also how to build native application using the same technology. Craig Sharkie took us through what HTML 5 the brand is, verses HTML 5 tags, and CSS 3. He then went through the steps to build a web app using this technology (code to appear on his GitHub). This app uses a manifest to assist with off-line asset (images).
A team from Massive Interactive discussed using HaXe to build multi-platform solutions. Paul Burnett (Adobe Australia) stood in for Greg Rewis (and Terry Ryan) that could not make it to Sydney as their flights were cancelled. He showed the yet to be released DreamWeaver 5.5 with built in jQuery Mobile support to build native iPhone and Android. Under the hood it is using PhoneGap to build the deployable solutions which you then need to get into the market places.
As I do not have access to a Mac, and I don’t want to have to pay for developer licences, and cannot be bothered with market places, I will stick to my plan of building mobile web applications.
Due to a schedule clash, I did not make it to Lachlan Hardy’s session on backbone.js. This, along with corMCV are frameworks for managing what can quickly can very messy jQuery coding. I will definitely be looking into these!
The other side of application development that I am keen to learn more about it is service oriented architecture via RESTful web services. This was one of the recommendations from Nick Randolph. Mark Drew’s session on CouchDB has convinced me to take a look at this solution for noSQL, but my main interest is it’s RESTful implementation – they seemed to have done it very well.
The first session of the conference I attend was by Matt Voerman about Front End optimisation. Even though the points covered were quite basic, I am sure most in the room would have been guilty of not implemented at least two of them, me included. This was a great session to have in the conference as it is important to remember the basics when moving into shiny new things like jQuery on mobiles as they are still applicable.
Probably the session, well keynote, that had the biggest impact on me was one that I thought was going to be quite dull and dry. Maybe that is because I was not acquainted with Mick Liubinskas from Pollenizer. It was far from dry and dull! His keynote on keeping focus when starting out on the ‘next big thing’ gave me plenty to think about. And like Matt Voerman’s session, was very timely. With all the new things I learnt at webDU I would have been very tempted to go back to work and try and squeeze as many of them as possible, if not more, into my next project. The main concept that Mick shared with us was to pick one thing and do that very well. Then move onto the next feature of your killer app. Another key part of this is to find about 200 people that would value you application the most and TEST TEST TEST on them. These people will be very tolerant of any mistakes and will go out of their way to help you as they can see the benefit of your solution.
The idea of market research was further discussed by Christine Soriano as she talked about the mobile application built by ANZ Bank. Things that seem obvious and brilliant to a developer may not be to the end client. Taking mockups and early prototype to the streets caught what could have been an application killer.
Christine went on to describe mobile usage as ‘snacking’; people have a task that want to perform and they want to be able to do it very quickly. Added to that, they may want to be able to do it while they are walking or on the train. Who has ever been frustrated by having to login with an email address and password that requires mixed case, numerals and a special character? As they say in twitter land #fail.
Thanks to Geoff Bowers and the rest of the Daemonites for another fantastic conference. Looking forward to the TENTH next year J
Railo Community in Australia
January 05, 2011
Railo Community in Australia
As the Railo Community Manager of Australia I would like to know who is using (or playing) with Railo in Australia (Kiwi's can be honorary Australians for this as well if they like). So please leave a comment here or send an email to andrew @ getrailo . org
Let me know if you have sites running on Railo or are just checking it out.
Do you (or your company) provide any services around Railo?
I will be adding your details to my contact list so I can keep you up to date with any Railo news and events. Let me know if you do not want to receive any emails.
Railo and webonix.net third anniversary
October 17, 2010
Railo and webonix.net third anniversary
I have documented the history of webonix.net on the server page, but I thought I would take the opportunity to take a walk down memory lane and see were it all started.
Railo first came to my attention around October 2006. I don't recall exactly how Railo came to my attention, but it would have been one of my searches for a free CFML engine. I messed around with New Atlanta's BlueDragon for awhile before I came across Railo, and while I had issues with both, it was Railo struck accord with me. On 27 October 2008 I sent a question to Railo via their contact us page and received a prompt reply from Gert Franz. Gert was extremely helpful and patient with me; helping me come to terms with J2EE and how to compile Apache connectors. In November 2006 I swapped from Fedora to Ubuntu 6.1 and on the 9th I had webonix.net/railo running. By then I was having a crack at machBlog (mach-ii). There were some issues running this, but very quicly address by Railo and Gert showed me how easy ith was to apply a patch. on the 15th of November Gert gave me a sneak peek of what was then called Railix (now call express) 1.1 and told about extracting the Railo jar files from lib to update my existing installation.
At the start of 2007 I was now also trying other frameworks on Railo - Fusebox, ColdBox and Farcy 3; this time with Jetty, then back to Resin. Gert was still there patiently helping with out with Apache vhosts. I was also around this time we started chatting about the CFML landscape. I was still having issues with frameworks and Railo kept pumping out fixes. In May Railo getting ready for Scotch on the Rocks and getting ready to announce Railo 2.0. On the first of June Railo 2 was announced and release, and download by yours truely to do some ColdBox 2 and Farcry 4 testing. On the same day I introduced Luis Majano to Gert to discuss the issues and was experiencing I was having with the ColdBox Dashboard. Luis was very happy to know the framework run on Railo and was determined to get the Dashboard running too - and well, you know the rest ;-) In Jun I was taking a look at Matt Woodward's UG application that run on mach-ii and ColdSpring and also Mark Mandel's Transfer. On the 23 October I received a nice little present from Railo - a licence key for Railo 2.0 Pro.
August 23 was when I first downloaded MangoBlog - the blog engine that is still running this site now. There was a couple of minor issues, and this time it was Michael Streit the stepped in to help me out. On the 1 September I had MangoBlog running on Railo which Laura was very happy about; and once again, you know the rest ;) It looks like the web server when through some more rebuild and early blog posts were lost, but I will go by my server page's date and say 15 October 2007 was go live.
Railo Training in Australia
September 01, 2010
Railo Training in Australia
updated 20 Sept 2010
Railo are once again sponsoring cf.Objective(anz) with Gert Franz and Mark Drew attending the conference. As well as doing a couple of sessions, they will both be available before and after the conference to run the 3 day Railo Administrator Training Course.
The course is of a technical nature, though will cover some of the basics of CFML, and is aimed at those that are planning to use Railo in production and need to know everything about the background of the engine. You will learn very many details about the engine and its configuration. Here are a couple of highlights from the course:
- Installing Railo
- Configuring the application server
- Performance tuning
- JVM tuning
- Railo tuning
- Hardening the Railo Server
The training will cost $1797 for the 3 days. As well as the training, you will be eligible to to apply for certification (and once qualified you can become a Railo Partner), and you will also receive 20% off Railo Professional Services. Please let me know if you would be interested in a compressed one day session. This would cost $599, but would not have the same benefits as the 3 day course.
If you would like more information please email me at andrew@getrailo.org (or leave a comment here) and I will keep you up to date. Please let me know what state you are from and if any others from your company may be attending as well. If the numbers warrant it, the training could be taken to your state.
Also, please let me know if you are able to provide a venue - Railo will offer a 50% discount for one attendee.
For those that are thinking about becoming a Railo Partner, your company will need at least one certified person which can be done after the training. There is no cost in becoming a partner once you have a certified engineer. You just provide support and promote Railo.
Schedule Update (14 October 2010)
Perth
10 -12 November - Perth
Melbourne
15 - 17 November - to be confirmed
Sydney
22 - 24 November - Northbridge (venue to be confirmed. please leave a comment if you have a venue)
The plan is to run the Sys Admin session over the first two days (which can leand to certification) and the third day as an open Q&A session
What is Railo?
Railo is a free open-source CFML Engine
This site is Running Mango Blog on Railo
Australia now has a Railo Community Manager
June 10, 2010
Australia now has a Railo Community Manager
...and it is me.
I have been a long time of CFML and over the last four years an ever increasing fan of Railo. This blog went live on Railo 15 October 2007. Gert Franz spent a lot of time with emails and Skye calls helping me set up my web server and we have been chatting about CFML and Railo ever since.
So last Thursday I get another call from him to discuss Railo AU, which I keep bugging him, and recently Peter Bell, about all the time. But this time he asks me if I would like to join the Railo Team and be the Community Manager for Australia (and APAC). I felt very honoured to be offered this voluntary role and immediately accepted.
So if there is anything you would like to know or need assistance with I can be contacted at andrew @ getrailo . org
ColdFusion Query of Query tips and tricks
March 04, 2010
ColdFusion Query of Query tips and tricks
Query of Query gives the developer that ability to manipulate a query result in memory. It is a real neat feature, but is very limited in what it can do and very quickly becomes extremely frustrating. It has a very limited range of SQL functions and syntax that almost makes it next to useless if you want to do anything serious with it. This blog post is a collection of things I have put to gather after many hours of scouring the 'net and even more trying to make it work. They include update and insert, sub-selects, and my latest - outer join.
I am using query QuerySim to create an populate a query to simulate a database table.
Everyone will no doubt be familiar with the normal SQL SELECT
<cfquery name="qryStudent" dbtype="query"> SELECT id,fistname,surname,dob FROM VARIABLES.qryStudents WHERE id = <cfqueryparam cfsqltype="CF_sql_integer" value="#ARGUMENTS.id#" /> </cfquery>
To DELETE a record - slect everything except the record(s) to be removed
<cfquery name="VARIABLES.qryStudents" dbtype="query"> SELECT #VARIABLES.fieldNames# FROM VARIABLES.qryStudents WHERE id != <cfqueryparam cfsqltype="CF_sql_integer" value="#ARGUMENTS.id#" /> </cfquery>
To save (INSERT or UPDATE) a record gets a little more interesting. As I mentioned, I am using QuerySim to create my query. I modified it slightly to accept datatypes as well, and I pass these in as variables.
<cfset VARIABLES.fieldNames = 'id,fistname,surname,dob' /> <cfset VARIABLES.fieldTypes = 'Integer,varchar,varchar,date' />
You would have noticed that I used VARIABLES.fieldNames in the DELETE SQL.
Passing data types to CreateQuery() will stop ColdFusion from guessing what the data types are and potentially screwing things up for you. Using a list of field names has a few benefits: it ensures fields are named, rather than select *; ensures fields are always selected in the same order; and you can programatically loop through them.
Rather than create sample code and potentially add mistakes, I will just show you my code that I have working
<cffunction name="save" access="public" output="false" returntype="void">
<cfargument name="createDate" type="string" required="false" default="#Now()#" />
<cfset var field = '' />
<cfset var fieldValues = '' />
<cfset var qryMaxItems = QueryNew('id', 'integer') />
<cfset var pk = 0 />
<cfif ARGUMENTS.endDate NEQ "">
<cfset ARGUMENTS.endDate = ParseDateTime(ARGUMENTS.endDate) />
<cfelse>
<cfset ARGUMENTS.endDate = DateAdd('d', 14, Now()) />
</cfif>
<cfif ARGUMENTS.id EQ 0>
<cfquery name="qryMaxItems" dbtype="query">
SELECT MAX(id) as id
FROM VARIABLES.qryItems
</cfquery>
<cfset pk = qryMaxItems.id />
<cfset ARGUMENTS.id = pk + 1 />
<cfelse>
<cfset pk = ARGUMENTS.id />
</cfif>
<cfquery name="VARIABLES.qryItems" dbtype="query">
SELECT #VARIABLES.fieldNames#
FROM VARIABLES.qryItems
WHERE id != <cfqueryparam cfsqltype="CF_sql_integer" value="#ARGUMENTS.id#" />
UNION
SELECT
<cfloop from="1" to="#ListLen(VARIABLES.fieldNames)#" index="field">
<cfqueryparam cfsqltype="CF_sql_#ListGetAt(VARIABLES.fieldTypes, field)#" value="#ARGUMENTS[ListGetAt(VARIABLES.fieldNames, field)]#" /> as #ListGetAt(VARIABLES.fieldNames, field)#
<cfif field NEQ ListLen(VARIABLES.fieldNames)>,</cfif>
</cfloop>
FROM VARIABLES.qryItems
WHERE id = <cfqueryparam cfsqltype="CF_sql_integer" value="#pk#" />
</cfquery>
</cffunction>
Today I run into an issue that previously would have made me give up - OUTER JOIN. But based on what I have cobled together so far I thought I would be able to solve it. But before I could I had to solve another issue - SUB-SELECT.
A sub select is where you have WHERE id NOT IN (select ID FROM anotherTable). Whilst QoQ dooes support IN, it does not like a SQL in there. But you can use ColdFusion functions within a QoQ.
<cfquery dbtype="query" name="qryClass"> SELECT name FROM VARIABLES.qryClasses WHERE studentID NOT IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#ValueList(VARIABLES.qrySudents.id)#" list="true" />) </cfquery>
An OUTER JOIN is when you join multiple tables, with one table that may not have a record match for the other table, but you still want to see the data for that tables. What you end up with is NULLS for the values of the other table. What I ended up doing is doing a UNION, the first SELECT is all the results that match, and the second is all the values from the main table that were not in the first select, and empty stings for all the fields of the outer table, and then an ORDER BY. Once again, I will show working code.
<cfquery dbtype="query" name="qryCompanies"> select qryCompanies.security as security, qryCompanies.exchange as exchange, qryCompanies.description as description, qryCompanies.companyName as companyName, qryCompanies.SECTYPE as secType, qryCompanies.GROUPTYPE as groupType, qryCompanies.gicsCode as gicsCode, qryCompanies.gicsName as gicsName, qryCompanies.gicsColourCode as gicsColourCode, qryCompanies.gicsParentColourCode as gicsParentColourCode, qryCompanies.gicsGroup as gicsGroup from qryCompanies, qryHoldings where qryCompanies.security = qryHoldings.security and qryCompanies.exchange = qryHoldings.exchange and (qryHoldings.AccountNumber = <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(ARGUMENTS.AccountNumber)#">) </cfquery> <cfquery dbtype="query" name="qryCompanies"> select qryHoldings.accountNumber as accountNumber, qryHoldings.asOf as asOf, qryHoldings.exchange as exchange, qryHoldings.portfolioType as portfolioType, qryHoldings.security as security, qryHoldings.totalCost as totalCost, qryHoldings.unitCost as unitCost, qryHoldings.units as units, qryCompanies.description as description, qryCompanies.companyName as companyName, qryCompanies.SECTYPE as secType, qryCompanies.GROUPTYPE as groupType, qryCompanies.gicsCode as gicsCode, qryCompanies.gicsName as gicsName, qryCompanies.gicsColourCode as gicsColourCode, qryCompanies.gicsParentColourCode as gicsParentColourCode, qryCompanies.gicsGroup as gicsGroup from qryCompanies, qryHoldings where qryCompanies.security = qryHoldings.security and qryCompanies.exchange = qryHoldings.exchange UNION select qryHoldings.accountNumber as accountNumber, qryHoldings.asOf as asOf, qryHoldings.EXCHANGE as exchange, qryHoldings.portfolioType as portfolioType, qryHoldings.security as security, qryHoldings.totalCost as totalCost, qryHoldings.unitCost as unitCost, qryHoldings.units as units, '' as description, '' as companyName, 0 as secType, '' as groupType, '' as gicsCode, '' as gicsName, '' as gicsColourCode, '' as gicsParentColourCode, '' as gicsGroup from qryHoldings where qryHoldings.security not in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#ValueList(qryCompanies.security)#" list="true" />) </cfquery>
Notice that secType has zero rather than an empty string; that is because it is an integer, not a varchar. I am going to see if I can get a NULL in there - should be able to do something with a JAVA type. If I can get that going I will come back and clean up these examples.
This is a quick dump of what I have at least for my own records as no doubt I will have forgotten how and where I did all this by the next time I need it. If you have found it useful, or have any suggestions, please leave a comment.
I am speaking at webDU 2010
February 24, 2010
I am speaking at webDU 2010
I have been to many webDU conferences, including the
first one when it was called webMX, and am going this year too - but
this time as a speaker
This will be my second time as a
conference speaker. The first time was at the inaugural
cf.objective(anz). My session was about not
getting hung up on trying to build applications with design patterns and
OO principle that you don't understand. The point being, if you don't
understand them, they are not for you; they are to solve a particular
problem, and if you don't understand it, then you probably don't have
the problem. The session was just talking (well to be truthful, maybe a
bit too much reading) - no slides, no code. I am not too sure if people
want this sort of session. I whizzed through it way too fast (nerves), but had intending to have a general discussion at the end for
people to share their experiences and thoughts. I think with bit may have
saved my session - so thank you to those that participated in the chat.
This year, I will be continuing on with the same theme, but this
time with slides, but probably more significantly, code. After taking a
look at a few lite frameworks for ColdFusion,
I have recently settled on Sean Corfield's Framework One framework.
My originally thinking that these lite frameworks would be a stepping
stone / learning application for the bigger frameworks, but the authors
argue that their frameworks are industrial strength.
The session, after the obligatory introduction slides, will be going
through not only the development life cycle of an application, but also
your personal development as a developer. It will start off with the
HTML mock up of a site, and how to move that into the FW/1 views and
then set up layouts. From there move into simulated data and form posts,
the 'bad way'. Next some re-factoring of the application to utilise
controllers and services. I am also planning on covering security and
time allowing, sub-sections.
In search of the Holy Grails of web development
February 10, 2010
In search of the Holy Grails of web development
Two things happened recently that have inspired me to step back from
CFML and take a look at a different web development environment. Paul Kukiel
post on how to install GlassFish and CF (with link to adding Railo) and Dan Vega's post about a free Grails book.
A lot of CFML projects have taken inspiration from the JAVA, PHP and
Rails world - ColdSpring, Transfer ORM (and more recently CF9 ORM),
CFWheels, SplashCMS ... As CFML engines are JAVA based and can utilise
JAVA classes easily, I have always thought if I was going to learn
something else, it would be from the JAVA side of the fence; but just
not JAVA. I have heard a few positive things from CF people about
Groovy, and the Rails thing is popular at the moment, so Grails seems
like it ticks all the right boxes.
So even if I don't be come a Grails developer, I still this there are 2
useful outcome; well maybe 3, but I am not entertaining that idea at
the moment
- An understanding of where the CFML projects are coming from and perhaps learn better modeling techniques
- More appreciation of what CFML is doing for the developer
- A new career choice
So far I have spent a few hours going there the guide and am impressed
by what Grails can do. And what is suprising me at the moment is that I
am not fussed by the fact I am using command line and a text editor. I
know there are IDE's out there, so if I do get serious I know there are
better options. But also, using a text editor is a great way to learn
new languages. It is too easy to look at code and think you understand
it. But it is not until you spend a few minutes tracking badly cased
class names or a missing curly bracket will you really learn the syntax.
I have taken a quick peak at the HTML Grails creates and it is not too
dissimilar from CFML; there is ${variableName} verses #variableName#
and <g:tag /> versed cfTag />.
I also created a war of my web app and dropped it into GlassFish - and what do you know, it run :-)
The other thing I am thinking about, but resisting the urge to get
distracted at this early stage, is to call the classes created by
Grails from CFML
Read In search of the Holy Grails of web development in full
CFUG Sponsors
