Okay, thanks, Mark. I'll hold off for now, but if I have some time, I may try to hack just a little further to get things working w/ PostgreSQL 8.3. I'm just concerned that after I address the current error, I'll hit another, and then another... Not sure how long of a thread I'd be pulling! Can you give me your best guess as to how much digging I may have to do to ensure the PostgreSQL UUID data type doesn't make my Transfer implementation error out?
Thanks! Jamie On Nov 11, 4:38 pm, "Mark Mandel" <[EMAIL PROTECTED]> wrote: > 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-ABD954... > > 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-wi... > > (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-coldfusio...). > > I downloaded postgresql-8.3-603.jdbc4.jar > > fromhttp://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 > > ... > > read more » --~--~---------~--~----~------------~-------~--~----~ 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 transfer-dev@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
