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
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
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?
> 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
'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?
On Thu, Nov 20, 2008 at 4:45 AM, Chris Blackwell <ch...@team193.com> 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
> 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?
>> 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
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 ;-)
> '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 <ch...@team193.com>
> 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
> > 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?
> > 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 <ch...@team193.com>
> >> 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
> >> 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
> 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 ;-)
>> '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 <ch...@team193.com>
>> 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
>> > 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?
>> > 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 <ch...@team193.com>
>> >> 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
>> >> 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
Fortunately this db schema is not of my design and unfortunately i'm not
able to change it.
Most applications have a User table, if those users are organized then there
may be a Group table, and if theres any kind of ecommerce or whatever going
on then it might have a Order table. How would you name these so as not to
use reserved words?
> Not sure why you would want to allow or continue to use badly formed sql.
I don't consider SELECT `UserId` FROM `User` to be badly formed sql. when
i'm writing sql i would enclose every table and column in back ticks whether
thay are reserved words or not - because then they change color in my sql
ide and that makes it look nice! :D
I've used all those words above as table names in the past (now you hate me
don't you ;-)) because they are accurate description of what they are. Just
because the dbms says its a reserved word doesn't mean you should not use it
- the dbms provides you with a way of escaping them for precisely this
reason.
If you use MySQL server you will know that the mysql db actually has a table
called "user"
I'm not spoiling for a fight, just curious for your opinion on this
Cheers, Chris
2008/11/19 Stephen Moretti <stephen.more...@gmail.com>
I prefer Users, Groups, Orders as my db tables .... avoids the reserved words problem .... I have an Order class and object though but my db tables are plural.
I'm with Stephen on this. Reserved words as table and column names annoy me too
Alan
________________________________
From: Chris Blackwell <ch...@team193.com>
To: transfer-dev@googlegroups.com
Sent: Wednesday, November 19, 2008 11:09:20 PM
Subject: [transfer-dev] Re: Escaping reserved column names
Hi Stephen,
Fortunately this db schema is not of my design and unfortunately i'm not able to change it.
Most applications have a User table, if those users are organized then there may be a Group table, and if theres any kind of ecommerce or whatever going on then it might have a Order table. How would you name these so as not to use reserved words?
> Not sure why you would want to allow or continue to use badly formed sql.
I don't consider SELECT `UserId` FROM `User` to be badly formed sql. when i'm writing sql i would enclose every table and column in back ticks whether thay are reserved words or not - because then they change color in my sql ide and that makes it look nice! :D
I've used all those words above as table names in the past (now you hate me don't you ;-)) because they are accurate description of what they are. Just because the dbms says its a reserved word doesn't mean you should not use it - the dbms provides you with a way of escaping them for precisely this reason.
If you use MySQL server you will know that the mysql db actually has a table called "user"
I'm not spoiling for a fight, just curious for your opinion on this
Cheers, Chris
2008/11/19 Stephen Moretti <stephen.more...@gmail.com>
I settled on a singular naming convention a long time ago and i'm probably
too stubborn to change that, but i will keep this in mind if i get the
chance to use transfer on any new projects.
Changing User to Users i can probably deal with that
but Category to Categories aww man that would drive me nuts, its worse that
4 spaces rather than a tab!
I am ever so slightly OCD when it comes to these sort of issues, and its
getting worse with age!
> I prefer Users, Groups, Orders as my db tables .... avoids the reserved
> words problem .... I have an Order class and object though but my db tables
> are plural.
> I'm with Stephen on this. Reserved words as table and column names annoy me
> too
> Fortunately this db schema is not of my design and unfortunately i'm not
> able to change it.
> Most applications have a User table, if those users are organized then
> there may be a Group table, and if theres any kind of ecommerce or whatever
> going on then it might have a Order table. How would you name these so as
> not to use reserved words?
> > Not sure why you would want to allow or continue to use badly formed sql.
> I don't consider SELECT `UserId` FROM `User` to be badly formed sql. when
> i'm writing sql i would enclose every table and column in back ticks whether
> thay are reserved words or not - because then they change color in my sql
> ide and that makes it look nice! :D
> I've used all those words above as table names in the past (now you hate me
> don't you ;-)) because they are accurate description of what they are. Just
> because the dbms says its a reserved word doesn't mean you should not use it
> - the dbms provides you with a way of escaping them for precisely this
> reason.
> If you use MySQL server you will know that the mysql db actually has a
> table called "user"
> I'm not spoiling for a fight, just curious for your opinion on this
> Cheers, Chris
> 2008/11/19 Stephen Moretti <stephen.more...@gmail.com>
> I prefer Users, Groups, Orders as my db tables .... avoids the reserved > words problem .... I have an Order class and object though but my db tables > are plural.
> I'm with Stephen on this. Reserved words as table and column names annoy me > too
This is pretty much what I do if I have to, but I try to be even more descriptive. Groups are usually UserGroup(s), ProductGroup(s) etc - pluralised depending on which way the wind is blowing. Its often the case that I have multiple groupings in an application and they shouldn't necessarily live in the same table. 5th normal form and all that....
Another method that I've used in the past is prefixes : tblUser, tblGroup, lnkUserGroup (lnk being "link" and o2m/m2o relationship table between User and Group), vSomeView, spSomeStoredProcedure.
At the minute I'm working with a internal framework that has a lot of tables for various functions. These all have their own prefix. The rule of thumb for custom client tables is to prefix them with a client code eg. Team193 would probably be t193_mytable.
Prefixes do have the benefit of helping to group your tables in a fashion that is useful to the database developer, but aren't always appropriate in the code and can make your code a bit unreadable, so this is why I like aliases in Transfer. :D I tend to hide the prefixes via the package/object set up, because when you start looking at data objects you don't really need to know that its tbl or a lnk and I can get rid of the client prefix I know this is client X's application - that's all being dealt with by transfer.
Regarding pluralising - I do tend towards not pluralising, but once in a while one slips through the net. I think Orders is one of those.
I think I could probably keep writing all sorts of stuff, but that'll do for the minute. Basically, just say NO! to using reserved words for database tables and columns! ;)
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.
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
> > 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 <mark.man...@gmail.com>
> >> '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 <ch...@team193.com>
> >> 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
> >> > 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?
> >> > 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 <ch...@team193.com>
> >> >> 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
> >> >> 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
On Sun, Nov 23, 2008 at 5:16 AM, Chris Blackwell <ch...@team193.com> 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.
> 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
>> > 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 <mark.man...@gmail.com>
>> >> '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 <ch...@team193.com>
>> >> 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
>> >> > 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?
>> >> > 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 <ch...@team193.com>
>> >> >> 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
>> >> >> 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
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.
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 <ch...@team193.com> 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.
> 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
>> > 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 <mark.man...@gmail.com>
>> >> '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 <ch...@team193.com>
>> >> 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
>> >> > 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?
>> >> > 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 <ch...@team193.com>
>> >> >> 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
>> >> >> 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
> 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:transfer-dev@googlegroups.com]
> On Behalf Of Mark Mandel
> Sent: 23 November 2008 06:24
> To: transfer-dev@googlegroups.com
> Subject: [transfer-dev] Re: Escaping reserved column names
> 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 <ch...@team193.com> 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.
>> 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
>>> > 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 <mark.man...@gmail.com>
>>> >> '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 <ch...@team193.com>
>>> >> 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
>>> >> > 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?
>>> >> > 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 <ch...@team193.com>
>>> >> >> 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
>>> >> >> 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
On Behalf Of Mark Mandel
Sent: 24 November 2008 21:21
To: transfer-dev@googlegroups.com
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
<ke...@objectiveinternet.com> 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: transfer-dev@googlegroups.com [mailto:transfer-dev@googlegroups.com]
> 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:
> 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 <ch...@team193.com>
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.
>> 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
>>> > 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 <mark.man...@gmail.com>
>>> >> '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 <ch...@team193.com>
>>> >> 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
>>> >> > 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?
>>> >> > 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 <ch...@team193.com>
>>> >> >> 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
>>> >> >> 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
|
