pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

[pgjdbc/pgjdbc] issue #2538: Preceding comments in SQL `CALL` statements prevent setting of OUT parameters for stored procedures / functions
2+ messages / 2 participants
[nested] [flat]

* [pgjdbc/pgjdbc] issue #2538: Preceding comments in SQL `CALL` statements prevent setting of OUT parameters for stored procedures / functions
@ 2022-06-07 13:48 "heimburgerj2 (@heimburgerj2)" <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: heimburgerj2 (@heimburgerj2) @ 2022-06-07 13:48 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

**I'm submitting a ...**

  <!--- What kind of an issue is this? Put an `x` in all the boxes that apply: -->
- [x] bug report
- [ ] feature request
    

**Describe the issue**
Preceding comments in SQL `CALL` statements prevent setting of OUT parameters for stored procedures / functions

**Driver Version?** 
42.3.6

**Java Version?**
- Java 8 (1.8.0_322)
- Java 17 (17.0.3)

**OS Version?**
Ubuntu 22.04.1

**PostgreSQL Version?**
14.3

**To Reproduce**
```JAVA
    @ParameterizedTest
    @CsvSource(value = {
            "call test_procedure(?,?);",
            "/* DeviceTagBatchDAO.generateBatch */ call test_procedure(?,?);",
    }, delimiter = '#')
    void testCallableStatement(final String sqlCall) throws SQLException {
        try (Connection conn = DriverManager.getConnection(postgreSqlContainer.getJdbcUrl(), postgreSqlContainer.getUsername(),
                postgreSqlContainer.getPassword());) {

            final CallableStatement stmt = conn.prepareCall(sqlCall);
            stmt.setInt(2, 100);
            stmt.setString(3, "value");
            stmt.registerOutParameter(1, java.sql.Types.INTEGER);//this will fail for statement with preceding comment
            stmt.executeUpdate();
            final int batchId = stmt.getInt(1);
            LOGGER.info("batch id = {}", batchId);
        }
    }
```

**Expected behaviour**
The out parameter should be registered successfully, no matter whether the SQL contains a comment before the actual statement or not.
Unfortunately this is not the case instead the out registration fails if the SQL contains a comment preceding the actual statement.

**Logs**
```
org.postgresql.util.PSQLException: This statement does not declare an OUT parameter.  Use { ?= call ... } to declare one.
	at org.postgresql.jdbc.PgCallableStatement.registerOutParameter(PgCallableStatement.java:210)
```

**Cause**
The *Parser* (`org.postgresql.core.Parser.modifyJdbcCall`) assumes that the String starts with the SQL statement and does not check for any comments:
```JAVA
      if (state == 1) {
        // Not an escaped syntax.

        // Detect PostgreSQL native CALL.
        // (OUT parameter registration, needed for stored procedures with INOUT arguments, will fail without this)
        i = 0;
        while (i < len && Character.isWhitespace(jdbcSql.charAt(i))) {
          i++; // skip any preceding whitespace
        }
        if (i < len - 5) { // 5 == length of "call" + 1 whitespace
          //Check for CALL followed by whitespace
          char ch = jdbcSql.charAt(i);
          if ((ch == 'c' || ch == 'C') && jdbcSql.substring(i, i + 4).equalsIgnoreCase("call")
               && Character.isWhitespace(jdbcSql.charAt(i + 4))) {
            isFunction = true;
          }
        }
        return new JdbcCallParseInfo(sql, isFunction);
      }
```

See PR #2539

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [pgjdbc/pgjdbc] issue #2538: Preceding comments in SQL `CALL` statements prevent setting of OUT parameters for stored procedures / functions
@ 2025-07-13 23:10 "raminorujov (@raminorujov)" <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: raminorujov (@raminorujov) @ 2025-07-13 23:10 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <[email protected]>

Hi, @davecramer @vlsi @heimburgerj2 could you please, review my PR to fix this issue? https://github.com/pgjdbc/pgjdbc/pull/3727

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2025-07-13 23:10 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-07 13:48 [pgjdbc/pgjdbc] issue #2538: Preceding comments in SQL `CALL` statements prevent setting of OUT parameters for stored procedures / functions "heimburgerj2 (@heimburgerj2)" <[email protected]>
2025-07-13 23:10 Re: [pgjdbc/pgjdbc] issue #2538: Preceding comments in SQL `CALL` statements prevent setting of OUT parameters for stored procedures / functions "raminorujov (@raminorujov)" <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox