|
VOLUME I (Jun 1998)
Sorts: Hidden Failures
Posted: before 6/9/98
Updated: 6/9/98
Ever found yourself pulling out your hair because a sort that you're performing (using SELECT ... ORDER BY) is just not working as expected? It may be because you listed the ORDER BY fields in the following way: i.e., "state and name". While doing it this way will not generate an error, it is logically incorrect and will not produce the desired results.
You should instead code it as "state, name".
I've not been able to find out yet quite how "state and name" translates to something acceptable, but in testing it's definitely not returning the same as "state, name". Forewarned is forearmed.
CF's javascript features: should you or shouldn't you?
Posted: before 6/9/98
You may be wondering what risks you take in using CF's client-side validation features like CFFORM. What if the users's browser doesn't support it (or they have JS support turned off)? Will they get an ugly error if their browser doesn't support javascript? (not likely) Are there varying levels of browser support that may affect support of the CF javascript features? (some) Should I be prepared to back up the client side validation with server-side? (Definitely).
Keep in mind that some enhanced interface features enabled by CF (like CFTREE, CFGRID, CFSLIDER, etc) are not based on javascript but are instead Java applets. That's another whole kettle of fish. It rules out even more older browsers (those that may have supported JS but don't support java), and even more people who may have turned of Java support for for security concerns.
Also, these latter tags tend be something you need to rely on for the interface they enable. That could be trouble for the older browsers, and for you, since it would take a lot of effort to provide an alternative interface.
Finally, if you rely on the javascript-based subtags of CFFORM like CFINPUT, CFSELECT, and CFTEXTINPUT, there is basically no way to "back them up" with non-javascript support. A non-javascript user just won't see any result! Unless you're prepared to test for javascript compatibility and programatically send the user to one approach or another, these things are probably best used only in an environment where you can be sure of the level of the browsers' javascript support (an intranet, or a site so useful people will upgrade to be able to use it.)
Testing Max Length in HTML TEXTAREA fields
Posted: before 6/9/98
There is no way in HTML (currently, as of 5/98) to code a maximum length value for HTML TEXTAREA fields, such that the typing is halted at that length in the way an INPUT text field does. You can, however, at least do a test on the client when the user leaves the field, using Javascript. This may be a little less annoying than their getting an error from a server-side error check. Try:
<textarea name="description" rows="10" cols="50" wrap="virtual"
onChange="if (this.value.length > 2000) {alert('Sorry, Description must be limited to 2000 Characters.')};">
#Description#
</textarea>
</td>
Textual data in DB field not showing "new lines"
Posted: before 6/9/98
Do you have a field in a database that you're trying to display (perhaps a "description") and you know in the database (perhaps Access), the data has carriage returns (new lines), but they don't display when you present the data with CFOUTPUT?
Try using the CF function "paragraphformat". So instead of:
<CFOUTPUT>
#description#
</cfoutput>
Use:
<CFOUTPUT>
#paragraphformat(description)#
</cfoutput>
Watch Out! Lost data on CFUPDATE and CFINSERT
Posted: before 6/9/98
Have you ever pulled your hair out trying to figure out why a form using CFUPDATE or CFINSERT fails to update one or more fields? The field is in the table, it's in the form with the proper name, yet data never gets updated! The problem is that the field name in question ends in one of the reserved words CF uses for server validation, such as "_date", "_time", or "_range". It's suprisingly easy to create a column using these strings (such as "start_date" or "end_time".)
The problem is that CF's form processing engine doesn't recognize them as valid column names but instead as validation indicators (ie, in the example above it thinks that "start" is the column name and it should be validated as a "date" type field.) Sadly, it doesn't confirm that the a form element named "start" exists, so it can't figure out that you don't mean "start_date" to be a column name. It also doesn't care that this is not used as a regular form input field rather than a "hidden" field.
The bottom line is that you will either have to rename the columns in the database or not use the CFINSERT and CFUPDATE tags with a form updating these columns.
- Warning: You should consider running an analysis to determine if you have any references to table columns with a name that ends with one of these reserved words. You may be losing data and not realize it.
The best way would be to look at the schemas for the tables you use. If you have CF Studio, you could also do a quick analysis of the code using "extended search and replace". Just be aware that if you do find hits for these strings, it may be that they're being used legitimately for defining input validation.
Regular Expressions
Posted: before 6/9/98
Updated: 7/19/98
Regular expressions are powerful. You can use them to create very flexible search and replace strings both within your code on CFIF and CFQUERY tags (using the functions REFind and REReplace) as well as within the Studio editor, in the "extended search and replace" command (they are not accepted in the "find and "replace" commands).
There is help about using them in the CF manuals (/cfdocs/lang/lr040004.htm).
Note also some dissimilarities with Unix and PERL regular expressions. See Allaire Knowledge Base Articles 3628, 3627, and 1544 (all appear to be the same), which include an attachment with further details.
Concerns for Remote Users
Posted: before 6/9/98
If your ColdFusion application is hosted somewhere other than your own workstation, be it with a commercial hosting provider or even a corporate lan environment where someone else controls the CF administrator, you face several challenges. This section discusses these, and augments further detail offered in the 1998 presentation provided elsewhere on the site, Remote Development in ColdFusion: Challenges and Solutions.
- Beware. Your datasource may be very vulnerable. Because of the lack of isolation provided in the CF Server for users sharing that server, there is nothing by default that ties a given datasource to a given application.
- What this means is that anyone else running CF applications on your server can also access the data in your datasource. They need merely know the datasource name and then the table and column names for your database. Pretty scary.
- It will likely be difficult for "just any" developer who happens to share your server to determine your datasource names. They could be given the information by the administrator, but most are careful about that. And they would likely find that the tools that might divulge that datasource information (the remote development features of Studio, the CFDIRECTORY tag, etc.) are turned off in a shared remote hosted environment. Just be aware that the potential for harm exists.
- Be aware also that even if you have set a password on the datasource, since all CF applications have access to any datasources in the CF server (because the server must have access and there is no isolation between applications), that will not protect you.
- There is a solution, though it will take some effort. You'll have to judge whether it's worthwhile. You could define a userid and password for the database and then enter it in all your templates on any data access statements (such as CFQUERY, CFUPDATE, CFINSERT, etc.). A further challenge is to remember to encrypt these, because (for reasons detailed in the presentation mentioned above) it's possible that a user on your server may be able to gain access to your templates quite easily. It they're encrypted, at least s/he can't so easily access your id and password (and table structure).
Clean up your code
Posted: before 6/9/98
There are several "better ways" of doing things one learns in the course of developing CF applications. Perhaps these "opportunities for improvement" linger in code you've written some time ago. Or you can try to incorporate them from the start if you're new to CF development. It's not that they're not documented, just not obvious to some.
These will be fleshed out and added to over time...
- Remove needless pound signs.
- Use CFPARAM to simplify setting defaults (to be developed)
- Use joins, and especially outer joins, for table lookups
- more to be developed
Using Joins
Posted: before 6/9/98
If you've normalized your database to separate lookup values (like states, occupations, etc.) into their own table with reference to their ids or abbreviations in a main record, you'll eventually want to list the values in the main record along with the values for the lookup codes. For instance, you might have an occupation_id of 10 and you'd want to show "carpenter" rather than 10. If you're not familiar with JOINs, you'll probably try to do something like this in your code:
<CFQUERY DATASOURCE="foo" NAME="foo">
SELECT * FROM MEMBERS
WHERE STATE = 'ct'
</CFQUERY>
<CFOUTPUT QUERY="foo">
<br>#name# #company#
<CFQUERY DATASOURCE="foo" NAME="lookup">
SELECT * FROM OCCUPATIONS
WHERE id = #foo.occupation_id#
</CFQUERY>
<CFOUTPUT QUERY="occs">
#occs.name#
</CFOUTPUT>
</CFOUTPUT>
Of course, it makes sense logically. But you run into a couple problems.
- You get an error trying to run a CFQUERY inside a CFOUTPUT. Of course, that's not a show-stopper if you know about CFLOOP. You can change the outer CFOUTPUT to CFLOOP. Then of course you have to put some CFOUTPUT tags back in at least surrounding the field values you want to display, so it becomes:
...
<CFLOOP QUERY="foo">
<CFOUTPUT>
<br>#name# #company#
</CFOUTPUT>
<CFQUERY DATASOURCE="foo" NAME="occs">
SELECT * FROM OCCUPATIONS
WHERE id = #foo.occupation_id#
</CFQUERY>
<CFOUTPUT QUERY="occs">
#occs.name#
</CFOUTPUT>
</CFLOOP>
Which runs fine, but you should ask yourself if CF barked so much, maybe there's something wrong with the way you're attacking this. Also, it's not real clean-looking code. All this should lead one to ask if there might be a better way to do this. There may very well be. Consider using "joins"!
Joins
If you use the SQL capability to "join" two tables in the SELECT statement, it achieves just the functionality we seek. We won't get into an explanation of joins just yet, but here's an example. The query gets a little more complicated but the CFOUTPUT loops are much simpler:
<CFQUERY datasource="BNI" name="foo">
select *, occupations.name as occname
from members, occupations
where (state = 'ct'
and members.occupation_id = occupations.id)
</cfquery>
<cfoutput query="foo">
<br>#last_name# #company#, #occname#
</CFOUTPUT>
Note that we've eliminated the inner CFQUERY as well as it's associated CFOUTPUT, and we no longer have to use CFLOOP for the outer loop. The query now incorporates what we were doing in the inner query (technically, we're doing an "INNER" join, so this may help better explain that term). It says, for each record found in the main table (members), find those in the lookup table (occupations) where the occupation id's match.
Something to note, which might even be missed by those familiar with joins, is that the use of "*" in the SELECT now means we are grabbing all fields from all listed tables. Not just the "first" one. There is no concept of "first" really. So if the "lookup" table we are joining had many fields we didn't really need, we might want to limit the use of * to the primary table. We could do that by specifying "members.*". Try it.
Note also that we referenced the "occupation name" on the select statement as "occname". That's primarily used when a field in both tables has the same name and we need to distinguish which one we want to later reference in the CFOUTPUT. (Note that if we had forced the "*" to be only on members, that may not be as important.) Anyway, it's also useful to do this in case it might have confused a future coder looking at the CFOUTPUT referencing just "#name#". Now it says "#occname#" so it's more clear.
There are a couple of problems with using joins. First, they take some time for the new SQL coder to get their "heads around" them. They don't look to obvious at first. But that really goes away quickly with practice on data you understand.
A couple more significant problems are that
- The join might not be faster than the other approach. You should test to see if this will be so. There are many factors that could influence performance.
- More importantly, and something to be very careful about, the join example may not include all the records you expect depending on your data and what you meant to accomplish.
To put in the the context of our examples, the "join" version may not return all the records that the first example did! If one of the "member" records doesn't have a value for "occupation_id", guess what? The record is not shown in the result! That could be a real shocker if you didn't understand this and started relying on joins to do this "simplification".
Outer Joins
There is a solution. It's one that's (again) designed just for this purpose but if you just read about this "join stuff", it likely won't have seemed valuable. What you want here is an "outer join". Don't be scared off. It's not that difficult to use or to understand.
An outer join is especially easy to understand in the context of what we've just learned. Since one of the tables (members) has records that don't have the field we're trying to join on, but we want to show it anyway, we need to tell the SQL engine to include them anyway. That's what an "outer join" does. It says "join these two tables as I ask you to, but if any of the records found lacks the join field, include the record anyway."
Using Access (technically the JET database which interprets SQL directed against Access), we would modify our join example like this:
select *
from members
left join occupations on members.occupation_id = occupations.id
where state = 'ct'
Note that we just move the join condition out of the WHERE clause and into a new subclause of the FROM. Also, in doing so we've had to switch from a WHERE for that join condition to an ON. We still have the WHERE clause to limit what records we mean to return, but the LEFT join clarifies that we mean that all records (meeting the WHERE criteria) which occur in the left side table listed in the join will be presented, even if they don't have a value for the join criteria listed.
In SQL Server, you can simplify this even further by keeping the syntax much more like a regular join, but using the "*=" comparator rather than just "=" on the join fields, as in:
select *
from members
where state = 'ct'
and members.occupation_id *= occupations.id
And there are equivalent RIGHT joins and even some SQL engines allow outer joining both tables. I'll leave that for you to research as needed.
So we have our answer to the dilemma of doing lookups more easily and not losing data along the way! And it takes a little bit more execution time (over an inner join). And still much less than the non-join.
For comparison sake, here are some quick and dirty benchmarks on this particular example on one database:
- non-join: 1300 ms
- inner join: 600 ms
- outer join: 700 ms
Of course, your mileage will vary, but it seems that for the small cost of learning how to use joins (especially outer joins where appropriate), you gain simpler looking code and performance improvements to boot!
|
Tips Contents:
|
