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 --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
