Saturday, December 27, 2008

Manual Query Safe List via ReReplace (Regex) on a Comma Delimited list of Numbers

So, I've been using this a little more often, here and here, because it's handy and I think it's really safe.

Making the string safe, easily and quickly:

<!--- not a digit or comma = replace it out. Allow only digits and commas = safe --->
<cfset variables.itemtypeid = REREPLACE(variables.itemtypeid,"[^\d,],","","ALL")>

On top of this, if you know on the processing side that there cna be a max of 20 items, truncate the list at the 20th. This makes it even safer.

Now you can safely query:

Select id, text
from table
where id IN (#variables.idlist#)

Happy SQLing...


And that's a wrap.

Thursday, December 18, 2008

2012: ? or !

So I'm cruising slashdot and read about holes in the natural magnetic shielding of our amazing planet Earth. These wholes are mysterious and shrink and grow, or come and go, whatever. We don't know too much. But then it reads that the other side of this issue with the Earth's magnetic shielding and these holes is solar particles form our nice, big Sun. It continued to read how the Sun is having increasing numbers of Sun Spots which will peak in intensity in 2012.

And I think to myself, 'That's the same year that the Aztec/Mayan calendars END!'. Why does all this junk have to align? This is worse than a believable conspiracy theory.

Slashdot:

The End:

Monday, December 15, 2008

Google Salt?

Google NaCl = Google Salt. 

A little elemental punning.  :)



Could be good, could be bad.

Tuesday, November 25, 2008

Hot-key for Firefox Google Search

I had searched long and hard for a hotkey to focus the browser google search box in Firefox. There isn't one.

But there is! CTRL+L focuses the address box, then tab focuses the Google Search box!

Yippee!



Sunday, November 2, 2008

Railo CFML Web Server - CFMail Gmail Set-up

Much thanks to Gary Gilbert over at http://www.garyrgilbert.com/blog/index.cfm/2008/9/15/Railo-3-Adding-Mail-Server

I was able to get my railo CFML home development server using my gmail account to send mail.
The only tricky part was that I was getting errors at first:

        "ERROR","web-0","11/02/2008","00:47","mail spooler","530 5.7.0 Must issue a STARTTLS command first. 6sm5231801ywi.1"

The solution is to put useTLS="true" right into the cfmail tag because it won't work in the railo-web.xml.cfm file.

        <cfmail to="you@gmail.com" from="getsoverwritten@bygmailaccount.com" subject="Railo Mailserver Test 2" useTLS="true">
                This is a test mail sent from railo.
        </cfmail>

But I'm rollin' now! Thanks!
 

Thursday, October 9, 2008

Layers over Embed Flash Objects - Issues Resolved Forever!

Catching Up - Starting with a small post.

I used to struggle with dhtml pop-ups and overlays - never getting them on TOP of flash or other embedded elements or objects.

That is a thing of the past when I invented this:

objtags = document.getElementsByTagName("EMBED");
for (x=0;x<objtags.length;x++) {
objtags[x].parentNode.style.visibility = "hidden";
}

That loops over all embedded objects and makes them invisible. (Not display:none; because that will affect the page layout.)

We put the above code in whatever javascript opens the layover. Inversely, we put the below script in the javascript function which closes the layover:

objtags = document.getElementsByTagName("EMBED");
for (x=0;x<objtags.length;x++) {
objtags[x].parentNode.style.visibility = "visible";
}

And that's a wrap!

Thursday, September 4, 2008

MySQL UNION vs. UNION ALL

I just learned a new quirk with MySQL.

If you a SQL buff you might find this interesting. This is definitely going on my blog. It's cool:

MySQL rules:

  1. Querieswith a UNION end up doing a AUTO-DISTINCT type of functionality. Thismakes sense because later the secondary unioned queries are onlysupposed to get new unique records. (ie. first query alone returns 10,second query along returns 6 but 3 are the same as the first query, soonly return 3 resulting in 13 records total.)

  2. However,UNION ALL is supposed to override that and get all records no matterwhat. (ie, in the above example this would return all 16 records)

  3. However,if the first query in the UNION has *results* that have two recordsthat appear identical (ie. if you are not returning primary keys, youonly select first and last name - and you have two real people named"John Doe" then results are identical) and the second unioned query haszero records, UNION ALL does not have the expected results. You onlyget one result on John Doe.
    This is especially bad, for example, ifyou are summing hours for each person, say for a timecard. If bothemployees named John Doe had 40 hours, the *results* are stillidentical.

    This was an issue of four unioned queries, inmy tests I was only doing UNION ALL on the first two queries which werethe ones having this issue. I learned thatmixing "UNION" with "UNION ALL" DOES NOT WORK. If you want UNION ALL results, all unions must be UNION ALL. If any union is missing the 'ALL' then all 'ALL's are ignored.

Solution #1: Derived Queries - heaven sent!

  1. Selectthe primary keys and they wrap the unioned queries in another query toperform the SUMs that you need and don't select the PKs in the outerquery.

Solution #2: UNION *ALL*

  1. As explained in the SQL rule #2 above, simply making each "UNION" statement be "UNION ALL" will solve the issue.
*** *** ***

Some examples of simple derived queries:

select fname, lname, hours
from (select 'John' as fname, 'Doe' as lname, 40 as hours
UNION
select 'John' as fname, 'Doe' as lname, 40 as hours
UNION
select 'Joe' as fname, 'Shmoe' as lname, 25 as hours
UNION
select 'Sam' as fname, 'Dam' as lname, 37 as hours
) as D1
where hours > 0


GETSQL - 3 Records
FNAME HOURS LNAME
John 40 Doe
Joe 25 Shmoe
Sam 37 Dam

_________________________________

WITH ID's but same results:

select fname, lname, hours
from (select 1 as ID, 'John' as fname, 'Doe' as lname, 40 as hours
UNION
select 2 as ID, 'John' as fname, 'Doe' as lname, 40 as hours
UNION
select 3 as ID, 'Joe' as fname, 'Shmoe' as lname, 25 as hours
UNION
select 4 as ID, 'Sam' as fname, 'Dam' as lname, 37 as hours
) as D1
where hours > 0

GETSQL - 4 Records
FNAME HOURS LNAME
John 40 Doe
John 40 Doe
Joe 25 Shmoe
Sam 37 Dam



Larger Unioned Query Examples:

select *
from (select 'John' as fname, 'Doe' as lname, 40 as hours
UNION ALL
select 'John' as fname, 'Doe' as lname, 40 as hours
UNION ALL
select 'Joe' as fname, 'Shmoe' as lname, 25 as hours
UNION ALL
select 'Sam' as fname, 'Dam' as lname, 37 as hours
) as D1
where hours > 0
UNION ALL
select *
from (select 'Jim' as fname, 'Dim' as lname, 35 as hours
UNION
select 'Ken' as fname, 'Doll' as lname, 36 as hours
UNION
select 'Rod' as fname, 'Ram' as lname, 37 as hours
UNION
select 'Jack' as fname, 'Black' as lname, 38 as hours
) as D2
where hours = 0 ## Make sure second query returns none, just for this example


GETSQL - 4 Records
FNAME HOURS LNAME
John 40 Doe
John 40 Doe
Joe 25 Shmoe
Sam 37 Dam

_________________________________

Mixed "UNION" and "UNION ALL"

select *
from (select 'John' as fname, 'Doe' as lname, 40 as hours
UNION ALL
select 'John' as fname, 'Doe' as lname, 40 as hours
UNION
select 'Joe' as fname, 'Shmoe' as lname, 25 as hours
UNION ALL
select 'Sam' as fname, 'Dam' as lname, 37 as hours
) as D1
where hours > 0
UNION ALL
select *
from (select 'Jim' as fname, 'Dim' as lname, 35 as hours
UNION
select 'Ken' as fname, 'Doll' as lname, 36 as hours
UNION
select 'Rod' as fname, 'Ram' as lname, 37 as hours
UNION
select 'Jack' as fname, 'Black' as lname, 38 as hours
) as D2
where hours = 0 ## Make sure second query returns none, just for this example

GETSQL - 3 Records
FNAME HOURS LNAME
John 40 Doe
Joe 25 Shmoe
Sam 37 Dam

__________________________________

select *
from (select 'John' as fname, 'Doe' as lname, 40 as hours
UNION ALL
select 'John' as fname, 'Doe' as lname, 40 as hours
UNION ALL
select 'Joe' as fname, 'Shmoe' as lname, 25 as hours
UNION ALL
select 'Sam' as fname, 'Dam' as lname, 37 as hours
) as D1
where hours > 0
UNION
select *
from (select 'Jim' as fname, 'Dim' as lname, 35 as hours
UNION
select 'Ken' as fname, 'Doll' as lname, 36 as hours
UNION
select 'Rod' as fname, 'Ram' as lname, 37 as hours
UNION
select 'Jack' as fname, 'Black' as lname, 38 as hours
) as D2
where hours = 0


GETSQL - 3 Records
FNAME HOURS LNAME
John 40 Doe
Joe 25 Shmoe
Sam 37 Dam

__________________________________

select *
from (select 'John' as fname, 'Doe' as lname, 40 as hours
UNION
select 'John' as fname, 'Doe' as lname, 40 as hours
UNION
select 'Joe' as fname, 'Shmoe' as lname, 25 as hours
UNION
select 'Sam' as fname, 'Dam' as lname, 37 as hours
) as D1
where hours > 0
UNION
select *
from (select 'Jim' as fname, 'Dim' as lname, 35 as hours
UNION
select 'Ken' as fname, 'Doll' as lname, 36 as hours
UNION
select 'Rod' as fname, 'Ram' as lname, 37 as hours
UNION
select 'Jack' as fname, 'Black' as lname, 38 as hours
) as D2
where hours = 0


GETSQL - 3 Records
FNAME HOURS LNAME
John 40 Doe
Joe 25 Shmoe
Sam 37 Dam



And that's a wrap!

Friday, August 22, 2008

Capitalize the first letter of every word in a string

Just a little post for today, I ran into a small situation where I wanted to capitalize the first letter of every word in a string and lowercase all others. There were various ways of doing this on the net, But I never found this way:

REReplaceNoCase(REReplaceNoCase(LCASE("TeSt tHIS sTrInG"),"(^[a-z])","\U\1"),"(\s[a-z])","\U\1")

It seems to work flawlessly and could so easily be turned into a function.

Have fun.


Wednesday, August 13, 2008

FireBugLuv

The love is strong these days. It's like they read my mind! It's only second to the relationship with my wife..when she's not stressed... and the kid is asleep... and she's gotten her to-do's done... I better stop there. But it's strong, buys, strong.

Two weeks ago I finally post a bug report. (I never do it. I read a lot of posts and forums, but I never [rarely] post issues. I don't know, after this good experience I will more often.) It was for one very annoying firebug *feature* and I got two great responses right away, so fast they were that I wasn't able to follow up on then and testing things until the next day. From these responses I learned that Firebug is not independent-per-tab as I had previously thought, and ignorantly expected. That was the bug report I posted and was quickly schooled on. It is not an independent firebug per tab (as I think it used to be, I may have always been in the dark on that), nor is independent-per-window, as it used to be. It's a single firebug engine. Smart.

Having learned that one fellow openly requested help on how to word this somewhere in the window or in the sites-enabled/disabled pop-up window so that this could be made clear. That's a tough one. Even after I understood I had no apifany for a better solution. ... But I did had a cool idea (and someone read my mind as I thunk it) -- why not make an easy toggle option to turn on/off the firebug engine so, even for enabled sites its only running when you want to use it! Walla, that solves all my problems for gmail in tabs with develpment sites needing firebug. So I submitted that in the bug thread I had submitted along with a good 'thank you' to both smart gentlemen who had responded. The next day one guy responds again to tell me that the latest dev build has that feature. (See, they read my mind! - How do they do it? Wait, I don't want to know, just keep it up, I'll keep thinking engenius thoughts.) The toggle works AWESOME!!!

Then the next dev build (two more days later - that's pretty fast, men!) has the toggle even better (I must have thought it while I was sleeping because I don't remember this one. it must be the hive intelligence). It's got an auto-toggle feature. It works like this: Your on your dev site, firebug is working it's cool jigginess, and you swap to gmail - it auto-toggles off! - you swap back to your dev site, it's still off, but that's okay, the minute I want a peek at the code I click the bug and it's on again showing it's magic!

And that is how the sweet stuff gets sweeter. You gotta love it! Yeah, it's strong.

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!)
STR_TO_DATE(CONCAT(YEARWEEK(NOW()),'Monday'), '%X%V %W')

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.
group_concat_max_len=8k

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:

window.event.returnValue=false;

   or, rather:

if (document.all) {
    window.event.returnValue=false;
} else if (e.cancelable) {
    e.preventDefault();
}


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: http://www.webdeveloper.com/forum/showthread.php?t=93262

Full Setup!

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

Go!

Tuesday, July 8, 2008

Beginning

Whew!

Blog created.

That's enough for today.