ORA-01745: invalid host/bind variable name on some ESQL parameters

[Dennis Wanke]

The following Entity SQL query causes "ORA-01745: invalid host/bind variable name":

Code: Select all

SELECT post FROM ForumData.Posts AS post WHERE post.User = @user

The problem is the parameter name that it is not properly escaped when translating to native SQL, in this case :"user" instead of :user.

Note, that ESQL doesn't provide any means for escaping query parameters.

[Shalex]

We have reproduced the issue with :user parameter. We will investigate it and notify you about the result. As a workaround, please rename your parameter.

[Shalex]

The bug with using reserved words in the names of ESQL parameters is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

New build of dotConnect for Oracle 8.3.135 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=29348.

[Dennis Wanke]

The ORA-01745 is gone but now I'm getting "ORA-01036 illegal variable name/number" when using "user" as a parameter name. It seems that there is simply no way to use such query parameters in Oracle (i.e. :user). If so, the fix have to be a bit more involved, e.g. prefixing parameters with "p_" or so under the hood. If it's not feasible, it would be better to rollback the fix, since ORA-01745 is more informative than ORA-01036.

[Shalex]

1. Please send us a small test project with the corresponding DDL/DML script for reproducing.
2. Specify your connection mode (Direct or OCI), the versions of your Oracle server and Oracle client.

[Dennis Wanke]

I sent you a test project.

[Shalex]

We have reproduced the issue and are investigating it. It is specific to Direct mode. As a workaround, please use the OCI mode (via Oracle client).

[Shalex]

Please try using the latest (8.4.274) build of dotConnect for Oracle. The problem is fixed.