Subject: [pgjdbc/pgjdbc] issue #3426: 42.7.4 introduced error when binding java.io.FileInputStream

From: "tract-rob (@tract-rob)" <[email-masked]>
Date: Mon, 28 Oct 2024 23:27:39 +0000

Please read https://stackoverflow.com/help/minimal-reproducible-example 

**Describe the issue**
Production code has been running error free since 2016, with the only change being regular driver upgrades.  Upgrading from version 42.7.4 causes code to error...rolling back to 42.7.3 fixes the issue.

The code is binding a java.io.FileInputStream via Anorm 2.5.3. Looking at Anorm source code it looks like it's bound using PreparedStatement.setBinaryStream.  The exception returned when the statement is executed is:

org.postgresql.util.PSQLException: Unable to bind parameter values for statement.
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:394)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:155)
	at com.zaxxer.hikari.proxy.PreparedStatementProxy.executeUpdate(PreparedStatementProxy.java:61)
	at com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.executeUpdate(PreparedStatementJavassistProxy.java)
...

**Driver Version?** 
42.7.4

**Java Version?**
openjdk version "1.8.0_412"

**OS Version?**
Amazon Linux 2

**PostgreSQL Version?**
15.8

**To Reproduce**
Steps to reproduce the behaviour:

1. Bind a java.io.FileInputStream object to e PreparedStatement using setBinaryStream.
2. call executeUpdate

**Expected behaviour**
A clear and concise description of what you expected to happen.
And what actually happens

For the last 8 years it's always successfully inserted the file into the database table.  After driver upgrade, it just throws an error "Unable to bind parameter values for statement." 

**Logs**
If possible PostgreSQL logs surrounding the occurrence of the issue
Additionally logs from the driver can be obtained adding

Using the following template code make sure the bug can be replicated in the driver alone.
```
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class TestNullsFirst {
    public static void main(String []args) throws Exception {

        String url = "jdbc:postgresql://localhost:5432/test";

        Properties props = new Properties();
        props.setProperty("user", "test");
        props.setProperty("password", "test");
        try ( Connection conn = DriverManager.getConnection(url, props) ){
            try ( Statement statement = conn.createStatement() ) {
                try (ResultSet rs = statement.executeQuery( "select lastname from users order by lastname asc nulls first") ){
                    if (rs.next())
                        System.out.println( "Get String: " + rs.getString(1));
                }
            }
        }
    }
}
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 29 Oct 2024 10:01:16 +0000

thanks for the report

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 29 Oct 2024 11:26:05 +0000

I cannot replicate this with
```
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class TestFileInputStream {
    public static void main(String[] args) throws Exception {

        try (Connection con = DriverManager.getConnection(
                "jdbc:postgresql://localhost/test", "test", "")) {
            con.createStatement().execute("create temporary table streamtable (bin bytea, str text)");
            FileInputStream fis = new FileInputStream("./pom.xml");
            try (PreparedStatement pstmt =
                         con.prepareStatement("INSERT INTO streamtable (bin,str) VALUES (?,?)")) {

                pstmt.setBinaryStream(1, fis);
                pstmt.setString(2, null);
                pstmt.executeUpdate();

            }
        }
    }
}
```

Do we have any idea what else is going on ?

---

From: "mEkdal (@mEkdal)" <[email-masked]>
Date: Tue, 12 Nov 2024 15:10:25 +0000

Your program works with both 42.7.3 and 42.7.4 in my environment.

I have tweak the program a bit and the new test program only works with 42.7.3 and older.

The Statement causing the problem is one I have used a lot in many programs. so I wold prefer not to rewrite all of them

---

From: "mEkdal (@mEkdal)" <[email-masked]>
Date: Tue, 12 Nov 2024 15:10:31 +0000

package Jvakt;

public class test07 {

    try (Connection con = DriverManager.getConnection(
            "jdbc:postgresql://localhost/Jvakt", "test", "test")) {
        Statement stmt = con.createStatement(ResultSet.CONCUR_READ_ONLY,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CLOSE_CURSORS_AT_COMMIT );
        
        	stmt.executeQuery("create table streamtable (bin text, str text)");
       	
        }
    }
}

---

From: "mEkdal (@mEkdal)" <[email-masked]>
Date: Tue, 04 Feb 2025 20:15:41 +0000

sadly the same problem remains in 42.7.5  :-(

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 06 Feb 2025 11:53:34 +0000

So I had a look at this today. I get a different error.
`Unknown value for ResultSet type`
Is that what you are seeing ?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 06 Feb 2025 13:08:18 +0000

Looks like 

'''
    // validation check for allowed values of resultset type
    if (rsType != ResultSet.TYPE_FORWARD_ONLY && rsType != ResultSet.TYPE_SCROLL_INSENSITIVE && rsType != ResultSet.TYPE_SCROLL_SENSITIVE) {
      throw new PSQLException(GT.tr("Unknown value for ResultSet type"),
          PSQLState.INVALID_PARAMETER_VALUE);
    }
'''
introduced in 
https://github.com/pgjdbc/pgjdbc/commit/e0a614b79fc39a01fc0b7911955f5bcb47d0179b 
is throwing the exception.

However looking at your code it seems that you have an error which this picks up. If
```
Statement stmt = con.createStatement(ResultSet.CONCUR_READ_ONLY,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CLOSE_CURSORS_AT_COMMIT );
``` 
is what you have the concurrency and resultset type are in the wrong positions. It should be

```
Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT );
```

Let me know .

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 28 May 2025 19:35:50 +0000

@tract-rob if you still observe the issue, please report the full stacktrace. There should be a "caused by" message for `PSQLException: Unable to bind parameter values for statement.`

---

From: "mEkdal (@mEkdal)" <[email-masked]>
Date: Thu, 29 May 2025 10:54:24 +0000

I tested the 42.7.6 and the same problem that started in 42.7.4 was still present. 

However, changing the order of the parameters from 
_conn.createStatement(ResultSet.CONCUR_READ_ONLY,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CLOSE_CURSORS_AT_COMMIT );_ 
to
_conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY,ResultSet.CLOSE_CURSORS_AT_COMMIT );_ 

makes it work!

Is this the solution?  

In that case I will have to scan my programs and change all of them, because I suspect the "wrong" order is every where.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 29 May 2025 11:14:37 +0000

Seems that is the API https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#createStatement-int-int-int-

---

From: "mEkdal (@mEkdal)" <[email-masked]>
Date: Thu, 29 May 2025 11:27:45 +0000

I suppose you are right. 

I will use this order:
int resultSetType,
int resultSetConcurrency,
int resultSetHoldability

I think I copied it from a google hit way back, so I suppose more persons will have problem when the order is enforced,

I found I have about 35 programs to change, no big deal.

Thanks

---

From: "mEkdal (@mEkdal)" <[email-masked]>
Date: Mon, 09 Jun 2025 08:43:57 +0000

Changed the order of the parameters of the createStatement to conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY,ResultSet.HOLD_CURSORS_OVER_COMMIT );
and now it works.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 09 Jun 2025 09:17:19 +0000

Thanks for the feedback