Re: Bogus rows in schema rowset

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Altman (rda_at_nospam.com)
Date: 05/19/04 

Date: Tue, 18 May 2004 19:12:56 -0700

Hi Kevin,

Here's another data point: When I try to get to the Oracle database using
Microsoft Access via an ODBC connection, Access is able to display the data
in most of the tables, but it chokes when it tries to display the data in
certain tables. The error message is completely useless. (Sorry, I'm on
the road, so I can't reproduce the issue and get the exact error message.)
My suspicion (not yet confirmed) is that the tables that Access chokes on
are also the tables that exhibit this "bogus rows in the schema dataset"
problem.

Curiously, Access has no problem displaying any of the tables in "design"
view. But, interestingly enough, the field properties (such as Text field
max length) sometimes differ from the data in the schema rowset. My
suspicion is that Access uses some other mechanism for getting the field
properties from the Oracle provider...

Anyway, here are bits and pieces of cut-and-pasted code:

Const ORACLE_CONNECTION_STRING As String = "Provider={0};Data
Source={1};User ID={2};Password={3}"
Const ORACLE_PROVIDER As String = "MSDAORA"
Dim cnOra As New OleDbConnection

' Open the Oracle database
cnOra.ConnectionString = String.Format( _
  ORACLE_CONNECTION_STRING, _
  ORACLE_PROVIDER, _
  OracleServerPath, OracleUserID, OraclePassword)

' Get the schema rowset
dim SchemaDataTable as DataTable = cnOra.GetOleDbSchemaTable( _
  OleDbSchemaGuid.Columns, _
  New Object() {Nothing, Nothing, tableName, Nothing})

 - Bob

"Kevin Yu [MSFT]" <v-kevy@online.microsoft.com> wrote in message
news:OVfBL7JPEHA.3080@cpmsftngxa10.phx.gbl...
> Hi Bob,
>
> First of all, I would like to confirm my understanding of your issue. From
> your description, I understand that you are getting bogus rows when using
> OleDbConnection.GetOleDbSchemaTable to retrieve column information for a
> table. If there is any misunderstanding, please feel free to let me know.
>
> Could you show us the code that how you get the schema? Since
> GetOleDbSchemaTable can get information for databases, tables and columns,
> I would like to see your code to retrieve the information for column.
> Please also check if there is another identical table in the database, so
> that all the columns for both tables might be get from the server.
>
> HTH.
>
> Kevin Yu
> =======
> "This posting is provided "AS IS" with no warranties, and confers no
> rights."
>

Relevant Pages

  • Re: ODBC cannot access Oracle tables under different schemas in one session
    ... >Access / Oracle problem, ... >referring to the proper server / SID of every branch. ... >table, when i try to query the second table under another schema, ODBC ... >shift to another schema and query their tables, ...
    (comp.databases.oracle.misc)
  • Re: UTL_FILE Permissions
    ... The schema APP has already been granted READ and WRITE to the APP_DIR, ... I used the usermod command so that oracle user has a secondary group ... it should be possible for your upload ...
    (comp.databases.oracle.server)
  • Re: UTL_FILE Permissions
    ... The schema APP has already been granted READ and WRITE to the APP_DIR, ... I used the usermod command so that oracle user has a secondary group ...
    (comp.databases.oracle.server)
  • Re: Intermittent problem in data transfer MSAccess to Oracle using VB
    ... the row it fails on has always had a numeric value in the Hours column. ... It retrieves data from a MSAccess database and writes to an Oracle database. ... Dim MSAccessConn As String ... In other words, it has always written to the log file, connected to the Access database, returned a recordset, connected to the Oracle database and deleted all rows from DATABASE1.TABLE1 with no failures. ...
    (microsoft.public.vb.general.discussion)
  • Re: ODBC cannot access Oracle tables under different schemas in one session
    ... >>Access / Oracle problem, ... >>referring to the proper server / SID of every branch. ... >>table, when i try to query the second table under another schema, ODBC ... >>shift to another schema and query their tables, ...
    (comp.databases.oracle.misc)