[
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 range.
>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 clauses.
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.
Thanks!
> 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: 10.6.0.0
>
> 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.
