Re: JDBC drive 42 release breaks seriously for RETURNING?

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(at)fastcrypt(dot)com> wrote:

> OK, so this
>
> https://github.com/pgjdbc/pgjdbc/blob/2958cc22a6a00609bfbb6eb17d4e40
> 1dfe433123/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
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>