Hi all,
This is my first email to the list, I think :)
I would like to start with a suggestion, I'm currently working on the
huge application using MD:Unity on Oracle, and what I'm facing is a luck
of validation for unique keys. Basically when I submit a value and the
value is already in the table, oracle throwing "Unique constrain
violation" error.
I've taken a good look into ObjectDao.cfc and would like to propose the
following oracle query to update oracle "readFields" method.
Below is updated cffunction. Basically I'm adding column "uniqueKey",
which identifies uniqueness of the key in the table. Please notice it
will only take a single key, not a compound ones. With that, changes
also required in object.cfc and metadata.project.xsl. You will also
notice other modifications to the query.
I'm not sure if this can be done on MSSQL, MySQL and others, but it is a
very useful addition to me.
Doug, others...thoughts?
__________________________________________
<cffunction name="readFields" access="private" hint="I populate
the table with fields." output="false" returntype="void">
<cfargument name="Object" hint="I am the object to read
fields into." required="yes" type="any" />
<cfset var qFields = 0 />
<cfset var Field = 0 />
<cfquery name="qFields" datasource="#getDsn()#"
username="#getUsername()#" password="#getPassword()#">
SELECT COL.COLUMN_NAME AS NAME,
CASE
WHEN PRIMARYCONSTRAINTS.COLUMN_NAME IS
NULL THEN
'false'
ELSE
'true'
END AS PRIMARYKEY,
/* Oracle has no equivalent to autoincrement
or identity */
'false' AS "IDENTITY",
CASE
WHEN COL.NULLABLE = 'Y' THEN
'true'
ELSE
'false'
END AS NULLABLE,
COL.DATA_TYPE AS DBDATATYPE,
CASE
/* 26 is the length of now() in ColdFusion
(i.e. {ts '2006-06-26 13:10:14'})*/
WHEN COL.DATA_TYPE = 'DATE' THEN
32
WHEN COL.DATA_TYPE = 'NUMBER' THEN
COL.DATA_PRECISION
WHEN COL.DATA_TYPE = 'VARCHAR2' THEN
COL.CHAR_LENGTH
ELSE
COL.DATA_LENGTH
END AS LENGTH,
COL.DATA_DEFAULT AS "DEFAULT",
CASE
WHEN UNIQUECONSTRAINTS.COLUMN_NAME IS
NULL THEN
'false'
ELSE
'true'
END AS UNIQUEKEY
FROM ALL_TAB_COLUMNS COL,
(SELECT COLCON.COLUMN_NAME, COLCON.TABLE_NAME
FROM ALL_CONS_COLUMNS COLCON,
ALL_CONSTRAINTS TABCON
WHERE TABCON.TABLE_NAME = <cfqueryparam
cfsqltype="cf_sql_varchar" maxlength="128"
value="#arguments.Object.getName()#" />
AND COLCON.CONSTRAINT_NAME =
TABCON.CONSTRAINT_NAME
AND COLCON.TABLE_NAME =
TABCON.TABLE_NAME
AND 'P' = TABCON.CONSTRAINT_TYPE)
PRIMARYCONSTRAINTS,
(SELECT COLCON.COLUMN_NAME, COLCON.TABLE_NAME
FROM ALL_CONS_COLUMNS COLCON,
ALL_CONSTRAINTS TABCON
WHERE TABCON.TABLE_NAME = <cfqueryparam
cfsqltype="cf_sql_varchar" maxlength="128"
value="#arguments.Object.getName()#" />
AND COLCON.CONSTRAINT_NAME =
TABCON.CONSTRAINT_NAME
AND COLCON.TABLE_NAME =
TABCON.TABLE_NAME
AND 'U' = TABCON.CONSTRAINT_TYPE
AND POSITION = 1) UNIQUECONSTRAINTS
WHERE COL.TABLE_NAME = <cfqueryparam
cfsqltype="cf_sql_varchar" maxlength="128"
value="#arguments.Object.getName()#" />
AND COL.COLUMN_NAME =
PRIMARYCONSTRAINTS.COLUMN_NAME(+)
AND COL.TABLE_NAME =
PRIMARYCONSTRAINTS.TABLE_NAME(+)
AND COL.COLUMN_NAME =
UNIQUECONSTRAINTS.COLUMN_NAME(+)
AND COL.TABLE_NAME =
UNIQUECONSTRAINTS.TABLE_NAME(+)
ORDER BY COL.COLUMN_ID
</cfquery>
<cfloop query="qFields">
<!--- create the field --->
<cfset Field = StructNew() />
<cfset Field.name = qFields.name />
<cfset Field.primaryKey = qFields.primaryKey
/>
<cfset Field.uniqueKey = qFields.uniqueKey />
<cfset Field.identity = qFields.identity />
<cfset Field.nullable = qFields.nullable />
<cfset Field.dbDataType = qFields.dbDataType
/>
<cfset Field.cfDataType =
getCfDataType(qFields.dbDataType) />
<cfset Field.cfSqlType =
getCfSqlType(qFields.dbDataType) />
<cfset Field.length = qFields.length />
<cfset Field.default =
getDefault(qFields.default, Field.cfDataType, Field.nullable) />
<cfset Field.sequenceName = "" />
<!--- add the field to the table --->
<cfset arguments.Object.addField(Field) />
</cfloop>
</cffunction>
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Reactor for ColdFusion Mailing List
[email protected]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
