Thursday, July 31, 2008

MySQL Hero-Function of the Month

MySQL has amazed me twice today. Once in a bad way and once in a good way.

The good news first: Today we have a new found Hero-Function from MySQL. (I have wanted this for soem time. Never found it until now. MySQL just has too much to offer!)

I have had to work with weekly reports quite a bit lately. Getting missing reports can be quite tricky and I feel I have gotten good at pulling data that isn't there. Finding the data that isn't in the database is hard to do. One of my previous reports was doing great until it case across a situation where a user submitted two reports in the same week. The main display is fine, but the linear report was broken - it was usign a GROUP_CONCAT() on the seven criteria for the weekly report. One of which had two records. The group_concat then had an extra item in it's delimited list. This effectively threw a wrench in the geers of the linear report.

Solution - get only the latest of each type of criteria. This also can be difficult without slowing down the query with sub-queries to get the max record in week X of type Y for project Z. Yes a pain in the rear.

Accomplishing the solution requires the new-found hero-function (Thank you once again to the genius folks at MySQL!)

This STR_TO_DATE(CONCAT(YEARWEEK(NOW()),'Monday'), '%X%V %W') gets any day of the given week without fail, without date_add minus the current day of the week + 1, or any other drawn out manual solution (of which there are probably many). I think this is by far the simplest, shortest and fastest. (I love it when those three get together!)

Now the bad news. (Not really all that bad. It's all good in the end [usually].)

Today, my favorite MySQL Hero-Function, the GROUP_CONCAT() function, failed me. It was concating some strings and it truncated the results. This not only cot the text short for a single item, but the remainder of the list was missing. Being that there were only 5 of the expected 7 items in the delimited string, it broke the report. (Yes, same report mention above.)

This was harder to find the solution to, but easier to fix. (No change of code or queries.)

Solution: In the my.ini file, add a line

My situation was slightly different from the reference. I first rummaged around in the MySQL GUI Administrator for the settings, found it, and set it.
It wanted a single number. It was defaulted to '1'. Like the reference above, I wanted to set it to '8'.
I set that and then checked the my.ini file which then had:

#The maximum length of the result of function  group_concat.

I restarted MySQL and the error was fixed on the report. I was pleased.

Just another fun day working with MySQL. Thanks guys!

Tuesday, July 22, 2008

CFC's - Arguments Scope Vs. local VAR

This morning I had some trouble in one of my CFC's. I thought I was a seasoned ColdFusion developer, but this morning I felt like a newbie. I learned that hard way about VAR'ing local variables inside CFC's. This is separate from SCOPING variables and this is only done inside functions (which make up CFCs).

I was always a little fuzzy about the "variables" scope being visible in the same component between CFC's. I had read it and knew it, but never had it crossed my path as being a problem. Occasionally, in a rare circumstance where I had code looping over a CFC I would get odd results. On a reload it would not repeat the problem. They were so rare in fact, and never cause errors, that I never took the time to investigate the occasional strange display issue that was difficult to repeat. I thought these were strange 'ghost' bugs. I digress.

So today I did my research. And, I cleaned up some of my CFC's so that there are no "Variables" scoped variables, only <cfset var myvarname = ""> initialized at the top of the CFC and below any <cfargument > tags. I found that I had been doing some of this:

<cffunction name="func_name" access="public" displayname="func_name" output="yes">
          <cfargument name="someID" type="numeric" required="No" default="0">
          <cfset arguments.loopCounter = 0>
          <cfloop ...

I tried to research the difference betwee the "Arguments" scope and the VAR scoping. I got no where. No one talks about it. They are both extremely local variables within the function, but they appear to be in separate scopes. I trusted this as I was cleaning up my CFC's and testing my code.

Then I ran into this:

<cffunction name="func_name" access="public" displayname="func_name" output="yes">
          <cfargument name="userID" type="numeric" required="No" default="0">
          <cfset arguments.userID = 0>

Error Occurred While Processing Request

Cannot declare local variable userID twice.

Local variables cannot have the same names as parameters or other local variables.

Now I know that the "Arguments" scope IS the local "VAR" scope. Hope that helps a few of you out there.

So this means that if you don't like the <cfset var someID = 0> you can use the real 'arguments' scope as <cfset arguments.someID = 0>.

If any java guru's out there can shed some light on any memory difference under the hood of the CFML engine between the local arguments scope and local var'ing, please do. I'm itching to know.

Monday, July 21, 2008

Linking dynamic CSS and JS files

A long while back I discovered a really smooth trick for creating server-side dynamic CSS and JavaScript files.

The web server takes care of all the work. Instead of this:

<link rel="stylesheet" href="/cssjs/css_pmtool.css" type="text/css" />
<SCRIPT langauge="JavaScript" src="cssjs/digit.js" type="text/javascript"></SCRIPT>

Do this:

<link rel="stylesheet" href="/cssjs/css_pmtool.cfm" type="text/css" />
<SCRIPT langauge="JavaScript" src="cssjs/digit.js" type="text/javascript"></SCRIPT>

And don't forget to put a content header in the CSS file. If I recall it made a difference. But didn't make any difference in the JS files.

<cfcontent type="text/css; charset=ISO-8859-1">


Wednesday, July 16, 2008

Javascript Life-Saver!

After much trial and head scratching while debugging a custom text input *hint* dropdown (much like the gmail email address hinting) I discovered how to prevent arrow key action without returning the function before the desired time:


   or, rather:

if (document.all) {
} else if (e.cancelable) {

Without this the up and down arrow keys to select hints also move the cursor the beginning or end of the input field (especially in Safari).
I found other (less effective) work-arounds which worked in all browsers except Safari on PC. So this little tid mit was a life saver indeed!
That's it!   It has almost the same effect as "return false;" without ending your script.

Thank you:

Full Setup!

I think it's all set, testing email post.


Tuesday, July 8, 2008



Blog created.

That's enough for today.