AW: RE: Column names with spaces

Hi,

I found now another workaround for me. I've changed the SQLBuilder and added the the method getTableName() a new parameter "boolean allowSpaces". If this parameter is true, then the inner method removeQualifiers() doesn't check for spaces inside the given column name.

Additionally I added a new parameter/field to Criteria to set this value per Criteria. The SQLBuilder.processSelectColumns() method takes the field and calls the getTableName() with it. Then I create a new Criteria and set criteria.setAllowSpaces(true).


The only other part that I have to setup is to change the problematic constants in generated Base...Peer classes. For example change the String from "tableName.column name" to "tableName.[column name]". I would make a new "escape character" for this in the config or XML template. Maybe it is enough to change the XML name for the column.

  <column name="[column name]" javaName="columnName" type="INTEGER"/>

But actually I have to edit the generated classes in both methods because the constant names for the column names is always like the column name with a space. Hmm, but for this I could also make a patch to remove all non-Java-characters (umlauts, spaces and brackets).

If you want a patch for the first part, please let me know.

bye
Thoralf




> -----Ursprüngliche Nachricht-----
> Von: Greg Monroe [mailto:[email protected]]
> Gesendet: Freitag, 11. April 2008 15:16
> An: Apache Torque Users List
> Betreff: RE: Column names with spaces
>
> OK, short version:  If you can't change the existing schema,
> use a view to get valid names.
>
> Longer version:
>
> Actually, the "[" syntax is not the only one that can be used
> with MS SQL. It also supports the SQL Standard defined
> delimited identifiers.  In the standard, normal identifiers
> (e.g. column/table names) have the same rules as Java/C
> variables. (with the exception that they are case insensitive...)
>
> If you want/need to use characters outside of this set, you
> need to use delimited identifiers.  These have the Standard
> defined syntax of "My Table!" (double quote's included).
> Delimited identifiers ARE case sensitive.
> So, the standards version id of a column in your table would
> be written like: "Time after".time_col
>
> AFAIK, this syntax is pretty widely supported by the major DB players.
>
> As Thomas F said, getting Torque to recognize and map the
> Java names to the real names is going to be a hard thing.
>
> One thing that *might* work out of the box is the various ant
> tasks.  If you define your names in the XML to include the
> double quotes, the SQL generated by these should work.
>
> To be complete, I guess you could also try to generate the
> code with the delimited cases and then "fix up" the classes
> that don't compile.  You'd also have to make sure the Map
> classes used the correct strings as well.  Not very
> maintainable but...
>
>
> > -----Original Message-----
> > From: Thoralf Rickert [mailto:[email protected]]
> > Sent: Friday, April 11, 2008 8:39 AM
> > To: 'Apache Torque Users List'
> > Subject: Column names with spaces
> >
> > Hi,
> >
> > Of course the list of problems with mssql doesn't stop...
> >
> > I've got now a xml schema from the existing database. Some "lovely
> boy"
> > decided years ago that it would be nice to use spaces inside the
> column
> > names of the database scheme. For example: "Time after".
> This is not a
> > problem if you use the special syntax "[Time after]" in a
> select. But
> I
> > don't have this kind of access with Torque (prove me wrong, if not).
> If
> > I try to make a Criteria, Torque quits this selection with
> >
> > Malformed column name in Criteria getTableName: 'T_Stammdaten.Time
> > after' is not of the form 'table.column'
> >
> > Okay, actually this is an error that doesn't have something
> to do with
> > the real problem. Is there a way to setup a database
> specific syntax
> > for column names. So in MySQL it would be "`" or in MSSQL/Sybase it
> > would be "[" and "]".
> >
> > Additionally - I think the "torque" task that generates the classes
> > should escape the spaces if it uses the database column names to
> > generate column constants (currently it produces
> >
> > public static String TIME AFTER = "...".
> >
> > Has somebody an idea, how to get around this problem?
> >
> >
> > Bye
> > Thoralf
> DukeCE Privacy Statement:
> Please be advised that this e-mail and any files transmitted
> with it are confidential communication or may otherwise be
> privileged or confidential and are intended solely for the
> individual or entity to whom they are addressed. If you are
> not the intended recipient you may not rely on the contents
> of this email or any attachments, and we ask that you please
> not read, copy or retransmit this communication, but reply to
> the sender and destroy the email, its contents, and all
> copies thereof immediately. Any unauthorized dissemination,
> distribution or copying of this communication is strictly prohibited.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>