pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: heimburgerj2 (@heimburgerj2) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: [pgjdbc/pgjdbc] issue #2538: Preceding comments in SQL `CALL` statements prevent setting of OUT parameters for stored procedures / functions
Date: Tue, 07 Jun 2022 13:48:10 +0000
Message-ID: <[email protected]> (raw)

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

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: github://pgjdbc/pgjdbc
  Cc: [email protected], [email protected]
  Subject: Re: [pgjdbc/pgjdbc] issue #2538: Preceding comments in SQL `CALL` statements prevent setting of OUT parameters for stored procedures / functions
  In-Reply-To: <<[email protected]>>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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