Thread: [JDBC] JDBC drive 42 release breaks seriously for RETURNING?
Hello:
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds)
and get the expected resultset after insertion using 9.4 series driver.I have a working call using
connection.prepareStatement(sqlInsert, generatedIds)
Is it a bug or change?
Thanks
Hello Tom:
Could you please provide an example code that reproduce the issue?
Could you please provide an example code that reproduce the issue?
Jorge Solórzano
On Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:
I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
Hello Tom:
Could you please provide an example code that reproduce the issue?
Could you please provide an example code that reproduce the issue?
Jorge Solórzano
On Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:
I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
Something like below
sqlInsert = "insert into aTable ((select nextval()), "stringdata") pstmt connection.prepareStatement(sq
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. series
On Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:
Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
Something like below
sqlInsert = "insert into aTable ((select nextval()), "stringdata") pstmt connection.prepareStatement(sq
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. series
On Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:
Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
Certainly not intended. Thanks for the report!
On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:
pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
Certainly not intended. Thanks for the report!
On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:
pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
I just tested this:
public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}
and it worked fine. This is using the latest code from github
On 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:
Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
I just tested this:
public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}
and it worked fine. This is using the latest code from github
On 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:
Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
I was using jar from maven 42.1.1
On Sat, Jun 17, 2017 at 6:25 PM, Dave Cramer <pg@fastcrypt.com> wrote:
I just tested this:public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost: 5432/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}and it worked fine. This is using the latest code from githubOn 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
I was using jar from maven 42.1.1
On Sat, Jun 17, 2017 at 6:25 PM, Dave Cramer <pg@fastcrypt.com> wrote:
I just tested this:public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost: 5432/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}and it worked fine. This is using the latest code from githubOn 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
Also, pg server 9.4
On Sat, Jun 17, 2017 at 7:14 PM, Tom Smith <tomsmith1989sk@gmail.com> wrote:
I was using jar from maven 42.1.1On Sat, Jun 17, 2017 at 6:25 PM, Dave Cramer <pg@fastcrypt.com> wrote:I just tested this:public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:543 2/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}and it worked fine. This is using the latest code from githubOn 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
Also, pg server 9.4
On Sat, Jun 17, 2017 at 7:14 PM, Tom Smith <tomsmith1989sk@gmail.com> wrote:
I was using jar from maven 42.1.1On Sat, Jun 17, 2017 at 6:25 PM, Dave Cramer <pg@fastcrypt.com> wrote:I just tested this:public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:543 2/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}and it worked fine. This is using the latest code from githubOn 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
If you change your code
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
with
PreparedStatement ps3 = c.prepareStatement("WITH id_temp AS ( select nextval('transportation_point_id_seq') id ) insert into transportation_point values ((select id from id_temp), 'foo')", new String[]{"id"} )) {
You might see the issue.
basically, I was using a CTE (id_temp) to generate the ID value and then I select that value from CTE for insert in a single SQL.
I just tested again with two driver version swapping back and forth and the 42 series always produced error due to empty result set
On Sat, Jun 17, 2017 at 6:25 PM, Dave Cramer <pg@fastcrypt.com> wrote:
I just tested this:public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost: 5432/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}and it worked fine. This is using the latest code from githubOn 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
If you change your code
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
with
PreparedStatement ps3 = c.prepareStatement("WITH id_temp AS ( select nextval('transportation_point_id_seq') id ) insert into transportation_point values ((select id from id_temp), 'foo')", new String[]{"id"} )) {
You might see the issue.
basically, I was using a CTE (id_temp) to generate the ID value and then I select that value from CTE for insert in a single SQL.
I just tested again with two driver version swapping back and forth and the 42 series always produced error due to empty result set
On Sat, Jun 17, 2017 at 6:25 PM, Dave Cramer <pg@fastcrypt.com> wrote:
I just tested this:public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost: 5432/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}and it worked fine. This is using the latest code from githubOn 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
OK, so this
would appear to be where we are getting tripped up. Now knowing that the CTE is going to insert something is going to be tricky to figure out.
Just curious but why are you using a CTE to do something relatively simple ?
On 17 June 2017 at 21:20, Tom Smith <tomsmith1989sk@gmail.com> wrote:
If you change your codePreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) { with
PreparedStatement ps3 = c.prepareStatement("WITH id_temp AS ( select nextval('transportation_point_id_seq') id ) insert into transportation_point values ((select id from id_temp), 'foo')", new String[]{"id"} )) { You might see the issue.basically, I was using a CTE (id_temp) to generate the ID value and then I select that value from CTE for insert in a single SQL.I just tested again with two driver version swapping back and forth and the 42 series always produced error due to empty result setOn Sat, Jun 17, 2017 at 6:25 PM, Dave Cramer <pg@fastcrypt.com> wrote:I just tested this:public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:543 2/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}and it worked fine. This is using the latest code from githubOn 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
OK, so this
would appear to be where we are getting tripped up. Now knowing that the CTE is going to insert something is going to be tricky to figure out.
Just curious but why are you using a CTE to do something relatively simple ?
On 17 June 2017 at 21:20, Tom Smith <tomsmith1989sk@gmail.com> wrote:
If you change your codePreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) { with
PreparedStatement ps3 = c.prepareStatement("WITH id_temp AS ( select nextval('transportation_point_id_seq') id ) insert into transportation_point values ((select id from id_temp), 'foo')", new String[]{"id"} )) { You might see the issue.basically, I was using a CTE (id_temp) to generate the ID value and then I select that value from CTE for insert in a single SQL.I just tested again with two driver version swapping back and forth and the 42 series always produced error due to empty result setOn Sat, Jun 17, 2017 at 6:25 PM, Dave Cramer <pg@fastcrypt.com> wrote:I just tested this:public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:543 2/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}and it worked fine. This is using the latest code from githubOn 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
The actual CTE is a complex one.
with call like c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
Is the checking like below necessary at all (from your provided link)? why was it there in the place? Since caller already provides new String[]{} arg, driver should just pass it along.
It is up to caller to ensure correct SQL.
if (currentCommandType != SqlCommandType.INSERT
&& currentCommandType != SqlCommandType.UPDATE |
&& currentCommandType != SqlCommandType.DELETE) { |
return false; |
On Sun, Jun 18, 2017 at 7:17 AM, Dave Cramer <pg@fastcrypt.com> wrote:
OK, so thiswould appear to be where we are getting tripped up. Now knowing that the CTE is going to insert something is going to be tricky to figure out.Just curious but why are you using a CTE to do something relatively simple ?On 17 June 2017 at 21:20, Tom Smith <tomsmith1989sk@gmail.com> wrote:If you change your codePreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) { with
PreparedStatement ps3 = c.prepareStatement("WITH id_temp AS ( select nextval('transportation_point_id_seq') id ) insert into transportation_point values ((select id from id_temp), 'foo')", new String[]{"id"} )) { You might see the issue.basically, I was using a CTE (id_temp) to generate the ID value and then I select that value from CTE for insert in a single SQL.I just tested again with two driver version swapping back and forth and the 42 series always produced error due to empty result setOn Sat, Jun 17, 2017 at 6:25 PM, Dave Cramer <pg@fastcrypt.com> wrote:I just tested this:public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:543 2/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}and it worked fine. This is using the latest code from githubOn 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks
The actual CTE is a complex one.
with call like c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
Is the checking like below necessary at all (from your provided link)? why was it there in the place? Since caller already provides new String[]{} arg, driver should just pass it along.
It is up to caller to ensure correct SQL.
if (currentCommandType != SqlCommandType.INSERT
&& currentCommandType != SqlCommandType.UPDATE |
&& currentCommandType != SqlCommandType.DELETE) { |
return false; |
On Sun, Jun 18, 2017 at 7:17 AM, Dave Cramer <pg@fastcrypt.com> wrote:
OK, so thiswould appear to be where we are getting tripped up. Now knowing that the CTE is going to insert something is going to be tricky to figure out.Just curious but why are you using a CTE to do something relatively simple ?On 17 June 2017 at 21:20, Tom Smith <tomsmith1989sk@gmail.com> wrote:If you change your codePreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) { with
PreparedStatement ps3 = c.prepareStatement("WITH id_temp AS ( select nextval('transportation_point_id_seq') id ) insert into transportation_point values ((select id from id_temp), 'foo')", new String[]{"id"} )) { You might see the issue.basically, I was using a CTE (id_temp) to generate the ID value and then I select that value from CTE for insert in a single SQL.I just tested again with two driver version swapping back and forth and the 42 series always produced error due to empty result setOn Sat, Jun 17, 2017 at 6:25 PM, Dave Cramer <pg@fastcrypt.com> wrote:I just tested this:public class TestReturning {
public static void main(String[] args) throws Exception {
try(Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:543 2/test", "test", "");
Statement stmt = c.createStatement())
{
stmt.execute("create table if not exists transportation_point(id serial primary key, address text, returning_allowed bool)");
try (
PreparedStatement ps3 = c.prepareStatement("insert into transportation_point values (nextval('transportation_point_id_seq'), 'foo')", new String[]{"id"} )) {
ps3.executeUpdate();
ResultSet rs3 = ps3.getGeneratedKeys();
if (rs3.next()) {
System.out.println("New Id: " + rs3.getInt(1));
}
else {
throw new RuntimeException("No Generated Keys for ps3");
}
}
finally {
stmt.execute("drop TABLE transportation_point");
stmt.close();
}
}
}
}and it worked fine. This is using the latest code from githubOn 17 June 2017 at 16:53, Dave Cramer <pg@fastcrypt.com> wrote:Certainly not intended. Thanks for the report!On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk@gmail.com> wrote:pstmt.executeUpdate()Something like belowsqlInsert = "insert into aTable ((select nextval()), "stringdata")
pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
resultSet = pstmt.getGeneratedKeys() ---> resultset is empty using 42 series, but one row returned using 94. seriesOn Sat, Jun 17, 2017 at 2:58 PM, Jorge Solórzano <jorsol@gmail.com> wrote:Hello Tom:
Could you please provide an example code that reproduce the issue?Jorge SolórzanoOn Sat, Jun 17, 2017 at 10:38 AM, Tom Smith <tomsmith1989sk@gmail.com> wrote:I downgraded to 9.4 drive and it worked again. It is a bit shocking.I just upgraded to 42 series latest, it is not getting the resultset anymore.Hello:and get the expected resultset after insertion using 9.4 series driver.
I have a working call using
connection.prepareStatement(sqlInsert, generatedIds) Is it a bug or change?Thanks