Description:
Trying to fetch data from a MySQL server into an ADO recordset using ASP or VBScript.
The user has access to the database server, but does not have access to the table in question.
The error message returned is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.
The error that is occurring is in fact an 'access denied' error, and thus the correct error message should be:
Microsoft OLE DB Provider for ODBC Drivers error '80040e30'
[MySQL][ODBC 3.51 Driver][mysqld-4.0.21-nt]Access denied for user: '<username>@%' to database '<dbname>'
The correct error message is actually returned/shown when using adOpenForwardOnly (and adOpenUnspecified), in all other cases the wrong error message is returned.
How to repeat:
1. Create a database 'xxxdb' with a table 'xxxtable'.
2. Create a user 'xxxuser' with password 'xxxpwd' which has access to the server, but not to any tables ('N' in all the enum columns in the mysql.user table).
3. Flush privileges on the server.
4. Copy/paste the following into a file named eg. "test.vbs":
'========================================
Const adOpenStatic = 3
Const adOpenDynamic = 2
Const adOpenKeyset = 1
Const adOpenForwardOnly = 0
Const adOpenUnspecified = -1
Const adLockBatchOptimistic = 4
Const adLockOptimistic = 3
Const adLockPessimistic = 2
Const adLockReadOnly = 1
Const adLockUnspecified = -1
Dim connStr, connObj, sql, rs
' Initialization stuff. Insert server hostname/ip below.
connStr = "Driver={MySQL ODBC 3.51 Driver};Server=<insert server here>;UID=xxxuser;PWD=xxxpwd;DATABASE=xxxdb;OPTION=3"
Set connObj = CreateObject("ADODB.Connection")
connObj.Open connStr
sql = "SELECT * FROM xxxtable"
Set rs = CreateObject("ADODB.RecordSet")
On Error Resume Next
' == This command fails and gives an appropriate error message.
rs.Open sql, connObj, adOpenForwardOnly, adLockReadOnly
If Err.Number <> 0 Then
MsgBox "RecordSet open failed. This message should be ok:" & vbNewLine & vbNewLine & Err.Description
Err.Clear
End If
rs.Close
' == This command fails and gives a wrong error message.
rs.Open sql, connObj, adOpenDynamic, adLockReadOnly
If Err.Number <> 0 Then
MsgBox "RecordSet open failed. This message should be wrong:" & vbNewLine & vbNewLine & Err.Description
Err.Clear
End If
rs.Close
connObj.Close
MsgBox "Test done."
'========================================
5. Execute the script using 'wscript' or 'cscript' and observe the error messages shown.
Suggested fix:
Fix in driver or mention in manual.
Description: Trying to fetch data from a MySQL server into an ADO recordset using ASP or VBScript. The user has access to the database server, but does not have access to the table in question. The error message returned is: Microsoft OLE DB Provider for ODBC Drivers error '80040e21' ODBC driver does not support the requested properties. The error that is occurring is in fact an 'access denied' error, and thus the correct error message should be: Microsoft OLE DB Provider for ODBC Drivers error '80040e30' [MySQL][ODBC 3.51 Driver][mysqld-4.0.21-nt]Access denied for user: '<username>@%' to database '<dbname>' The correct error message is actually returned/shown when using adOpenForwardOnly (and adOpenUnspecified), in all other cases the wrong error message is returned. How to repeat: 1. Create a database 'xxxdb' with a table 'xxxtable'. 2. Create a user 'xxxuser' with password 'xxxpwd' which has access to the server, but not to any tables ('N' in all the enum columns in the mysql.user table). 3. Flush privileges on the server. 4. Copy/paste the following into a file named eg. "test.vbs": '======================================== Const adOpenStatic = 3 Const adOpenDynamic = 2 Const adOpenKeyset = 1 Const adOpenForwardOnly = 0 Const adOpenUnspecified = -1 Const adLockBatchOptimistic = 4 Const adLockOptimistic = 3 Const adLockPessimistic = 2 Const adLockReadOnly = 1 Const adLockUnspecified = -1 Dim connStr, connObj, sql, rs ' Initialization stuff. Insert server hostname/ip below. connStr = "Driver={MySQL ODBC 3.51 Driver};Server=<insert server here>;UID=xxxuser;PWD=xxxpwd;DATABASE=xxxdb;OPTION=3" Set connObj = CreateObject("ADODB.Connection") connObj.Open connStr sql = "SELECT * FROM xxxtable" Set rs = CreateObject("ADODB.RecordSet") On Error Resume Next ' == This command fails and gives an appropriate error message. rs.Open sql, connObj, adOpenForwardOnly, adLockReadOnly If Err.Number <> 0 Then MsgBox "RecordSet open failed. This message should be ok:" & vbNewLine & vbNewLine & Err.Description Err.Clear End If rs.Close ' == This command fails and gives a wrong error message. rs.Open sql, connObj, adOpenDynamic, adLockReadOnly If Err.Number <> 0 Then MsgBox "RecordSet open failed. This message should be wrong:" & vbNewLine & vbNewLine & Err.Description Err.Clear End If rs.Close connObj.Close MsgBox "Test done." '======================================== 5. Execute the script using 'wscript' or 'cscript' and observe the error messages shown. Suggested fix: Fix in driver or mention in manual.