[I] Firebird Proxy rejects preparation of a DECFLOAT input parameter [shardingsphere]

["makssent (via GitHub)" <gi...@apache.org>]

makssent opened a new issue, #40412:
URL: https://github.com/apache/shardingsphere/issues/40412

   ### Before reporting a bug
   
   - [x] I have searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
   - [x] I have read the [ShardingSphere documentation](https://shardingsphere.apache.org/document/current/en/overview).
   
   ### ShardingSphere version
   
   master, commit [`68f85339472`](https://github.com/apache/shardingsphere/commit/68f85339472dc0496c28a6177a1ba0c22c2c3c18)
   
   ### ShardingSphere project
   
   ShardingSphere-Proxy
   
   ### Expected behavior
   
   Preparing an INSERT with a DECFLOAT(16) or DECFLOAT(34) parameter should succeed and allow the value 1.5 to be inserted.
   
   ### Actual behavior
   
   The same operation was executed directly on the server and through the proxy.
   
   | Steps | Firebird server | Proxy |
   | --- | --- | --- |
   | Prepare separate INSERT statements with DECFLOAT(16) and DECFLOAT(34) parameters and insert 1.5 | Both prepare and insert successfully | Both preparations fail with ``Unknown exception. More details: java.lang.IllegalArgumentException: Can not find JDBC type `%d` in column type [2015]`` |
   
   The application cannot execute the prepared INSERT. The related #40230 concerns decimal wire widths rather than this JDBC type mapping.
   
   ### Reason analysis
   
   [`FirebirdReturnColumnPacket#write`](https://github.com/apache/shardingsphere/blob/68f85339472dc0496c28a6177a1ba0c22c2c3c18/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/prepare/FirebirdReturnColumnPacket.java#L57-L59) calls [`valueOfJDBCType`](https://github.com/apache/shardingsphere/blob/68f85339472dc0496c28a6177a1ba0c22c2c3c18/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/FirebirdBinaryColumnType.java#L144-L146), which rejects a type absent from its mapping.
   
   ### Steps to reproduce
   
   Use a Firebird-compatible server and Jaybird 6.0.7. Create the following table:
   
   ```sql
   CREATE TABLE T_DECFLOAT (ID INTEGER NOT NULL PRIMARY KEY, D16 DECFLOAT(16), D34 DECFLOAT(34));
   ```
   
   Start ShardingSphere-Proxy with this configuration:
   
   ```yaml
   # global.yaml
   authority:
     users:
       - user: root@%
         password: root
     privilege:
       type: ALL_PERMITTED
   props:
     proxy-frontend-database-protocol-type: Firebird
   ```
   
   ```yaml
   # database-firebird.yaml
   databaseName: firebird_db
   dataSources:
     ds_0:
       url: jdbc:firebirdsql://<host>/<database file>?encoding=UTF8
       username: <user>
       password: <password>
   rules:
     - !SINGLE
       tables:
         - "*.*"
   ```
   
   Set the environment variables used by the example for the server and proxy connections, run the example once against each target and compare the results. Remove the rows with ID 913 and 914, or ID 3903 for the batch case, before repeating the case.
   
   ### Example code
   
   Set TARGET_URL, TARGET_USER and TARGET_PASSWORD for the selected target. For the numeric example also set SERVER_URL, SERVER_USER and SERVER_PASSWORD for the direct read.
   
   ```java
   import java.math.BigDecimal;
   import java.sql.*;
   
   public class DecfloatParameter {
       public static void main(String[] args) throws Exception {
           try (Connection target = DriverManager.getConnection(System.getenv("TARGET_URL"), System.getenv("TARGET_USER"), System.getenv("TARGET_PASSWORD"))) {
               runCase(target, "D16", 913);
               runCase(target, "D34", 914);
           }
       }
   
       // Direct: each prepare and insert succeeds. Proxy: each prepare fails with an unknown exception.
       static void runCase(Connection target, String column, int id) {
           try (PreparedStatement statement = target.prepareStatement("INSERT INTO T_DECFLOAT (ID, " + column + ") VALUES (?, ?)")) {
               statement.setInt(1, id);
               statement.setBigDecimal(2, new BigDecimal("1.5"));
               System.out.println(column + " inserted=" + statement.executeUpdate());
           } catch (SQLException ex) {
               System.out.println(column + " error=" + ex.getMessage());
           }
       }
   }
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org