WebDH blog

Using ColdFusion with a stubborn MS Access Date/Time field

Sat, 20 Mar 2010 17:16:00 -0700

I have a small application using a MS Access database. Yes, I know all the reasons why it shouldn't be used, let's not go there. Fact is, many developers still use Access for small apps, prototyping, etc, and may come across the same roadblock in which I figured out a solution. For reference, today's date is 3/19/2010 which was used in the query examples shown.

Requirement: Query a table of Jobs (JobTitle, Dept, Salary, etc) that contains a Date/Time field named PostingEndDate. This field is configured as Required = No. That means the admin user who populates records in the table will either supply a date, or may leave the date blank. Think of it as a field that allows NULL in MS SQL. On the end-user side, the query needs to list only current jobs by filtering the records where:
1) PostingEndDate has not passed today's date
OR
2) PostingEndDate is empty (blank value means the Job can be displayed to the user indefinitely) Here is a cfdump (jobList_raw) of all records:


SELECT   JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM     tblJob 
WHERE 	 ActiveJob = 1
ORDER BY JobTitle

Problem: no easy way to write the WHERE clause. Here are some attempts:
1) Len(PostingEndDate) = 0
No error, but does not pick up those with blank dates:


SELECT   JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM     tblJob 
WHERE 	 ActiveJob = 1
AND   (Now() <= PostingEndDate OR Len(PostingEndDate) = 0)
ORDER BY JobTitle

2) PostingEndDate = ''
Generates error: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
We can't compare the date field to an empty string.


SELECT   JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM     tblJob 
WHERE 	 ActiveJob = 1
AND   (Now() <= PostingEndDate OR PostingEndDate = '')
ORDER BY JobTitle

3) Cstr(PostingEndDate) = ''
Generates error: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Invalid use of Null
This was my attempt to Cast the date to a string using an Access function. Guess not.


SELECT   JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM     tblJob 
WHERE 	 ActiveJob = 1
AND   (Now() <= PostingEndDate OR Cstr(PostingEndDate) = '')
ORDER BY JobTitle

Solution: write a ColdFusion Query of Query (QoQ) to UNION the two conditions into one resultset. Here are the steps that lead to the final solution.
1) I still need a way to get a string representation (varchar) of the date field. So I added another column (PostingEndDate_str) to the Query object.

2) Loop query and populate the new varchar field. This was intended to produce a blank string for what cfdump showed as [empty string]. Then I should be able to use: PostingEndDate = ''

NOPE! Still see [empty string] in the new field, THIS BECAME THE HAIR PULLING MOMENT OF THE SOLUTION AT THIS POINT, SO I STARTED GOOGLE SEARCHING.
I decided to try using '-' before and after the value, a trick I noticed in Ben Nadel's blog:

Now I see "--" for all those [empty string] values, much better. I can work with that in the QoQ.
3) Last is the Query of Query UNION.


SELECT	JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM    jobList_raw 
WHERE   #ParseDateTime(DateFormat(Now(),'mm/dd/yyyy'))# <= PostingEndDate  
UNION
SELECT	JobID, DeptID, JobTitle, Salary, PostingEndDate
FROM    jobList_raw 
WHERE 	 PostingEndDate_str = '--'  
ORDER BY JobTitle

Here is the final recordset. It correctly leaves the Architect job filtered out because its PostingEndDate of Mar 9, 2010 has passed. The Mortgage Processor and Supervisor jobs with blank dates are kept in the results!

Excited for special ColdFusion Builder event in St. Paul on March 23

Wed, 17 Mar 2010 21:56:00 -0700

Josh Adams, Adobe Senior Solutions Engineer for ColdFusion, will be presenting live as part of a special CFUG tour. Josh will bring us the latest details and demonstration of the new Eclipse based IDE, ColdFusion Builder. We are planning for a fun event hosted at our usual user group location, Easel Solutions. Adobe is shipping us some special event swag as well, so be there for chance to take home a unique prize. Hope to see a packed house, register now! www.colderfusion.com/CFBuilder10.cfm Date: Tuesday, March 23, 2010 Agenda:
5:45 Food and Social
6:30 Presentation begins
8:15 Q & A - Prizes
8:30 After party at local bar TBD Location: Easel Solutions, St. Paul, MN Updated 3/24/2010
We had a great event, here are some pics!

ColdFusion Builder - Josh Adams - March 2010

ColdFusion UG Tour coming to St. Paul June 11

Sun, 31 May 2009 23:36:00 -0700

I'm thrilled to report that Ben Forta is visiting the Twin Cities again as part of the Adobe worldwide user group tour for the upcoming versions of ColdFusion and Flex. I got the official word a few weeks ago and have been head down in planning mode ever since. Figuring attendance will bust down the doors at Easel Solutions, we needed a bigger venue, so I made a bunch of calls, sent emails, and even visited one potential location. In the end we choose the University of St. Thomas, St. Paul Campus and I think it is going to work out great. I want to thank the TCCFUG's co-manager, Ben Ellefson, for major work on the event registration website. It's now live and I urge you to get this on your calendar and register now. This is one user group meeting you will not want to miss, plus you'll be fed and could win an IPod touch to boot. Hope to see you there! Updated 6/12/2009
We had a great event, here are some pics!

Adobe CF9/Flex4 UG Tour St. Paul 2009

Completed my first Flash project

Thu, 30 Apr 2009 13:19:00 -0700

Over the past two months, I've been working with a new client on a Flash project. She happens to be a former co-worker in my Creative Internet Solutions days back in 1999-2000. We reconnected on Facebook and I learned about a new women's handbag system she was developing and needed a Flash demo for her website. We met for lunch so I could see the prototype handbags firsthand and she had a basic script on paper of how she wanted it to look and flow. From there I dove into some Lynda.com training and also picked up some best practices from a couple Flash gurus I know. After a few revisions, she had exactly what she wanted and was very happy with the end result. Heddy Freddy handbag system Flash demo Client quote: "Thanks so much for the great animation, it's just what I imagined!"

How to send a fax using Ooma

Mon, 09 Mar 2009 14:15:00 -0700

We just got an Ooma VOIP phone system ($220 deal right now at Costco) and I've been testing exactly how I'll position the Hub and Scout in our house. I also wanted to make sure our fax machine will work. We don't fax very often, but it's sure nice to have when when needed (usually about once a month.) Ooma says to position a fax machine with direct phone line connection to the Hub. I tried this, but it wasn't working. I could hear the high pitch "faxing sound", but after that, the connection would fail. I found a web forum with an easy solution, simply prefix the number you are dialing with *99 and it works! Here is the Ooma forum link. Ok, now I just need to get our home phone number ported and it will be time to drop Comcast phone service and save $40 per month!

ColdFusion page added to SalesForce wiki

Tue, 03 Mar 2009 16:02:00 -0700

Today I added a section for Adobe ColdFusion to the SalesForce Developer Wiki under the Web Services API section. I then added the first code sample article, showing how to do a Basic Web2Lead Implementation. I looked at a similar PHP sample done by Wayne Abbott as the basis for my article. Hopefully this will spur others in the CF Community to start adding more content to this wiki and spread the knowledge of CF as a viable web development platform.

Tips for testing web-to-lead on SalesForce sandbox server

Tue, 03 Mar 2009 14:14:00 -0700

The SalesForce Web-to-Lead URL is well known to be: https://www.salesforce.com/servlet/servlet.WebToLead?encoding=UTF-8 If you have access to a Sandbox server to do your development, you may want to test Web-to-Lead against it. You first should go into the setup area to find your OID (Organization ID) which will differ from your production OID. Find this from SalesForce's top "Setup" link. Then drill down into the left navigation menu:
App Setup / Customize / Leads / Web-to-Lead
Make sure the checkbox for Web-to-Lead Enabled is checked. Then click the "Create Web-to-Lead Form" button. On the next page, keep all selected fields as defaults and click the "Generate" button. Look in the generated HTML output for your unique OID which is given in the first hidden form field. You should see lines of code like this:

Remember, you are developing for tests against the Sandbox server, so you will need to modify the Form action to match the URL of your Sandbox homepage. Look at your Sandbox url and replace "www" with the proper subdomain, such as "cs2" in this example.
Example Sandbox homepage: https://cs2.salesforce.com/home/home.jsp
New Web-to-Lead Form action: https://cs2.salesforce.com/servlet/servlet.WebToLead?encoding=UTF-8

Update your Twitter status from ColdFusion using a JSP tag library

Wed, 04 Feb 2009 15:35:00 -0700

Today I wanted to find an easy way to put a status update into my Twitter account using ColdFusion. I went searching the API docs, and found this page. http://apiwiki.twitter.com/Libraries Unfortunately, there are no CF examples listed, boo! However, there are some for Java, so I continued my search in that direction. Eventually I found this JSP taglib, and decided to give it a "Twirl" :) http://www.servletsuite.com/servlets/twittertag.htm To use any JSP taglib, you simply drop the .jar file into your /webroot/WEB-INF/lib/ directory. I believe it also requires the Enterprise edition of ColdFusion. A good article that fully explains their usage was done by Charlie Arehart back in May 2002. After copying the file, you must then restart ColdFusion. This is a must or you will get an error when attempting to import the library. That's it, I was now ready to test a Twitter post. Here is the sample code I used that will put a new status message in my Twitter account. I saved this in a file twitter.cfm, then browsed to the page on my local machine as http://localhost/twitter.cfm


 

My posting to Twitter from CF #now()#

Upon success, an XML dataset is returned in the "result" variable. If you cfdump it, looks like this.


  Wed Feb 04 21:29:26 +0000 2009 1177644114 My posting to Twitter from CF {ts '2009-02-04 15:29:28'} web false   false   20062919 Twin Cities CFUG TCCFUG St. Paul, MN USA Adobe ColdFusion User Group -of Minneapolis / St. Paul, Minnesota http://s3.amazonaws.com/twitter_production/profile_images/75391930/colderfusion_twitter_normal.jpg http://groups.adobe.com/groups/bd9082a926/ false 0

If you fail to restart CF, you will see this TagExtraInfo error message:


The TagExtraInfo class com.cj.twitter.strVariable for the update tag could not be found.

The CFML compiler was processing:
    * A cfimport tag beginning on line 1, column 2.

This is not the best solution if you are in a shared hosting environment, as they may not install the twittertag.jar file for you. I'm curious what other methods developers have found to accomplish status posts to Twitter.

ColdFusion UDFs long2ip() and ip2long()

Tue, 06 Jan 2009 11:57:00 -0700

Yesterday I was working on porting some PHP code into ColdFusion. The PHP code was using a function called long2ip(), and I researched this PHP Manual website to learn more about it. I needed this function in CF, but couldn't find it at cflib or anywhere else doing a few Google searches. The closest I came was this blog post by Brandon Purcell, which got me started in the right direction. I learned from what Brandon wrote, along with the comments input by Gabriel Malca (if the function doesn't exist) at the PHP Manual site and came up with the logic for my CF version of the function. After I had this working, I figured I better implement the opposite conversion function as well, so I also created ip2long(). Again I found a user comment for when the function doesn't exist to base my function's logic. I submitted these to cflib.org today, so look for them soon. Examples:
long2ip(3401190660) = 202.186.13.4
ip2long(202.186.13.4) = 3401190660 Here is the code for UDFs below:



/**
 * Generates an (IPv4) Internet Protocol dotted address (aaa.bbb.ccc.ddd) from the proper address representation. Returns 0 if error occurs.
 * 
 * @param longip Numeric value of the address you want to convert. (Required)
 * @return Returns a String. 
 * @author Troy Pullis (tpullis@yahoo.com) 
 * @version 1, Jan 5, 2009 
 */
function long2ip(longip)
{
	var ip = "";
	var i = "";
    if (longip < 0 || longip > 4294967295) 
		return 0;
    for (i=3;i>=0;i--) {
        ip = ip & int(longip / 256^i);
        longip = longip - int(longip / 256^i) * 256^i;
        if (i>0) 
			ip = ip & ".";
    }
    return ip;
}

/**
 * Converts a string containing an (IPv4) Internet Protocol dotted address (aaa.bbb.ccc.ddd) into a proper address representation.  Returns 0 if error occurs.
 * 
 * @param ip Dotted address value you want to convert. (Required)
 * @return Returns a String. 
 * @author Troy Pullis (tpullis@yahoo.com) 
 * @version 1, Jan 5, 2009 
 */
function ip2long(ip) {
	var iparr = ListToArray(ip,".");
	if (ArrayLen(iparr) != 4)
		return 0;
	else 
	 	return iparr[1]*256^3 + iparr[2]*256^2 + iparr[3]*256 + iparr[4];
}

CFC for Building a Zip Code Proximity Search with ColdFusion

Fri, 07 Nov 2008 15:46:00 -0700

Back in Oct 2005, SysCon published an article I wrote in CFDJ magazine. Unfortunately, the Webmonkey.com tutorial I originally based my article on has been removed by Wired. I contacted them and hopefully they'll dig it up and repost it under their new wiki site. In the meantime, here is a link to the CFC file zipfinder.cfc used in that article. One more note... if you make use of my code examples from the CFDJ article, please write your queries using cfqueryparam, which I should have done in the first place.

Twin Cities CFUG November meeting taking shape

Tue, 28 Oct 2008 09:08:00 -0700

We have a great meeting planned for both intermediate as well as brand new CF developers. This meeting is Wednesday, Nov 5th at Easel Training. Room 1) Kurt Wiersma - BlazeDS
Have you ever wondered what BlazeDS is and what it can do? If so this session is for you. We will cover how you can install BlazeDS inside of CF and how CF can communicate with BlazeDS to make Flex applications get real time messages from ColdFusion. Room 2) Jason Dean - New Developer Breakout Session
This break out session will be an informal meeting for new developers and for developers who are new to CFML. Bring your questions or just learn the basics of ColdFusion. Both topics will be presented simultaneously in different rooms. Learn more at colderfusion.com

Dispute with web host gisol.com

Wed, 15 Oct 2008 23:23:00 -0700

Today I got into a financial dispute with my hosting provider of the last 8 months, Global Internet Solutions, gisol.com. They totally misrepresented a special rebate offer, whereby I was supposed to get my hosting plan at only $3.57/month. Apparently, they've done this to countless others, as this "report-gisol.com" website I found today has documented. Warning to all CF developers, never host with this company, they are crooks! Therefore, this blog is probably going away for the short term until I get going with another host. If/when they shut me down, you can visit my site here, setup under my Comcast ISP hosting. Hopefully I'll be back soon.

Whirlwind trip to BFusion/BFlex was a success

Tue, 09 Sep 2008 15:26:00 -0700

Just got back from the BFusion/BFlex conference and wanted to summarize my weekend. I joined a few local CF developers and we drove 11+ hours to Bloomington, Indiana to attend this FREE 2-day conference put on by some of the Adobe User Groups in conjunction with Indiana University. Day 1 was ColdFusion focused, and I was in the Intermediate Track. We spent a full day of hands on training learning about the Mach II framework. This was my first formal exposure to a framework and it was great to get a recap of OO principles and see them in action. Day 2 was Flex focused and I was in the Beginner Track. We started building a basic photo gallery application following the actual Adobe Flex course materials. I only stayed until noon as we had to get back on the road for the long drive home. Plus, I had a similar training back in April at the local Flex Camp in Minneapolis, so I don't think I missed too much. Highlights of the trip:
- good intro to Mach II, hope to start using it on a small app at work
- my 2nd exposure to Flex, I really need to put this into a work app soon!
- met some new CFers and had a good time at The Upland Brewery restaraunt
- got started on Twitter and now following over 20 others
- scored tons of swag for giveaways at CFUG (CF tag posters, Fusion Authority frameworks issue, Flex Authority first issue, etc)
- won a new book: ColdFusion 8 Developer Tutorial
- arrived there and back home safely as we covered approx 1400 miles by car

Connect for FREE using Adobe ConnectNow

Fri, 15 Aug 2008 22:30:00 -0700

This week I began using Adobe ConnectNow. This tool is a fantastic, money-saving alternative to WebEx or GoToMeeting for desktop sharing meetings. It is a perfect FREE solution for anyone who needs to share his/her screen with only 1 or 2 other people. I often need this capability at work to help support others using a Web application, or to show my coworker halfway across the country a walkthrough of some new development in progress. The only requirement is the Flash player 9 plugin which most people have installed in their Web browser of choice. ConnectNow features include:

unique meeting room URL
screen sharing
give desktop control to another user
webcam
chat pod
whiteboard

Learn more about ConnectNow at http://www.adobe.com/acom/connectnow A first-time user must sign up for an Acrobat.com account.
Sign up here - https://www.acrobat.com/#/connectnow/ConnectNowBegin
After this, you can visit the same URL and sign into your meeting room to start a new meeting.
Then you simply share your personal URL with up to 2 others to join your meeting for FREE. For example, here is the format of the personal URL, which I would email to my manager, Joe Smith. Then Joe would join my meeting as a guest.
https://connectnow.acrobat.com/mynamehere
Once Joe Smith submits this form, I receive an alert popup and must click "Accept" to bring Joe into my meeting.
It's as simple as that, thanks Adobe! PS: While you're checking this out, you should also give BuzzWord a try.

Seal Guard Systems pondering ColdFusion

Sat, 09 Aug 2008 15:37:00 -0700

I've started working with a new client over the past couple of weeks. Ken Wolfbauer and Kathi Wolfbauer of Seal Guard Systems who approached me to help with their HTML and SEO. We have been working together in their great showroom in Blaine, MN. I've suggested that they move their site under ColdFusion. I'm looking forward to working with Ken and Kathi to promote their products and services on the Internet, including Milgard fiberglass windows and Metro steel roofing. Hopefully I'll convince Kathi to start using ColdFusion so we can take their website to the next level.