Here are the "tips in progress". Use in their current form if that helps you.

The Tips in Progress:

  • If users are reporting errors (by email, or by CFERROR, or by review of error logs) where expected form parms are not being passed to a template that is processing the form, the first thought should be to see if they may be calling the page directly (such as by typing the url for that form *result* page in their browser, or from a bookmark), which would be a no-no. You may want to catch that by testing for the cgi.referer field having a non-null value. If it's null, then (with most modern browsers) this means they did not get to the page from the expected form page but by direct entry of the template. You could throw up an error telling them not to do that. (Of course, you could go a step further and check that the referer is from within your domain, or even the expected previous page. Though don't rely on this for security, as it's possible to forge the referer header.)

    Another thing to do would be to test if the user is reaching the page with a cgi.request_method of "post". If not, that too means they have entered the url directlly (which would cause the request_method to be "get").

  • soundex function, usable in SQL Server but not in Access: select * from contacts where soundex(last_name)=soundex('#url.name#'). Forces scan of entire table, even if an indexed column is included in query. may be better to create indexed column to hold soundex values for desired column. Also, beware that soundex isn't perfect. Phone will not match fan, etc.
  • if you need to find a list of records that meet one criteria (perhaps a complex join), and then you want the converse of that or the records that DON't meet that criteria, rather than use a query that attempts to find the opposite criteria (which can itself be more complex and time-consuming to execute), try using a query to get the records not in that previous query, as in "id not in (#valuelist(previous_query.id)#)".
  • can use Access queries in exact same way as tables (renamed table x to y, created query x; all apps worked fine, including edits). Could be handy.
  • Access Security
    • can use Access "database password" to secure file. Can then either specify this password on the CF Admin datasource definition as the "password" for "default login" (ther userid is ignored), which is not terribly secure if anyone else can access the admin or use ODBC to open the file. A more secure means is coding the password on a CFQUERY with the "password" parm (note that again, though it is ignored, you must specify a "username" value as well). (One problem with using a simple password is that it opens access to all parts of the file, and also precludes using replication. If either of these is important, use "workgroup security", explained under "database security" in the Access help file. A problem with this is that to secure this best, you should create a new workgroup file, and then according to the help:

      "To have others join the workgroup defined by your new workgroup information file, copy it to a shared folder (if you didn't already save it in a shared folder in step 4), and then have each user run the Workgroup Administrator to join the new workgroup information file."

      This makes registry entries and would seem to complicate distribution of any such file if the file must be distributed (if remaining on the web server, this is a non-issue).

    • I don't think it's necessary to do all that. Here are some steps I'm working up:

      Following is a work in progress....!!!!

      to secure an access db so that it can only be with a given userid and password using workgroup security, do the following. Briefly, the steps are to define a new Access "user", remove permissions for the Admin user and the Admins and Users groups to access the file's tables, give permission only to the newly defined user. And use that ...

      Assumes that the db is not currently secured with workgroup security:

      • open the file
      • execute "tools>security>user and group accounts".
        • on the "users" tab (the first one shown), select "new" and enter a name and "personal id". This latter field is an equally important element of security later, and it's critical that you remember it as it cannot be accessed again once entered (and unlike password prompts there's no double-entry validation of what you enter).
        • By default, this id will be added to the group "users". Just be aware of this for now. Though it's not critical, add the user to the "admins" group as well, by seleting "admins" from the left group and selecting "add". (Doing this will simply enable you to manage this list of users and groups later, if you start Access using this new user. This won't be critical to being able to access the database from ColdFusion.)
      • be aware that what you are changing here is the definition of users within the default "workgroup" (again, we presumed you had no workgroup security in place at the outset). This default workgroup is defined in a file called "system.mdw" which is normally kept in \windows\system. Be careful about making any changes other than those outlined here.

      • execute "tools>security>user and group permissions".
        • first, we want to remove permissions to all tables from all user groups and each of the listed users other than the new id you have created. If no other files are yet using workgroup security, the only other user listed should be "admin".
        • Note that this command does indeed effect just this permissions of the currently open file.
        • for reasons explained later, let's remove the permissions from all the groups rather than users.
        • Choose the option to "list" "users". Select the first name. If that is admin, you will probably notice that all the options have already been de-selected. If not, follow the instructions in the next step to turn off permissions for the "admins" group.
        • Select the "users" group. Then select all elements (table names and queries, plus the "<new tables/queries>" option. (Either use ctrl-click to select each item, or to be sure select them all, put the cursor on one of the elements, hit the "home" key to move to the top of the list, hold shift and hit "end" to select them all.)
        • Deselect the "read design" option which should cause the de-selection of all the other options. (If the check box is greyed out, select it anyway, which should cause the option to turn white, indicating it's been turned off.) Be sure to press the "Apply" button at this step before proceeding to subsequent steps.
        • The list of tables/queries will remain all selected. Choose any other names in the list of group names, by again selecting the "read design" option to cause it to be "de-selected".
        • Then, we want to remove permissions for all users except the one which we intend to use to secure the file.
        • Choose the option to "list" "users". The list of tables/queries will remain all selected. Choose any other names in the list of user names (except the one you intend to use to secure the file), by again selecting the "read design" option to cause it and all other options to be "de-selected".
        • As you modify user permissions, you will be prompted to confirm the choice: "You changed the permissions of 'Admin' for '<new tables/queries>.' Do you want to assign these permissions now?" Answer YES.
        • Be sure to press the "Apply" button at this step before proceeding to subsequent steps.
        • Finally, we want to GIVE permissions to the newly identified user. This will enable the file to be accessed later using just this user.
        • While the list of tables/queries remain all selected, select the user name to which permission will be granted, and select the "read design" "modify design", and "administer" options. These will also cause selection of all other options.

      • having made these changes
      • To start or "log into" Access using the new userid, create a shortcut for Access that includes a call to your local copy of Access with the following aditional pams: /User xxx
        where xxx is the name of the user you created above.
      • To confirm what user you are logged in as, there are two approaches. The easiest is to use "tools>security>user and group permissions". There, at the bottom of the window is shown "current user". The other approach is to use "tools>security>user and group accounts" and select the "change logon password" tab. You don't need to (and should be careful not to) change the password to use the screen, but it will list the current user next to "user name:". One benefit of this approach over the first is that you may do it even without a file open, which may be useful if you try the other approach with a file that has been secured to not let you see the "user and group permission" command.

    End of Access DB Security tip.

  • add use of expedia maps service to descriptions of using maps. see http://www.expediamaps.com/maps-on-your-site.asp
  • You may know that you can have a form tag specify target="new" which will open a new window for the form processing template. Unfortunately, it appears that an AOL browser will fail to properly pass form fields to such a template loaded in a new window. This is a guess, but we've experienced errors where AOL users execution of the form resulted in the action template not having any of the form fields passed. We can't test AOL ourselves, but for now we have put in code to not do the action="new" on the form tag for AOL browsers (testing CFIF cgi.http_user_agent contains "aol"...).
  • using Allaire Knowledge Base, seems that selecting "any" for product will not find across all the others. Tried keyword "sourcesafe" and product "Studio", and got a document. Selected product "any" and got 0 records!
  • how to call custom tags in directory from which call is made (if you don't have access to the customtags directory on the server). And how to call such "custom tags" from even several apps in a given domain without access to the customtags directory, using CFMODULE to call the tag when placed in a root directory for the domain.
  • be careful with using interval=execute in a CFSCHEDULE. it created a task that ran every minute, unbeknownst to us for many weeks before we noticed the schedule log growing large. Had the app been doing something destructive, or processor intensive, that would have been potentially disastrous.
  • If you need to spawn a cf process within another template (as opposed to some external program), consider using cfhttp to call it. (Will that be synchronous? Would somehow sending the sooner cause the response to come to the CFHTTP tag while it continued to work in the background?
  • Ever wanted to setup a search mechanism where the user could make selections for one search, and selections for another search, and you want to combine the two so that there are no duplicates? It might be easy. This approach may not suit all needs, but it's worked for us.

    Assumption: a screen that prompts the user for search parms and creates a search result from a single query (myquery), whose primary key is "mykey". A desire to gather the search results over several screens such that the result is a list of records that met all the queries, with no duplicate records.

    On the first screen, save the id numbers of the result query into a list, as follows:

    <cfset session.mylist=listappend("",valuelist(myquery.mykey))>

    Prefacing the list name "mylist" with "session." will allow it to be kept over subsequent calls.

    Technically, if this call will be made in the same template (over subsequent calls to it), you would need to test if the list already exists, and act appropriately. If it doesn't exist, you'll want to create a new list, otherwise you'll want to add to that existing list, such as:

    <cfif not isdefined("session.list")<
      <cfset session.list=listappend("",valuelist(test1.contactid))>
    <cfelse>
      <cfset session.list=listappend(session.list,valuelist(test1.contactid))>
    </cfif>
    

    Similarly, you will want to put in appropriate code so that a new query can be executed that DOES NOT add to previous results. One way would be to pass a form variable to the results processing such that if the form variable is set, the session.list does

  • BE CAREFUL NOT TO OVERUSE SELECT *. When you're just trying to retrieve a record set to count records, or you really just need a couple of columns out of a select, avoid SELECT * and use just the columns you need (such as SELECT ID or SELECT ID,EMAIL). And remember that you need not specify columns in the SELECT in order to use them in a WHERE subclause. Those in the SELECT need merely be those you intend to reference in your program within a CFOUTPUT, CFMAIL, etc.
  • If a CFCOOKIE is used followed by CFLOCATION, the cookie will not be set. (See tag Talk March 1998 in Allaire Alive.)
  • If ftp's fail to download or an ftp feature to simply "view" a file fails, each because "can't close data gracefully", try waiting 15 seconds for the web server to release its hold on the file. IIS shows a propensity to make such a lock when the file is referenced.
  • Upload project: in project tab, with project open, appears only as icon on button bar above list of projects. Rightmost one.

    calls up an upload project wizard. optionally does comparison and only uploads changed files (just be sure to specify the correct folder!)

  • Save remote copy: File>save remote copy

    On window, right click to get "add ftp server/add ColdFusion server". Can add ftp server even if cf server not allowed.

    Won't get error if password fails. Just won't get listing of files when attempting to connect.

  • In studio, use Settings>editor>autoindent to cause auto indent.
  • Some will have known this but for others it may be a discovery: Did you ever wonder why a display of the "from" field (as well as header and others) from a CFPOP showed no actual email address? It's not that they're not there, just that you've tripped over a subtle issue.

    If you display these sorts of fields in a CFOUTPUT without using the CF function htmlcodeformat(), it will look like there is no email address in them. This is because they are usually enclosed in brackets <>, which get interpreted by your browser as html tags, which because they are not recognized as valid are simply ignored.

  • found that a form that tried to use a password field with the name "password" was being rejected by AOL's browser with "error in password text" (not being generated by OUR program!). On a hunch, changed it to password2, and it worked! Very curious.
  • Finding that AOL rejects CFLOCATION, but not always. user got message "this program does not support the protocol for accessing" and the name of the template. Strangely, the template doing this had itself been called by a cflocation. both had some cgi url variables being passed in the CFLOCATION "url". the first one had fewer than the second. Perhaps there's a limit?
  • experiencing error with some form fields not passed to template. may have been incorrect enctype
  • if a form has a method of "get", any hidden fields will be converted to url parms! WOW! This is cool, because if you try to pass url parms on the "action" url, they are ignored. This is the way to get them there.
  • if you add fields to be edited in a screen created by the record viewer wizard, not that it stores the list of fields to be edited in "formfieldslist". You can either edit this (and risk losing data if you forget), or change the logic to have the "recordaction" program (which performs the updates) take all formfields EXCEPT the recordid. (that's the only reason it uses this approach in the first place. You can instead remove the recordid form field if it's null. Don't know why the wizard doesn't do this for you.
  • problem if case of datasource name is different from one query to another (see this forum thread
  • forcing expiration of cached pages (technically, forcing a refresh with now())
  • using BOTH client and server validation (custom tag to create both at once, some client validation values not available on server)
  • see javascript tree under "script" tab of tag toolbar (top of studio interface, below menu, above editor). It offers a wealth of organized javascript functions, parms, etc. Also, see the activex button there as well. Surprised how many activex controls are already installed on my system.
  • ever wanted to use copyright symbol but couldn't remember the html tag for that? there are several such special characters, including <>&©®™, as well as "­   —–·¶ and more. You can access these and more using the "special characters toolbar", via View>Special Characters, or ctrl-shift-x.
  • -to convert several lines of text into a table or list, select it, right-click on it, choose "selection", and use any of several options there, including stripping all tags in the selection, adding breaks (<br>), changing the case, and more.
Tips Contents:

| Home | ColdFusion | Articles | Presentations
| User Groups | Other Resources | Press Releases | Company

© 1998-2018, Charles Arehart, SysteManage
Our Practice Makes You Perfect