GetOleDbSchemaTable question

  • Thread starter Thread starter Burak
  • Start date Start date
B

Burak

Hello,

I am trying to retrieve the list of tables in local oracle database,
"SKILLS".

When I use the following set of commands

strsql = "select TABLE_NAME from user_tables"
objconnection = New OleDbConnection(strConnection)
da = New OleDbDataAdapter(strsql, objconnection)
da.Fill(ds, "tables")
DataGrid1.DataSource = ds

I get exactly the tables in the SKILLS schema.

However, when I try the same thing using

Dim dt As System.Data.DataTable
objconnection.Open()
dt = objconnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New
Object() {Nothing, Nothing, Nothing, "TABLE"})
DataGrid1.DataSource = dt
objconnection.Close()

I get *all* the tables in the root database, not just those in the
SKILLS schema.

It looks like the GetOleDbSchemaTable command disregards the User ID
= "skills" that i pass into it. But in oracle, user id is the same
thing as the database name, so it is basically ignoring the database
name and returning every database.

Is there a way to just return the tables specified by the connection
string, without having to explicitly type

dt = objconnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New
Object() {Nothing, "SKILLS", Nothing, "TABLE"})

Thank you,

Burak
 
On 5 Apr 2004 14:27:53 -0700, (e-mail address removed) (Burak) wrote:

¤ Hello,
¤
¤ I am trying to retrieve the list of tables in local oracle database,
¤ "SKILLS".
¤
¤ When I use the following set of commands
¤
¤ strsql = "select TABLE_NAME from user_tables"
¤ objconnection = New OleDbConnection(strConnection)
¤ da = New OleDbDataAdapter(strsql, objconnection)
¤ da.Fill(ds, "tables")
¤ DataGrid1.DataSource = ds
¤
¤ I get exactly the tables in the SKILLS schema.
¤
¤ However, when I try the same thing using
¤
¤ Dim dt As System.Data.DataTable
¤ objconnection.Open()
¤ dt = objconnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New
¤ Object() {Nothing, Nothing, Nothing, "TABLE"})
¤ DataGrid1.DataSource = dt
¤ objconnection.Close()
¤
¤ I get *all* the tables in the root database, not just those in the
¤ SKILLS schema.
¤
¤ It looks like the GetOleDbSchemaTable command disregards the User ID
¤ = "skills" that i pass into it. But in oracle, user id is the same
¤ thing as the database name, so it is basically ignoring the database
¤ name and returning every database.
¤
¤ Is there a way to just return the tables specified by the connection
¤ string, without having to explicitly type
¤
¤ dt = objconnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New
¤ Object() {Nothing, "SKILLS", Nothing, "TABLE"})

The connection string simply refers to a specific database. It doesn't filter on schema. When you
query user_tables it's only looking at the table schema owned by the logged on user.

By default GetOleDbSchema doesn't limit you to the schema in user_tables owned by the logged on
user. You need to specify the filter as in your above example.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top