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