ODBC SQLColumns() and Oracle synonyms....

I am experiencing problems using SQLColumns() against Oracle synonyms. SQLColumns() returns all of the rows in the synonym (as it should) but it also returns rows that used to be in the table. For instance, if the table that is the target of the synonym is defined to have a column TOTAL NUMBER(5) and then that table is dropped and recreated with a column TOTAL VARCHAR2(5), SQLColumns() will return two entries for the TOTAL column in its result set. One with a NUMBER type and the other with a VARCHAR2 type.

However, if I run SQLColumns() against the table that is the target of the synonym, I get the correct results. The problem only manifests itself when an Oracle synonym is used.

The problem is recreatable using either the Microsoft Oracle ODBC Driver or the Oracle 8.1.5 ODBC Driver. I have tested against Oracle 8.0.5 and Oracle 8.1.5 databases and get the same results. I have also tried to do something similar with DB2 5.2 but was unable to recreate the problem.

I am using SQLColumns() to retrieve all columns in that match a pattern so that I can dynamically build my SQL statements based upon which columns the user has defined in their tables. My app calls ODBC directly by using the SQL*() functions. I don't believe there is another way for me to retrieve the names columns in a table (synonym) from ODBC.

Does anyone know how to get SQLColumns() to return only the columns that are currently defined in the table that a synonym points to? Where are these "phantom" columns being stored in Oracle after a table is dropped and recreated?

Regards,

Greg Scarborough Received on Tue Aug 08 2000 - 00:00:00 CDT