DPY-4008: Bind variable not found if between two comment blocks #105
Description
- What versions are you using?
platform.platform: Linux-5.4.0-131-generic-x86_64-with-glibc2.31
sys.maxsize > 2**32: True
platform.python_version: 3.9.15
oracledb.__version__: 1.1.1
-
Is it an error or a hang or a crash?
error -
What error(s) or behavior you are seeing?
oracledb.exceptions.DatabaseError: DPY-4008: no bind placeholder named ":test_bind_var" was found in the SQL text -
Does your application call init_oracle_client()?
No -
Include a runnable Python script that shows the problem.
import oracledb
connection = oracledb.connect(user="<USER>", password="<PASSWORD>", dsn="<DSN>")
cur = connection.cursor()
sql = """
select /* comment 1 */
:test_bind_var as test_value
/* comment 2 */
from dual
"""
parameters = {'test_bind_var': '2022-11-23'}
cur.execute(statement=sql, parameters=parameters)
cur.close()
connection.close()For the example to work one needs an oracle db and has to replace <USER>, <PASSWORD> and <DSN>.
The error seems to lie in oracledb/impl/thin/statement.pyx, where the regular expression should remove all comments. Since this expressions is greedy the afore mentioned code line removes everything from the start of the first comment to the end of the last comment.
If the bind variable is only referenced between two comment blocks it is thus removed from the sql before the search for bind variables.
This can be fixed by making the pattern between start and stop of the comment to be non greedy, i.e.:
sql = re.sub(r"/\*[\S\n ]+?\*/", "", sql)I would be happy to provide a pull request for this issue as a reference, however, I won't be able to sign the OCA.