CTE with Recursion Example

Common Table Expressions with Recursion using WITH. A Common Table Expression is a temporary result set upon which we can work against. It's kind of like a sub query in memory that we can manipulate. Referencing this temporary table to create a subset is an example of recursion. This example may help explain and provide reference to create your own CTE with Recursion query.

This is very easy in ColdFusion but not so in Microsoft SQL Server or TSQL. We'd like to keep it on the database side because it is so much faster with less overhead on servers and network. It is a lot faster than trying to use sub-queries too.

We are given this table called dbo.JoeTest1:

view plain print about
1EVID    BillDocket
2191    External:277
3192    External:277
4193    External:277
5194    External:277
6195    Internal:117
7197    Internal:HB2255
8206    External:4;5;7
9206    Action:123
10206    Combo:1;0;67
11206    Action:1321;5445;3545
12206    Internal:1233;1323
13207    Internal:11;22;33
14207    External:44;55;66
15211    External:729
16214    External:481

Notice EVIDs 206 and 207. They have multiple records. We want to combine those records by concatenating the BillDocket and showing one result set for each unique EVID.

We want the results to look like the following (again, notice 206 & 207):

view plain print about
1EVID    BillDocket
2191    External:277
3192    External:277
4193    External:277
5194    External:277
6195    Internal:117
7197    Internal:HB2255
8206    Action:123,Action:1321;5445;3545,Combo:1;0;67,External:4;5;7,Internal:1233;1323
9207    External:44;55;66,Internal:11;22;33
10211    External:729
11214    External:481

Compare that to the table above, looking at 206 & 207. We also have 5 less rows in the result set due to the combining of column values.

How do we get this result? Here's the query with some explanations commented inline:

view plain print about
1-- we use the 'WITH' CTE to create the 'anchor' table
2-- also define the temporary table name and columns
3WITH Bills (EVID, BillDocket)
4AS
5(
6-- this is the anchor table upon which we will recurse
7-- if run separately, this query will return the 1st table above
8SELECT J.EVID, BillDocket
9FROM [dbo].JoeTest1 J
10)
11(
12-- this does the recursion
13SELECT Bills2.EVID,
14    -- stuff command, concatenates and combines for us
15    STUFF(
16        -- we are adding the comma plus the next BillDocket to the string
17        (SELECT DISTINCT ',' + J1.BillDocket
18            FROM [dbo].JoeTest1 J1
19            -- matched the anchor to the CTE table
20            WHERE Bills2.[EVID] = J1.[EVID]
21                FOR XML PATH (''))
22            , 1, 1, '')
23        AS BillDocket
24FROM Bills Bills2
25GROUP BY Bills2.EVID
26)
27-- have to keep this to break any type of infinite loop we may cause
28OPTION (MAXRECURSION 1000);

convert UTC date in Unix timestamp format

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.

view plain print about
1DateDiff("s", CreateDate(1970,1,1), CreateODBCDateTime(Now()))

Google Custom Search Results

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.

view plain print about
1<p class="welcome">Googe Custom Search</p>
2<form action="joe-google-custom-search.cfm">
3    <label>
4        Search Terms:
5        <input type="text" name="googlesearchterms" id="googlesearchterms" size="30" />
6    </label>
7    <button id="googlesearch" name="googlesearch" type="submit">Search</button>
8</form>
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.
view plain print about
1<cfset searchterms = Replace(trim(form.googlesearchterms, " ", "+", "all")) />

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.

view plain print about
1<!--- set our result counter --->
2    <cfset CountVar = 1 />
3    <!--- limit returns to 1000 --->
4    <cfloop condition="CountVar LESS THAN 1000">

This is where the action happens. We add our query string, our API key and our incremented count.

view plain print about
1<!--- hit up Google --->
2        <cfhttp url="https://www.googleapis.com/customsearch/v1?key=#myGoogleAPIKey#&q=#searchterms#&start=#CountVar#"
3                resolveURL="yes"
4                result="variables.response"
5                method="get">

We will have to convert the returned JSON to an easily usable format:
view plain print about
1<cfset variables.GoogleSearchResults = deserializeJSON(variables.response.Filecontent) />

Then pluck out our array of returned result items:

view plain print about
1<cfset variables.GoogleSearchResults = deserializeJSON(variables.response.Filecontent) />

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.

view plain print about
1<!--- loop thru and add to list (dump to screen) --->
2        <cfloop array="#variables.GoogleArray#" index="arrayitem">
3            <cffile file="C:\path\to\my\text\file\googlesearchtext.txt" action="append" output="#arrayitem.link#" />
4            <!--- <cfoutput>#arrayitem.link#</cfoutput><br/> --->
5        </cfloop>

Check your text file, it should be much larger now. From here you can cut and paste into Excel or whatever format you need. If desired, you could create a SQL insert statement too.

Here's all the code in one piece, it's called joe-google-custom-search.cfm. So if you change the name, don't forget the form action attribute.

view plain print about
1<p class="welcome">Googe Custom Search</p>
2<form action="joe-google-custom-search.cfm">
3    <label>
4        Search Terms:
5        <input type="text" name="googlesearchterms" id="googlesearchterms" size="30" />
6    </label>
7    <button id="googlesearch" name="googlesearch" type="submit">Search</button>
8</form>
9
10
11<!--- form submitted? --->
12<cfif StructKeyExists(form, "googlesearchterms") />
13    
14    <!--- format search terms --->
15    <cfset searchterms = Replace(trim(form.googlesearchterms, " ", "+", "all")) />
16    <cfset myGoogleAPIKey = "123412342342341234:oiuo12341234" />
17    
18    <!--- set our result counter --->
19    <cfset CountVar = 1 />
20    <!--- limit returns to 1000 --->
21    <cfloop condition="CountVar LESS THAN 1000">
22        
23        <!--- hit up Google --->
24        <cfhttp url="https://www.googleapis.com/customsearch/v1?key=#myGoogleAPIKey#&q=#searchterms#&start=#CountVar#"
25                resolveURL="yes"
26                result="variables.response"
27                method="get">

28    
29        <!--- pick out what we want --->
30        <cfset variables.GoogleSearchResults = deserializeJSON(variables.response.Filecontent) />
31        <cfset variables.GoogleArray = variables.GoogleSearchResults.items />
32    
33        <!--- loop thru and add to list (dump to screen) --->
34        <cfloop array="#variables.GoogleArray#" index="arrayitem">
35            <cffile file="C:\path\to\my\text\file\googlesearchtext.txt" action="append" output="#arrayitem.link#" />
36            <!--- <cfoutput>#arrayitem.link#</cfoutput><br/> --->
37        </cfloop>
38    
39        <!--- Google returns results in groups of 10 --->
40        <cfset CountVar = CountVar + 10 />
41    </cfloop>
42</cfif>

Trim characters off of right on a string

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

view plain print about
1len(MyString)
and then subtract the number of characters you want removed, e.g., 3
view plain print about
1len(MyString) - 3
We just use this result to count thenumber of characters from the left and keep those using left() function.
view plain print about
1left(MyString, (len(MyString) - 3))

ColdFusion CLIENT cookie issue

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.

view plain print about
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.

[data] is a text field permitting nulls. So I checked for null:

view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] is null
pulled up everything, to test, I ran this:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] is not null
which turned up nothing! So I added a space in the quotes:
view plain print about
1SELECT TOP 1000 [cfid]
2 ,[app]
3 ,[data]
4 FROM [hoton].[dbo].[cdata]
5 where [data] != ' '
The data types text and varchar are incompatible in the not equal to operator.
No go! But this worked:
view plain print about
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.

CFM Pages Not Working After Server IP Change

BlueOnyx JSP and Servlets, including ColdFusion, CFM, CFML, Railo templates do not run after changing the IP address of the site and server. CFM displays as text.

Datacenter adjustments forced a change in IP addresses for my BlueOnyx server. The IP address of the server, and subsquently, the IP addresses of each of the sites within, had to be changed. Not a big issue, but then all of the CFM pages displayed as text. Not cool.

When the IP address was changed through the BlueOnyx Admin GUI, all of the vhosts were changed, inadvertantly dropping the JSP and Servlet switches. This can be added back either manually entering or through the GUI.

Option 1
Manually entering: find your SiteID for the site.

view plain print about
1basename $(ls -l /home/sites/| grep www.example.tld | awk '{ print $11}')

Then edit the vhosts file, using the correct SiteID

view plain print about
1vi /etc/httpd/conf/vhost/site1

adding back the JSP code:

view plain print about
1# BEGIN JSP SECTION. DO NOT EDIT MARKS OR IN BETWEEN.
2JkMount /* ajp13
3JkMount /*.jsp ajp13
4JkMount /servlet/* ajp13
5# END JSP SECTION. DO NOT EDIT MARKS OR IN BETWEEN.

Then restart httpd:

view plain print about
1/etc/init.d/httpd restart

Now you should be serving up those fancy CFM pages.

Option 2
Through the GUI, in the Web settings for the site, uncheck Enable JSP and Servlets and save. When it finishes saving and resetting, recheck or enable Enable JSP and Servlets and save. This will add 2 lines of the above code block, but you still need to add this to run CFML.

view plain print about
1JkMount /* ajp13

So you still have to edit vhosts (SiteID!)

view plain print about
1vi /etc/httpd/conf/vhost/site1

Change the JSP section from this:

view plain print about
1# BEGIN JSP SECTION. DO NOT EDIT MARKS OR IN BETWEEN.
2JkMount /*.jsp ajp13
3JkMount /servlet/* ajp13
4# END JSP SECTION. DO NOT EDIT MARKS OR IN BETWEEN.
to this:
view plain print about
1# BEGIN JSP SECTION. DO NOT EDIT MARKS OR IN BETWEEN.
2JkMount /* ajp13
3JkMount /*.jsp ajp13
4JkMount /servlet/* ajp13
5# END JSP SECTION. DO NOT EDIT MARKS OR IN BETWEEN.

Then restart httpd:

view plain print about
1/etc/init.d/httpd restart

Back in business, running your fantastice CFML site!

Ubuntu Mounting HTC One M8 SD Card

error: exited with non-zero exit status 32: mount: unknown filesystem type 'exfat'

I added a 64 Gigabyte SDcard to my HTC One M8 phone and could not connect to add music or files. The issue is that Ubuntu 14.01 cannot read exFAT formatted drives, which includes most SDCards.

We have to enable exFAT in Ubuntu 14.01 using fuse. Fuse is already in Ubuntu but we have to install the exFAT packages.

view plain print about
1sudo apt-get install exfat-fuse exfat-utils

Then uplug your phone from the USB port and plug it back in. Then, in your phone, go into settings (I just scrolled the top bar down) and tap USB File sharing - USB file sharing mode. Then enable Disk Drive - Mount as disk drive.

In just a second you will than be able to see your SDCard in File Manager. Start dragging and dropping your folder onto your phone.

Block Special Characters from a Text Box Form Input

We want to keep users from entering special characters into our textbox input on or form. Ideally we want to not allow those characters to be entered at all, as opposed to let them enter the characters, check to see if they did, and then alerting them. Too much work, simply keep them from entering special characters from the beginning. For this we'll use JavaScript's onKeyPress event, which looks like this:

view plain print about
1<input type="text" onkeypress="myFunction()">

We'll construct a function that will check for special characters. And, well, here it is:

view plain print about
1function isAlphaNum(event) {
2 var regex = new RegExp("^[a-zA-Z0-9\\s]+$");
3 //alert(event + ' - ' + regex);
4 var key = String.fromCharCode(!event.charCode ? event.which : event.charCode);
5 if (!regex.test(key)) {
6 event.preventDefault();
7 return false;
8 }
9}

A quick walk through of the code.... the function is called "isAlphaNum()". The regex we are checking against is "^[a-zA-Z0-9]+$", which checks for numbers and upper and lowercase letters. I wanted allow spaces too, so I added the "\\s" to make it "^[a-zA-Z0-9\\s]+$". The commented alert is for testing. The function checks to see if the keypress does NOT match the expression and returns false. It does nothing if it passes the regex test.

My final HTML form text box looks like:

view plain print about
1<input type="text" onkeypress="isAlphaNum(event);">

Try it out! You will be unable to type in a character other than a number, letter or space.

MSSQL Remove duplicate records

MSSQL Remove duplicate records from a table leaving a single unique record.

The situation arose where we had multiple records for items where there should be only a single record. We have JobIDs which will have multiple unique TaskIDs. We found we several (lots) of JobIDs which had 2 of the same TaskIDs. It's OK to have many TaskIDs in each job, there just can't be 2 of the same TaskIDs per JobID. Many TaskIDs to one JobID and one unique TaskID to one JobID.

First we find which TaskIDs are duplicated.

view plain print about
1Select DISTINCT TaskID FROM (
2select JobID, TaskID, (*) as Count from JobTable
3Where TaskID IS NOT NULL
4Group by JobID, TaskID HAVING COUNT(*) >
1) X

We are going to remove the 2nd entry for each duplicate TaskID. The original TaskID is correct in this case. The 2nd TaskID is superfluous and will have a greater Identity Key, they were added at a later time. Let's pull up the duplicate TaskIDs with the larger Identity Key (ID).

view plain print about
1SELECT T1.ID
2FROM JobTable T1
3JOIN
4(SELECT JobID,TaskID , MAX(ID) AS ID FROM JobTable
5GROUP BY JobID,TaskID
6Having COUNT(*) >
1) AS T2
7ON T1.TaskID = T2.TaskID AND T1.Qbid = T2.QBID AND T1.ID = T2.ID

This pulls up what we want, so lets get rid of them.

view plain print about
1DELETE JobTable WHERE ID in (
2SELECT T1.ID
3FROM JobTable T1
4JOIN
5(SELECT JobID,TaskID , MAX(ID) AS ID FROM JobTable
6GROUP BY JobID,TaskID
7Having COUNT(*) >
1) AS T2
8ON T1.TaskID = T2.TaskID AND T1.Qbid = T2.QBID AND T1.ID = T2.ID
9)

Done! Now we have unique TaskIDs for each JobID.

Find Duplicates in MSSQL

Find duplicate entries in MS SQL table columns. Need to see if you have 2 or more duplicate entries in your table? Want to find out if the key is really unique and there are not multiples? Simple!

view plain print about
1SELECT [ID], COUNT([TaskID])as NumberOfDups
2FROM [dbo].[MyBigTable]
3GROUP BY ID,[TaskID]
4HAVING (COUNT([TaskID]) >
1 )

ID = the identifier, could be a name or whatever
TaskID = looking for duplicates of this

More Entries

BlogCFC was created by Raymond Camden. This blog is running version 5.9.7. Contact Blog Owner