Re: JDBC drive 42 release breaks seriously for RETURNING?

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(at)fastcrypt(dot)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 github
>
>
> Dave Cramer
>
> davec(at)postgresintl(dot)com
> www.postgresintl.com
>
> On 17 June 2017 at 16:53, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
>> Certainly not intended. Thanks for the report!
>>
>> Dave Cramer
>>
>> davec(at)postgresintl(dot)com
>> www.postgresintl.com
>>
>> On 17 June 2017 at 16:40, Tom Smith <tomsmith1989sk(at)gmail(dot)com> wrote:
>>
>>> Something like below
>>>
>>> sqlInsert = "insert into aTable ((select nextval()), "stringdata")
>>> pstmt connection.prepareStatement(sqlInsert, new String[]{"id"})
>>> pstmt.executeUpdate()
>>> 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(at)gmail(dot)com>
>>> wrote:
>>>
>>>> Hello Tom:
>>>>
>>>> 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(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>> Hello:
>>>>>
>>>>>
>>>>> I have a working call using
>>>>>
>>>>> connection.prepareStatement(sqlInsert, generatedIds)
>>>>>
>>>>> and get the expected resultset after insertion using 9.4 series
>>>>> driver.
>>>>> I just upgraded to 42 series latest, it is not getting the resultset
>>>>> anymore.
>>>>>
>>>>> I downgraded to 9.4 drive and it worked again. It is a bit shocking.
>>>>> Is it a bug or change?
>>>>>
>>>>> Thanks
>>>>>
>>>>>
>>>>
>>>
>>
>