Re: JDBC drive 42 release breaks seriously for RETURNING?

OK, so this

https://github.com/pgjdbc/pgjdbc/blob/2958cc22a6a00609bfbb6eb17d4e401dfe433123/pgjdbc/src/main/java/org/postgresql/core/Parser.java#L272

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 ?

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 17 June 2017 at 21:20, Tom Smith <tomsmith1989sk(at)gmail(dot)com> wrote:

> 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
>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>