Mark, That sounds good to me, because I wouldn't have to remember to do it in every TQL statement.
Kevin -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Mark Mandel Sent: 24 November 2008 21:21 To: [email protected] Subject: [transfer-dev] Re: Escaping reserved column names I think the easiest way of doing this is being able to add escape characters to the transfer.xml, and have them work. Trying to do this automatically at this stage, could well be problematic. Mark On Mon, Nov 24, 2008 at 8:44 PM, Kevin Roche <[EMAIL PROTECTED]> wrote: > > I would vote for this one. I have tables in MSSQL that have tables called > Section, User, and Role. So I need to add [] around those names and some of > the column names too. > > It seem like Without it, I will be unable to use Transfer in my application, > unless I rename all the tables. > > Kevin Roche > > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] > On Behalf Of Mark Mandel > Sent: 23 November 2008 06:24 > To: [email protected] > Subject: [transfer-dev] Re: Escaping reserved column names > > > Its not crazy Chris, and it has come up as an enhancement ticket previously: > http://tracker.transfer-orm.com/issue.cfm?p=89977683-A728-9CD3-ABD9545A91734 > 422&i=0D079B94-C0FA-6455-DF8C9502C74BB3A7 > > Honestly, there hasn't been that much demand for it, so it never been > a huge priority. > > Mark > > > > On Sun, Nov 23, 2008 at 5:16 AM, Chris Blackwell <[EMAIL PROTECTED]> wrote: >> Arrghh.. I'm afraid my reserved word nightmare continues ... >> >> I have a table called Contact which has a column named Primary - ooh > lovely >> i hear you all say :) >> I used backticks around the column name and aliased the column to >> "isPrimary" to get transfer working. >> >> <object name="Contact" table="customer_contacts"> >> <id name="ID" type="numeric" /> >> <property name="isPrimary" type="numeric" > column="`Primary`" >> /> >> .... >> </object> >> >> However if i try to retrieve an object for which i know Primary = 1, it is >> always zero. Also if i place backticks around another column name, one > which >> is NOT a reserved word that property will be returned as zero / empty > string >> so something inside transfer is "loosing" the value. >> >> I'm very new to transfer so this has been a little voyage into the depths, >> and i might be way off but here's what i've found. >> >> transfer.com.sql.SQLValue.getPropertyColumnValue() uses the column name >> returned by com.transfer.object.Property.getColumn() to retrieve the value >> from the query object - as this will always be wrapped in back ticks it >> returns null, and getPropertyColumnValue() then assumes the value should > be >> null and substitutes an empty string or zero accordingly >> >> Property.getColumn() would need to return the column name with back ticks >> for queries to work correctly (i'm assuming), so for now i've chucked a > hack >> in SQLValue.getPropertyColumnValue() to remove the back ticks before > calling >> query.getString(). But i'm sure theres probably a better way to handle >> this? >> >> going slightly off-topic, would it not be possible for transfer to escape >> the table and column names as it creates the SQL. I see theres already > dbms >> specific SQL factories could they not have methods for wrapping column > names >> in escape characters? If that were the case then this issue would go away >> entirely, and having to enter table/column names in the xml wrapped in >> escape characters which is ugly would be unnecessary. Sorry if this is >> controversial and/or been covered before >> >> Cheers, Chris >> >> >> >> 2008/11/19 Mark Mandel <[EMAIL PROTECTED]> >>> >>> Ah yeah.. that's not going to work. >>> >>> It is a limitation of Transfer. >>> >>> Mark >>> >>> On Thu, Nov 20, 2008 at 8:19 AM, Chris Blackwell <[EMAIL PROTECTED]> >>> wrote: >>> > It has sort of worked for me, this property works fine >>> > >>> > <property name="OrderNumber" type="string" column="`Order`" /> >>> > >>> > however what you can't do is >>> > >>> > <property name="Order" type="string" column="`Order`" /> >>> > >>> > because transfer will generate sql like >>> > >>> > SELECT `Order` AS Order FROM ... >>> > >>> > I'm coming to transfer after a while working with reactor which escapes >>> > every column and table name using dbms specific syntax, so maybe i've >>> > been a >>> > bit spoiled ;-) >>> > >>> > Cheers, Chris >>> > >>> > 2008/11/19 Mark Mandel <[EMAIL PROTECTED]> >>> >> >>> >> 'Order' is just a reserved word in SQL, so it will throw an error when >>> >> you attempt to use it without escaping it. >>> >> >>> >> By changing the XSD to allow back ticks (should be a small change), >>> >> I'm surprised it didn't work out for you. Did you recreate Transfer >>> >> between attempts, to ensure the meta data was refreshed? >>> >> >>> >> Mark >>> >> >>> >> On Thu, Nov 20, 2008 at 4:45 AM, Chris Blackwell <[EMAIL PROTECTED]> >>> >> wrote: >>> >> > I've just tried editing the attribute definition for column in the >>> >> > xsd >>> >> > to >>> >> > allow the backtick character, which allows me to use the following > in >>> >> > my >>> >> > object >>> >> > >>> >> > <property name="Order" type="string" > column="`Order`" >>> >> > /> >>> >> > >>> >> > but when i try and do a list() on the table i get an error because >>> >> > the >>> >> > generated SQL doesn't include my backticks >>> >> > >>> >> > You have an error in your SQL syntax.. near 'Order, ID > from >>> >> > >>> >> > However if i change the column name to be something other than Order >>> >> > it >>> >> > works ok. Other columns don't seem to have this issue, is transfer >>> >> > looking >>> >> > for reserved words and treating them specially? >>> >> > >>> >> > <property name="Surname" type="string" >>> >> > column="`Surname`" /> >>> >> > <property name="OrderNumber" type="string" >>> >> > column="`Order`" >>> >> > /> >>> >> > >>> >> > generates this SQL >>> >> > >>> >> > select `Surname` as Surname, `Order` as OrderNumber, ID from ... >>> >> > >>> >> > I'm about 2 hours into using transfer so i've no idea whats going on >>> >> > here :) >>> >> > >>> >> > Chris >>> >> > >>> >> > 2008/11/19 Chris Blackwell <[EMAIL PROTECTED]> >>> >> >> >>> >> >> Hi all, >>> >> >> >>> >> >> My clients db has a column "Order", which i need to use. >>> >> >> I searched the list archive and found a thread about escaping >>> >> >> reserved >>> >> >> table names >>> >> >> >>> >> >> >>> >> >> >>> >> >> > http://groups.google.com/group/transfer-dev/browse_thread/thread/d3fe1499046 > 4d26b/c18097e57e9ded11?lnk=gst&q=escape#c18097e57e9ded11 >>> >> >> >>> >> >> It looks like this is done by including square brackets in the > table >>> >> >> definition. So i have 2 problems, this doesn't seem to work for >>> >> >> <property column="" />, and secondly i'm using MySQL so it needs to >>> >> >> be >>> >> >> a backtick ` >>> >> >> >>> >> >> I'm guessing this may have come up before, so hoping someone can >>> >> >> point >>> >> >> me in the right direction >>> >> >> >>> >> >> Cheers, Chris >>> >> >> >>> >> > >>> >> > >>> >> > > >>> >> > >>> >> >>> >> >>> >> >>> >> -- >>> >> E: [EMAIL PROTECTED] >>> >> W: www.compoundtheory.com >>> >> >>> >> >>> > >>> > >>> > > >>> > >>> >>> >>> >>> -- >>> E: [EMAIL PROTECTED] >>> W: www.compoundtheory.com >>> >>> >> >> >> > >> > > > > -- > E: [EMAIL PROTECTED] > W: www.compoundtheory.com > > > > > > > -- 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 -~----------~----~----~----~------~----~------~--~---
