How long is a piece of String? ;) Mark
On Wed, Nov 12, 2008 at 8:45 AM, Jamie Krug <[EMAIL PROTECTED]> wrote: > > 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 ยป > > > -- 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 -~----------~----~----~----~------~----~------~--~---
