Category Archives: ColdFusion

Chris 10:02 pm on May 23, 2014
Tags:   

ColdFusion Builder 3 Fuzzy Features

Between ColdFusion Builder 2 and 3, I used Sublime Text editor for about a year. One of the best features that Sublime Text had was fuzzy searches.

For example, on code assist, for a cfqueryparam attribute, I could type “var” and get type=”cf_sql_varchar”. In ColdFusion Builder, you’d have to type “cf_sql_v” to get the same. May not seem like much, but when your brain is focusing on the differences, why should I have to type “cf_sql_” when everyone has that?

Another example would be when using the “Goto Anything” feature, I regularly knew approximately what file name I was looking for, but maybe not exact. So if I type “page”, I may get 30 results. Then I continue to type to get “pagedsp” and find my file “pagedisplay.cfm”. Very handy and quick.

In ColdFusion Builder 3, everything relies upon starting from the beginning of line and pretty exact. It’s very rigid. The file content searches also always take a bit of time.

2014-05-23_1654

 

The alleviate the file search issue, I installed the InstaSeach plugin : http://marketplace.eclipse.org/content/instasearch

This plugin instantly returns fuzzy results if your keyword matches text inside the current open file, a file name or content inside a file — INSTANTLY —

Highly recommend to replace your Search > File and Search > Search

 

To alleviate the code assist issue:

Go to Preferences->ColdFusion->Profiles->Editor->Code Assist and select option ‘Filter Proposals Containing Text’

Thanks to Ramchandra Kulkarni for this tip.

Now I can just type “v”, arrow down once to pass up “cf_sql_longvarchar” and hit enter.

 

It’s the little things in life.

#coldfusion-2

Chris 9:20 pm on March 17, 2014
Tags: , , , Microsoft Web Platform Installer   

IIS URL Rewrite Config for FW/1 SES

SES_Screen_ShotAfter a bit of research, I was never able to find a definitive answer as how to properly set up SES (Search Engine Safe URL’s) to work with FW/1 (Framework 1) using IIS 7.5 and IIS URL Rewrite 2.0.

SES makes turns your URL’s from this:

http://www.mysite.com/index.cfm?action=main.default&ID=0

Into this:

http://www.mysite.com/main/default/ID/0

First of all you may need to install URL Rewrite 2.0 using Microsoft Web Platform Installer. There are other options out there, but I’m using this since it’s simple and nicely integrated.

From the URL Rewrite options screen, add a new rule and select “User-friendly URL” under the “Inbound and Outbound Rules”.

The requested URL should match the pattern using regular expressions. The pattern being:

^(.*)$

Add the conditions that the type is not a file or a directory.

The action type is rewrite and the rewrite URL is:

/index.cfm/{R:1}

Be sure to check “Append query string” and “Stop processing of subsequent rules”

Continue reading →

#coldfusion-2, #fw1, #iis, #microsoft-web-platform-installer

Chris 7:35 pm on March 17, 2014
Tags: ,   

Default TimeStamp Gotcha with ColdFusion ORM

Let’s say for example you have a simple forum on your site. When ever a new post is added, you want to associate a date/time with the post.

Using SQL and ColdFusion there are three ways to do this:

INSERT INTO
    Forum(body, dateTimeInserted)
VALUES(
    <cfqueryparam value="#form.body#" cfsqltype="cf_sql_longvarchar">,
    <cfqueryparam value="#now()#" cfsqltype="date">
)

OR

INSERT INTO
    Forum(body, dateTimeInserted)
VALUES(
    <cfqueryparam value="#form.body#" cfsqltype="cf_sql_longvarchar">,
    GETDATE()
)

OR

Set your default column value to ‘GETDATE()’ (in MSSQL)

INSERT INTO
    Forum(body)
VALUES(
    <cfqueryparam value="#form.body#" cfsqltype="cf_sql_longvarchar">
)

The third has always been the most recommended method. The reason being less network traffic and the SQL Server is the common denominator between the SQL Server and your application servers.

Now introduce ColdFusion ORM into the mix. The default value method still works when you set the property attribute of “insert” to false in your bean.

component persistent="true" table="Forum" {
    property name="ID" fieldType="id" generator="native";
    property name="body";
    property name="dateTimeInserted" insert="false";
}

However the case where it doesn’t work as needed is when you save the entity and load all the entities within the same ORM Session (ColdFusion request).

So let’s say our controller method looks like this in FW/1:

void function forum( required struct RC ) {
    if( structKeyExists( RC, "body" ) ) {
        getForumService().add( body = RC.body );
    }
    RC.forumEntities = getForumService().get();
}

Because the entity we just saved is still in the same ORM session, it doesn’t look at the database again for it. But because we rely upon SQL to add the timestamp, ORM doesn’t know about it yet. Thus it returns an empty string instead of the date and time it was added.

So we get something like this record set:

1|’Body 1’|’1/25/2014′
2|’Body 2’|’1/26/2014′
3|’Body 3’|”

This is of no use to me. I suppose I could loop through the array and if one has an empty date/time added value, then reload that entity, but that seems like overkill for my application.

So in the end, unless precise date/time stamps are needed, I’m going to use the application server’s date/time (now()) instead of SQL’s GETDATE() default.

void function add( required string body, required date dateTimeInserted ) {
    var forumEntity = entityNew('forum');
    forumEntity.setBody(arguments.body);
    forumEntity.setDateTimeInserted(arguments.dateTimeInserted);
    entitySave(forumEntity);
    ormFlush();
};

#coldfusion-2, #orm

Chris 5:26 pm on May 15, 2013
Tags: CF Builder, Resources,   

CF Builder vs Sublime Text 2 Resources Comparison

Now that I’ve got my Sublime Text 2 plugins all installed and configured to what I mostly prefer, here’s what both take up in terms of resources when first booted up:

IDE RAM Disk
CF Builder 2.0.1 185,420 K 525 MB
Sublime Text 2.0.1 47,232 K 20 MB + 67.5MB App Data

Also take into consideration that Sublime Text takes only a few seconds to start, which ColdFusion Builder takes around 1/2 minute.

Now if I could only get some better “IntelliSense” type support for CF like CFC introspection – that’d be awesome!

#cf-builder, #resources, #sublime-text

Chris 4:05 pm on January 21, 2013
Tags: , dateAdd, dateFormat, shortcut   

DateFormat() Add Day Shortcut

I ran into some old code that adds 7 days to a date and spits it out in a DateFormat().

Normally you’d do this:

dateFormat( dateAdd( 'd', 7, now() ), 'mm/dd/yyyy' );

But the code I ran into doesn’t use the dateAdd() method. It just simply adds days.

dateFormat( now() + 7, 'mm/dd/yyyy' );

Though I can’t find this shortcut documented anywhere it seems to be working well with at least ColdFusion v8 and v9.

#coldfusion-2, #dateadd, #dateformat, #shortcut

Chris 10:37 pm on December 7, 2012
Tags: , ,   

Getting the MS SQL Identity ID With a Complex UPDATE / INSERT Statement

As I posted in my blog entry MSSQL Update/Insert Based Upon Existing Record, it is better to use “IF @@ROWCOUNT=0” rather than “IF EXISTS ()” when updating or inserting a record based upon existence. This is because the preferred method only does a table/index scan once, while the later will do it twice.

What if you need the Identity Column ID whether the record is updated or inserted? Normally in ColdFusion you can use “queryResult[“GENERATEDKEY”]”, however what you will find is this variable only works on simple insert statements. If you try to use @@IDENTITY or SCOPE_IDENTITY, you will find it only returns the Identity Column ID when the insert is triggered.

Introducing MS SQL’s OUTPUT clause, introduced in MS SQL 2005. We can use the OUTPUT clause to return the Identity Column ID(s).

When we add this to the INSERT and UPDATE clauses, the problem we run into is that during an insert it will return an empty set and another set containing the Identity Column ID that was inserted. Therefore we use a temporary table to help us with this.

Now with a temporary table, we introduce back in the second table scan. However this table scan will have very limited rows, if not just one. Plus the temporary table will not need disk access. So the second table scan is of no consequence.

So with all this in mind here’s an example that returns the Identity column that was either inserted or updated. Keep in mind that multiple IDs will be returned as separate rows if more than one table row was updated.

<cfquery name="local.qSetCart">			
	DECLARE @T TABLE (
	    cartID INT NOT NULL
	)
	
	UPDATE
		UserCart
	SET
		dateModified = GETDATE(),
		isGift = <cfqueryparam value="#arguments.isGift#" cfsqltype="cf_sql_bit">
	OUTPUT
		INSERTED.cartID INTO @T
	WHERE
		userID = <cfqueryparam value="#arguments.userID#" cfsqltype="cf_sql_integer">
	
	IF @@ROWCOUNT = 0
	
		INSERT
			INTO UserCart ( userID, isGift)
		OUTPUT
			INSERTED.cartID INTO @T
		VALUES (
			<cfqueryparam value="#arguments.userID#" cfsqltype="cf_sql_integer">,
			<cfqueryparam value="#arguments.isGift#" cfsqltype="cf_sql_bit">
		)
	
	SELECT cartID FROM @T
</cfquery>
		
<cfreturn local.qSetCart.cartID>

#coldfusion-2, #identity, #sql

Chris 8:34 pm on November 30, 2012  

Develop For The Future

Let’s say we start a specialized e-commerce site that sells fishing lures that are dropped shipped from Henry’s, a supplier. We found a good deal with Henry’s and we’re so excited to start generating revenue and quite our day jobs a pencil pushers. To get this site up and running we create some database tables:

  • HenrysProducts
    • SKU
    • Brand
    • Model
    • Price
  • Orders
    • OrderID
    • Name
    • Address
    • ShippingPrice
    • Tax
    • OrderTotal
    • TrackingNumber
    • ShippingCarrier
    • DateShipped
  • OrderItems
    • OrderID
    • SKU
    • Quantity
    • Price

You slap up a few files:

  • index.cfm
  • products.cfm
  • cart.cfm
  • shippingInfo.cfm
  • paymentInfo.cfm
  • orderConfirmation.cfm
  • contactUs.cfm

Put in some images and “bam” you’re rolling. The lures you’re selling are “hot stuff” and you start bringing in revenue. Yea!

Oh! What’s this? An order for $500 worth of lures? Yes!

The order goes through, but Henry’s says “Sorry, we don’t have 100 of the 500 lures you ordered. We’ll place those on back-order for you.”. You think, “Okay, no problem.”. But wait! How are we going to handle that? Time for a new table and some logic to handle emails when things are backordered, cancelled and shipped:

  • HenrysOrderFulfilment
    • OrderID
    • SKU
    • QuantityShipped
    • QuantityBackOrdered
    • QuantityCancelled
    • ShipmentID
  • HenrysShipments
    • ShipmentID
    • TrackingNumber
    • DateShipped
    • Carrier

Now that we’ve started getting popular, people really want some items that are only available from the manufacturer. But now we have everything setup for Henry’s, not thinking ahead for the future. So we have a few options:

  1. Keep using what we have and append a column to tell us where it’s really coming from
  2. Create new tables for the new manufacturer
  3. Refactor the tables to be more generic

And of course, we should opt for #3. This should have been where we started in the first place. You should never assume your business model will never change, your contract might never expire from your supplier or that your business will never grow. Because with that kind of thinking, it becomes much more costly to grow your business having to deal with the side affects of your starting decisions. That growing time also usually involves lack of time because you become so busy with the messy system. Here’s an example of how it should have been built in the first place.

  • Products
    • ProductID
    • Brand
    • Title
  • ProductChildren
    • ProductChildID
    • ProductID
    • ModelTitle
    • SKU
    • UPC
    • Price
    • Weight
  • ProductSupplierLookup
    • ProductChildID
    • SupplierID
    • Cost
    • Priority
  • Suppliers
    • SupplierID
    • Name
  • Orders
    • OrderID
    • Name
    • Address
    • ShippingPrice
    • Tax
    • OrderTotal
    • TrackingNumber
    • ShippingCarrier
    • DateShipped
  • OrderItems
    • OrderItemID
    • OrderID
    • SKU
    • Quantity
    • Price
  • OrderFulfilment
    • OrderItemID
    • SupplierID
    • ItemCost
    • QuantityOrdered
    • QuantityShipped
    • QuantityBackOrdered
    • QuantityCancelled
    • ShipmentID
  • Shipments
    • ShipmentID
    • TrackingNumber
    • DateShipped
    • Carrier
    • Method

With this type of structure you can add multiple supplier, order from multiple suppliers for each customer order using a priority number, handle back-orders and cancellations, use the most cost efficient supplier, change, add and remove suppliers and keep track of multiple packages shipped.

Of course this is a fairly basic structure, but it’s a good way of thinking forward whether you’re starting from scratch or enhancing your system.

That touches the SQL aspect of it. However if you re-factored your code using the above file structure, I’m betting you’re code is complex and messy. You may have even coded it a year ago and forgot where everything was. You may even have a different developer or developers working on the enhancement.

This is why you should always split out your view code into .CSS, .JS and template files to generate HTML. You should also be using CFC’s, if using ColdFusion, and a FrameWork is a must. You now have a much more manageable file structure that you can manipulate your code with much more ease. Also don’t forget your unit testing so you reduce your chances of accidentally breaking something. I suggest using something like FW/1 as it’s lightweight and easy, one you get the hang of it.

Chris 5:02 pm on November 19, 2012  

Hacking ColdFusion 9 Application Scopes

You can find many hidden jems in the coldfusion.runtime.ApplicationScopeTracker Java class.

appTracker = createObject( 'java', 'coldfusion.runtime.ApplicationScopeTracker' );
writeDump( var = appTracker );

List all application scope names:

applications = appTracker.getApplicationKeys();
while( applications.hasNext() ) {
writeOutput( applications.next() & '<br>' );
}

Dump a specific application scope:

appScope = appTracker.getApplicationScope( 'myAppName');
writeDump( appScope );
Chris 2:23 am on October 26, 2012
Tags: cfquery, , GENERATEDKEY, , NOCOUNT,   

“Element GENERATEDKEY is undefined…” after ColdFusion SQL Statement

This is a followup post to “Returning IDENTITY ID via ColdFusion 9′s CFQuery“.

Today I created an Insert statement that I needed the new identity value returned. It’s pretty simple, calling the result.generatedkey as such:

<cfquery result="myResult">
    INSERT INTO users( username )
    VALUES( 'test' )
</cfquery>
<cfreturn myResult["GENERATEDKEY"]>

The above code would normally result in the new identity value created for that record. However I received a “GENERATEDKEY is undefined…” error. Not sure what to think I dumped out the myResult variable, put the query into a file of itself and even tried using myResult.IDENTITY instead to no avail. After taking awhile figuring this out and ended up going home for the day and trying there again after a couple of hours. All of a sudden the standalone test worked!

So I’m thinking, okay this has to be some setting somewhere, and I was right. I narrowed it down to a query that runs before it during the login process for my website.

SET NOCOUNT ON

UPDATE TABLE1
SET COL1 = 'myVal'

Notice that I didn’t set “SET NOCOUNT OFF”. There lies the issue. Apparently when you set NOCOUNT off and keep it off, ColdFusion doesn’t retrieve the new Identity value. So the following code fixed my issue:

SET NOCOUNT ON

UPDATE TABLE1
SET COL1 = 'myVal'

SET NOCOUNT OFF

#cfquery, #coldfusion-2, #generatedkey, #identity, #nocount, #sql

Chris 6:27 pm on October 23, 2012
Tags: , join, query, , sub-query, undefined   

ColdFusion CFQuery Zero Records and the “Element x is undefined in x” Error

When you run a CFQuery tag in ColdFusion you “will always” get a query object returned whether or not any results where found. But recently, after running a complicated query in ColdFusion 9.0.1, I found this to not be true. When there where results, everything worked as expected. However, when there where no results then I received a “Element x is undefined in x.” error when calling the CFQuery’s name variable. Here’s a simplified example of my code:

<cfquery name="local.products">
SELECT P.product
FROM Products P
JOIN (
    SELECT PC.productID, MIN( PC.price ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
</cfquery>
<cfdump var="local.products">

After banging my head and doing some research I ran through this thread that helped me resolve the issue: http://forums.adobe.com/message/2595679 . They mention a couple of bug ID’s, but Adobe updated their bug-base and I can’t find them. So at some point I’ll create a new ticket for this issue.

The problem lies in the fact that my SQL was generating a warning message inside the joined sub-query and CF wasn’t handling that very well. One fix is to fix why this message is being generated in the first place by adding a ISNULL() or COALESCE() around the column the MIN() method is reading. For example:

<cfquery name="local.products">
SELECT P.product
FROM Products P
JOIN (
    SELECT PC.productID, MIN( COALESCE( PC.price, 0 ) ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
</cfquery>
<cfdump var="local.products">

The second method would be to add “SET ANSI_WARNINGS OFF;” to the beginning of the SQL statement. For example:

<cfquery name="local.products">
SET ANSI_WARNINGS OFF;

SELECT P.product
FROM Products P
JOIN (
    SELECT PC.productID, MIN( PC.price, 0 ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
</cfquery>
<cfdump var="local.products">

The first fix is probably the best method to choose, however both of these resolved my issue.

There are other workarounds such as using the JDBC driver, but this approach seems to be the most practical.

#coldfusion-2, #join, #query, #sql, #sub-query, #undefined