🐞 Prepared statements send the wrong types as parameters to the server

[Nicoretti]

Summary

Prepared statements using the websocket based dialect seem to not send the correct types required/expected by the server.

Reproducing the Issue

Reproducible: always

0. Remove the @ISSUE341 marker from the tests in test/sqlalchemy/test_suite.py

1. Run the SQLA compliance test suit with the websocket based dialect as target
   pytest --log-debug=DEBUG --dropfirst --db exasol-websocket test/integration/sqlalchemy

2. Have a look at the following tests

• test/integration/sqlalchemy/test_suite.py::CastTypeDecoratorTest_exasol+exasol_driver_websocket_dbapi2::test_special_type - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
• test/integration/sqlalchemy/test_suite.py::ExistsTest_exasol+exasol_driver_websocket_dbapi2::test_select_exists - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
• test/integration/sqlalchemy/test_suite.py::ExistsTest_exasol+exasol_driver_websocket_dbapi2::test_select_exists_false - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
• test/integration/sqlalchemy/test_suite.py::InsertBehaviorTest_exasol+exasol_driver_websocket_dbapi2::test_insert_from_select - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
• test/integration/sqlalchemy/test_suite.py::InsertBehaviorTest_exasol+exasol_driver_websocket_dbapi2::test_insert_from_select_with_defaults - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
• test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_float_as_decimal - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
• test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_float_as_float - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)
• test/integration/sqlalchemy/test_suite.py::NumericTest_exasol+exasol_driver_websocket_dbapi2::test_float_custom_scale - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)

Expected Behaviour

Statements (test cases) get executed successfully.

Actual Behaviour

Statements (test cases) fail due to invalid types for the parameters are sent to the server.

e.g.:

getString: JSON value is not a string. (Session: 1766587743101452288)

Root Cause

Not a 100% identified yet

Context

This is a snippet of the client/server conversation for test case

• test/integration/sqlalchemy/test_suite.py::CastTypeDecoratorTest_exasol+exasol_driver_websocket_dbapi2::test_special_type - sqlalchemy.exc.DBAPIError: (exasol.driver.websocket._errors.Error)

...

127.0.0.1:57568 -> 127.0.0.1:8888
{"command": "execute", "sqlText": "CREATE TABLE t (\n\tx VARCHAR(50)\n)"}

127.0.0.1:8888 -> 127.0.0.1:57568
{"status":"ok","responseData":{"results":[{"resultType":"rowCount","rowCount":0}],"numResults":1}}

127.0.0.1:57568 -> 127.0.0.1:8888
{"command": "createPreparedStatement", "sqlText": "INSERT INTO t (x) VALUES (CAST(? AS VARCHAR(50)))"}

127.0.0.1:8888 -> 127.0.0.1:57568
{"status":"ok","responseData":{"statementHandle":7,"parameterData":{"numColumns":1,"columns":[{"name":"","dataType":{"type":"VARCHAR","size":2000000,"characterSet":"UTF8"}}]},"results":[{"resultType":"rowCount","rowCount":0}],

127.0.0.1:57568 -> 127.0.0.1:8888
{"command": "executePreparedStatement", "statementHandle": 7, "numColumns": 1, "numRows": 3, "columns": [{"name": "", "dataType": {"type": "VARCHAR", "size": 2000000, "characterSet": "UTF8"}}], "data": [[1, 2, 3]]}

127.0.0.1:8888 -> 127.0.0.1:57568
{"status":"error","exception":{"text":"getString: JSON value is not a string. (Session: 1765399198642536448)","sqlCode":"00000"}}

127.0.0.1:57568 -> 127.0.0.1:8888
{"command": "closePreparedStatement", "statementHandle": 7}

127.0.0.1:8888 -> 127.0.0.1:57568
{"status":"ok"}

...

Related Issues (optional)

• 🐞 Various SQLA compliance tests are failing for the websocket based dialect #342

[Nicoretti]

consider exposing/adding the debug feature of pyexasol, see here.