[jira] [Commented] (DERBY-6948) INSERT .. SELECT produces NULL for getGeneratedKeys()

2017-07-07 Thread Lukas Eder (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-6948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16077722#comment-16077722
 ] 

Lukas Eder commented on DERBY-6948:
---

I see, thanks [~rhillegas]. Unfortunately, I cannot make that single user 
assumption...

> INSERT .. SELECT produces NULL for getGeneratedKeys()
> -
>
> Key: DERBY-6948
> URL: https://issues.apache.org/jira/browse/DERBY-6948
> Project: Derby
>  Issue Type: Bug
>  Components: JDBC
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> The following code:
> {code}
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import org.apache.derby.jdbc.EmbeddedDataSource;
> public class Derby {
> public static void main(String[] args) throws Exception {
> EmbeddedDataSource ds;
> ds = new EmbeddedDataSource();
> ds.setDatabaseName("memory:test;create=true");
> try (Connection con = ds.getConnection();
>  Statement s = con.createStatement()) {
> s.execute(
> "CREATE TABLE test ("
>   + "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,"
>   + "name VARCHAR(255))");
> try (PreparedStatement ps = con.prepareStatement("insert into 
> test (name) select 'a' from sysibm.sysdummy1", 
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (PreparedStatement ps = con.prepareStatement("insert into 
> test (name) select 'a' from sysibm.sysdummy1", 
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (ResultSet rs = s.executeQuery("select id from test")) {
> while (rs.next())
> System.out.println("ID: " + rs.getObject(1));
> }
> }
> }
> }
> {code}
> Produces this output:
> {code}
> GEN_ID: null
> GEN_ID: null
> ID: 1
> ID: 2
> ID: 3
> {code}
> The expected output would be:
> {code}
> GEN_ID: 1
> GEN_ID: 2
> GEN_ID: 3
> ID: 1
> ID: 2
> ID: 3
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (DERBY-6948) INSERT .. SELECT produces NULL for getGeneratedKeys()

2017-07-06 Thread Rick Hillegas (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-6948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16077378#comment-16077378
 ] 

Rick Hillegas commented on DERBY-6948:
--

If you are the only one inserting into that table, and you have just inserted N 
rows, then the last N key values inserted could be calculated from the 
increment step and the value returned by SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY(): 
http://db.apache.org/derby/docs/10.13/ref/rrefsyscspeekidentity.html. If 
multiple users are inserting simultaneously, then this won't work. I don't know 
of a better workaround.

> INSERT .. SELECT produces NULL for getGeneratedKeys()
> -
>
> Key: DERBY-6948
> URL: https://issues.apache.org/jira/browse/DERBY-6948
> Project: Derby
>  Issue Type: Bug
>  Components: JDBC
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> The following code:
> {code}
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import org.apache.derby.jdbc.EmbeddedDataSource;
> public class Derby {
> public static void main(String[] args) throws Exception {
> EmbeddedDataSource ds;
> ds = new EmbeddedDataSource();
> ds.setDatabaseName("memory:test;create=true");
> try (Connection con = ds.getConnection();
>  Statement s = con.createStatement()) {
> s.execute(
> "CREATE TABLE test ("
>   + "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,"
>   + "name VARCHAR(255))");
> try (PreparedStatement ps = con.prepareStatement("insert into 
> test (name) select 'a' from sysibm.sysdummy1", 
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (PreparedStatement ps = con.prepareStatement("insert into 
> test (name) select 'a' from sysibm.sysdummy1", 
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (ResultSet rs = s.executeQuery("select id from test")) {
> while (rs.next())
> System.out.println("ID: " + rs.getObject(1));
> }
> }
> }
> }
> {code}
> Produces this output:
> {code}
> GEN_ID: null
> GEN_ID: null
> ID: 1
> ID: 2
> ID: 3
> {code}
> The expected output would be:
> {code}
> GEN_ID: 1
> GEN_ID: 2
> GEN_ID: 3
> ID: 1
> ID: 2
> ID: 3
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (DERBY-6948) INSERT .. SELECT produces NULL for getGeneratedKeys()

2017-07-05 Thread Lukas Eder (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-6948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16075961#comment-16075961
 ] 

Lukas Eder commented on DERBY-6948:
---

I see, thanks for linking, Rick. Is there any SQL-based workaround for the 
missing functionality, e.g. along the lines of the PostgreSQL INSERT .. 
RETURNING statement, or along DB2's (and the SQL standard's) SELECT * FROM 
FINAL TABLE (INSERT ..)?

> INSERT .. SELECT produces NULL for getGeneratedKeys()
> -
>
> Key: DERBY-6948
> URL: https://issues.apache.org/jira/browse/DERBY-6948
> Project: Derby
>  Issue Type: Bug
>  Components: JDBC
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> The following code:
> {code}
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import org.apache.derby.jdbc.EmbeddedDataSource;
> public class Derby {
> public static void main(String[] args) throws Exception {
> EmbeddedDataSource ds;
> ds = new EmbeddedDataSource();
> ds.setDatabaseName("memory:test;create=true");
> try (Connection con = ds.getConnection();
>  Statement s = con.createStatement()) {
> s.execute(
> "CREATE TABLE test ("
>   + "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,"
>   + "name VARCHAR(255))");
> try (PreparedStatement ps = con.prepareStatement("insert into 
> test (name) select 'a' from sysibm.sysdummy1", 
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (PreparedStatement ps = con.prepareStatement("insert into 
> test (name) select 'a' from sysibm.sysdummy1", 
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (ResultSet rs = s.executeQuery("select id from test")) {
> while (rs.next())
> System.out.println("ID: " + rs.getObject(1));
> }
> }
> }
> }
> {code}
> Produces this output:
> {code}
> GEN_ID: null
> GEN_ID: null
> ID: 1
> ID: 2
> ID: 3
> {code}
> The expected output would be:
> {code}
> GEN_ID: 1
> GEN_ID: 2
> GEN_ID: 3
> ID: 1
> ID: 2
> ID: 3
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (DERBY-6948) INSERT .. SELECT produces NULL for getGeneratedKeys()

2017-07-05 Thread Rick Hillegas (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-6948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16075675#comment-16075675
 ] 

Rick Hillegas commented on DERBY-6948:
--

I have linked this issue to DERBY-3609. This is another example of the fact 
that Derby does not really support getGeneratedKeys(). In its defense, Derby 
doesn't claim to. In fact the return value of 
DatabaseMetaData.supportsGetGeneratedKeys() is false, as can be see by running 
the following script:

{noformat}
connect 'jdbc:derby:memory:db;create=true';

call syscs_util.syscs_register_tool( 'databaseMetaData', true );

values supportsGetGeneratedKeys();
{noformat}

> INSERT .. SELECT produces NULL for getGeneratedKeys()
> -
>
> Key: DERBY-6948
> URL: https://issues.apache.org/jira/browse/DERBY-6948
> Project: Derby
>  Issue Type: Bug
>  Components: JDBC
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> The following code:
> {code}
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import org.apache.derby.jdbc.EmbeddedDataSource;
> public class Derby {
> public static void main(String[] args) throws Exception {
> EmbeddedDataSource ds;
> ds = new EmbeddedDataSource();
> ds.setDatabaseName("memory:test;create=true");
> try (Connection con = ds.getConnection();
>  Statement s = con.createStatement()) {
> s.execute(
> "CREATE TABLE test ("
>   + "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,"
>   + "name VARCHAR(255))");
> try (PreparedStatement ps = con.prepareStatement("insert into 
> test (name) select 'a' from sysibm.sysdummy1", 
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (PreparedStatement ps = con.prepareStatement("insert into 
> test (name) select 'a' from sysibm.sysdummy1", 
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (ResultSet rs = s.executeQuery("select id from test")) {
> while (rs.next())
> System.out.println("ID: " + rs.getObject(1));
> }
> }
> }
> }
> {code}
> Produces this output:
> {code}
> GEN_ID: null
> GEN_ID: null
> ID: 1
> ID: 2
> ID: 3
> {code}
> The expected output would be:
> {code}
> GEN_ID: 1
> GEN_ID: 2
> GEN_ID: 3
> ID: 1
> ID: 2
> ID: 3
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (DERBY-6948) INSERT .. SELECT produces NULL for getGeneratedKeys()

2017-07-05 Thread Lukas Eder (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-6948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16074772#comment-16074772
 ] 

Lukas Eder commented on DERBY-6948:
---

>From the description, I wasn't sure if this issue is the same as 
>https://issues.apache.org/jira/browse/DERBY-3439 or 
>https://issues.apache.org/jira/browse/DERBY-4015

> INSERT .. SELECT produces NULL for getGeneratedKeys()
> -
>
> Key: DERBY-6948
> URL: https://issues.apache.org/jira/browse/DERBY-6948
> Project: Derby
>  Issue Type: Bug
>  Components: JDBC
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> The following code:
> {code}
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import org.apache.derby.jdbc.EmbeddedDataSource;
> public class Derby {
> public static void main(String[] args) throws Exception {
> EmbeddedDataSource ds;
> ds = new EmbeddedDataSource();
> ds.setDatabaseName("memory:test;create=true");
> try (Connection con = ds.getConnection();
>  Statement s = con.createStatement()) {
> s.execute(
> "CREATE TABLE test ("
>   + "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,"
>   + "name VARCHAR(255))");
> try (PreparedStatement ps = con.prepareStatement("insert into 
> test (name) select 'a' from sysibm.sysdummy1", 
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (PreparedStatement ps = con.prepareStatement("insert into 
> test (name) select 'a' from sysibm.sysdummy1", 
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (ResultSet rs = s.executeQuery("select id from test")) {
> while (rs.next())
> System.out.println("ID: " + rs.getObject(1));
> }
> }
> }
> }
> {code}
> Produces this output:
> {code}
> GEN_ID: null
> GEN_ID: null
> ID: 1
> ID: 2
> ID: 3
> {code}
> The expected output would be:
> {code}
> GEN_ID: 1
> GEN_ID: 2
> GEN_ID: 3
> ID: 1
> ID: 2
> ID: 3
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)