Escaping reserved column names

[Chris Blackwell]

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

[Chris Blackwell]

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 <ch...@team193.com>

[Mark Mandel]

'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

--
E: mark....@gmail.com
W: www.compoundtheory.com

[Chris Blackwell]

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 <mark....@gmail.com>

[Mark Mandel]

Ah yeah.. that's not going to work.

It is a limitation of Transfer.

Mark

[Stephen Moretti]

2008/11/19 Mark Mandel <mark....@gmail.com>

Ah yeah.. that's not going to work.

It is a limitation of Transfer.

Probably quite a good limitation.  
Not sure why you would want to allow or continue to use badly formed sql.

To be honest, if you're building something new and have the access to change the database schema, I would get it changed. 

Sorry grumpy pet-peeve of mine is using reserved words in database columns or in general. Makes my blood boil.....

Stephen

[Chris Blackwell]

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...@gmail.com>

[Alan Livie]

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: transf...@googlegroups.com
Sent: Wednesday, November 19, 2008 11:09:20 PM
Subject: [transfer-dev] Re: Escaping reserved column names

[Chris Blackwell]

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!

Cheers, Chris

2008/11/20 Alan Livie <alan...@yahoo.com>

[Stephen Moretti]

2008/11/20 Alan Livie <alan...@yahoo.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! ;)

Regards

Stephen

[Chris Blackwell]

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

[Mark Mandel]

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-ABD9545A91734422&i=0D079B94-C0FA-6455-DF8C9502C74BB3A7

Honestly, there hasn't been that much demand for it, so it never been
a huge priority.

Mark

[Kevin Roche]

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: transf...@googlegroups.com [mailto:transf...@googlegroups.com]
On Behalf Of Mark Mandel
Sent: 23 November 2008 06:24
To: transf...@googlegroups.com
Subject: [transfer-dev] Re: Escaping reserved column names

[Mark Mandel]

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

[Kevin Roche]

Mark,

That sounds good to me, because I wouldn't have to remember to do it in
every TQL statement.

Kevin

-----Original Message-----
From: transf...@googlegroups.com [mailto:transf...@googlegroups.com]
On Behalf Of Mark Mandel
Sent: 24 November 2008 21:21
To: transf...@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:
>