Oh My: Select @@identity works in Access. Worth exploring.

I just came across a knowledge base article at Microsoft, INFO: Identity and Auto-Increment Fields in ADO 2.1 and Beyond. It indicates that the SELECT @@IDENTITY statement, that's long been used in SQL Server to get the value of autoincremented primary key the last inserted record, now works in Access. Wow, I'd not heard about that! (And the note's from 1999). It seems worth exploring, with some caveats.

I guess I'm just surprised because I'd not heard about it. Maybe it's not news to others.

I just did this and it worked (in CF5 against an Access 2000 database):

<cftransaction>
<cfquery datasource="dsn" name="addkey">
insert into tablename (columnname) values ('value')
</cfquery>
<cfquery datasource="dsn" name="getkey">
select @@identity as key
</cfquery>
</cftransaction>
<cfoutput>#getkey.key#</cfoutput>

And it worked. Very interesting and worth exploring further.

One of the oldest technotes in the Macromedia (then Allaire) knowledge base, "ColdFusion Server (All Versions): Getting Auto-Increment Primary Key Values(Article 156)" from 1996 showed the approach of doing the insert and a select max(pkeyname) within a CFTRANSACTION.

Well, now you can do the same cftransaction and insert but instead do a SELECT @@IDENTITY. Why is this worthwhile? Well, one of my long held beefs with the old approach is that the CFTRANSACTION tag in and of itself doesn't guarantee that 2 templates executing the same code at the same time wouldn't get each other's pkey values. It would be critical to use the right ISOLATION attribute on the CFTRANSACTION and have that be supported by the DB to protect against two inserts at once, and since most examples leave it off, that means you'd get the default isolationlevel for your database, which varies by DBMS (and by optional configuration of a specific database). So you couldn't know FOR SURE that you were getting the right pkey, even with CFTRANSACTION.

Maybe that's only a concern in high volume environments, but it's still a worthwhile concern, especially if you've not focused on the isolationlevel to know things worked as expected. (The MM technote was updated in 2001 to add a CFLOCK around the insert and SELECT, but that only would prevent concurrent execution of that CF code--others running other CF code not in the same lock or outside of CF entirely could certainly do an insert at the same time.)

But the SELECT @@IDENTITY would appear to be much more appropriate. According to the technote, it guarantees not to get the value of another inserted record.

Now, it's not entirely clear how much more reliable this is in the CF environment. For one thing, we can't issue the SELECT in the same CFQUERY (or "batch") as the INSERT as is done in some of their examples. So whether the CFTRANSACTION really performs the same role as the batch in those examples and guarantees it's always right is yet to be proven.

Also, since the "client" in this scenario is CF and CF shares and reuses threads, it seems possible that the DBMS (the Jet engine) might be fooled into thinking more than one user is coming in on the same "thread". I'll rely on others with deeper experience or access to CF code to look into that. Frankly, one could argue that that issue is no more threatening to this approach than to the old one.

There is one potential difference, but it really doesn't apply to Access. If you were to use this approach against a SQL server table that had triggers causing inserts into other records, the SELECT @@IDENTITY would return the value of that other table's pkey. Again, this isn't unique to this opportunity to do the statement from within CF. The same issue would apply if you did the same thing in an SP in SQL Server. Just something to be aware of. It's discussed in the KB.

Indeed, I recommend anyone interested in this matter check out that MS KB. And if you learn anything more about things, please do pass along your findings and I'll post them (with accreditation) here.

Oh, and to be technically correct, this is a change in the underlying ADO mechanisms that CFQUERY handles for us, so as I implied above it's not just a change for Access but for any Jet 4 database (like Access) as well as SQL Server 6.5+. I suppose it may require a certain level of MDAC support, but I have a pretty vanilla machine (Windows 2000 pro, CF5, Access 2000 and standard installs of stuff) and it worked for me. Again, if anyone notices other details, please share them.

Turning off the CF Admin Password in CFMX

There are occasions when you don't know the admin password on a CF server but need to get into the admin. Assuming you are authorized to do so and can access the registry for that server, in CF 5 you could simply change the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\Server to set the value UseAdminPassword to "no". Then you could login, change the password, and check the checkbox on that page in the admin to start requiring it again.

In CFMX, however, this like many things is no longer in the registry. Instead, you need the authority to edit the file neo-security.xml (yep, even in the final release of MX it's says neo) in the \lib directory. Change the value for:

<var name='admin.security.enabled'>
<boolean value='true'/>
</var>

from true to false and follow the same process in the admin to update the password and reset its being required.

Just as you should take care when editing the registry, so too take care when editing any of the CFMX xml-based config files. Leave off a closing slash by mistake, or the like, and the server may not start!

Mapping DataType Keywords When Using SQL DDL

In my July 2000 CFDJ article, Reconfiguring Remote Databases via SQL (available online from my site under "articles" at systemanage.com), I talked about how to use SQL DDL statements like CREATE TABLE and ALTER table to perform manipulation of your table, column, index, and constraint (primary/foreign key) definitions.

One of the challenges in using such commands, for instance when manipulating an Access database this way, is mapping the datatypes names for a column you might want to create. For instance, if in Access you refer to a column as being a "yes/no" datatype, that won't work in a CFQUERY doing a CREATE TABLE, for instance.

Indeed, keeping these mappings of Access to ODBC terminology for datatypes can be challenging.

I address this and some related issues in the article. I even offer a table there that shows these mappings such as yesno/boolean/bit/logical would equate to that yes/no. There are a couple of links offered in that article which are now (2 years later) no longer valid. See the comments at the bottom of the front page of the online article for some updates to those.

Also, I found another page recently that has some of this same info, and still some more other useful tidbits:

http://admin.nj.devry.edu/~kjudge/accessddl.htm#Comparison

It also offers several examples of how to perform SQL DDL to do all sorts of things, such as create/alter tables indexes, indexes, and constraints (such as primary and foreign keys).

Nifty stuff, and something that a lot of CFers never learn about. It can be useful for managing a remote DB especially, versus downloading the file to make the changes and then uploading it back. I discuss all that in the article.

More CFMX Hosting Alternatives

For those who can't yet run CFMX in producton (or perhaps even test) but are open to putting your code on a hosting provider, I mentioned a free one in my first blog entry on 6/27. That's limited for non-production use.

Here is at least one provider offering an inexpensive plan (CrystalTech and their "plan 1") for hosted CFMX services: http://www.crystaltech.com/plan1.htm. As I learn of others, I'll update this list (at least while it's novel to find such providers).

Of course, one tends to get what one pays for. For 19.95, the features seem very compelling. Just beware in shared hosting situations about who else is on your box, and how many other sites (and how many visitors to those), as well as support issues (including tech support, control over your resources, security to protect you from others on the server, etc.). But then, sometimes people have problems with expensive services, so price really isn't always a good gauge.

Obtaining updates for CF and CF Studio 4 and 5 releases


If you find yourself needing to reinstall your CF 4,4.5+, or 5 or Studio 4.5+ software, you may find that your install from the base CDs leaves you needing any of several updates or patches. You may have to struggle to find things, such as Studio 4.5.2 to update your Studio 4.5 install (for those still sticking it out at that release), or some Studio hotfixes, or the tag updater for making CF5 tags and help available in a 4.5+ copy of Studio.

See the page http://www.macromedia.com/support/coldfusion/downloads_updates.html.

It also lists all the CF server service packs, updates, and hot fixes for both for 4.0.1 and 4.5.1, as well as links to some security patches.

And if you're looking for an updater that provides a way to add CFMX support to Studio 4.5, 5 for the tag insight, tag edit, tag help, tag completion, and related features, see http://www.macromedia.com/software/coldfusionstudio/productinfo/resources/tag_updaters/.

Zip Code database

Folks, if you've wanted to perform zip code based calculations (find zips within a given distance from a zip code), there's a free database (well, a txt file) with that info being offered at http://www.cfdynamics.com/zipbase/. The site offers it free and looks forward to updates from users (and a note of how you're using it).

There's no code offered there showing how to use it in any particular way. There are some CF custom tags that perform distance processing with the latitude and longitude values (also there), though none are offered on that site. See below for the details.

Still other possibilities include using the zipcode to perform lookups in data entry applications to avoid the user having to enter their city and state (be careful not to rely on this data but instead simply use it as a tool to aid data entry). I can't get into the details here, but one way it to use javascript and hidden frames, a technique that has been discussed in various CFDJ article and other resources.

Also, since it's a .txt file, you either need to import that into a DBMS or leverage any of the choices available for processing text files.

Following is some code that will at least allow you to process the file as a text file, should you want to do so, using the new CF5 Merant text drivers. Note that the first line sets the file and path name for the zip_codes.txt file that you'd download at the above site.

<cfset zipcode_filepath = "E:\INETPUB\WWWROOT\cfdemo\ZIP_CODES.txt">

<cfquery name="get" dbtype="dynamic"
connectstring="Driver={MERANT 3.70 ColdFusion OEM 32-BIT TextFile (*.*)};TableType=comma;FirstLineNames=0;IntlSort=1;ApplicationUsingThreads=1;UndefinedTable=guess" cachedwithin="#createtimespan(1,0,0,1)#">
select
field_1 as ZIP,
field_2 as LATITUDE,
field_3 as LONGITUDE,
field_4 as CITY,
field_5 as STATE,
field_6 as COUNTY,
field_7 as ZIP_CLASS
from #zipcode_filepath#
</cfquery>

<cfoutput query="get" maxrows="5">
#zip# - #city# (#county#), #state# ( #latitude#, #longitude#) [#zip_class#]<br>
</cfoutput>

Notes:
- I'm using the MAXROWS attribute of the CFOUTPUT to limit how many records are displayed in the loop

- I'm using the "connectstring" approach for referring to a datasource, which means I don't need to create a DSN in the administrator.

- Since this file has no column names listed in the first line of the file, the Merant driver finds them and by default names then field_1 thru field_x. I've renamed them with an alias in the SELECT to make them more useful later in the code.

- I've used the CachedWithin attribute on the CFQUERY, since the file would not change often. Be aware that this file is 3mb, so loading the entire thing into memory may have some performance impact (likely modest in most systems).

- Finally, this Merant text driver may or may not work in CFMX. My CFMX system is unavailable at the moment so I can't test this right now myself.

Again, rather than process it as a text file, if you're going to search it often, you may as well import it into a database and leverage the indexing possible there.

If I do create or find some code to extend the process of working with these zips, latitudes, longitudes, etc, I'll share it.
For those motivated to get a head start, the GreatCircle and FreeZip custom tags at the MM Dev Exchange look promising. The former does calculations of miles between given lat/lon pairs (though I'm having some trouble with it) and the latter is tied to its own included 1990 census zip code DB. The logic in it could be pulled out to work with this zipcode file.

Validating Form Input With Regular Expressions


OK, so this one's not new to CFMX, but it's a CF5 feature that many missed.

If you've ever wanted to validate form input such as email addresses and phone numbers, but have found the built-in validations of CFFORM to be lacking, note that CF5 has added new functionality to perform regular expression validation within CFINPUT. All you need is the right expression. If you're not familiar with them, no worries. Here's how to validate both a phone number and email address. The key is the new VALIDATE="regular_expression" and associated PATTERN attributes.

<CFFORM ACTION="">
Phone: <CFINPUT TYPE="Text" NAME="phone" VALIDATE="regular_expression" PATTERN="^(\(?\d{3}\)?)?\s?\d{3}[\s\-]?\d{4}$" MESSAGE="Phone is improperly formatted">

Email: <cfinput type="text" name="email" message="Email address is impoperly formatted." validate="regular_expression" pattern="^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*\.(([a-z]{2,3})|(aero|coop|info|museum|name))$">

<INPUT TYPE="Submit">
</CFFORM>

You can actually run that example, leaving the ACTION="". It will just try to submit to itself. If you give it a valid email and phone, it will submit. If you don't, it won't and will offer an error mesage. Of course, you can change the MESSAGE to suit your taste and would want to change the ACTION and NAME values to those appropriate to your application.

For more information, see my article in the Dec 2001 CDFJ, "Validating Input with Regular Expressions: Little Known Features of CF 5 ". You can read all 30+ of my CFDJ articles online from the "articles" link on my site, www.systemanage.com. Note, however, that the expressions offered above differ slightly from those offered in the article. I have offered these corrected versions in the "comments" area at the front page of the online article.