Hacking SQL to Save CFGRID: LEN, CHARINDEX, Filename parsing

OK, so then I ran into the same problem I had that I blogged about previously, which is this: getting a full link, image, with unique record ID, into the single column value from SQL so I can have it in my CFGRID.

With one hitch: this time, I needed to use logic to evaluate the image filename. The filename that is in the database is the one for the actual photo, but the filename I needed is a mini-thumbnail that is based on that photo. Now, the thumbnail naming is consistent, at least in regards to matching the database's photo filename. But there are two big caveats, that kept causing SQL errors:

1 - some columns didn't have any value at all there. Their original upload was a PDF for example so there are no files and no value in the database.

2 - the filename entries in that column varied in length. So I couldn't just say, give me the first 5 characters only, then add a new extension (as all the mini thumbnails are JPG, but the filetypes in the database may vary).

This means I had to do four things:

1 - Get the real length of the filename entry

2 - Get what the length would be MINUS 3 digits (the filename extension)

3 - Get only that quantity (#2 above) of characters from the original filename

4 - Then add strings to it, to add the 'mini_' and 'jpg'. And while we're at it, add the strings that will give it the IMG REF and A HREF because CFGRID is not going to be doing that for me either.

5 - And during all this, we have to make sure we are NOT trying to do database math on column values that are incapable of having it done to them or it will probably trigger an error for the user. So if there is no filename or it's too short we just have to do something else.

The first thing I had to figure out was how to stop the logic if the value wasn't up to it. So my first "condition" in my CASE WHEN statement related to that:

CASE WHEN LEN(dsfbfile) > 5

If the filename isn't 5 digits long this is all gonna fail and/or it isn't a real file anyway, so we've taken care of that.

Then I tried to combine the LEN (char length in SQL Server) function and the math subtraction function, to put it only one statement, both 'get the length' and 'remove the length of the extension'. That worked fine.

Unfortunately when I tried to use that it failed, because this is not like CFML, that column name you create is not creating a 'variable' you can just go using everywhere else from then on. It would give me the error INVALID COLUMN every time I tried to use it "as if" it were a number. This is what I tried to do, which failed:

SELECT myrecordID,

LEN(dsfbfile) -4 as THENEWLENGTH,

LEFT(dsfbfile, THENEWLENGTH) as FILETRIM,

'mini_' + filetrim + 'jpg' AS minipic

You see in line 3 I was trying to make that new 'thenewlength' column that I had created, stand in place for an actual number, which SQL Server was not happy about. I actually don't think it would have been happy about me using the calculated column in the last line either but I didn't get that far before I changed it.

Then I found reference to this cool function called PARSENAME:

PARSENAME retrieves parts of string delimited by dots.
It is used to split DataBaseServer, DataBaseName, ObjectOwner and ObjectName
but you can use it to split IP addresses, names etc.

DECLARE @ParseString VARCHAR(100)
SELECT @ParseString = 'DataBaseServer.DataBaseName.ObjectOwner.ObjectName'
SELECT PARSENAME(@ParseString,4)

got that from this address

I wasn't entirely sure how it worked but I got to something else before then so I didn't use it, but thought I'd mention it as it'd be a cool thing I bet for dealing with "filename" manipulation (getting full name or extension easily).

Then I happened to see another example on that same page I linked above, that did something neat I hadn't seen before. First, it used a function called CHARINDEX. Here's a neat article on CHARINDEX and PATINDEX (pattern-index). Basically this function works for 'searching strings' in a database. You can look for a pattern (PATINDEX does wilcards) or something specific like a single character or string (CHARINDEX does that).

Well I didn't know about the use of it in search (I'm still using LIKE % which is probably why my search forms suck), but I saw this example of it used LIKE A NUMBER in a LEFT function -- exactly what I'd been trying to do, but SQL-Server was balking at my using my 'generated value' (or even the logic for the generated value) "as" a number.

This logic, and I'm not even sure why but it does work, looks at my column value, goes to the period in it, goes back one character (-1, to BEFORE the period), and then gets me the string of text that is left of that location, all from the 'imagefile' column exampled below:

LEFT(imagefile,CHARINDEX('.',dsfbfile)-1)

I was able to drop that into a string manipulation, and from an input of an 18 character filename (WHEN it exists), output a link, and image (a different image), with the unique record ID on the link, and if the column wasn't a real picture or any value it would write in a filename value, so I can put a 'placeholder' image file there to keep displays from being funky.

So the good news is, here's the code that worked:

SELECT recordid,
CASE WHEN LEN(imagefile) > 5
THEN '<a href="rvg_showpractice.cfm?id=' + CAST(recordid AS varchar(12)) + '"><img ref="http://www.mysite.com/folder/' + 'mini_' + LEFT(imagefile,CHARINDEX('.',imagefile)-1) + '.jpg" /></a>'
ELSE 'nofilehere.gif' END
AS 'See Session',
anothercolumn here
FROM table

Now the bad news: it took >59000ms seconds to run on 1000 records. I kept taking the quantity of records down, down, down, until I only had 150 records, and still it was taking like >41000ms.

RATS!!

So I ditched that whole phrase about the mini-thumbnail with link. It is still a long query, even on fairly few records, but it's not so long it's at risk of timeout at least. I will have to put the images somewhere else I guess, where the query is not on a view table made of two other view tables, but something more straightforward so assumed fast.

Still. I was damn proud of getting it to work at all.

CFGRID and TEXT WRAP, hover row color and HEADER row style

You would think text wrap would be a parameter on the tag itself. It took me quite some searching to find the answer to that one.

There is actually CSS styling you can do in huge detail on CFGRID, but it isn't provided with the standard documentation, so it is totally metaphysically obscure. None of it seems able to remove the invasive underline should you be crazy enough to use HREF in your CFGRID, but aside from that, everything else seems do-able.

Here are the three little things I critically needed to find and use:

1. TEXT WRAPPING in CFGRID

It would just truncate my text in every row. Since it won't let you size the grid by % but insists on a hard coded pixel number, that just made it worse.

Your CFGRID must be in a CFFORM. Give that an ID value. Mine:

<cfform name="myform" id="myformid">
<CFGRID . . .

Then in your stylesheet do the following:

<style>
#myformid
.x-grid-row td {white-space:normal;}
</style>

That will make it wrap. The above means it affects everything 'inside' the 'myformid' ID value, and it affects the entire grid, and all the row, and the table cells within the grid rows, and the white-space:normal makes that wrap.

2. HEADER ROW STYLING in CFGRID

I wanted the header row of labels to look different than the default. I know everyone loves those silver gradient skins but the top row seemed to sort of fade into unnoticed in this case. There is another style that lets you mess with that:

.x-grid-hd-text {background-color: #D9CCFF; color: #661A99; font-size: 120%; font-weight: bold;}

That'll do it.

3. ROW HOVER STYLING in CFGRID

I'd set all these style values but the entire row color change when you mouse over it wasn't changing, and it was some kind of cyan-like color by default. Looked really interesting with my purple-based grid as you might imagine. I finally found the code to change that row-sized hover color:

.x-grid-row-over td { background:#661A99;}

So there you have it. To set my row styling, text wrapping, and a decent hover, I had this:

<style type="text/css">
#myformid
.x-grid-row td {white-space:normal; text-decoration: none;}
.x-grid-hd-text {background-color: #D9CCFF; color: #661A99; font-size: 120%; font-weight: bold;}
.x-grid-row-over td { background:#661A99;}
</style>

And it looks like this:

Hacking SQL to Save CFGRID: Image as column value, HREF link with RECORD ID

This is only part of solving a larger problem. The problem is using the HREF on CFGRID underlines every damn thing and I couldn't figure out how to get rid of it. I did find extensive notes on CSS styling for the grid, down to every single cell, but nobody crying about it on the internet had gotten any answer on how to get rid of the underline.

So I took the HREF parameter off my CFGRID because the underline looked so horrible and made it so unreadable I wouldn't even use the grid if I had to suffer that. Which left me with the following dilemma: since the grid no longer links to something semi-automatically (posts to a form, click chooses a whole row, you can pass the ID value you need), I would have to link to it manually.

As part of doing this I decided that much like the simple HTML table I've been using prior to trying to implement CFGRID, I would use a little icon that would intuitively tell people (a) to click that for a link, and (b) that it would open in a new window. So I had three things to figure out:

1. How to get an image as a column value, because CFGRID only lets you insert a query column name, not html, and

2. How to get an html HREF link into a column value, for the same reason, and

3. How to get the unique record ID into that link.

To get a single (just one, same image on every record) into a column value, I did the below. Note that if you need a different image for each record, and you can build that filename/loc from whatever values are in the query, you could do that too. Use the logic of the Record ID link below, and modify that.

SQL Server uses + to concatenate strings and columns. Other DBs may use things like CONCAT function. You use single quotes around every string, and no quotes around actual column names.

You have to render, aka CAST or CONVERT, integer fields to strings before you can concatenate them.

SELECT
'<a target="_blank" href="myactionfile.cfm?id=' + CAST(p.myid AS varchar(12)) + '">' + '<img src="../shared/icons/external2.gif" border="0" /></a>' as 'See',

then in CFGRID, I just did

<cfgridcolumn name="See" width="35" display="yes" >

and that's it! That changed my ID to a string, and it tied it into a link and an image, so now in my column it looks like this:



You click on the little image and it opens that record in a new page.

Hacking SQL to Save CFGRID: Date Format

In the HTML format CFGRID, my date "mask" formatting, using the CF documentation, didn't seem to work at all. I don't know why but my impression from cfdocs was that it wasn't going to work in the HTML format grid I was using. I hacked this by changing my SQL to begin with, rather than doing it in CF.

Formerly it was:

SELECT mydatefield as DateEntered

then

<cfgridcolumn
name="mydatefield"
display="yes"
header="Date"
mask="EEE DD-MMM-YY H:NN A">

Which sucked and continued to show me whatever it wanted, rather than the "mask" attributes the cfdocs had. So I changed it to this:

SELECT CONVERT(VARCHAR(11), mydatefield, 106) AS DateEntered

then

<cfgridcolumn
name="mydatefield"
display="yes"
header="Date">

And the resulting output looks like this:

22 Jun 2008

You can google for SQL DATE FORMAT or something like that and see the whole huge list of numbers (mine 106, above) that will format dates differently.

CFGRID and CFC Binding How To

I avoided using a CFC binding initially because I didn't grok how and, before I knew that my route made pagination impossible, a query in the HTML CFGRID seemed so much easier. But here is how the binding part is done.

In the CFC file you're going to have the following:

1. A function name with arguments
2. Your query
3. Not always but in my case, a query return for the grid.

The function is going to have remote access and return a structure. Here is my code for it:

<cffunction name="comments" access="remote" returntype="struct">
<cfargument name="page" required="true">
<cfargument name="pageSize" required="true">
<cfargument name="gridsortcolumn" required="true">
<cfargument name="gridsortdirection" required="true">

Page means, "Do you want this to do paging, where things are broken into separate pages?" PageSize means, "How big (how many records) should each page be?" The sorts are fairly obvious I think...

If you have the above it means you will have to have the page and pageSize defined in your CFGRID tag in your CFM page, and you can control it from there. I did not define my grid sorts and it didn't break.

Next you have your query. Whatever query you were using to output records prior to CFGRID will probably work fine, but I ended up having to hack mine to do all kinds of stuff that CFGRID made a royal pain in the butt, so I fixed it through SQL instead of CFML. Will cover that in another post.

Then you have your query "return". You find this in documentation under functions, queryconvertforgrid function.

In my case it has three values:
1 - the name of my query
2 - page
3 - pagesize

And I mean those strings (page and pagesize) not true/numeric. Here's my code, where my query is called selectAll:

<cfreturn queryconvertforgrid(selectAll,page,pagesize)/>

Now to put the CFC together you'll want these three things in order. I have a file called getcomments.cfc and here is the code, with the query revised for public use of course. Note that the CFC did not find my datasource which is a variable set in my Application.cfm file, so I had to manually CFSET that value before running the query.

<cffunction name="comments" access="remote" returntype="struct">
<cfargument name="page" required="true">
<cfargument name="pageSize" required="true">
<cfargument name="gridsortcolumn" required="true">
<cfargument name="gridsortdirection" required="true">

<cfquery datasource="myds" name="selectAll" maxrows="1000">
My query goes here
</cfquery>

<cfreturn queryconvertforgrid(selectAll,page,pagesize)/>
</cffunction>

Then in my CFM file, my HTML format CFGRID used this code to call it:

<cfform name="myform" id="myformid">
<cfgrid
name = "myGrid"
format="html"
bindOnLoad="yes"
bind="cfc:getcomments.comments({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"
pageSize="10"
preservePageOnSort="true"
selectMode="single"
selectOnLoad="no"
font="Verdana"
fontsize="11"
label="True"
stripeRows="true"
stripeRowColor="##D9CCFF"
selectColor="##994FE6"
>
<cfgridcolumn name="id" display="no" >
<cfgridcolumn name="DateEntered" display="yes" header="Date" mask="EEE DD-MMM-YY H:NN A">
<cfgridcolumn name="Alias" italic="yes" display="yes" header="From">
<cfgridcolumn name="See" width="35" display="yes" >
<cfgridcolumn name="theComment" fontsize="1.2em" textColor="##661A99" width="700" display="yes" header="Comment">
</cfgrid>
</cfform>

Notice that there is some styling in one of the columns and that works, and date formatting in the date column which did not work at all so I did something different, which I'll cover in another post. The 'see' value there is a big string from a special SQL call that gave me an HREF link, and an image, and appended the record ID, because stupid CFGRID wouldn't let me get rid of underlining every damn thing if I used the HREF attribute.

CGRID and Auto Pagination

Everyone raved about how cool it was that ColdFusion's CFGRID tag automatically would do the pagination for you.

All the little examples I looked at, had that nifty little page number and button at the bottom.

Except mine. I could do code pretty much just like the examples, except that I was using an HTML form and a QUERY in it, but the little pagination thing never appeared.

It turns out that you cannot do pagination in a QUERY HTML CFGRID in CF8. Instead, you have to put your query out in a CFC page and "bind" that to the CFGRID. It is the CFC page that actually works out the pagination stuff.

I'll cover the CFC and binding separately.