Thanks again to those of you who have sent in questions or comments about this monthly column. If you'd like to see an archive of previous questions and answers, please visit www.NetsiteDynamics.com/AskCFDJ.

This month we have only three questions to consider, but the response to each is somewhat lengthy. I hope they prove helpful to you!

Q:How can I create a site that uses Session variables but not cookies?

A:Ah, the "cookie-less" Web site! It can certainly be done, but it does take considerably more programming effort than a site that relies on cookies to identify one user from another. What most CF programmers are aware of is that CF looks for the existence of two cookies (CFID and CFTOKEN) to keep track of the Session variables for each user. What many don't know is that if CF doesn't find these two variables in the cookie scope, it looks for them in the URL scope. In other words, if you don't want to use cookies, you can (and have to) pass the values of CFID and CFTOKEN on the URL for every single page request in your site.

One thing you should do if you really want a cookie-less application is tell CF not to create session cookies at all. Do this in your CFAPPLICATION tag in Application.cfm. For example:

<CFAPPLICATION NAME="CoffeeValley" SESSIONMANAGEMENT="Yes" SETCLIENTCOOKIES="No">

Here's where the extra programming effort comes in. You now have to create these two variables and pass them along the URL for every link, form post, or redirect in your site. This isn't difficult; it's just tedious. However, there are two tools you can use to make this easier. The first is the ADDTOKEN attribute of the CFLOCATION tag. If you're doing any page redirections using the <CFLOCATION> tag, write it like this: <CFLOCATION URL=" page2.cfm" ADDTOKEN= "Yes">. This will append CFID and CFTOKEN to the page request, thus maintaining the session information for each user.

For hyperlinks and form posts your code should make use of the relatively unknown Session.URLTOKEN variable, a single variable the CF server creates that contains the equivalent of both CFID and CFTOKEN as URL variables. For example, the two links shown in Listing 1 would yield the same result, but the first requires much less coding on your part. For form posts use the code in Listing 2.

Q:I use a third-party hosting company to host my CF application and an MS Access database for my data source. My problem is that I sometimes need to upload a new database file to the server, but their server keeps my database file locked, preventing me from replacing it with the new file. Is there a way around this?

A:Yes! This is a common problem encountered by people using hosting companies. The problem (which is actually a good thing as far as the performance of your site is concerned) is that the data source created in the CF Administrator has been set to "Maintain database connections." With this enabled, CF doesn't have to reconnect to your database every time someone wants information from it, thus making all your queries faster. Unfortunately for you, CF keeps that file locked as long as the connection is active. What you need is a way to force CF to release its connection. There are two ways you can do this remotely.

The first method may not be available to you if your hosting company has disabled the use of certain "undocumented" functions. Try the following to see if it works. Upload and run a .cfm file with this one line of code in it: <CFSET tmp = CFUSION_DBCONNECTIONS_ FLUSH()>. This function, CFUSION_DBCONNECTIONS_FLUSH(), will cause CF to release all existing database connections. Out of respect for your hosting company, I wouldn't recommend using this method since it causes CF to release all database connections.

Before that function was discovered, I used a much safer method that causes CF to release only your database. It's the behavior of CF to drop a database connection if the query passed to it causes an ODBC error. Therefore, all you need to do is upload and execute a file with a query in it that will always cause an error:

<CFQUERY NAME="KillConnection" DATASOURCE="FastTrack_Lab">
Select * From NoTable
</CFQUERY>

Since there's no table called "NoTable" in my database, CF will receive an ODBC error and release its connection to that file. You can now upload a new file to replace it.

Q:I have an application that uses MS Access so I can't write any "triggers" as you described in the Advanced ColdFusion class. What's the best way to retrieve the ID number of a record that has just been inserted into the database?

A:This is a very common question. When you insert a new record into the database, you often need to know the primary key value the database automatically assigned to that record. As you've mentioned, the best way to do this is through a trigger. Unfortunately, as you've stated, Access doesn't support triggers. The traditional way of doing this is to run a query that will retrieve the highest number (using the SQL Max() function) in the primary key column (see Listing 3). Since the database assigns these number sequentially, Max() will usually retrieve the record you just inserted.

However, this will only work if you never have more than one user inserting records into the database. In a multiuser environment, it's easy to see that this could retrieve the wrong record. There are at least two ways to prevent this. The easiest is to ensure that no more than one user ever executes this code at the same time. To do this add a CFLOCK around the entire "transaction" (see Listing 4). Assuming that you don't insert records from any other template in your application, CFLOCK will ensure that only one user at a time will execute these two queries.

Another alternative is to insert in the database an absolutely unique value that can always be retrieved to identify that record (in addition to the primary key). You could add a column to your database table called "UUID" (it should be a text column that allows 35 characters). Then, have CF generate a Universally Unique Identifier (UUID) using the CreateUUID() function. Insert that value, then retrieve it (see Listing 5). While this increases the size of your database and can take longer to retrieve, it should never fail regardless of the number of simultaneous users inserting records.

Please send your questions about ColdFusion (CFML, CF Server, or CF Studio) to AskCFDJ@sys-con.com.

• Source Code