You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "A B (JIRA)" <ji...@apache.org> on 2007/05/09 17:43:15 UTC
[jira] Created: (DERBY-2631) Expose existing auto-generated key
functionality through more JDBC APIs in embedded mode.
Expose existing auto-generated key functionality through more JDBC APIs in embedded mode.
-----------------------------------------------------------------------------------------
Key: DERBY-2631
URL: https://issues.apache.org/jira/browse/DERBY-2631
Project: Derby
Issue Type: Improvement
Components: JDBC
Affects Versions: 10.3.0.0
Environment: Embedded mode only.
Reporter: A B
Assigned To: A B
Priority: Minor
Derby currently supports the following JDBC methods for auto-generated keys:
// Indicate that we want to retrieve auto-generated key values.
Connection.prepareStatement(String sql, int autoGeneratedKeys);
Statement.execute(String sql, int autoGeneratedKeys);
Statement.executeUpdate(String sql, int autoGeneratedKeys);
// Retrieve the auto-generated values (only applies to INSERT statements).
ResultSet rs = Statement.getGeneratedKeys();
The current implementation of getGeneratedKeys() internally maps to the "IDENTITY_VAL_LOCAL()" method, which means that Derby's implementation only returns generated keys for autoincrement columns (no other default columns are supported). Further:
1. The generated key result set only ever has a single column. This is
because Derby only allows one autoincrement column per table.
2. The type of the single column in the result set will be DECIMAL(31,0).
This is defined by IDENTITY_VAL_LOCAL().
3. The generated key result set will only ever have a single row. This is
because IDENTITY_VAL_LOCAL() only returns values that were assigned as
the result of a *single row* INSERT statement using a VALUES clause.
For a single row INSERT statement, at most one autoincrement value
will be generated.
All of that said, JDBC 3.0 also defines the following methods, which allow the user to explicitly indicate, via column position or column name, the columns for which the auto-generated keys should be made available:
Connection.prepareStatement(String sql, String[] columnNames);
Connection.prepareStatement(String sql, int[] columnIndexes);
Statement.execute(String sql, String[] columNames);
Statement.execute(String sql, int[] columIndexes);
Statement.executeUpdate(String sql, String[] columnNames);
Statement.executeUpdate(String sql, int[] columnIndexes);
Derby currently throws a "Feature not supported" error for all of these methods. However, it seems like the above methods could be "mapped" onto the existing Derby behavior with relatively little effort (in embedded mode). Most of the required code is already in place.
Doing so would make it easier for applications that rely on the columnNames and/or columnIndexes APIs to work with Derby (assuming the app just wants generated keys for identity (autoincrement) columns).
Note that this Jira does *not* entail removing any of the restrictions nor changing any of the behavior outlined above. All of that will remain exactly as it is. This Jira simply exposes the existing functionality (restrictions and all) through additional (standard) API methods. In particular this means that any column specified by index (position) or name must be an auto-increment column for the INSERT table; otherwise Derby should throw an error. Or put differently, a user who specifies a column name/position will get--in the absence of errors--the *exact* same results as s/he would get from invoking the "(String sql, int autoGeneratedKeys)" method.
Note also: This Jira is specifically for embedded mode. I think it would be harder to support these methods for Derby Client and so do not plan to address that.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2631) Expose existing auto-generated key
functionality through more JDBC APIs in embedded mode.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12495664 ]
A B commented on DERBY-2631:
----------------------------
Thank you very much for your feedback, Kathey and Knut Anders. I will indeed create a new issue for client driver once the changes for this Jira are committed.
I'll try to commit within the next day or so if there are no other comments.
> Expose existing auto-generated key functionality through more JDBC APIs in embedded mode.
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-2631
> URL: https://issues.apache.org/jira/browse/DERBY-2631
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> Affects Versions: 10.3.0.0
> Environment: Embedded mode only.
> Reporter: A B
> Assigned To: A B
> Priority: Minor
> Attachments: d2631_v1.patch, d2631_v1.stat
>
>
> Derby currently supports the following JDBC methods for auto-generated keys:
> // Indicate that we want to retrieve auto-generated key values.
> Connection.prepareStatement(String sql, int autoGeneratedKeys);
> Statement.execute(String sql, int autoGeneratedKeys);
> Statement.executeUpdate(String sql, int autoGeneratedKeys);
> // Retrieve the auto-generated values (only applies to INSERT statements).
> ResultSet rs = Statement.getGeneratedKeys();
> The current implementation of getGeneratedKeys() internally maps to the "IDENTITY_VAL_LOCAL()" method, which means that Derby's implementation only returns generated keys for autoincrement columns (no other default columns are supported). Further:
> 1. The generated key result set only ever has a single column. This is
> because Derby only allows one autoincrement column per table.
> 2. The type of the single column in the result set will be DECIMAL(31,0).
> This is defined by IDENTITY_VAL_LOCAL().
> 3. The generated key result set will only ever have a single row. This is
> because IDENTITY_VAL_LOCAL() only returns values that were assigned as
> the result of a *single row* INSERT statement using a VALUES clause.
> For a single row INSERT statement, at most one autoincrement value
> will be generated.
> All of that said, JDBC 3.0 also defines the following methods, which allow the user to explicitly indicate, via column position or column name, the columns for which the auto-generated keys should be made available:
> Connection.prepareStatement(String sql, String[] columnNames);
> Connection.prepareStatement(String sql, int[] columnIndexes);
> Statement.execute(String sql, String[] columNames);
> Statement.execute(String sql, int[] columIndexes);
> Statement.executeUpdate(String sql, String[] columnNames);
> Statement.executeUpdate(String sql, int[] columnIndexes);
> Derby currently throws a "Feature not supported" error for all of these methods. However, it seems like the above methods could be "mapped" onto the existing Derby behavior with relatively little effort (in embedded mode). Most of the required code is already in place.
> Doing so would make it easier for applications that rely on the columnNames and/or columnIndexes APIs to work with Derby (assuming the app just wants generated keys for identity (autoincrement) columns).
> Note that this Jira does *not* entail removing any of the restrictions nor changing any of the behavior outlined above. All of that will remain exactly as it is. This Jira simply exposes the existing functionality (restrictions and all) through additional (standard) API methods. In particular this means that any column specified by index (position) or name must be an auto-increment column for the INSERT table; otherwise Derby should throw an error. Or put differently, a user who specifies a column name/position will get--in the absence of errors--the *exact* same results as s/he would get from invoking the "(String sql, int autoGeneratedKeys)" method.
> Note also: This Jira is specifically for embedded mode. I think it would be harder to support these methods for Derby Client and so do not plan to address that.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2631) Expose existing auto-generated key
functionality through more JDBC APIs in embedded mode.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B updated DERBY-2631:
-----------------------
Attachment: d2631_v1.stat
d2631_v1.patch
Attaching a patch, d2631_v1.patch, which does the following:
1. Updates the following JDBC methods so that they no longer throw a "Feature
not implemented" error. Instead they make calls to an already existing
internal method and pass in the appropriate arguments:
Connection.prepareStatement(String sql, String[] columnNames);
Connection.prepareStatement(String sql, int[] columnIndexes);
Statement.execute(String sql, String[] columNames);
Statement.execute(String sql, int[] columIndexes);
Statement.executeUpdate(String sql, String[] columnNames);
Statement.executeUpdate(String sql, int[] columnIndexes);
2. Changes the (already existing but not currently used) code in
sql/execute/InsertResultSet that handles autogen column indexes/names
to throw an error for any target column that is not an auto-increment
column. This is because Derby's implementation of getGeneratedKeys()
internally maps to the IDENTITY_VAL_LOCAL() function, which only returns
keys for identity (autoincrement) columns. So if the user specifies
something else, we'll throw an error.
3. Changes the names of two existing (but unused) errors to more accurately
reflect their intended use (they are now called by the changes for #2).
Also changes the text for those errors as the old text seemed a tad
awkward. No regression impact here because the two errors in question
were never exposed to users before now.
4. Makes a slight change to jdbcapi/statementJdbc30.java (a test) to
reflect the new behavior (we no longer throw "Feature not implemented"
errors).
5. Adds test cases for the newly supported APIs to the JUnit test
lang/AutoGenJDBC30Test.java. This involved adding two more utility
methods to junit/BaseJDBCTestCase.java, as well.
I ran derbyall and suites.All on SUSE Linux with ibm142 and the only failures were those which also show up in the Tinderbox results. I.e. they are not caused by these changes. So I believe this patch is ready for commit.
Review comments or other feedback are appreciated...
> Expose existing auto-generated key functionality through more JDBC APIs in embedded mode.
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-2631
> URL: https://issues.apache.org/jira/browse/DERBY-2631
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> Affects Versions: 10.3.0.0
> Environment: Embedded mode only.
> Reporter: A B
> Assigned To: A B
> Priority: Minor
> Attachments: d2631_v1.patch, d2631_v1.stat
>
>
> Derby currently supports the following JDBC methods for auto-generated keys:
> // Indicate that we want to retrieve auto-generated key values.
> Connection.prepareStatement(String sql, int autoGeneratedKeys);
> Statement.execute(String sql, int autoGeneratedKeys);
> Statement.executeUpdate(String sql, int autoGeneratedKeys);
> // Retrieve the auto-generated values (only applies to INSERT statements).
> ResultSet rs = Statement.getGeneratedKeys();
> The current implementation of getGeneratedKeys() internally maps to the "IDENTITY_VAL_LOCAL()" method, which means that Derby's implementation only returns generated keys for autoincrement columns (no other default columns are supported). Further:
> 1. The generated key result set only ever has a single column. This is
> because Derby only allows one autoincrement column per table.
> 2. The type of the single column in the result set will be DECIMAL(31,0).
> This is defined by IDENTITY_VAL_LOCAL().
> 3. The generated key result set will only ever have a single row. This is
> because IDENTITY_VAL_LOCAL() only returns values that were assigned as
> the result of a *single row* INSERT statement using a VALUES clause.
> For a single row INSERT statement, at most one autoincrement value
> will be generated.
> All of that said, JDBC 3.0 also defines the following methods, which allow the user to explicitly indicate, via column position or column name, the columns for which the auto-generated keys should be made available:
> Connection.prepareStatement(String sql, String[] columnNames);
> Connection.prepareStatement(String sql, int[] columnIndexes);
> Statement.execute(String sql, String[] columNames);
> Statement.execute(String sql, int[] columIndexes);
> Statement.executeUpdate(String sql, String[] columnNames);
> Statement.executeUpdate(String sql, int[] columnIndexes);
> Derby currently throws a "Feature not supported" error for all of these methods. However, it seems like the above methods could be "mapped" onto the existing Derby behavior with relatively little effort (in embedded mode). Most of the required code is already in place.
> Doing so would make it easier for applications that rely on the columnNames and/or columnIndexes APIs to work with Derby (assuming the app just wants generated keys for identity (autoincrement) columns).
> Note that this Jira does *not* entail removing any of the restrictions nor changing any of the behavior outlined above. All of that will remain exactly as it is. This Jira simply exposes the existing functionality (restrictions and all) through additional (standard) API methods. In particular this means that any column specified by index (position) or name must be an auto-increment column for the INSERT table; otherwise Derby should throw an error. Or put differently, a user who specifies a column name/position will get--in the absence of errors--the *exact* same results as s/he would get from invoking the "(String sql, int autoGeneratedKeys)" method.
> Note also: This Jira is specifically for embedded mode. I think it would be harder to support these methods for Derby Client and so do not plan to address that.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2631) Expose existing auto-generated key
functionality through more JDBC APIs in embedded mode.
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12494541 ]
Kathey Marsden commented on DERBY-2631:
---------------------------------------
I am a little hesitant about introducing this new difference between embedded and client. Will it be possible with some future work to implement this functionality for client?
> Expose existing auto-generated key functionality through more JDBC APIs in embedded mode.
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-2631
> URL: https://issues.apache.org/jira/browse/DERBY-2631
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> Affects Versions: 10.3.0.0
> Environment: Embedded mode only.
> Reporter: A B
> Assigned To: A B
> Priority: Minor
> Attachments: d2631_v1.patch, d2631_v1.stat
>
>
> Derby currently supports the following JDBC methods for auto-generated keys:
> // Indicate that we want to retrieve auto-generated key values.
> Connection.prepareStatement(String sql, int autoGeneratedKeys);
> Statement.execute(String sql, int autoGeneratedKeys);
> Statement.executeUpdate(String sql, int autoGeneratedKeys);
> // Retrieve the auto-generated values (only applies to INSERT statements).
> ResultSet rs = Statement.getGeneratedKeys();
> The current implementation of getGeneratedKeys() internally maps to the "IDENTITY_VAL_LOCAL()" method, which means that Derby's implementation only returns generated keys for autoincrement columns (no other default columns are supported). Further:
> 1. The generated key result set only ever has a single column. This is
> because Derby only allows one autoincrement column per table.
> 2. The type of the single column in the result set will be DECIMAL(31,0).
> This is defined by IDENTITY_VAL_LOCAL().
> 3. The generated key result set will only ever have a single row. This is
> because IDENTITY_VAL_LOCAL() only returns values that were assigned as
> the result of a *single row* INSERT statement using a VALUES clause.
> For a single row INSERT statement, at most one autoincrement value
> will be generated.
> All of that said, JDBC 3.0 also defines the following methods, which allow the user to explicitly indicate, via column position or column name, the columns for which the auto-generated keys should be made available:
> Connection.prepareStatement(String sql, String[] columnNames);
> Connection.prepareStatement(String sql, int[] columnIndexes);
> Statement.execute(String sql, String[] columNames);
> Statement.execute(String sql, int[] columIndexes);
> Statement.executeUpdate(String sql, String[] columnNames);
> Statement.executeUpdate(String sql, int[] columnIndexes);
> Derby currently throws a "Feature not supported" error for all of these methods. However, it seems like the above methods could be "mapped" onto the existing Derby behavior with relatively little effort (in embedded mode). Most of the required code is already in place.
> Doing so would make it easier for applications that rely on the columnNames and/or columnIndexes APIs to work with Derby (assuming the app just wants generated keys for identity (autoincrement) columns).
> Note that this Jira does *not* entail removing any of the restrictions nor changing any of the behavior outlined above. All of that will remain exactly as it is. This Jira simply exposes the existing functionality (restrictions and all) through additional (standard) API methods. In particular this means that any column specified by index (position) or name must be an auto-increment column for the INSERT table; otherwise Derby should throw an error. Or put differently, a user who specifies a column name/position will get--in the absence of errors--the *exact* same results as s/he would get from invoking the "(String sql, int autoGeneratedKeys)" method.
> Note also: This Jira is specifically for embedded mode. I think it would be harder to support these methods for Derby Client and so do not plan to address that.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-2631) Expose existing auto-generated key
functionality through more JDBC APIs in embedded mode.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B closed DERBY-2631.
----------------------
> Expose existing auto-generated key functionality through more JDBC APIs in embedded mode.
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-2631
> URL: https://issues.apache.org/jira/browse/DERBY-2631
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> Affects Versions: 10.3.1.4
> Environment: Embedded mode only.
> Reporter: A B
> Assignee: A B
> Priority: Minor
> Fix For: 10.3.1.4
>
> Attachments: d2631_v1.patch, d2631_v1.stat
>
>
> Derby currently supports the following JDBC methods for auto-generated keys:
> // Indicate that we want to retrieve auto-generated key values.
> Connection.prepareStatement(String sql, int autoGeneratedKeys);
> Statement.execute(String sql, int autoGeneratedKeys);
> Statement.executeUpdate(String sql, int autoGeneratedKeys);
> // Retrieve the auto-generated values (only applies to INSERT statements).
> ResultSet rs = Statement.getGeneratedKeys();
> The current implementation of getGeneratedKeys() internally maps to the "IDENTITY_VAL_LOCAL()" method, which means that Derby's implementation only returns generated keys for autoincrement columns (no other default columns are supported). Further:
> 1. The generated key result set only ever has a single column. This is
> because Derby only allows one autoincrement column per table.
> 2. The type of the single column in the result set will be DECIMAL(31,0).
> This is defined by IDENTITY_VAL_LOCAL().
> 3. The generated key result set will only ever have a single row. This is
> because IDENTITY_VAL_LOCAL() only returns values that were assigned as
> the result of a *single row* INSERT statement using a VALUES clause.
> For a single row INSERT statement, at most one autoincrement value
> will be generated.
> All of that said, JDBC 3.0 also defines the following methods, which allow the user to explicitly indicate, via column position or column name, the columns for which the auto-generated keys should be made available:
> Connection.prepareStatement(String sql, String[] columnNames);
> Connection.prepareStatement(String sql, int[] columnIndexes);
> Statement.execute(String sql, String[] columNames);
> Statement.execute(String sql, int[] columIndexes);
> Statement.executeUpdate(String sql, String[] columnNames);
> Statement.executeUpdate(String sql, int[] columnIndexes);
> Derby currently throws a "Feature not supported" error for all of these methods. However, it seems like the above methods could be "mapped" onto the existing Derby behavior with relatively little effort (in embedded mode). Most of the required code is already in place.
> Doing so would make it easier for applications that rely on the columnNames and/or columnIndexes APIs to work with Derby (assuming the app just wants generated keys for identity (autoincrement) columns).
> Note that this Jira does *not* entail removing any of the restrictions nor changing any of the behavior outlined above. All of that will remain exactly as it is. This Jira simply exposes the existing functionality (restrictions and all) through additional (standard) API methods. In particular this means that any column specified by index (position) or name must be an auto-increment column for the INSERT table; otherwise Derby should throw an error. Or put differently, a user who specifies a column name/position will get--in the absence of errors--the *exact* same results as s/he would get from invoking the "(String sql, int autoGeneratedKeys)" method.
> Note also: This Jira is specifically for embedded mode. I think it would be harder to support these methods for Derby Client and so do not plan to address that.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2631) Expose existing auto-generated key
functionality through more JDBC APIs in embedded mode.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B updated DERBY-2631:
-----------------------
Derby Info: [Patch Available]
> Expose existing auto-generated key functionality through more JDBC APIs in embedded mode.
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-2631
> URL: https://issues.apache.org/jira/browse/DERBY-2631
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> Affects Versions: 10.3.0.0
> Environment: Embedded mode only.
> Reporter: A B
> Assigned To: A B
> Priority: Minor
> Attachments: d2631_v1.patch, d2631_v1.stat
>
>
> Derby currently supports the following JDBC methods for auto-generated keys:
> // Indicate that we want to retrieve auto-generated key values.
> Connection.prepareStatement(String sql, int autoGeneratedKeys);
> Statement.execute(String sql, int autoGeneratedKeys);
> Statement.executeUpdate(String sql, int autoGeneratedKeys);
> // Retrieve the auto-generated values (only applies to INSERT statements).
> ResultSet rs = Statement.getGeneratedKeys();
> The current implementation of getGeneratedKeys() internally maps to the "IDENTITY_VAL_LOCAL()" method, which means that Derby's implementation only returns generated keys for autoincrement columns (no other default columns are supported). Further:
> 1. The generated key result set only ever has a single column. This is
> because Derby only allows one autoincrement column per table.
> 2. The type of the single column in the result set will be DECIMAL(31,0).
> This is defined by IDENTITY_VAL_LOCAL().
> 3. The generated key result set will only ever have a single row. This is
> because IDENTITY_VAL_LOCAL() only returns values that were assigned as
> the result of a *single row* INSERT statement using a VALUES clause.
> For a single row INSERT statement, at most one autoincrement value
> will be generated.
> All of that said, JDBC 3.0 also defines the following methods, which allow the user to explicitly indicate, via column position or column name, the columns for which the auto-generated keys should be made available:
> Connection.prepareStatement(String sql, String[] columnNames);
> Connection.prepareStatement(String sql, int[] columnIndexes);
> Statement.execute(String sql, String[] columNames);
> Statement.execute(String sql, int[] columIndexes);
> Statement.executeUpdate(String sql, String[] columnNames);
> Statement.executeUpdate(String sql, int[] columnIndexes);
> Derby currently throws a "Feature not supported" error for all of these methods. However, it seems like the above methods could be "mapped" onto the existing Derby behavior with relatively little effort (in embedded mode). Most of the required code is already in place.
> Doing so would make it easier for applications that rely on the columnNames and/or columnIndexes APIs to work with Derby (assuming the app just wants generated keys for identity (autoincrement) columns).
> Note that this Jira does *not* entail removing any of the restrictions nor changing any of the behavior outlined above. All of that will remain exactly as it is. This Jira simply exposes the existing functionality (restrictions and all) through additional (standard) API methods. In particular this means that any column specified by index (position) or name must be an auto-increment column for the INSERT table; otherwise Derby should throw an error. Or put differently, a user who specifies a column name/position will get--in the absence of errors--the *exact* same results as s/he would get from invoking the "(String sql, int autoGeneratedKeys)" method.
> Note also: This Jira is specifically for embedded mode. I think it would be harder to support these methods for Derby Client and so do not plan to address that.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2631) Expose existing auto-generated key
functionality through more JDBC APIs in embedded mode.
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12494599 ]
Kathey Marsden commented on DERBY-2631:
---------------------------------------
Thanks Army. It would be great if you could open an issue for client and link to DERBY-310 once this goes in.
> Expose existing auto-generated key functionality through more JDBC APIs in embedded mode.
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-2631
> URL: https://issues.apache.org/jira/browse/DERBY-2631
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> Affects Versions: 10.3.0.0
> Environment: Embedded mode only.
> Reporter: A B
> Assigned To: A B
> Priority: Minor
> Attachments: d2631_v1.patch, d2631_v1.stat
>
>
> Derby currently supports the following JDBC methods for auto-generated keys:
> // Indicate that we want to retrieve auto-generated key values.
> Connection.prepareStatement(String sql, int autoGeneratedKeys);
> Statement.execute(String sql, int autoGeneratedKeys);
> Statement.executeUpdate(String sql, int autoGeneratedKeys);
> // Retrieve the auto-generated values (only applies to INSERT statements).
> ResultSet rs = Statement.getGeneratedKeys();
> The current implementation of getGeneratedKeys() internally maps to the "IDENTITY_VAL_LOCAL()" method, which means that Derby's implementation only returns generated keys for autoincrement columns (no other default columns are supported). Further:
> 1. The generated key result set only ever has a single column. This is
> because Derby only allows one autoincrement column per table.
> 2. The type of the single column in the result set will be DECIMAL(31,0).
> This is defined by IDENTITY_VAL_LOCAL().
> 3. The generated key result set will only ever have a single row. This is
> because IDENTITY_VAL_LOCAL() only returns values that were assigned as
> the result of a *single row* INSERT statement using a VALUES clause.
> For a single row INSERT statement, at most one autoincrement value
> will be generated.
> All of that said, JDBC 3.0 also defines the following methods, which allow the user to explicitly indicate, via column position or column name, the columns for which the auto-generated keys should be made available:
> Connection.prepareStatement(String sql, String[] columnNames);
> Connection.prepareStatement(String sql, int[] columnIndexes);
> Statement.execute(String sql, String[] columNames);
> Statement.execute(String sql, int[] columIndexes);
> Statement.executeUpdate(String sql, String[] columnNames);
> Statement.executeUpdate(String sql, int[] columnIndexes);
> Derby currently throws a "Feature not supported" error for all of these methods. However, it seems like the above methods could be "mapped" onto the existing Derby behavior with relatively little effort (in embedded mode). Most of the required code is already in place.
> Doing so would make it easier for applications that rely on the columnNames and/or columnIndexes APIs to work with Derby (assuming the app just wants generated keys for identity (autoincrement) columns).
> Note that this Jira does *not* entail removing any of the restrictions nor changing any of the behavior outlined above. All of that will remain exactly as it is. This Jira simply exposes the existing functionality (restrictions and all) through additional (standard) API methods. In particular this means that any column specified by index (position) or name must be an auto-increment column for the INSERT table; otherwise Derby should throw an error. Or put differently, a user who specifies a column name/position will get--in the absence of errors--the *exact* same results as s/he would get from invoking the "(String sql, int autoGeneratedKeys)" method.
> Note also: This Jira is specifically for embedded mode. I think it would be harder to support these methods for Derby Client and so do not plan to address that.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-2631) Expose existing auto-generated key
functionality through more JDBC APIs in embedded mode.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B resolved DERBY-2631.
------------------------
Resolution: Fixed
Tinderbox run ran cleanly after commit, so resolving this issue.
> Expose existing auto-generated key functionality through more JDBC APIs in embedded mode.
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-2631
> URL: https://issues.apache.org/jira/browse/DERBY-2631
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> Affects Versions: 10.3.0.0
> Environment: Embedded mode only.
> Reporter: A B
> Assigned To: A B
> Priority: Minor
> Fix For: 10.3.0.0
>
> Attachments: d2631_v1.patch, d2631_v1.stat
>
>
> Derby currently supports the following JDBC methods for auto-generated keys:
> // Indicate that we want to retrieve auto-generated key values.
> Connection.prepareStatement(String sql, int autoGeneratedKeys);
> Statement.execute(String sql, int autoGeneratedKeys);
> Statement.executeUpdate(String sql, int autoGeneratedKeys);
> // Retrieve the auto-generated values (only applies to INSERT statements).
> ResultSet rs = Statement.getGeneratedKeys();
> The current implementation of getGeneratedKeys() internally maps to the "IDENTITY_VAL_LOCAL()" method, which means that Derby's implementation only returns generated keys for autoincrement columns (no other default columns are supported). Further:
> 1. The generated key result set only ever has a single column. This is
> because Derby only allows one autoincrement column per table.
> 2. The type of the single column in the result set will be DECIMAL(31,0).
> This is defined by IDENTITY_VAL_LOCAL().
> 3. The generated key result set will only ever have a single row. This is
> because IDENTITY_VAL_LOCAL() only returns values that were assigned as
> the result of a *single row* INSERT statement using a VALUES clause.
> For a single row INSERT statement, at most one autoincrement value
> will be generated.
> All of that said, JDBC 3.0 also defines the following methods, which allow the user to explicitly indicate, via column position or column name, the columns for which the auto-generated keys should be made available:
> Connection.prepareStatement(String sql, String[] columnNames);
> Connection.prepareStatement(String sql, int[] columnIndexes);
> Statement.execute(String sql, String[] columNames);
> Statement.execute(String sql, int[] columIndexes);
> Statement.executeUpdate(String sql, String[] columnNames);
> Statement.executeUpdate(String sql, int[] columnIndexes);
> Derby currently throws a "Feature not supported" error for all of these methods. However, it seems like the above methods could be "mapped" onto the existing Derby behavior with relatively little effort (in embedded mode). Most of the required code is already in place.
> Doing so would make it easier for applications that rely on the columnNames and/or columnIndexes APIs to work with Derby (assuming the app just wants generated keys for identity (autoincrement) columns).
> Note that this Jira does *not* entail removing any of the restrictions nor changing any of the behavior outlined above. All of that will remain exactly as it is. This Jira simply exposes the existing functionality (restrictions and all) through additional (standard) API methods. In particular this means that any column specified by index (position) or name must be an auto-increment column for the INSERT table; otherwise Derby should throw an error. Or put differently, a user who specifies a column name/position will get--in the absence of errors--the *exact* same results as s/he would get from invoking the "(String sql, int autoGeneratedKeys)" method.
> Note also: This Jira is specifically for embedded mode. I think it would be harder to support these methods for Derby Client and so do not plan to address that.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2631) Expose existing auto-generated key
functionality through more JDBC APIs in embedded mode.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A B updated DERBY-2631:
-----------------------
Derby Info: (was: [Patch Available])
Fix Version/s: 10.3.0.0
Committed _v1 patch with patch with svn # 538260:
URL: http://svn.apache.org/viewvc?view=rev&rev=538260
Filed DERBY-2653 for the Derby Client and linked that issue to DERBY-310, as requested by Kathey and Knut Anders.
Thanks again for the feedback.
> Expose existing auto-generated key functionality through more JDBC APIs in embedded mode.
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-2631
> URL: https://issues.apache.org/jira/browse/DERBY-2631
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> Affects Versions: 10.3.0.0
> Environment: Embedded mode only.
> Reporter: A B
> Assigned To: A B
> Priority: Minor
> Fix For: 10.3.0.0
>
> Attachments: d2631_v1.patch, d2631_v1.stat
>
>
> Derby currently supports the following JDBC methods for auto-generated keys:
> // Indicate that we want to retrieve auto-generated key values.
> Connection.prepareStatement(String sql, int autoGeneratedKeys);
> Statement.execute(String sql, int autoGeneratedKeys);
> Statement.executeUpdate(String sql, int autoGeneratedKeys);
> // Retrieve the auto-generated values (only applies to INSERT statements).
> ResultSet rs = Statement.getGeneratedKeys();
> The current implementation of getGeneratedKeys() internally maps to the "IDENTITY_VAL_LOCAL()" method, which means that Derby's implementation only returns generated keys for autoincrement columns (no other default columns are supported). Further:
> 1. The generated key result set only ever has a single column. This is
> because Derby only allows one autoincrement column per table.
> 2. The type of the single column in the result set will be DECIMAL(31,0).
> This is defined by IDENTITY_VAL_LOCAL().
> 3. The generated key result set will only ever have a single row. This is
> because IDENTITY_VAL_LOCAL() only returns values that were assigned as
> the result of a *single row* INSERT statement using a VALUES clause.
> For a single row INSERT statement, at most one autoincrement value
> will be generated.
> All of that said, JDBC 3.0 also defines the following methods, which allow the user to explicitly indicate, via column position or column name, the columns for which the auto-generated keys should be made available:
> Connection.prepareStatement(String sql, String[] columnNames);
> Connection.prepareStatement(String sql, int[] columnIndexes);
> Statement.execute(String sql, String[] columNames);
> Statement.execute(String sql, int[] columIndexes);
> Statement.executeUpdate(String sql, String[] columnNames);
> Statement.executeUpdate(String sql, int[] columnIndexes);
> Derby currently throws a "Feature not supported" error for all of these methods. However, it seems like the above methods could be "mapped" onto the existing Derby behavior with relatively little effort (in embedded mode). Most of the required code is already in place.
> Doing so would make it easier for applications that rely on the columnNames and/or columnIndexes APIs to work with Derby (assuming the app just wants generated keys for identity (autoincrement) columns).
> Note that this Jira does *not* entail removing any of the restrictions nor changing any of the behavior outlined above. All of that will remain exactly as it is. This Jira simply exposes the existing functionality (restrictions and all) through additional (standard) API methods. In particular this means that any column specified by index (position) or name must be an auto-increment column for the INSERT table; otherwise Derby should throw an error. Or put differently, a user who specifies a column name/position will get--in the absence of errors--the *exact* same results as s/he would get from invoking the "(String sql, int autoGeneratedKeys)" method.
> Note also: This Jira is specifically for embedded mode. I think it would be harder to support these methods for Derby Client and so do not plan to address that.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2631) Expose existing auto-generated key
functionality through more JDBC APIs in embedded mode.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12495236 ]
Knut Anders Hatlen commented on DERBY-2631:
-------------------------------------------
I'm +1 to the patch. I don't consider this as introducing differences between embedded and the client, but rather as making the embedded driver more complete, which is a good thing. I agree with Kathey that we should open an issue for the client driver and link it to DERBY-310.
> Expose existing auto-generated key functionality through more JDBC APIs in embedded mode.
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-2631
> URL: https://issues.apache.org/jira/browse/DERBY-2631
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> Affects Versions: 10.3.0.0
> Environment: Embedded mode only.
> Reporter: A B
> Assigned To: A B
> Priority: Minor
> Attachments: d2631_v1.patch, d2631_v1.stat
>
>
> Derby currently supports the following JDBC methods for auto-generated keys:
> // Indicate that we want to retrieve auto-generated key values.
> Connection.prepareStatement(String sql, int autoGeneratedKeys);
> Statement.execute(String sql, int autoGeneratedKeys);
> Statement.executeUpdate(String sql, int autoGeneratedKeys);
> // Retrieve the auto-generated values (only applies to INSERT statements).
> ResultSet rs = Statement.getGeneratedKeys();
> The current implementation of getGeneratedKeys() internally maps to the "IDENTITY_VAL_LOCAL()" method, which means that Derby's implementation only returns generated keys for autoincrement columns (no other default columns are supported). Further:
> 1. The generated key result set only ever has a single column. This is
> because Derby only allows one autoincrement column per table.
> 2. The type of the single column in the result set will be DECIMAL(31,0).
> This is defined by IDENTITY_VAL_LOCAL().
> 3. The generated key result set will only ever have a single row. This is
> because IDENTITY_VAL_LOCAL() only returns values that were assigned as
> the result of a *single row* INSERT statement using a VALUES clause.
> For a single row INSERT statement, at most one autoincrement value
> will be generated.
> All of that said, JDBC 3.0 also defines the following methods, which allow the user to explicitly indicate, via column position or column name, the columns for which the auto-generated keys should be made available:
> Connection.prepareStatement(String sql, String[] columnNames);
> Connection.prepareStatement(String sql, int[] columnIndexes);
> Statement.execute(String sql, String[] columNames);
> Statement.execute(String sql, int[] columIndexes);
> Statement.executeUpdate(String sql, String[] columnNames);
> Statement.executeUpdate(String sql, int[] columnIndexes);
> Derby currently throws a "Feature not supported" error for all of these methods. However, it seems like the above methods could be "mapped" onto the existing Derby behavior with relatively little effort (in embedded mode). Most of the required code is already in place.
> Doing so would make it easier for applications that rely on the columnNames and/or columnIndexes APIs to work with Derby (assuming the app just wants generated keys for identity (autoincrement) columns).
> Note that this Jira does *not* entail removing any of the restrictions nor changing any of the behavior outlined above. All of that will remain exactly as it is. This Jira simply exposes the existing functionality (restrictions and all) through additional (standard) API methods. In particular this means that any column specified by index (position) or name must be an auto-increment column for the INSERT table; otherwise Derby should throw an error. Or put differently, a user who specifies a column name/position will get--in the absence of errors--the *exact* same results as s/he would get from invoking the "(String sql, int autoGeneratedKeys)" method.
> Note also: This Jira is specifically for embedded mode. I think it would be harder to support these methods for Derby Client and so do not plan to address that.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2631) Expose existing auto-generated key
functionality through more JDBC APIs in embedded mode.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12494570 ]
A B commented on DERBY-2631:
----------------------------
> Will it be possible with some future work to implement this functionality for client?
I have not looked in detail at how to implement this for client, but I would be surprised if such a thing was not possible with some future work. My guess, though, is that it will require more work than was needed for embedded (most of the embedded code already existed). Since the fish I'm hoping to fry right now is limited to embedded, and since there does not appear to be a straightforward mapping to the client code, I was hoping to focus this Jira on embedded only.
But as far as I know there's no reason this functionality couldn't be implemented in client, once someone has the desire to fry that fish. It may be more difficult, but it seems like it should be possible...
> Expose existing auto-generated key functionality through more JDBC APIs in embedded mode.
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-2631
> URL: https://issues.apache.org/jira/browse/DERBY-2631
> Project: Derby
> Issue Type: Improvement
> Components: JDBC
> Affects Versions: 10.3.0.0
> Environment: Embedded mode only.
> Reporter: A B
> Assigned To: A B
> Priority: Minor
> Attachments: d2631_v1.patch, d2631_v1.stat
>
>
> Derby currently supports the following JDBC methods for auto-generated keys:
> // Indicate that we want to retrieve auto-generated key values.
> Connection.prepareStatement(String sql, int autoGeneratedKeys);
> Statement.execute(String sql, int autoGeneratedKeys);
> Statement.executeUpdate(String sql, int autoGeneratedKeys);
> // Retrieve the auto-generated values (only applies to INSERT statements).
> ResultSet rs = Statement.getGeneratedKeys();
> The current implementation of getGeneratedKeys() internally maps to the "IDENTITY_VAL_LOCAL()" method, which means that Derby's implementation only returns generated keys for autoincrement columns (no other default columns are supported). Further:
> 1. The generated key result set only ever has a single column. This is
> because Derby only allows one autoincrement column per table.
> 2. The type of the single column in the result set will be DECIMAL(31,0).
> This is defined by IDENTITY_VAL_LOCAL().
> 3. The generated key result set will only ever have a single row. This is
> because IDENTITY_VAL_LOCAL() only returns values that were assigned as
> the result of a *single row* INSERT statement using a VALUES clause.
> For a single row INSERT statement, at most one autoincrement value
> will be generated.
> All of that said, JDBC 3.0 also defines the following methods, which allow the user to explicitly indicate, via column position or column name, the columns for which the auto-generated keys should be made available:
> Connection.prepareStatement(String sql, String[] columnNames);
> Connection.prepareStatement(String sql, int[] columnIndexes);
> Statement.execute(String sql, String[] columNames);
> Statement.execute(String sql, int[] columIndexes);
> Statement.executeUpdate(String sql, String[] columnNames);
> Statement.executeUpdate(String sql, int[] columnIndexes);
> Derby currently throws a "Feature not supported" error for all of these methods. However, it seems like the above methods could be "mapped" onto the existing Derby behavior with relatively little effort (in embedded mode). Most of the required code is already in place.
> Doing so would make it easier for applications that rely on the columnNames and/or columnIndexes APIs to work with Derby (assuming the app just wants generated keys for identity (autoincrement) columns).
> Note that this Jira does *not* entail removing any of the restrictions nor changing any of the behavior outlined above. All of that will remain exactly as it is. This Jira simply exposes the existing functionality (restrictions and all) through additional (standard) API methods. In particular this means that any column specified by index (position) or name must be an auto-increment column for the INSERT table; otherwise Derby should throw an error. Or put differently, a user who specifies a column name/position will get--in the absence of errors--the *exact* same results as s/he would get from invoking the "(String sql, int autoGeneratedKeys)" method.
> Note also: This Jira is specifically for embedded mode. I think it would be harder to support these methods for Derby Client and so do not plan to address that.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.