I am supporting an ASP classic application that uses ADODB.Recordset objects to interact with an Oracle database through an ODBC connection. The application works as desired in our QA environment, but we are encountering problems using the Recordset.Delete and Recordset.Update functions in our production environment. I'm describing the Recordset.Delete scenario below because it is simpler to explain, but the Update scenario is similar.
My question is: whether anyone can explain the problematic behavior or can suggest ways to address it.
The following code is a slightly redacted extract from a procedure that allows the user to select and delete a record from any one of a number of tables.
Dim dbConnection
Dim objRS
Dim table
Dim filter_key
Set dbConnection = openDBConnection()
filter_key = Request.Form("pk")
table = getValidTableName(Request.Form("table"))
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open table, dbConnection, , adLockOptimistic, adCmdTable
objRS.Filter = filter_key
objRS.Delete
The target table and the record to be deleted are passed in the "table" and "pk" form parameters. For example, they could be defined as
- table: PERSON
- pk: person_id = 12345
This code works as desired in QA. The objRS.Delete line fails in production with the error
80004005|[Oracle][ODBC]Wrong_number_of_parameters
The Oracle client is the same in both environments, but the msado15.dlls containing the ADODB.Recordset have slightly different versions: QA - 10.0.14393.4169, Prod - 10.0.17763.1697. My current hypothesis is that the version difference explains the difference in behavior and accounts for the errors in production.
I've enabled ODBC tracing, and while I'm not positive that I'm intepreting the output correctly, it seems like the generated DELETE statement includes a WHERE clause that is not limited to the simple clause assigned as a filter_key (e.g. WHERE person_id = 12345). Instead, the WHERE clause appears to include every column in the target table, as in
WHERE (LAST_NAME=? AND FIRST_NAME=? AND ...
In QA, the parameter values are all being bound properly. In production, it seems like there are calls to bind each parameter value, but the calls fail to increment the ParameterNumber properly (ParameterNumber described here - https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlbindparameter-function?view=sql-server-ver16). That is, it appears that there are multiple calls to bind different values, but each call binds the value to ParameterNumber 1, leaving the remaining parameters unbound.
Again, I'm not positive that the interpretation above is correct. If so, this seems like a bug. However, I don't know ASP or ADODB well enough to know if instead the application is doing something wrong, so any pointers would be appreciated.
