Guess I can not attach files so maybe as inline text will work :(
Color.cfc
=========
<cfcomponent displayname="Color" extends="TableWrapper" hint="Base Color
methods.">
<cfset this.dsn = "">
<!--- CONSTRUCTOR --->
<cffunction name="init" access="public" returntype="struct"
output="no">
<cfargument name="dsn" type="string"
default="#request.db.dsn#">
<cfset this.dsn = dsn>
<cfset this.tablename = "Colors">
<cfset this.itemkey = "ColorID">
<cfset this.namekey = "ColorName">
<cfset this.orderkey = "ColorName">
<cfset this.fields = GetTableDetails()>
<cfreturn this>
</cffunction>
</cfcomponent>
EditColor.cfm
=============
<CFINCLUDE TEMPLATE="EditColor_Inc.cfm">
<CFOUTPUT>
<FORM ACTION="#CGI.Script_Name#" METHOD="post" NAME="NavForm"
ID="NavForm">
<INPUT TYPE="Hidden" NAME="ColorID"
VALUE="#oColor.GetField('ColorID')#">
<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0"
CLASS="listing">
<TR>
<TD>Color Name</TD>
<TD><INPUT TYPE="Text" NAME="ColorName"
MAXLENGTH="#oColor.GetFieldMaxLength('ColorName')#"
VALUE="#oColor.GetField('ColorName')#" STYLE="width: 300px;"></TD>
</TR>
<TR>
<TD>Color Code</TD>
<TD><INPUT TYPE="Text" NAME="ColorCode"
MAXLENGTH="#oColor.GetFieldMaxLength('ColorCode')#"
VALUE="#oColor.GetField('ColorCode')#" STYLE="width: 300px;"></TD>
</TR>
<TR>
<TD>Hex Code 1</TD>
<TD><INPUT TYPE="Text" NAME="HexCode1"
MAXLENGTH="#oColor.GetFieldMaxLength('HexCode1')#"
VALUE="#oColor.GetField('HexCode1')#" STYLE="width: 300px;"></TD>
</TR>
<TR>
<TD>Hex Code 2</TD>
<TD><INPUT TYPE="Text" NAME="HexCode2"
MAXLENGTH="#oColor.GetFieldMaxLength('HexCode2')#"
VALUE="#oColor.GetField('HexCode2')#" STYLE="width: 300px;"></TD>
</TR>
<TR>
<TD COLSPAN="2" ALIGN="center">
<INPUT TYPE="Submit" NAME="Save"
VALUE="Save">
<INPUT TYPE="Submit" NAME="Cancel"
VALUE="Cancel">
</TD>
</TR>
</TABLE>
</FORM>
</CFOUTPUT>
EditColor_Inc.cfm
=================
<CFIF IsDefined("Form.ColorID")>
<CFSET ColorID = Form.ColorID>
<CFELSEIF IsDefined("URL.ColorID")>
<CFSET ColorID = URL.ColorID>
</CFIF>
<CFIF NOT IsNumeric(ColorID)>
<CFSET ColorID = 0>
</CFIF>
<CFINVOKE COMPONENT="#request.cfcs.Color#" METHOD="init"
RETURNVARIABLE="oColor" DSN="#request.db.dsn#">
<CFSET oColor.Load(ColorID)>
<CFIF IsDefined("Form.Save")>
<CFSET oColor.Update(form)>
<CFIF oColor.IsRecordValid()>
<CFSET oColor.Save()>
<CFLOCATION URL="Colors.cfm">
</CFIF>
<CFELSEIF IsDefined("Form.Cancel")>
<CFLOCATION URL="Colors.cfm">
</CFIF>
IsPK.sql
========
CREATE FUNCTION IsPK (@TableName VARCHAR(80), @FieldName VARCHAR(80))
RETURNS INT AS
BEGIN
declare @full_table_name varchar (80)
set @full_table_name [EMAIL PROTECTED]
declare @table_id numeric
SELECT @table_id = object_id(@full_table_name)
declare @curfield varchar(80)
declare @ret int
set @ret = 0
declare cur_pks cursor for
select
COLUMN_NAME = convert(sysname,c.name)
from sysindexes i, syscolumns c, sysobjects o --, syscolumns
c1
where o.id = @table_id
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
and (c.name = index_col (@full_table_name, i.indid, 1)
or
c.name = index_col (@full_table_name, i.indid, 2)
or
c.name = index_col (@full_table_name, i.indid, 3)
or
c.name = index_col (@full_table_name, i.indid, 4)
or
c.name = index_col (@full_table_name, i.indid, 5)
or
c.name = index_col (@full_table_name, i.indid, 6)
or
c.name = index_col (@full_table_name, i.indid, 7)
or
c.name = index_col (@full_table_name, i.indid, 8)
or
c.name = index_col (@full_table_name, i.indid, 9)
or
c.name = index_col (@full_table_name, i.indid, 10)
or
c.name = index_col (@full_table_name, i.indid, 11)
or
c.name = index_col (@full_table_name, i.indid, 12)
or
c.name = index_col (@full_table_name, i.indid, 13)
or
c.name = index_col (@full_table_name, i.indid, 14)
or
c.name = index_col (@full_table_name, i.indid, 15)
or
c.name = index_col (@full_table_name, i.indid, 16)
)
order by 1
open cur_pks
fetch next from cur_pks into @curfield
while @@FETCH_STATUS = 0
begin
if @curfield = @fieldname
begin
set @ret = 1
end
fetch next from cur_pks into @curfield
end
close cur_pks
deallocate cur_pks
return @ret
END
TableWrapper.cfc
================
<cfcomponent displayname="TableWrapper">
<!--- data members --->
<cfparam name="this.dsn"
type="string" default="">
<cfparam name="this.TableName" type="string"
default="">
<cfparam name="this.ItemKey" type="string"
default="">
<cfparam name="this.NameKey" type="string"
default="">
<cfparam name="this.OrderKey" type="string"
default="">
<cfparam name="this.ImportKey" type="string"
default="">
<cfparam name="this.Fields" type="query"
default="#QueryNew('Empty')#">
<cfparam name="this.fieldCacheDuration" type="date"
default="#CreateTimeSpan(0,0,10,0)#">
<!--- methods --->
<!--- Initialize the Table Wrapper --->
<cffunction name="init" access="public" returntype="struct"
output="yes">
<cfargument name="dsn" required="Yes" type="string">
<cfset StructAppend(this, arguments, "Yes")>
<cfset this.fields = getTableDetails()>
<cfreturn this>
</cffunction>
<!--- load a single item, pass values into the fields query --->
<cffunction name="load" access="public" returntype="boolean"
output="yes">
<cfargument name="id" type="numeric" required="yes">
<cfset var qItem = selectData(id)>
<cfloop index="Field" list="#qItem.ColumnList#">
<cfset setField(Field,
evaluate("qItem.#Field#"))>
</cfloop>
<cfreturn true>
</cffunction>
<!---
Description: Get table structure
Parameters: None
Returns: Query of the table structure
Note: Do not put the primary key at
the bottom of
the field definition of
your table.
--->
<cffunction name="getTableDetails" access="public"
returntype="query" output="no">
<cfargument name="reset" type="boolean" default="true">
<cfset var cachePeriod = this.fieldCacheDuration>
<cfif reset>
<cfobjectcache action="clear">
</cfif>
<cfif not this.Fields.recordCount>
<cfquery
name="Application.fields_#this.TableName#" datasource="#this.dsn#"
cachedwithin="#cachePeriod#">
SELECT COLUMN_NAME,
COLUMN_DEFAULT,
rtrim(ltrim(IS_NULLABLE)) as IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
dbo.IsPK('#this.TableName#', COLUMN_NAME) AS IS_PRIMARYKEY,
'' AS
CURRENT_VALUE,
CF_DATA_TYPE =
CASE
DATA_TYPE
WHEN 'bigint' THEN 'CF_SQL_BIGINT'
WHEN 'char' THEN 'CF_SQL_CHAR'
WHEN 'float' THEN 'CF_SQL_FLOAT'
WHEN 'int' THEN 'CF_SQL_INTEGER'
WHEN 'money' THEN 'CF_SQL_MONEY'
WHEN 'ntext' THEN 'CF_SQL_LONGVARCHAR'
WHEN 'numeric' THEN 'CF_SQL_NUMERIC'
WHEN 'real' THEN 'CF_SQL_REAL'
WHEN 'binary' THEN 'CF_SQL_BLOB'
WHEN 'bit' THEN 'CF_SQL_BIT'
WHEN 'datetime' THEN 'CF_SQL_TIMESTAMP'
WHEN 'decimal' THEN 'CF_SQL_DECIMAL'
WHEN 'image' THEN 'CF_SQL_BLOB'
WHEN 'nchar' THEN 'CF_SQL_CHAR'
WHEN 'nvarchar' THEN 'CF_SQL_VARCHAR'
WHEN 'smalldatetime' THEN 'CF_SQL_DATE'
WHEN 'smallint' THEN 'CF_SQL_SMALLINT'
WHEN 'smallmoney' THEN 'CF_SQL_MONEY'
WHEN 'sql_variant' THEN 'CF_SQL_VARCHAR'
WHEN 'text' THEN 'CF_SQL_LONGVARCHAR'
WHEN 'timestamp' THEN 'CF_SQL_TIMESTAMP'
WHEN 'tinyint' THEN 'CF_SQL_TINYINT'
WHEN 'uniqueidentifier' THEN 'CF_SQL_IDSTAMP'
WHEN 'varbinary' THEN 'CF_SQL_BLOB'
WHEN 'varchar' THEN 'CF_SQL_VARCHAR'
ELSE 'varchar'
END
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME =
'#this.TableName#'
ORDER BY IS_PRIMARYKEY DESC
</cfquery>
<cfset this.Fields =
duplicate(Application["fields_#this.TableName#"])>
</cfif>
<cfreturn this.Fields>
</cffunction>
<!---
Description: Gets a fields value from the Fields
query
Parameters: FieldName = Name of the field
value to get
Returns: Value of the specified field
Throws error if
requested field doesn't exist in
field query
--->
<cffunction name="getField" access="public" returntype="any"
output="no">
<cfargument name="FieldName" type="string"
required="Yes">
<cfargument name="fields" type="query"
default="#this.fields#">
<cfloop query="fields">
<cfif column_name EQ fieldName>
<cfreturn current_value>
</cfif>
</cfloop>
<cfthrow errorcode="TableWrapper::getField"
message="Current field value for (#FieldName#)
could not be retrieved."
detail="Field: '#FieldName#' does not exist in
table: '#this.TableName#'.">
</cffunction>
<cffunction name="getFieldNames" access="public"
returntype="any" output="no">
<CFSET FieldData = this.Fields>
<CFRETURN VaLueList(FieldData.Column_Name)>
</cffunction>
<cffunction name="getFieldMaxLength" access="public"
returntype="any" output="no">
<cfargument name="FieldName" type="string"
required="Yes">
<cfargument name="fields" type="query"
default="#this.fields#">
<cfloop query="fields">
<cfif column_name EQ fieldName>
<cfreturn CHARACTER_MAXIMUM_LENGTH>
</cfif>
</cfloop>
<cfthrow errorcode="TableWrapper::getField"
message="Current field value for (#FieldName#)
could not be retrieved."
detail="Field: '#FieldName#' does not exist in
table: '#this.TableName#'.">
</cffunction>
<cffunction name="getFieldIsNullable" access="public"
returntype="any" output="no">
<cfargument name="FieldName" type="string"
required="Yes">
<cfargument name="fields" type="query"
default="#this.fields#">
<cfloop query="fields">
<cfif column_name EQ fieldName>
<cfreturn Is_Nullable>
</cfif>
</cfloop>
<cfthrow errorcode="TableWrapper::getField"
message="Current field value for (#FieldName#)
could not be retrieved."
detail="Field: '#FieldName#' does not exist in
table: '#this.TableName#'.">
</cffunction>
<!---
Description: Sets a fields value in the Fields query,
validates the
passed in value with the
Fields data type
Parameters: FieldName = Name of the field to
set
FieldValue = Value to
set the field to
Returns: row number modified
--->
<cffunction name="setField" access="public" returntype="numeric"
output="no">
<cfargument name="FieldName" type="string"
required="Yes">
<cfargument name="FieldValue" type="any" required="yes">
<cfset var row = 0>
<!--- find the row with the right column_name --->
<cfloop query="this.Fields">
<cfif column_name eq arguments.fieldname>
<cfswitch expression="#DATA_TYPE#">
<cfcase
value="numeric,int,decimal,bigint,smallint,tinyint,float,real,money,smal
lmoney">
<cfif
isnumeric(arguments.fieldvalue) or (is_nullable and arguments.fieldvalue
eq "")>
<cfset row =
currentRow>
<cfbreak>
</cfif>
</cfcase>
<cfcase value="bit">
<cfif
isboolean(arguments.fieldvalue) or (is_nullable and arguments.fieldvalue
eq "")>
<cfset row =
currentRow>
<cfbreak>
</cfif>
</cfcase>
<cfcase
value="datetime,smalldatetime,timestamp">
<cfif
isdate(arguments.fieldvalue) or (is_nullable and arguments.fieldvalue eq
"")>
<cfset row =
currentRow>
<cfbreak>
</cfif>
</cfcase>
<cfdefaultcase>
<cfset row = currentRow>
<cfbreak>
</cfdefaultcase>
</cfswitch>
</cfif>
</cfloop>
<!--- set the current_value --->
<cfif row>
<cfset this.fields.current_value[row] =
arguments.fieldvalue>
</cfif>
<cfreturn row>
</cffunction>
<!---
Description: Checks to see if the current item's data
is valid.
Compares the fields
values to see if they are empty
and if the field allows
nulls
Parameters: None
Returns: TRUE if data is valid
FALSE if data is not
valid
--->
<cffunction name="isRecordValid" access="public"
returntype="boolean" output="no">
<cfset var datavalid = true>
<cfloop query="this.Fields">
<cfif not is_primarykey>
<cfif not is_nullable and
isblank(current_value)>
<cfset datavalid = false>
<cfbreak>
</cfif>
</cfif>
</cfloop>
<cfreturn datavalid>
</cffunction>
<!---
Description: Checks to see if the passed in value is
blank or not
Parameters: Value = Value to check
Returns: TRUE if value is blank
FALSE if value is not
blank
--->
<cffunction name="isBlank" access="public" returntype="boolean"
output="no">
<cfargument name="str" type="any" required="Yes">
<cfset var isvalueblank = false>
<cfif not len(trim(str))>
<cfset isvalueblank = true>
</cfif>
<cfreturn isvalueblank>
</cffunction>
<!--- public generic db methods --->
<cffunction name="save" access="public" returntype="boolean"
output="no">
<cfset var id = getField(this.itemkey)>
<cfif isnumeric(id) and id>
<cfset id = updateData(id)>
<cfelse>
<cfset id = insertData()>
</cfif>
<cfreturn id>
</cffunction>
<!--- delete the record form the database --->
<cffunction name="remove" access="public" returntype="boolean"
output="no">
<cfargument name="id" type="numeric"
default="#getField(this.itemkey)#">
<cfreturn deleteData(id)>
</cffunction>
<!--- private generic db methods --->
<cffunction name="selectData" returntype="query" output="no"
access="public">
<cfargument name="id" type="numeric" required="Yes">
<cfargument name="ColumnList" type="string"
required="No" default="*">
<cfquery name="qSelect" datasource="#this.dsn#">
SELECT #ColumnList#
FROM [#this.TableName#]
WHERE [#this.ItemKey#] = #id#
</cfquery>
<cfreturn qSelect>
</cffunction>
<cffunction name="updateData" access="private"
returntype="numeric" output="no">
<cfargument name="id" type="numeric" required="yes">
<cfquery name="qUpdate" datasource="#this.dsn#">
UPDATE [#this.TableName#]
SET modified = getdate()
<cfloop query="this.Fields">
<cfif is_primarykey eq 0 and COLUMN_NAME
neq "Modified">
, [#COLUMN_NAME#] =
<cfqueryparam cfsqltype="#CF_DATA_TYPE#" value="#CURRENT_VALUE#"
null="#isBlank(CURRENT_VALUE)#">
</cfif>
</cfloop>
WHERE [#this.ItemKey#] = #id#
</cfquery>
<cfreturn id>
</cffunction>
<cffunction name="insertData" access="private"
returntype="numeric" output="no">
<cfset var id = 0>
<cfquery name="qInsert" datasource="#this.dsn#">
INSERT INTO [#this.TableName#]
(
created
<cfloop query="this.Fields">
<cfif not is_primarykey and
len(current_value)>
, [#COLUMN_NAME#]
</cfif>
</cfloop>
)
VALUES
(
getdate()
<cfloop query="this.Fields">
<cfif not is_primarykey and
len(current_value)>
, <cfqueryparam
cfsqltype="#CF_DATA_TYPE#" value="#dataFormat(CURRENT_VALUE,
DATA_TYPE)#" null="#isBlank(CURRENT_VALUE)#">
</cfif>
</cfloop>
)
SELECT @@IDENTITY AS [#this.ItemKey#]
</cfquery>
<cfset id = qInsert[this.itemKey][1]>
<cfset setfield(this.itemKey, id)>
<cfreturn id>
</cffunction>
<cffunction name="Clear" access="public" output="no">
<cfloop query="this.Fields">
<cfset setField(Column_Name, "")>
</cfloop>
<cfreturn this>
</cffunction>
<cffunction name="dataFormat" access="private" returntype="any"
output="no">
<cfargument name="val" required="yes">
<cfargument name="type" required="yes">
<cfset var result = val>
<!--- convert stuff --->
<cfswitch expression="#type#">
<cfcase value="datetime">
<cfset result = CreateODBCDateTime(val)>
</cfcase>
<cfcase value="date">
<cfset result = CreateODBCDate(val)>
</cfcase>
</cfswitch>
<cfreturn result>
</cffunction>
<cffunction name="deleteData" access="private"
returntype="numeric" output="no">
<cfargument name="id" type="numeric" required="yes">
<cfquery name="qDelete" datasource="#this.dsn#">
delete from [#this.TableName#] WHERE
[#this.ItemKey#] = #id#
</cfquery>
<cfreturn true>
</cffunction>
<!---
Description: Updates the current items Fields with
the values in the updateFields structure.
Only fields that are
different are changed.
Parameters: upadteFields = Structure with
fields to update
Returns: TRUE if fields were changed
FALSE if no fields were
changed
--->
<cffunction name="update" access="public" output="false"
returntype="boolean">
<cfargument name="updateFields" type="struct">
<CFSET var wasModified = FALSE>
<CFTRY>
<CFLOOP QUERY="this.fields">
<CFIF StructKeyExists(updateFields,
column_name)>
<CFIF
Compare(GetField(column_name), updateFields[column_name]) NEQ 0>
<CFSET
SetField(column_name, updateFields[column_name])>
<CFSET wasModified =
TRUE>
</CFIF>
</CFIF>
</CFLOOP>
<CFCATCH>
<CFDUMP VAR="#CFCatch#">
<CFABORT>
</CFCATCH>
</CFTRY>
<cfreturn wasModified>
</cffunction>
<!---
Description: Checks to see if the current field is a
Primary Key.
Parameters: Field = Name of field to check
Returns: TRUE if Field is a Primary Key
FALSE if Field is not a
Primary Key
--->
<cffunction name="isPrimaryKey" access="public"
returntype="boolean" output="no">
<cfargument name="field" TYPE="string" required="yes">
<cfset var ispk = false>
<cfloop query="this.Fields">
<CFIF column_name EQ field>
<cfif is_primarykey>
<cfset ispk = true>
</cfif>
<cfbreak>
</cfif>
</cfloop>
<cfreturn ispk>
</cffunction>
<!--- utilities --->
<cffunction name="dataToStruct" access="public"
returntype="struct" output="no">
<cfset var fields = this.fields>
<cfset var result = structNew()>
<cfloop query="fields">
<cfset StructInsert(result, column_name,
current_value, 1)>
</cfloop>
<cfreturn result>
</cffunction>
</cfcomponent>
-----Original Message-----
From: Trevor Orr [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 02, 2005 12:01 PM
To: CF-Community
Subject: Database Table CFC
I have written a base CFC that I call tablewrapper. It is a CFC that
takes care of all database functions, EG: Insert, Delete, Update. It
automatically grabs all field information for the tabel on init. Allows
to update the current data from a form post. I just wanted to get
peoples opinions on it, either good or bad, suggestions on imporovements
or whatever. For me it is a huge time saver when making site
administrator forms. Attached is the base CFC, and the CFC it extends,
a SQL script to generate the SQL Server UDF and example files for
updating from a form. The one big draw back on this is that it is SQL
Server specific, because of primary keys.
All Comments would be greatly appreciated.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Stay Ahead of Hackers - Download ZoneAlarm Pro
http://www.houseoffusion.com/banners/view.cfm?bannerid=65
Message: http://www.houseoffusion.com/lists.cfm/link=i:5:159476
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/5
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:5
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
