VOLUME II (Jun-Aug 1998)
Be Careful with Cascading Deletes
If you have found the benefits of defining referential integrity in your tables (called "relationships" in Access, and available by other means in other DBMS's), you've probably seen the option to "cascade deletes" from one table to another. (The concept of cascading deletes is not explained in detail here. This is a tip for those who may have used it without considering a certain danger.)
This may seem like an appropriate measure for any relationship but beware: sometimes what you want is a cascading "null", not a cascading delete. This is not at all the same thing, and incorrect (or indiscriminate) use of cascading deletes may cause unexpected data loss.
For instance, consider a table that tracks "members" which has a field called "chapter_id". This is a lookup (a foriegn key) to the id in a chapters table. If you delete a chapter you probably DON'T mean that any members in that chapter should be deleted. You probably instead mean that the value for chapter_id in the members table should be set to null. This is not what a cascaded delete will do. It will instead delete the member records which had a chapter_id value set to the deleted chapter.
In this situation, you would need to code the intended changes manually (sadly, removing some of the intended purpose of such automatic referential integrity). In some DBMS's, you can use a "trigger" to achieve this effect. (Stored procedures would work well, too.)
Of course, if in this example you really do mean to delete any member records that no longer belong to a valid chapter, then a cascading delete would be appropriate. The point is that you should consider the choice carefully.
On the other hand, there are situations where cascaded deletes do make obvious sense, as in many-to-many lookup table. For instance, in the membership database example, if a table called chapter_officers was setup to define what members held what offices in a chapter, such that it held a chapter_id, member_id, and rank, if a member was deleted or a chapter was deleted, you WOULD want to delete the records in chapter_officers that referred to either of those.
Cascading deletes are powerful if you understand them.
Just be careful when you define cascading deletes. You may want to review all your databases to correct any incorrect settings, if you turned on cascading deletes without this consideration. And in that case, consider too the possibility that you may have lost valuable data unintentionally if a delete was cascaded in such an incorrectly set instance.
Suffer no longer! See more directory info AND file content in Studio
Here's a customization option for Studio that you may have missed, and you may just love!
By default, Studio's interface shows the file directories at left and the file content at right. That left pane is itself split into 2 parts: the directory tree at the top and the file list in a bottom.) That makes it look like a traditional interface used in many tools, which is ok.
But there are problems. And you may have just lived with them, but you need not. For instance, if your work is in a deep sub-directory structure (like c:\webshare\wwwroot\myproject\...), it gets to where the top pane's display becomes rather cumbersome to navigate, not to mention only able to show a very little. Of course, you can slide the length of the window up and down, but that then infringes on the display of the file list.
And how about that? How often have you wished to be able to, say, sort the list by date, only to find that the "date" column doesn't appear in the narrow slice of real estate available for the display of the file info. Of course, you could also slide that whole left pane display to the right, but now you're infringing on the display of the file content in the right window.
And then there's that content display. How many of you have dimply lived with having a display of about a half-screen's width to edit your code? Perhaps you knew that one trick to solve that is to use the Ctrl-F12 key to switch to a full-screen display. This removed all toolbars and other resources so that it's a nice clean screen. Ctrl-F12 toggles you back.
That's probably something to get used to using when you're editing a lot. You may not like the loss of the toolbars at the top. In that case, consider the F9 key. Its result looks the same, but it doesn't remove those top toolbars. What it really does is simply hide that left pane of information. (What we've been referring to as the file directory info. Sometimes, it's a help viewer, or a project viewer, which are among the available tabs at the bottom of that pane. Check those out if you never had!)
This left pane is technically called the "resource toolbar". But knowing this offers an incredible opportunity if you hadn't noticed it before. This is the real meat of this tip! Just as you can manipulate all the toolbars in Studio, you can manipulate this one. In fact, you may never have noticed that there are 4 "arrow" icons at the top right of this pane (and an "x" to close it, but F9 is probably better to use to hide it).
The really cool thing is in solving the original problem offered above, that of having to compromise with a chintzy display of directories and a half-screen display of file content. Using this newfound knowledge, you can choose to put this "toolbar" at the top or bottom of the screen. The key is that then the directory display is wider, as well as the file information display. You can definitely see the date column now! And sort to your heart's content. You may need to resize the pane showing the directory tree, but you'll have ample room to widen it, with little impact on the file information display.
But as they say, "wait, there's more!" Another benefit of this layout is that now the file content is displayed the full width of your screen. You may not need to use either the f9 or Ctrl-f12 options to give you more room. You'll have it all the time now. Of course, you may prefer to have more space for the number of lines shown in the file. You can adjust the size of the window and easily find a happy medium for showing a reasonable number of files in the "resources" pane.
So, you now have access to powerful tools to make your Studio display the most effective it can be, for YOU!
Quick and Easy Indenting of Code
Indenting your code in CF is smart, and using the tab key to do so will work. But if you use Studio and really want to make indenting easy, you should know about these 2 features. You can set Studio to "auto indent", so that each new line you enter is indented at the same level as the previous line. Do this with Options>Settings. Select the "Editor" tab and check on "auto-indent". This appears to be the default on a new installation of Studio, but if it's not setup this way for you, now you know how.
What's perhaps not as obvious is that if you need to indent several lines of code at once, highlight the lines and select Edit>Indent. This will indent the lines by one tab amount. If you use this often, you'll find the keyboard shortcut easier to use: Shift+Control+. (that's a period). You can use the same approach to "unindent" a group of lines, and its shortcut is Shift+Control+, (comma).
Generating Maps Dynamically from Mapquest
We have a client with an association site that shows a "chapter" information page for each chapter. The page shows various details about the chapter, including the address, city, state, and zip. We showed the client how they could have a map generated automatically at the request of their site visitors, by simply providing a button that says "generate map". They were impressed!
You can have that too. We happened to use Mapquest to generate the map, but there are other services. Even with Mapquest, there are a couple of ways to do it. One is using Mapquest's "linkfree" service, which does indicate it's free for commercial use.
Another is to simply use CFHTTP to fill in the same form that any user would to generate a map on the Mapquest site. Only with CFHTTP we are able to do it for them automatically. We've set this up in such a way that the code to generate the map can be called from any of our applications. (Don't worry, if you haven't got access to the custom tags directory on your server, you can still use it, as we'll show you.)
First, create a template (we'll call it view_chapter.cfm) that includes a form that shows the user a button they can use to generate the map. The form should have an "action" which calls a second program (we'll call it view_map.cfm). It should pass it the hidden form fields address, city, state, zip, and optionally country to view_map.cfm.
View_map.cfm can have any sort of validation you want it to, but if those validations pass (or if you want none at all), it should simply call the module that processes the map. We've written this to be called as a custom tag or in our case, using CFMODULE. Since we don't have access to the custom tags directory on this client's commercially hosted server, this approach allows us to put the code in a root directory and share it among several applications on that server.
The method we use to call the module is:
You might have to customize this call in your code if you don't end up placing the mapquest.cfm template in the directory above where this template resides.
Call to mapquest.cfm, to be placed in template called by form that passes it the address, city, state, and zip as hidden fields.
ADDRESS = "#form.address#"
CITY = "#form.city#"
STATE = "#form.state#"
ZIP = "#form.zip#">
Finally, the "custom tag" that's called is named mapquest.cfm, and should include:
Store the following code as mapquest.cfm:
which uses CFHTTP to call on the mapquest service to generate the map.
<CFPARAM name="attributes.address" default="">
<CFPARAM name="attributes.city" default="">
<CFPARAM name="attributes.state" default="">
<CFPARAM name="attributes.zip" default="">
<CFPARAM name="attributes.country" default="">
You can also pass the country, but mapquest does not map all countries (or even all addresses). This program makes no checks for valid data. It's just a starting place for those interested in the ideas.
Finally, if you wanted instead to call Mapquest's Linkfree service instead, you could use the following in the mapquest.cfm file.
(The line above was split for easier readability)
Easy access to CF's hundreds of powerful functions (and more)
You've probably discovered the incredible wealth of functions available in CF, for performing everything from string and date manipulation, to array and list processing. (If you haven't discovered the power of lists, check that out!)
It can sometimes be a pain to use (let alone remember) all these available functions. There are just so many to choose from. And it's even more difficult to remember any parameters required, and their expected order. You may find yourself hopping to Studio's help to find them, or you may have a bookmark in your browser to the CF Docs html files. Those work well, especially to learn about them.
But to access them even more quickly and easily, use the "insert expression" command, available while you're editing text in the Studio editor. Just right click where you intend to put the function, and select "insert expression". (You can also access the expression builder with the keystroke Ctrl-shift-E or from the Tools menu command.)
You'll be presented a hierarchically organized list of the available functions, and more than that, the expression builder will show you the expected parameters, in their proper order. Neat!
You can also easily insert the expression into your text by clicking "insert". What's more, you can obtain help on the selected item, by way of the two small icons in the lower right corner. The first will present a new window for the help text while the second will open a small frame in the current window of the expression builder. The former will be more useful to browse the contents of the help text, especially longer help files, while the latter may be suitable for quick reference on a small topic.
While you're looking at the list of topics you may be currently most interested in, scroll down the expression builder's list of elements and you'll see that below the functions are also available:
- constants (including date/time/number formatting mask components, as well as regular and boolean expression elements),
- operators (including comparison, arithmetic, and logical operators), and
- pre-defined variables (including all the variables of type CGI, CFSEARCH, CFPOP, CFERROR, and more!)
This is a great way to be reminded about what's possible in CF. And the ease of access will probably increase the likelihood of your taking the time to use them.
For folks most comfortable with keyboard shortcuts, you'll probably notice that the expression builder (and the separate help window if you open it) won't close by simply pressing escape. Nor by Ctrl-F4 or Ctrl-W (often used to close windows in various other contexts). While you may be reluctant to try it, it is indeed an Alt-F4 that will close the window. Just be sure it has focus, or you may close CF Studio by mistake!
Note one quirk about using the right-mouse-click to call up the expression builder: you may have the cursor on the line at which you're typing and intend to enter the expression, but if happen to casually right-click some other place on the screen, the inserted code will go to the place closest to that mouse-click. Not where you were typing. Be sure to place the mouse where you will want the code inserted (or avoid the problem by using the menu command or the key-cut, ctrl-shift-e).
Finally, check out the related "tag chooser" and "insert sql statement" commands as well. The former will call up the tag editor to insert an html, cfml, hdml, vtml, or custom tag (though if the tag is very simple, it will just place it into your text), while the powerful "insert sql statement" will connect to your databases and help you visually build and insert SQL for queries.
Installing Visual SourceSafe for single user development and testing
One of the coolest things about studio seems its integration with third party source control tools, like MS Visual Source Safe, Intersolv PVCS, and StarBase Versions. I just installed VSS for the first time to use with Studio and encountered a couple issues (including an error and solution) that may help others.
If, like me, you're working on a non-networked workstation (maybe with CF installed on a remote server and a single-user version on your desktop), you'll probably want to install VSS on that desktop. In doing so, you'll need to install both the server and client portions of VSS on that desktop, which is not really obvious from the doc.
Also, you may find that Studio fails to recognize the installation (and running the VSS programs like SS.exe also fail). In my case it was because in this single-user client-as-server situation, VSS stupidly creates a cyclical reference in the srcsafe.ini file which points to itself. All I needed to do is remove that bad #include line (the only one there in my install). This error and solution are described at http://premium.microsoft.com/support/kb/articles/q159/7/75.asp, but I hope this explanation helps anyone searching for pointers here.
Be careful getting the record id after an "insert"
You may know about the mechanism for determining the record id of a record just inserted. It involves using a CFQUERY with SELECT MAX(id) as max_id following an insert, and it's recommended that you wrap the insert and the select in a CFTRANSACTION.
We just want to share that we have experienced a problem where occasionally, if a CFSET was executed to assign that max_id to a local variable, the template would fail stating that there was no such column in the query. Of course, it IS. But something's amiss.
Our initial analysis is that the CFSET was inside the CFTRANSACTION, and while that works nearly all the time, there may be database or driver issues that can cause it to fail SOMETIMES in that configuration. As a precaution, we have moved such CFSET statements outside the CFTRANSACTION. There's absolutely no difference to the result logically, but perhaps it will present this problem.
|Update (3/23/00):||It's worth noting that this long offered approach of determining the value of the primary key of a just-inserted record should be updated to point out that in SQL Server, a better approach is to use "select @@identity" after the update. |
Yet another approach is to setup an insert trigger for the table and do a "select <primarykey> as newpk from inserted". Then you'd have access to a column called "newpk" after the insert (even in a CFQUERY) which would have the value of the just-created primary key.
Read your favorite SQL server resource for more info.
CFHTTP Gotcha Down? Don't use relative urls!
We were seriously unhappy with initial attempts to use CFHTTP. We were testing it against our local server and it was never returning anything. Just a blank page.
We weren't even trying to save to a file. Just a plain old CFOUTPUT of the cfhttp.filecontent variable. Grr!
Then we took a chance and changed the URL parm to specify a complete URL rather than just a relative one. So "recruit.cfm" became "http://127.0.0.1/foo/recruit.cfm".
Voila! So the lesson learned, don't use relative URL's with CFHTTP.
BTW, we were surprised to learn that in addition to specifying form fields on a post operation, you can also specify a referer (or any other sort of cgi variable that the form might expect), by using 'type="cgi"' on an appropriate CFHTTPPARAM tag, as in:
<cfhttpparam name="http_referer" value="foo.cfm" type="cgi">
Facilitating upload of changed Access database
Most CF developers will have encountered the problem where they wish to upload a changed copy of an Access database (perhaps they added a column to a table, etc.). The attempt to FTP the file to the server fails because the file is "in use". This is because a lock is placed on the database once it's accessed by a CF template, and that lock is generally not released until the server is restarted.
Of course, some may argue that the simplest solution to this is to remove the mechanism that creates this lock. It's controlled by the "maintain database connections" option which is available in the CF administrator under the datasource's definition tab (specifically under "CF settings" button). Removing this option is said to have a performance impact (though some have argued this is not so).
But if you are working on a hosted server where the administrator is controlled by someone else who will not let you change this setting, or if you choose to use the setting for performance reasons, you need another solution to the problem when you want to upload a changed database.
There are various work-arounds to this, but recently we learned of a very clever (albeit curious) one.
The simplest way to unlock the file is to run a query against the datasource with a SQL syntax. We have found that the following very generic query will do the trick:
select xxxxx from yyyyy
By naming this proc "unlock.cfm" and calling it with a url parameter, such as unlock.cfm?dsn=mydb, it will cause an error against the "mydb" datasource (if the datasource exists), and will release the CF database connection lock, thus allowing you to upload a new copy of the database.
(It's a little disconcerting to learn that a syntax error on the CFQUERY will lose the cached database connection. We wonder if this is a hole that may be closed in future releases of CF or the ODBC drivers. Anyway, for now, it works and it's quite simple. And it only works on IIS servers.)
Of course, there are some gotchas to be careful of when using this sort of mechanism.
Any attempt to download, change, and upload a changed database file (using this or any other means) should really be setup with a corresponding mechanism to prevent updates to the file on the server by site users while you're in the process of modifying the file on your workstation.
- Someone could lock the file again in the instant between running this code and trying to upload.
- Then again, an even bigger problem is that someone may have updated the file between the time you downloaded it and tried to upload the changes.
One mechanism would be to turn on a flag that is monitored by application.cfm so that updates are prevented if the flag is set. Another would be to at least confirm that the file's data and size have not changed between the time you downloaded and time you uploaded. This could even be automated with CFFTP. I'll leave these as an academic exercise for the curious.
Watch Out! using "Action=Insert" or "Action=Update" as form or url variables
Posted: before 6/9/98
We were pulling more hair out (and I personally haven't got much to spare!) trying to resolve a CF error message we were getting:
What?! There were no errors in the syntax of any code, and the code had been working fine. We were stumped for about an hour before we realized that it was due to our having added a url parameter in calling the request, with the value "ACTION=INSERT".
- Form field 'DATASOURCE' not found in script input.
This field is required in order to process the insert request.
Our code was calling a template in the form:
Turns out the use of url (or form) parms with the name "action" and a value of either "insert" or "update" was a means of effecting a CFINSERT and CFUPDATE in CF 1.0, and the use of these parms continues to be recognized in 3.1.
Only the error when you try to use them in a way that CF does not expect is pretty cryptic and anyone will be sorely surprised if they trip over this as we did. Hope the heads up helps.
As a work-around, just change the parm name to anything else (even just "actionx").
Emulating a "Words" Function
Posted: before 6/9/98
Though CF provides many string functions, one common one that it doesn't offer is a "words" function, to count the number of words in a string. This can be easily emulated, with:
For the above CFSET, "words" would be set to 3. Pretty nifty!
- <cfset words = ListLen("string to count", " ")>
Be careful with checks for Nulls
Posted: before 6/9/98
We were shocked to learn of the behavior of the following query string in SQL Server:
select * from table where column <> null
It was producing 0 records found, though we knew that records in the table had valid values for the named column. The same SELECT statement run against the MS Access version of the database, with the tables having the same data, had produced the expected recordcount result (172).
We did some digging in SQL references and changed the query to:
select * from table where column is not null
not only did it produce the desired number in SQL Server, it also produced the same number in Access (and the same as it had with the old syntax). This would suggest, of course, that the latter syntax should be used instead when writing code that will run against both DBMS's.
But we wonder how many applications out there are relying on the former syntax?! CHECK YOUR CODE!
(Update: the explanation is that a comparison to null, that is use of =, >, <,; etc., should technically always fail. Nothing is equal, nor not equal--not greater nor less than--null. The fact that the first line of code above was working in Access shows more of a lack of following standards in that version of Access. Still, be warned.)