History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: CASTOR-953
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Werner Guttmann
Reporter: francisco.castaneda
Votes: 0
Watchers: 0

If you were logged in you would be able to see more operations.

Sequence id generation produces an incorrect sql statement

Created: 06/Dec/04 03:37 PM   Updated: 19/Apr/05 10:53 AM
Component/s: JDO
Affects Version/s:
Fix Version/s: None

Time Tracking:
Not Specified

File Attachments: 1. Text File castor-1816.patch (1 kb)
2. Text File patch.txt (2 kb)

Operating System: Windows XP
Platform: PC

Bugzilla Id: 1816

 Description  « Hide
Sequence id generation produces an incorrect sql statement

I have the following sequence generator:

<key-generator name="SEQUENCE" alias="secuencias_basicas">
<param name="trigger" value="false"/>
<param name="returning" value="false"/>
<param name="sequence" value="{0}_SEQ"/>

And i'm using it in the following object:

<class name="mpk.tiempos.entidades.organizacion.Areas" key-
generator="secuencias_basicas" identity="id">
<map-to table="organizacion.Areas"/>
<field name="id" type="int">
<sql name="id" type="integer"/>
<field name="descripcion" type="string" required="true">
<sql name="descripcion" type="char"/>
<field name="estaActivo" type="boolean" required="true">
<sql name="estaActivo" type="integer"/>

When I try to create an object the following sql statement is produced:


looking carefully to the statement you will notice
that "ORGANIZACION"".""AREAS_SEQ"."NEXTVAL" has 2 double quotes around the dot
sign and of course that is an illegal statement in Oracle.

This feature used to work in previous releases, and now it only happens when i
use the <schema>.<table> nomenclature to define tables. It also doesn't happen
if instead of ={0}_SEQ I use the full name of the sequence (p.e.

I'll try to look at the source code, but since I'm not familiar with it, it'll
take me some time to fix it, so if someone of the exolab team could take a
look at this problem I'll really appreciate it because my whole project
depends on this.

Thanks in advance

 All   Comments   Work Log   Change History      Sort Order: Ascending order - Click to sort in descending order
francisco.castaneda - 06/Dec/04 05:32 PM
I've traced the error down to the
org.exolab.castor.jdo.drivers.SequenceKeyGenerator class.

The method public String patchSQL( String insert, String primKeyName ) didn-t
get rid of every double quote that the table name had, and specifically in the
case of sequences identified using its full name <schema>.<object> the dot
sign was enclosed between double quotes.

I fixed the problem and i tested it against my examples and worked ok, so I'd
like to post the fix, so How do I do that?

Here is the code


  • Gives a possibility to patch the Castor-generated SQL statement
  • for INSERT (makes sense for DURING_INSERT key generators)
    public String patchSQL( String insert, String primKeyName )
    throws MappingException {
    StringTokenizer st;
    String tableName;
    String seqName;
    String nextval;
    StringBuffer sb;
    int lp1; // the first left parenthesis, which starts fields list
    int lp2; // the second left parenthesis, which starts values list

if ( _style == BEFORE_INSERT ) { return insert; }

// First find the table name
st = new StringTokenizer( insert );
if ( !st.hasMoreTokens() || !st.nextToken().equalsIgnoreCase
("INSERT") ) { throw new MappingException( Messages.format ( "mapping.keyGenCannotParse", insert ) ); }
if ( !st.hasMoreTokens() || !st.nextToken().equalsIgnoreCase("INTO") ) { throw new MappingException( Messages.format( "mapping.keyGenCannotParse", insert ) ); } }
if ( !st.hasMoreTokens() ) { throw new MappingException( Messages.format ( "mapping.keyGenCannotParse", insert ) ); }
tableName = st.nextToken();

* This method didnīt remove every double quote so it was
// remove quotes
/* if ( tableName.startsWith("\"") ) { tableName = tableName.substring( 1, tableName.length() - 1 ); }*/

* This procedure removes every double quote in the tablename.

StringBuffer buffer2 = new StringBuffer(tableName);

for (int index=buffer2.indexOf("\"");index
>=0;index=buffer2.indexOf("\"")){ buffer2.deleteCharAt(index); }

tableName = buffer2.toString();

seqName = MessageFormat.format( _seqName, new String[]
nextval = _factory.quoteName(seqName + ".nextval");
lp1 = insert.indexOf( '(' );
lp2 = insert.indexOf( '(', lp1 + 1 );
if ( lp1 < 0 ) { throw new MappingException( Messages.format( "mapping.keyGenCannotParse", insert ) ); } }
sb = new StringBuffer( insert );
if (!_triggerPresent) { // if no onInsert triggers in the DB, we have
to supply the Key values manually
if ( lp2 < 0 ) { // Only one pk field in the table, the INSERT statement would be // INSERT INTO table VALUES () lp2 = lp1; lp1 = insert.indexOf( " VALUES " ); // don't change the order of lines below, // otherwise index becomes invalid sb.insert( lp2 + 1, nextval); sb.insert( lp1 + 1, "(" + _factory.quoteName( primKeyName ) + ") " ); } else { // don't change the order of lines below, // otherwise index becomes invalid sb.insert( lp2 + 1, nextval + ","); sb.insert( lp1 + 1, _factory.quoteName( primKeyName ) + "," ); }
if ( _style == DURING_INSERT ) { // append 'RETURNING primKeyName INTO ?' sb.append( " RETURNING " ); sb.append( _factory.quoteName( primKeyName ) ); sb.append( " INTO ?" ); }
return sb.toString();

Werner Guttmann - 07/Dec/04 01:24 AM
Hola Francisco, can you please post your patch in unified format ?
Running 'diff -u' normally does the job alright.

francisco.castaneda - 07/Dec/04 04:06 PM
Created an attachment (id=812)
Proposed patch for SequenceKeyGenerator.java

diff -u -r1.5 SequenceKeyGenerator.java

francisco.castaneda - 07/Dec/04 04:09 PM
Hello Werner,

I hope this is what's needed, if not please give me a hand and provide me with
further instructions.

Mis mejores saludos

Werner Guttmann - 08/Dec/04 02:44 AM
Muchas gracias, Franscisco. Eso 'patch' es valido.

Martin, just wondering whether this is a similar problem to what we are trying
to resolve in bug 1793? (postgreSQL specific) ?

Werner Guttmann - 08/Dec/04 05:58 AM
I actually meant bug 1317, but never mind .. . It is a similar problem, indeed.

Werner Guttmann - 08/Dec/04 06:07 AM
Actually, whilst the patch is a valid patch, it's not a unified patch against
the CVS repository. Apparently, you created a second copy of
SequenceKeyGenerator in a directory named b, and diffed it against

What I'd need is a unified patch against the latest version of the code in the
CVS repository. Would you be willing the get the latest sources from the CVS
server, make the change in your local code and run the diff command to create a
unified patch between your local changes and the CVS code repository.

I know that I may be asking a lot (and it definitely is not really required for
a patch of this size), but it'd ease my life in general - working with a lot of
patches at this time of the year.

Martin Fuchs - 08/Dec/04 11:32 AM
Created an attachment (id=814)
proposed patch

It's not the same problem as that in bug 1317, but is also about double quotes.

This patch is a somewhat optimized version of Francisco's bug fix.

francisco.castaneda - 09/Dec/04 04:47 PM
Werner, Martin,

I didn't have the chance to submit my diff patch, but today I read Martin's
mail and then I guess the patch that Martin sent closes the bug, Am I right?

Then, Can I expect to see this as an add on to version 9.5.4?

How does this work?

Best regards


Werner Guttmann - 11/Dec/04 01:59 AM
Francisco, I'll commit Martin's patch in due time (i.e. read some when this
weekend), but I am afraid you'll have to wait for 0.9.6 for this to surface in
an official release. has been released already, and unless there are any
urgent regression issues with features/bugs introduced in, there won't
be any enhancements to this release any more.

Werner Guttmann - 11/Dec/04 02:26 AM
Patch committed as supplied. Please don't forget to close this bug once you've
assessed its correctness.