Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1310086899.1258729779577.JavaMail.jira@brutus>
Date: Fri, 20 Nov 2009 15:09:39 +0000 (UTC)
From: "Martin D. (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-4452) possible inconsistent internal id
 counter for generated keys
In-Reply-To: <2092637653.1258717779785.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-4452?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12780586#action_12780586 ] 

Martin D. commented on DERBY-4452:
----------------------------------

Thanks for your quick reply!
Indeed, this is the usual behavior. But the reference manual could contain a notice or hint that the way I tried it is unlikely to succeed :( Instead, the example code ...
(http://db.apache.org/derby/docs/10.5/ref/rrefsqlj37836.html#rrefsqlj37836)
"  create table greetings (i int generated by default as identity, ch char(50));
  -- specify value "1":
  insert into greetings values (1, 'hi');
  -- use generated default
  insert into greetings values (DEFAULT, 'salut');
  -- use generated default
  insert into greetings(ch) values ('bonjour');   "
... implies that everything will be ok.
Also the different behavior of other database systems (like MySQL) made me think that it was a bug...
Regards, Martin

> possible inconsistent internal id counter for generated keys
> ------------------------------------------------------------
>
>                 Key: DERBY-4452
>                 URL: https://issues.apache.org/jira/browse/DERBY-4452
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC, SQL
>    Affects Versions: 10.5.3.0
>         Environment: derby 10.5.3.0, jdbc, embedded db
>            Reporter: Martin D.
>
> it seem that the internal auto id counter for generating the keys (ids) is not updated when a value with a fix id is inserted.
> sample code:
> try {
> 	Connection con = DriverManager.getConnection("jdbc:derby:db/data/tmp;create=true");
> 	Statement stmt = con.createStatement();
> 	stmt.executeUpdate(
> 		"CREATE TABLE testtable (id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, value INT NOT NULL, PRIMARY KEY(ID))");
> 	// insert a few test values with auto id
> 	stmt.execute("INSERT INTO testtable (value) VALUES (1),(2),(3)");
> 	// insert more values with fix id
> 	stmt.execute("INSERT INTO testtable (id,value) VALUES (4,4),(5,5),(6,6)");
> 	// try to insert more values with auto id
> 	// -> will throw SQLIntegrityConstraintViolationException (SQLState 23505)
> 	stmt.execute("INSERT INTO testtable (value) VALUES (7)");
> 	stmt.close();
> 	con.close();
> } catch (Exception e) {
> 	e.printStackTrace();
> }

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