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.
No comments:
Post a Comment