A table with a Totals column is a perfect use case for computed columns in MSSQL. Making the Totals column a computed column allows MSSQL to do all the preassigned math for me, eliminating the need to write SQL or ColdFusion code to sum up the monthly totals. Every time I update the table I would have to run other code or SQL to again obtain the correct total.

Presented with a string of concatenated ID's, I had to break them up into a comma delimited list. These ID's were all stored in a database field concatenated in one long string with no breaks, tabs or other delimiters. Why, I don't know.

Fortunately, I knew that all the ID's were 6 characters each. I just had to come up with the regular expression to separate them and insert a comma and a space.

With this filter, the regex removes all non-numeric characters except the decimal points. The Round function does it's job rounding the closest integer.

I wanted to use only regex but regex cannot do math, i.e., round the decimal. The first statement only removed "$" and "." and ",". With JavaScript filtering on the front end the first example addresses 99% of the cases but the second is more thorough and removes all occurrences of non-numeric characters except decimal points. The only downfall to both is if there is more than one period / decimal point.

Converting a date in ColdFusion into a UNIX timestamp format is not straight forward if you don't know what the UNIX timestamp format is. Unix time is defined as the number of seconds that have elapsed since midnight Coordinated Universal Time (UTC), 1 January 1970. Here's how you convert using ColdFusion.

ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.orgDateDiff("s", CreateDate(1970,1,1), CreateODBCDateTime(Now()))1DateDiff("s", CreateDate(1970,1,1), CreateODBCDateTime(Now()))

Want to find out what Google has on your web site? Do you wish to tabulate a list of all the pages Google has searched and indexed? Here's how to document it! Caveat: you must have a google business account to use the API and not have ads returned with your results.

To view what google has indexed, go to Google, of course, and type in your site's domain, prefixed with "site:", e.g., "site:mysite.com". This will give you the first 10 results. You could copy and paste, but you are too smart and lazy for that.

This tutorial will NOT go into how to set up Google Custom Search or access the API. I'm assuming you either already know how to do that or will look that up on your own. The Google Custom Search API returns a whole bunch of stuff, but we only want in this case, the URLs of every page indexed.

I have the CFM template in its entirety below, but we'll step through what's going on here first.
The form which calls itself for the action.

To submit the form, you would want to enter, "site:yourdomain.com". Or you could put in whatever search terms you wish.
Once the form is submitted, we want to format the search terms by converting the spaces to plus signs (+). The query string will be appended to the API call's URL.

We want to set our counter and make sure we get a maximum of 1000 returns, if there are that many. This will error out at the end. We are going to go in increments of 10 because that's the default number of results Google will return.

The results come back in groups of 10, so we have to loop this set and write to our file, which should already be created and in place. You can output to the screen too, but that's a bit too much. For testing I set the CountVar to 100 and returned to the screen, a reasonable amount for verification.

Trim characters off of right on a string.
Trim off a certain number of characters on the right side of a string. Here's a quick easy method to cut off an exact number off the right portion without reversing the string and using various other string manipulation techniques.
The concept is simple, find the length of the string

MSSQL error: The data types text and varchar are incompatible in the equal to operator.

In the ColdFusion administrator under there is the option to save CLIENT variables as cookies, in the registry, a datasource or not at all.

If the client variables are stored as cookies, it is fairly easy to retrieve and parse to determine things like the identity of a returning user from the cookie. This cookie is called CFCLIENT_<cfapplication name>.
If the client variables are stored in the database, it's not so easy. Actually impossible, the cookie variables are NOT stored in the datasource.

First to locate the CLIENT variables, which are stored in a table called CDATA. CFGLOBALS client variables are session variables, like CFID, CFTOKEN and timestamp details and are stored in the table CGLOBAL.

So what about the error? The data types text and varchar are incompatible in the equal to operator.
I ran some queries to try and see if there was some information I could glean from the client variables table.

ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.orgSELECT TOP 1000 [cfid]
,[app]
,[data]
FROM [hoton].[dbo].[cdata]
where [data] != ''1SELECT TOP 1000 [cfid]2 ,[app]3 ,[data]4 FROM [hoton].[dbo].[cdata]5 where [data] != ''

Error: The data types text and varchar are incompatible in the not equal to operator.

ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.orgSELECT TOP 1000 [cfid]
,[app]
,[data]
FROM [hoton].[dbo].[cdata]
where [data] is null1SELECT TOP 1000 [cfid]2 ,[app]3 ,[data]4 FROM [hoton].[dbo].[cdata]5 where [data] is null

ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.orgSELECT TOP 1000 [cfid]
,[app]
,[data]
FROM [hoton].[dbo].[cdata]
where [data] is not null1SELECT TOP 1000 [cfid]2 ,[app]3 ,[data]4 FROM [hoton].[dbo].[cdata]5 where [data] is not null

ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.orgSELECT TOP 1000 [cfid]
,[app]
,[data]
FROM [hoton].[dbo].[cdata]
where [data] not like ''1SELECT TOP 1000 [cfid]2 ,[app]3 ,[data]4 FROM [hoton].[dbo].[cdata]5 where [data] not like ''

which pulled up what I wanted to look at, but unfortunately could not use. No cookie info is stored in with the datasource client session storage option in the ColdFusion administrator.

I was looping an array and and using each item in a cfqueryparam. Running a query in each loop. Each iteration was displaying a null query result, which was incorrect. The same problem occurred with a list of the same items.

The simple answer was to surround the cfqueryparam value with the Trim() function! It worked with the list loop too.

ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org<cfqueryparam value="#Trim(MSArray[j])#" cfsqltype="CF_SQL_VARCHAR">1<cfqueryparam value="#Trim(MSArray[j])#" cfsqltype="CF_SQL_VARCHAR">

Let's take a look at the data we are gathering. I created this table to keep data which I thought would be pertinent.

Hey this image will be loaded soon!

Here's a key explaining the basics on this table:
ATTUID = individual user id
RequestID = Individual ID per request (more below)
Body = the actual query
Line = line number of query or stored procedure
Name = assigned name of query or stored procedure
CFScript = not what you think, this is the script name calling the query or stored procedure
CFTimeStamp = ColdFusion's time stamp
SQLTimeStamp = MS SQL's time stamp
Type = query or stored procedure

ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org<!--- Set up Logging Service - call it once and reuse it --->
<cfset CFLog = createObject("component","optimization.cfc.Optimization_Code_Log").init() />
<cfset CFLogger = createObject("component","optimization.cfc.Optimization_Code_LogDAO").init(application.sqlSource) />1<!--- Set up Logging Service - call it once and reuse it --->2<cfset CFLog = createObject("component","optimization.cfc.Optimization_Code_Log").init() />3<cfset CFLogger = createObject("component","optimization.cfc.Optimization_Code_LogDAO").init(application.sqlSource) />

Add this in to help keep individual page requests identified together.

A little detail about this... I desired a random number be generated but wanted it to be a whole number. The Rand() function creates a decimal. I am treating the Rand() produced number as string, removing the preceding "0.", and converting to a number with the Val() function. The number length varied, so multiplication would not be work.

To get the data we want we are going to query the debugging data, "queries" called above, as shown below:

Loop the query containing all the debug data for queries run on your template, then set the data in the bean using the Illudium PU-36 setters, e.g. CFLog.setBlahBlah(variablescope.blahblah). Once they are all set, then we save using the generated DAO.

You can see that we are gathering more information than what was provided from the service factory, for example, the client user id, and several CGI variables.

We do the same thing for the Stored Procedures, except for one small but important change. Note also that I do not have to create another set of bean and DAO objects, I just reuse the existing ones, CFLog and CFLogger.

That change is for Name. Inexplicably, in successive Stored Procedure calls in my template, the query output name remained the same for the following Stored Procs, ignoring the actual name. I had to dig down into the result column output to get the correct name for the Stored Procedure called.

ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org<cfset CFLog.setName(cfdebug_storedproc.result[cfdebug_storedproc.currentRow][1].name) />1<cfset CFLog.setName(cfdebug_storedproc.result[cfdebug_storedproc.currentRow][1].name) />

If you pull a <cfdump> on the query output, you'll readily see from where this came, and possibly the situation in to which I ran.

Here is the entire chunk of code for my logging template. I'll not provide the Illudium PU-36 generated bean and DAO unless there are some requests. It's easy enough to generate your own.

Why would you want to create your own custom debugging template? In my case, I wanted to create a feature that would log actions from a particular template when accessed only by particular users. It's on a shared development server where debugging is normally not utilized, thus no conflicting debugging usage. I dropped in my custom debugging (logging) template, turned on debugging in the CF Admin and added the appropriate IP addresses of the developers we were logging. There are certainly other ways to skin this cat, for example, we could have dropped in the debug/log template into any directory and added a <cfinclude> in the template we were tracking. But, in my particular case, we didn't want to make any changes to existing code.

Next, I'll go into detail on how I created a Custom Debugging template (logging template) utilizing the ColdFusion Service Factory.