|
Volume III (Aug-Nov 1998)
Be careful with listing values for listfind
Posted: 11/15/98
We discovered a somewhat surprising behavior regarding the specification of list values in a LISTFIND (or LISTFINDNOCASE) function. In this function, one specifies a list of values in the first parameter, and the function returns a value representing the location in the list of the value in the second parameter. The surprise was that if that list of values contains any spaces between the delimiter (comma) and another value in the list, it would not match the given 2nd parameter if it did not also have spaces.
This may surprise the unaware, intending to match a single word against a list of words. You must be careful not to casually enter a space within the list of values being matched.
For instance, the following two statements are not the same:
<cfset test = listfindnocase("update,insert", url.type)>
and
<cfset test = listfindnocase("update, insert", url.type)>
The difference is the space before "insert" in the second example. In that assignment statement, even if the value of "url.type" was "insert", the value of test would be 0, indicating that the string was not found in the list. Again, this is because the space before "insert" means that only " insert" would match, not simply "insert".
The lesson learned is to be careful about specifying the values in the list parameter of LISTFIND.
NOTE:
Another approach to protect against the mistake might seem to specify both commas and spaces as value delimiters for the list. To do this, you would add a third parameter:
<cfset test = listfindnocase("update, insert", url.type, ", ")>
The problem with this approach is that it will cause unexpected results if the values in the list are two-word values. Then, the space between the two words would be mistakenly interpreted as a delimiter, thus causing each word to be considered a value for matching.
The best solution to the original problem is to simply ensure that there are no spaces between values in a LISTFIND function.
Beware providing "name" attribute on FORM tag (in PWS)
Posted: 10/24/98
If you're using Microsoft Personal Web Server, beware this problem we encountered with a form that mistakenly had a "NAME" attribute on a FORM tag. It caused the form to process incorrectly. This is probably more a PWS problem than a CF problem, but beware nonetheless.
We were given an HTML form to convert to CF processing. And we were surprised to find that when the form was passed to a CF template for processing, it would not pass the form field names in the "form.fieldnames" parameter (this is a great way to get a list of the field names on a form). Instead, it was passing the form field VALUES. That was unexpected!
In other words, when in the template we asked for the following output:
<CFOUTPUT>
#form.fieldnames#
</CFOUTPUT>
instead of getting the values (for example):
we got:
charlie arehart, ellicott city, md
This is incorrect. Even viewing the value of the form fields in the debugging output of the page showed this problem.
On testing, we found that the form had had a "NAME=" attribute defined, as in:
<FORM ACTION="foo.cfm" METHOD="POST" NAME="_MainTopic">
We removed the "NAME" attribute and the problem went away.
This did not happen when the form was moved to an IIS server. It occurred only on Microsoft Personal Web Server.
Joining Multiple Tables, the correct syntax
Posted: 9/27/98
If you've tried to join more than one table in SQL, you may have encountered a challenge in using (or finding) the correct syntax. Here is a 3 table join in the two different forms of join syntax.
SELECT * FROM
(tablea INNER JOIN tableb ON tablea.columna = tableb.columna)
INNER JOIN tablec ON tablea.columnb = tablec.columnb
Note that the first join is enclosed in parens and also that it lists the first table name before the INNER JOIN clause, whereas the second has no parens and DOES NOT list a table before the INNER JOIN. In fact, the first join can be thought of as the "table" on which the second join is made.
Using the simpler syntax, the join would be:
SELECT * FROM tablea, tableb, tablec
WHERE tablea.columna = tableb.columna
AND tablea.columnb = tablec.columnb
You could join more tables, but the performance impact should be seriously considered.
Beware incorrect use of "List" functions
Posted: 9/23/98
This problem really confounded us, until we discovered that the behavior was just not quite what was expected. It involved the use of "lists"; specifically, the use of list functions to assign values to a list. Lists are a simple data structure in CF, with a list of values delimited with a string of your choosing. See further discussion of "list" functions in the CF Reference Manual.
There are functions to add items to a list, get them from a list, determine the number of items in the list, etc. The function for adding items to a list is ListAppend, and is used as follows:
ListAppend(list,value[,delimiter])
This says to add the given "value" to the given "list", optionally indicating a delimiter with which to separate the values (if none is provided, a comma is presumed). Therefore, if you had a list called "listx" and you wanted to add an item (let's say, "1") to it, the function would be:
ListAppend(listx,"1")
Of course, you can't simply use that function call on its own but rather must use it in the context of some CFML tag. A natural choice might be the CFSET tag, and if you have programmed in another language, you might presume that the following would work:
<cfset x = ListAppend(listx,"1")>
To most programmers, this would read "add '1'" to listx" and set the return code for the result of the function to the variable "x". Since this sort of list add is so trivial, one may not be concerned about the value of the result of calling such a function, thus the choice of the "throw away" variable named x.
There's a real problem with the logic here, however, and it has to do with a subtle difference in the way CF functions work and the way many programmers may expect them to. First, ListAppend and related functions don't actually modify the given list, and they don't return a "return code" that indicates success or failure. Instead, CF merely returns as the result the modified list!
If you tried to report the number of items in the list after the function call as indicated above, it would not show any increase at all. That's because the function call in the context of that CFSET did not change the value of the list "listx"; instead, it assigned the variable "x" with the modified list! Not what many programmers would expect!
Clearly, the appropriate solution here is merely to use the list name as the recipient of the value of calling the function. As such, the proper syntax would be:
<cfset listx = ListAppend(listx,"1")>
Try to keep this in mind next time you're using lists, lest you spin your wheels as we did for a time.
CF 4 Tips
Posted: 9/15/98
The latest release of CF and CF Studio, release 4, is now in the final phases of beta testing (as of this 9/12/98). In order to share info and tips with those beta testing (or later implementing) version 4 of these 2 products, we have started a new tip area just for that. See CF4 Tips.
Credit card, telephone validations and more with CFINPUT
Posted: 9/10/98
Most will know that you can insert hidden input fields in a form to perform various server-side validation when a form is submitted. Among the validations here are: date, eurodate, float, integer, range, time, and .
There is also a set of client-side validations made possible using the CFFORM tag and its associated CFINPUT tag. Specifically, this client side validation is much more extensive and supports the same ones above (except required), but it adds several interesting new ones, including: telephone, zip code, credit card, and social security number. These are explained further in Chapter 9 of the CF User Guide.
Note, too, that while the client-side validation offers more types of validation, there is another important difference between the two approaches (which may or may not please you). For some types of validation, the server-side approach modifies the data after validating it (dates and times are converted to ODBC format, integers are stripped of fractional values, and and interger/float/range numbers are stripped of commas and dollar signs). See the next tip for more on this.
Beware reformatting of dates with "_date" form input validation
Posted: 9/10/98
We just tracked down an annoying discovery, and it traces back to the use of CF's server side form input validation, specifically the "_date" validation.
See the tip above for info on validation tags. We've just discovered, though, that if you use "_date" server-side validation, CF is converting the date from the string format we had--mm/dd/yyy--to the ODBC date format--{d 'yyyy-mm-dd'}--when it is stored in the database. While CF programs recognize and display this formatted date as dd/mm/yyyy by default, it still caused confusion when looking at the data (or performing sorts) from within Access itself.
So this is just a warning for those who may discover similar anamolies. If we removed the "_date" validation field from the form, the date was stored as we expected.
Now that we see that the data stored in that strange format still comes out nice when displayed in CF (without need to do a dateformat), we may just bite the bullet and put in the date validation on all input forms. Then we'd run a process to convert all the existing dates to that format, using CF's CreateODBCDate function.
(Note also that according to the CF3 User Guide, "Because numeric values often contain commas and dollar signs, these characters are automatically stripped out of fields with _integer, _float, or _range rules before they are validated and saved to the database.")
A Handy Regular Expression: Finding mistaken update
Posted: 9/9/98
We discovered that an "extended find and replace" operation executed some days ago was mistaken in that it created some erroneous code. (Extended find and replace is a powerful feature in CF Studio, available under the "Search" menu command. It allows you to search and optionally replace code in all templates that are open, all in a project, or all in a directory.)
We needed a way to find any of these mistaken edits and correct them, but it wasn't easy to be sure we had corrected all such mistakes as we weren't sure exactly how many variants of the change were done. There was a consistent characteristic, though. And use of a regular expression in another extended find and replace was the solution, but it took some digging to determine what expression to use. (Regular expressions are a means of searching for text using wildcards and other special characters to describe the search criteria. Further help is available in Chapter 4 of the CF Language Reference Manual. There is also a brief tip here, in Volume 1.)
The change had resulted in a directory full of templates that had queries that looked like this:
<cfquery username="#application.unm#" password="#application.pwd#"name="user" datasource="#application.datasource#">
Notice that the "password" parameter does not have a space before the "name" parameter. The username and password parms had been inserted in the first extended find and replace, and we chose to insert them at the start of any CFQUERY. We didn't know if some of the CFQUERY tags may have had the "name" parm first, or the "datasource" parm, so this approach assured that all queries would be changed without having to anticipate the possible variants.
The problem, obviously, was that we forgot to include a trailing space on the insert, resulting in this erroneuous code. And because of the many possible variants that might follow the inserted string, we now needed to be able to find ANY occurrence where:
password="#application.pwd#"
was followed by a character without an intervening blank. It was tempting to use the regular expression:
pwd#"+
where the "+" indicates any single character. But this also matched spaces, so we needed a way to find the string followed by any character EXCEPT a space. Fortunately, we found it:
pwd#"[^ ]
This means just that. The brackets mean find any occurrence where the pwd#" string is followed by the following set of characters within the brackets. But the caret means "not" and the blank space is correctly interpreted as a space character, so the result is 'the pwd#" string followed by anything but the space.
Similarly, we were able to double-check our work and make sure that we had indeed placed the username parm as the first parm in all <CFQUERY tags, using the following (it's not an exact match for the full "username" parm but was close enough for double-checking):
<cfquery [^u]
Pretty nifty. Hope these may help some other reader.
(BTW, the extended find and replace command can also be used against just the current document only. In fact, if you want to use regular expressions to search the current document, you must use this form of the extended find and replace, rather than the standard "find" command as it does not support regular expressions. Also, regular expressions can be used within CF programming as well, using the REFIND and REREPLACE functions.)
Access Replication Dangers!!!
Posted: 9/7/98
Tragedy struck "systemanor" when we discovered an egregious error/behavior on the part of Access when processing tables in replication mode. We don't have time here to explain replication, but let this warning be heeded by anyone using the feature.
If you have any templates that perform table inserts and that then capture the "record id" for that inserted record by querying the maximum id for the table you just inserted into, this logic WILL NOT WORK with replicated tables. It seems that the replication feature inserts a randomly ordered (and even sometimes negative) number for the key field of replicated tables; therefore, a test for max(id) after an insert WILL NOT return the id of the just-inserted record. It will just as likely be any other record inserted before that, which merely happens to have the highest valued of these randomly generated key values.
Suffice it to say, the discovery of this behavior put a major halt to an application while an alternative approach could be investigated. When we discover one, we will share it, but for now forwarned is forearmed.
Using "Application" (and "Session") Queries
Posted: 8/4/98
Did you know that you could name provide a name for a CFQUERY which, if prefaced by "application." or "session.", caused that query to be available in the appropriate application or session context? This proved a real valuable discovery, both for setting up frequently referenced tables and for carrying queries across templates. The latter was particularly useful in processes to scroll long search results, or pass control from a list of records to a record edit screen and return to the same spot, all without re-executing the query.
Of course, with such benefits come responsibilities. If you have any code that would update such persistent queries, be aware that unless you are careful those edits will not be available in the persistent query. It's not as onerous as it may sound. For instance, if you have a lookup table in an application variable, just be sure to re-execute the query that builds the "application." query. This will make the changed list available to all users of the application.
A session.query may be a little more complicated. If the data list created by the original CFQUERY would be changed because another user has added records that would show up on the list, for instance, there's no real way to force the list to be updated for the user currently looking at it. This is just something to be aware of in designing such an application. If you use a session.query, it will be static as of the time of the CFQUERY that created it.
A good way to use such an "application." query is to set it up in application.cfm in such a way that it gets executed only once in the lifetime of the application. (A simple way is to test for the existence of such an application item, and only execute the creation if it doesn't already exist.)
When CFUPDATE seems not to work
Posted: 8/4/98
We went through some serious hair-pulling some months back when a form to update some records (using CFUPDATE) was seeming to work (no error) but no updates were taking place.
(Note, if you are having a problem with CFUPDATE and it's not related to this problem, look into our other tip, "Watch Out! Lost data on CFUPDATE and CFINSERT".)
It took quite a while to determine that the problem was related to the use (in an Access DB) of a field which had been set in Access to have the characteristic of being "indexed" with "Yes - No duplicates". This is a logical, both for primary keys and for fields in which you only want unique data.
The problem, we learned, was that the CFUPDATE tag was failing (with no error) because of a quirk in the ODBC driver. It considers any "index-no duplicates" field to be a part of the primary key for the table, even though the field is not at all defined that way in Access. What this means is that, if the field is not a required field and we passed this null valued field to the CFUPDATE, it in effect led to no update because the primary key could not be formed. (It did not matter whether this was the only record with a null value or if there were others. It plain doesn't work.)
The moral of the story? First, don't cavalierly define fields as "index-no duplicates". First confirm that the field is not used in a template that passes data to a CFUPDATE (not a trivial thing to look for, to be sure). Second, find out if you have any fields that are "index-no duplicates" and "required-no". If you do, again, you should look for any uses of this field in templates that are passed to a CFUPDATE.
| 
