FWIW, I created a custom tag to tackle the problem because I
didn't like the way cf_max_id worked. One of these days I'm
going to turn it into a stored procedure (but hide it behind
the tag, 'cuz I like the simple interface).
<!---
||BEGIN FUSEDOC||
|| Properties ||
Name: CF_GetNextID
Author: Patrick McElhaney ([EMAIL PROTECTED])
Version: 1.0
|| Responsibilities ||
I increment a number in a database and return the
incremented value. The database table is really just a
hash. It has two fields: "key" and "value." Given
the key, I increment and return the value.
If the key is not found in the database, I set the key to
the value of #seed#. If no seed parameter is passed to me,
I throw an exception.
|| History ||
|| Attributes ||
==> datasource : STRING (the datasource name)
==> table? : STRING (table in which to perform the update,
default='control')
==> key : STRING (name of the key to update)
==> variable? : STRING (name of variable in which to put the new value,
default='attributes.#key#')
==> seed? : NUMBER (What value to start off with)
==> increment? : NUMBER (How much to increment by, default=1)
<== caller.#variable# : INTEGER (value returned after incrementing)
|| Exceptions ||
customTags.getNextID.keyNotFound
Thrown when the key is not found in the database and no seed is given.
||END FUSEDOC||
--->
<cfparam name="attributes.datasource">
<cfparam name="attributes.table" default="control">
<cfparam name="attributes.key">
<cfparam name="attributes.variable" default="attributes.#attributes.key#">
<cfparam name="attributes.increment" default="1">
<cftransaction>
<cfquery datasource="#attributes.datasource#" name="getID">
SELECT [value]
FROM #attributes.table#
WHERE [key] = '#attributes.key#'
</cfquery>
<cfif getID.recordcount>
<cfset thisID = getID.value>
<cfset nextID = getID.value + attributes.increment>
<cfquery datasource="#attributes.datasource#" name="getID">
UPDATE #attributes.table#
SET [value] = '#nextID#'
WHERE [key] = '#attributes.key#'
</cfquery>
<cfelse>
<cfif isDefined("attributes.seed")>
<cfset thisID = attributes.seed>
<cfset nextID = attributes.seed + attributes.increment>
<cfquery datasource="#attributes.datasource#" name="getID">
INSERT INTO #attributes.table# ([key], [value])
VALUES ('#attributes.key#', '#attributes.seed#')
</cfquery>
<cfelse>
<cfthrow
type="customTags.getNextID.keyNotFound"
message="Error in custom tag CF_GetNextID. The key provided was not
found in the database, and no seed was provided to start a new key."
detail="The key, <strong>#attributes.key#</strong>, was not found in
the table <strong>#attributes.table#</strong>, in the datasource
<strong>#attributes.datasource#</strong>. Please check the spelling of the
key name. If neccessary, enter a starting value for the key in the database.
If you like, CF_GetNextID can generate a new key for you. Simply add the
attribute 'seed' to the custom tag and set it to the value you would like to
start out with.">
</cfif>
</cfif>
</cftransaction>
<cfset setVariable("caller.#attributes.variable#", thisID)>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
