Message-ID: <gh-pgjdbc-pgjdbc-2538@github.com>
From: "heimburgerj2 (@heimburgerj2)" <noreply+heimburgerj2@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Tue, 07 Jun 2022 13:48:10 +0000
Subject: [pgjdbc/pgjdbc] issue #2538: Preceding comments in SQL `CALL` statements prevent setting of OUT parameters for stored procedures / functions
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Id: 97025721
X-GitHub-Author-Login: heimburgerj2
X-GitHub-Issue: 2538
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-State: open
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/2538
Content-Type: text/plain; charset=utf-8

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