There is an enhancement in the tracker to allow you to extend property definitions to use your own custom query params, with prefixes, suffixes, etc
http://tracker.transfer-orm.com/issue.cfm?p=89977683-A728-9CD3-ABD9545A91734422&i=0DCDC600-91FC-A244-C3114EB37C2EDD1C It has yet to be implemented. Mark On Wed, Nov 12, 2008 at 1:29 AM, Jamie Krug <[EMAIL PROTECTED]> wrote: > > Sorry in advance for a lengthy one... > > Using the UUID data type in PostgreSQL 8.3 (http://www.postgresql.org/ > docs/8.3/interactive/datatype-uuid.html) is problematic with > cfqueryparam. There is no explicit cfsqltype, and using cf_sql_varchar > will cause the following error when attempting a query like this: > > select * from my_table > where my_uuid=<cfqueryparam value="#my_uuid_value" > cfsqltype="cf_sql_varchar" /> > > org.postgresql.util.PSQLException: ERROR: operator does not exist: > uuid = character varying > > I stumbled upon this post, which fixes a similar issue regarding the > interval data type: > http://www.ghidinelli.com/2007/06/14/postgresql-interval-data-type-with-cfqueryparam > (Here's the important part:) > UPDATE table > SET someInterval = <cfqueryparam cfsqltype="cf_sql_varchar" > value="#interalValue#">::interval > > So I tried sticking a "::uuid" on the end of my cfqueryparam and it > works! (But don't stop reading now, I found another solution and then > another problem...) > > By the way, I should note that I'm using property type "GUID" in my > Transfer configuration file, not UUID, even though that is the > PostgreSQL data type name. The standard output format used by > PostgreSQL for UUID data type matches what is considered GUID format > in a Transfer property type. > > I couldn't find any mention of this issue on this list or noted in the > Transfer issue tracker, but please let me know if I've missed > something. > > So, I thought I had a quick fix... I believe I've identified all > relevant cfqueryparam instances in the Transfer 1.1 code (just 4 of > them, in 3 files). I saved the diff/patch for the fix if anyone cares. > It's just a conditional at the end of the cfqueryparam tags used for > Transfer property types that currently result in use of > cfsqltype="cf_sql_varchar". This made things work for simple object > definitions (no onetomany/manytoone/manytomany relationships). > > A less quirky solution to the cfqueryparam/uuid issue is to simply > download the latest PostgreSQL JDBC drivers, replace your current .jar > file and restart CF (got this idea from > http://jochem.vandieten.net/2008/02/06/postgresql-uuids-and-coldfusion-1/). > I downloaded postgresql-8.3-603.jdbc4.jar from > http://jdbc.postgresql.org/download.html, > placed it in the proper directory, renamed the existing > postgresql-8.1-407.jdbc4.jar to postgresql-8.1-407.jdbc4.jar.BAK and > restarted CF. (To find your existing postgre driver path click on the > "I" icon for System Information in CF Admin and find "postgre" on the > page -- my default install location for CF8.01 multiple server > configuration on Linux is /opt/jrun4/servers/cfusion/cfusion-ear/ > cfusion-war/WEB-INF/cfusion/lib/). I can now use cfqueryparam with > cfsqltype="cf_sql_other" for PostgreSQL UUID data type columns. I also > tried this fix in the Transfer code, but still ran into another > issue... > > Unfortunately, the above solutions only worked with simple Transfer > object definitions. As soon as I added a onetomany to a basic object > definition, I received a new error, which also appears to be a type/ > cast inconsistency issue: > > ERROR: UNION types text and integer cannot be matched > > It looks like a CAST(text as NULL) in one part of a UNION is matched > up with an actual column value of data type UUID, which throws the > above error. I haven't dug nearly deep enough into the Transfer code > to know if this is Transfer-related, or just a compatibility issue > between PostgreSQL 8.3 and ColdFusion 8.01 (or more likely, the driver > included w/CF). I've pasted details of my environment, relevant > Transfer config snippets and more details from the error output at the > bottom of this message. > > My config in this case is very much like the basic idea of a Post > object with a onetomany to Comment, as in the tBlog sample > application, only I'm using PostgreSQL 8.3 UUID data types for primary > keys instead of numeric. > > My immediate goal is to get this relatively small project moving > quickly, so I'm trying to discern whether I should try to work around > the use of UUID data types, not use UUID, not use Transfer (this is my > first real dive in and was excited until this) until I figure the UUID > stuff out, etc. To keep things moving here, I may have to leave out > Transfer, temporarily, for this app. > > Has anyone else tackled this problem? Others using PostgreSQL with > Transfer, maybe an older version? Is PostgreSQL 8.3 support in the > Transfer road map yet? Any feedback would be much appreciated. > > Thanks, > Jamie > > *Dev environment:* > > Transfer 1.1 (final) > ColdFusion 8.01, multiple server (JRun) configuration > PostgreSQL 8.3.3 > Ubuntu 8.04 > Apache 2.2 > > > *Relevant snippets from my transfer.xml:* > > <package name="page"> > <object name="Page" table="page"> > <id name="idpage" type="GUID" /> > <property name="isActive" type="boolean" > column="is_active" /> > <property name="href" type="string" column="href" /> > <property name="anchorText" type="string" > column="anchor_text" /> > <property name="anchorTitle" type="string" > column="anchor_title" /> > <onetomany name="ContentPage"> > <link to="content.ContentPage" column="idpage" /> > <collection type="array"> > <order property="IDContent_Page" order="asc" / >> > </collection> > </onetomany> > </object> > </package> > <package name="content"> > <object name="Content" table="content"> > <id name="idcontent" type="GUID" /> > <property name="content" type="string" > column="content" /> > </object> > <object name="ContentPage" table="content_page"> > <id name="idcontent_page" type="GUID" /> > <property name="title" type="string" column="title" / >> > <property name="description" type="string" > column="description" /> > <property name="keywords" type="string" > column="keywords" /> > <manytoone name="Content"> > <link to="content.Content" column="idcontent" /> > </manytoone> > </object> > <object name="ContentSnippet" table="content_snippet"> > <id name="idcontent_snippet" type="GUID" /> > <property name="friendlyID" type="string" > column="friendly_id" /> > <manytoone name="Content"> > <link to="content.Content" column="idcontent"/> > </manytoone> > </object> > </package> > > > *Test code:* > > <cfscript> > transferConfig = structNew(); > transferConfig["datasourcePath"] = "/temp/config/transfer/ > datasource.xml"; > transferConfig["configPath"] = "/temp/config/transfer/ > transfer.xml"; > transferConfig["definitionPath"] = "/temp/model/data/transfer"; > transferFactory = > createObject("component","transfer.TransferFactory").init(argumentCollection=transferConfig); > transfer = transferFactory.getTransfer(); > page = transfer.get('page.Page', '7a85e8c7-ebb7- > f962-0fe4-6043ce7e6721'); > </cfscript> > <cfdump var="#page#" /> > > > *Error output:* > > Error Executing Database Query. > ERROR: UNION types text and uuid cannot be matched > > The error occurred in /home/jkrug/www/common/transfer/com/sql/ > QueryExecution.cfc: line 82 > Called from /home/jkrug/www/common/transfer/com/sql/ > TransferSelecter.cfc: line 53 > Called from /home/jkrug/www/common/transfer/com/sql/SQLManager.cfc: > line 87 > Called from /home/jkrug/www/common/transfer/com/dynamic/ > TransferPopulator.cfc: line 50 > Called from /home/jkrug/www/common/transfer/com/dynamic/ > DynamicManager.cfc: line 72 > Called from /home/jkrug/www/common/transfer/com/Transfer.cfc: line 120 > Called from /home/jkrug/www/temp/scribble.cfm: line 10 > Called from /home/jkrug/www/common/transfer/com/sql/ > QueryExecution.cfc: line 82 > Called from /home/jkrug/www/common/transfer/com/sql/ > TransferSelecter.cfc: line 53 > Called from /home/jkrug/www/common/transfer/com/sql/SQLManager.cfc: > line 87 > Called from /home/jkrug/www/common/transfer/com/dynamic/ > TransferPopulator.cfc: line 50 > Called from /home/jkrug/www/common/transfer/com/dynamic/ > DynamicManager.cfc: line 72 > Called from /home/jkrug/www/common/transfer/com/Transfer.cfc: line 120 > Called from /home/jkrug/www/temp/scribble.cfm: line 10 > > 80 : <cfquery name="queryValue" > datasource="#getDataSource().getName()#" > username="#getDataSource().getUsername()#" > password="#getDataSource().getPassword()#"> > 81 : <cfset eLen = ArrayLen(evaluation) /> > 82 : <cfloop from="1" to="#eLen#" index="eCounter"> > 83 : <cfset block = evaluation[eCounter] /> > 84 : #PreserveSingleQuotes(block.preSQL)# > > SQLSTATE 42804 > SQL SELECT page_1.href, page_1.idpage, Cast(NULL as text) as > idcontent_page, Cast(NULL as text) as content, Cast(NULL as text) as > description, Cast(NULL as text) as idcontent, Cast(NULL as text) as > title, page_1.is_active, Cast(NULL as text) as keywords, > page_1.anchor_title, page_1.anchor_text, Cast(NULL as text) as > transfer_parentKey, 1 as transfer_orderIndex,'page.Page' as > transfer_className,'' as transfer_parentClassName,'' as > transfer_parentParentClassName,'' as > transfer_parentCompositeName,'false' as transfer_isArray,'' as > transfer_compositeName, 'false' as transfer_isProxied FROM page page_1 > WHERE page_1.idpage = (param 1) AND page_1.idpage IS NOT NULL UNION > ALL SELECT Cast(NULL as text) as href, content_page_2.idpage, > content_page_2.idcontent_page, Cast(NULL as text) as content, > content_page_2.description, Cast(NULL as text) as idcontent, > content_page_2.title, Cast(NULL as boolean) as is_active, > content_page_2.keywords, Cast(NULL as text) as anchor_title, Cast(NULL > as text) as anchor_text, CAST(page_1.idpage as varchar(1000)) as > transfer_parentKey, 2 as transfer_orderIndex,'content.ContentPage' as > transfer_className,'page.Page' as transfer_parentClassName,'' as > transfer_parentParentClassName,'' as > transfer_parentCompositeName,'true' as transfer_isArray,'ContentPage' > as transfer_compositeName, 'false' as transfer_isProxied FROM page > page_1 INNER JOIN content_page content_page_2 ON page_1.idpage = > content_page_2.idpage WHERE page_1.idpage = (param 2) AND > content_page_2.idcontent_page IS NOT NULL UNION ALL SELECT Cast(NULL > as text) as href, Cast(NULL as text) as idpage, Cast(NULL as text) as > idcontent_page, content_3.content, Cast(NULL as text) as description, > content_3.idcontent, Cast(NULL as text) as title, Cast(NULL as > boolean) as is_active, Cast(NULL as text) as keywords, Cast(NULL as > text) as anchor_title, Cast(NULL as text) as anchor_text, > CAST(content_page_2.idcontent_page as varchar(1000)) as > transfer_parentKey, 3 as transfer_orderIndex,'content.Content' as > transfer_className,'content.ContentPage' as > transfer_parentClassName,'page.Page' as > transfer_parentParentClassName,'ContentPage' as > transfer_parentCompositeName,'false' as transfer_isArray,'Content' as > transfer_compositeName, 'false' as transfer_isProxied FROM page page_1 > INNER JOIN content_page content_page_2 ON page_1.idpage = > content_page_2.idpage INNER JOIN content content_3 ON > content_page_2.idcontent = content_3.idcontent WHERE page_1.idpage = > (param 3) AND content_3.idcontent IS NOT NULL ORDER BY > transfer_orderIndex ASC, idcontent_page asc > > > > -- E: [EMAIL PROTECTED] W: www.compoundtheory.com --~--~---------~--~----~------------~-------~--~----~ Before posting questions to the group please read: http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer You received this message because you are subscribed to the Google Groups "transfer-dev" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/transfer-dev?hl=en -~----------~----~----~----~------~----~------~--~---
