[Opensource] Using DBObjects on tables that have keywords as field names

[Dcomeau at xenos.com]

Thanks Mike,

It wasn't actually the isReservedWord() test that was giving me problems
(yet, my schema is not up to date), it was SQL Server complaining that I was
using a keyword in my select statement (as a field name) that wasn't wrapped
in double quotes.  

So I actually went ahead and modified Expresso slightly since my attempts to
get the tables changed didn't work and I needed to keep moving :).  I
created my own class that can be instantiated with a "definition" of what
needs to be done for a paticiular database.  The definition includes the
list of reserved words (either the full list or just a subset of the ones
causing conflicts for a paticular app) and also the left and right "wrap"
characters needed by that database.  I think SQL Server either lets you wrap
reserved words with double quotes, or with [ and ].  I think Oracle only
lets you use double quotes.

So the changes I made were in DBObject, only about 6 one line changes in all
(I didn't worry about table names, just field names for now, so that might
make it 10 changes or so)

Basically lines like this inside code that assembles
SELECT/UPDATE/INSERT/DELETE statements:

  sqlCommand.append(oneField.getName());

became:

 
sqlCommand.append(DbReservedWordUtil.verifySafetyViaInstance(oneField.getNam
e()));

I just whipped it together so I didn't bother setting up my
"DbReservedWordUtil" to work with multiple contexts.  But it easily could
be, and it's configuration could be setup in the expresso-config.xml
file...sorta along the same lines as what Mike Rimov suggested in this
lonely thread:

http://mail.jcorporate.com/pipermail/opensource/2000-July/007436.html

I think this solution works well, is there interest in incorporating
something like this into the Expresso code base?  It is definetly a handy
feature to have, most importantly for folks who have to work with an
existing database design that can't be modified.

Regards,
Dave.




-----Original Message-----
From: Mike Traum [mailto:mtraum at cirnetwork.org]
Sent: Wednesday, March 05, 2003 6:50 PM
To: 'opensource at jcorporate.com'
Subject: RE: [Opensource] Using DBObjects on tables that have keywords
as field names


Not sure, but it doesn't look like it's possible without modifying expresso.
You need to overload DBObjectDef.addField(), but allFields is private.

A quick fix that *might* (it looks like this is done to enforce
cross-compatibility and expresso doesn't really need it, but I haven't
investigated thoroughly) work would be to remove the isReservedWord test in
DBObjectDef.addField(). Generally, modifying Expresso yourself can be a bad
idea because you then get locked into the current version of expresso
without duplicating these changes in an upgrade. In this case, the changes
are simple enough that I'd go for it.

If you try the above and it works, let me know. I'll add a method to
DBObjectDef which won't check the reserved words. 

You could, of course, drop DBObject and just use the connection pool to grab
a connection directly, but I'm sure that's not what you wanted to hear.

mike

-----Original Message-----
From: Dcomeau at xenos.com [mailto:Dcomeau at xenos.com]
Sent: Tuesday, March 04, 2003 7:30 PM
To: opensource at jcorporate.com
Subject: [Opensource] Using DBObjects on tables that have keywords as
field names




Hi All,

The Expresso app I'm writing is a component of suite of "executables" where
my Expresso app writes to the database tables and other components read from
it to perform business logic.

I have very little control over the format of the database as it was handed
to me along with the requirements for my component.  And for some reason the
guys who built the tables decided to use SQL Server keywords for some of
their field names (ex: Column, Position, Section, Value).

And I just discovered that this is not doesn't go over too well when using
DBObjects.


I read this thread:

  http://mail.jcorporate.com/pipermail/opensource/2001-November/009413.html

...but is there a way I can get around this with some Expresso parameters
rather than fighting to get the fields changed?  

I'm way outnumbered and they've been plagued with tons of database changes
already as it is...they are writing a conversion utility to import data for
clients running a previous version of the product and are forced to modify
it each time there is a table change.

I have my fingers crossed but something tells me I'm not gonna like what I
hear! :P


Cheers
Dave