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: transfer-dev@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Mark Mandel Sent: 23 November 2008 06:24 To: transfer-dev@googlegroups.com 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 --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
