db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-712) Support for sequences
Date Fri, 22 Jan 2010 18:26:21 GMT

    [ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12803804#action_12803804

Rick Hillegas commented on DERBY-712:

Thanks for the new patch, Suran. It looks very good. First some responses to your questions:

>What is the correct SQLState code to be used for Exception messages in cases like the
INCREMENT BY value being given as '0' or the MINVALUE being larger than MAXVALUE? In this
patch I used message ID "22008.S" (LANG_INVALID_FUNCTION_ARGUMENT) for such scenarios.

You will need to create some new codes in org.apache.derby.shared.common.reference.SQLState
and some corresponding new messages in trunk/java/engine/org/apache/derby/loc/messages.xml

For the codes themselves, I can't find any guidance in the ANSI/ISO standard beyond the general
rule that SQLStates for syntax errors should begin with 42. I see that there is an empty range
of Derby SQLStates starting with 42XAC. I recommend that you allocate your codes out of that

>Also, I noticed that the START WITH and INCREMENT BY options are order bound, i.e. they
have to appear just after the DATATYPE, and INCREMENT BY has to follow START WITH if both
are present. Is this a valid constraint or does the parser code need to be modified?

You are right, Derby is being over-strict here. The ANSI/ISO standard allows the optional
clauses to appear in any order. The only restriction is that a given option can be specified
at most once. You are welcome to relax Derby's restrictions here. You may want to use the
CREATE FUNCTION/PROCEDURE syntax as a template for how to do this. However, I would recommend
doing this work in another patch after you have finished the bind() logic for the optional

Concerning the patch:

In CreateSequenceNode, I like the way that you put the defaulting logic in the initializer
and left validation to the bind() stage.

As noted above, you will want to create specific error messages for the validation checks
you perform in CreateSequenceNode.bindStatement(). The generic error messages don't give the
user enough information to figure out what the problem is.

It seems to me that you only need to compare max, min, and initial values in one place, rather
than duplicating this check for each datatype.


> Support for sequences
> ---------------------
>                 Key: DERBY-712
>                 URL: https://issues.apache.org/jira/browse/DERBY-712
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>         Environment: feature request 
>            Reporter: Tony Dahbura
>            Assignee: Suran Jayathilaka
>             Fix For:
>         Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, catalogs_c.patch,
catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, catalogs_f_2.patch, catalogs_g.diff,
catalogs_h.diff, create_drop_sequence_a.patch, create_drop_sequence_b.patch, create_drop_sequence_c.patch,
create_drop_sequence_d.patch, create_sequence_a.patch, createseq_args_bind_a.diff, derby-712-02-aa-privilegeNodeCleanup.diff,
derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, derby-712-04-aa-dblook.diff,
SequenceGenerator.html, sequences_next_value_a.patch
> Would like to see support added for sequences.  This would permit a select against the
sequence to always obtain a ever increasing/decreasing value.  The identity column works fine
but there are times for applications where the application needs to obtain the sequence number
and use it prior to the database write.  Subsequent calls to the table/column would result
in a new number on each call.
> SQL such as the following:
> SELECT NEXT VALUE FOR sequence_name FROM sometable ; would result in a next value.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message