Bug #8285 OpenSchema does not work with Ado-Connection
Submitted: 3 Feb 2005 14:38 Modified: 5 Feb 2005 16:09
Reporter: Hans Simon Email Updates:
Status: Duplicate Impact on me:
Category:Connector / ODBC Severity:S2 (Serious)
Version:4.1.9 OS:Microsoft Windows (Win2K)
Assigned to: CPU Architecture:Any

[3 Feb 2005 14:38] Hans Simon
Hello people on MySql! Im new in MySql-DataBase-World but I'm familiar with some other great db-managent-systems. It's now the first time I'm checking, if mysql will suid our needs too. But there is a great problem:
We're developing our application with Microsoft Visual-basic 6.0 by Using ADO 2.7. 
Now I'm testing with mysql-server 4.1.9 by using your odbc-connector 3.5.1.

I do not get any information about columns or indexes by using the Ado-Command Openschema with option adSchemaColumns or adSchemaIndexes (I did not test adSchemaForeignKeys).

There is allready a bug reported (#6033) as non-critical since Oktober 2004. But i think this is critical! This works on all other dbms (we run our applications on sybase Asa 7,8 and 9, on MS SqlServer, on Access and on Oracle. I'm a little bit sad now. Can this be on mySql?

How to repeat:
Dim cn As ADODB.Connection
Dim rs AS ADODB.RecordSet

Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DSN=mySql" 'Expecting you have defined an ODBC-Connection

Set rs = cn.OpenSchema(adSchemaColumns,  Array(Empty, Empty, Empty, Empty))

If rs.EOF And rs.BOF Then
  Debug.Print "not good, when there are tables in the db..."
End If

Set rs = cn.OpenSchema(adSchemaIndexes,  Array(Empty, Empty, Empty, Empty, Empty))

If rs.EOF And rs.BOF Then
  Debug.Print "not good when there are indexes in the db..."
End If

Suggested fix:
The OpenSchema-Command must supply all informations (All columns, all indexes, all foreign keys etc.), when all array-parameters are empty! These parameters are constraints, to get less information only when i want that. When parameters are not used or they are empty, every information must be supplyd.

All other dbms do practise this behaviour.

If this would not work, i would have to do hard work to get our application run on mySQL.
[5 Feb 2005 16:09] Jorge del Conde
Please try our upcomming release of MyODBC.  Many bugs have been fixed.
[7 Feb 2005 7:17] Hans Simon
when will this release be available? 
There is another problem in the current release:
When you use OpenSchema with adSchemaForeignKeys the tablenames and fieldnames of the foreign keys are supplyd in quotationmarks (that is wrong).