Re: How can user name/table name concatenation be prevented when using ODBC?

In article <NEWTNews.818179657.26699.bruce_at_ga.bst.bls.com>, bruce.douglas_at_bridge.bst.bls.com says...
>
>
>In Article<49mfp0$a55_at_mrnews.mro.dec.com>, <ronald_at_mix004.nwg.dec.com>
 writes:
>
>>
>> Oracle concatenates user names and table names, for example user "diane"
 and  

>> table "product_table" becomes "diane.product_table". When the database is
>> accessed via ODBC, I would like to access tables using table names (or
 public synonyms) only.
>>
>> I tried public synonyms. This works if a user logs in into Oracle directly.
>> But if access is done via ODBC, these synonyms don't show up in a normal
>way. They are only visible if system names are requested also. But system
>names are not visible if you try to use attached tables with MS-Access 2.0.
>
>
>Here's one approach, although it's not very pretty:
>
>Create private synonyms in each user's schema, which should allow you to
>reference the unqualified table names(or views, or procedures) via ODBC. The
>pain is in maintaining the synonyms. We've developed a script (which must be
>run as "system") to create all the synonyms we need for each user.
>
>At some point, we were going to look into the use of public synonyms to ease
>our maintenance work. I'm surprised to hear that public synonyms don't work
 as
>expected.

Using Microsoft ODBC Test utility it is possible to connect to a data source and execute catalog|SQLTABLES. You get a list of names for tables, synonyms, etc., which are defined in the data source (see below).

In the output of ODBC test (below) you will find the meaning of the columns on the first line.
Next a table "AGENT" defined by user "CALLCENTER", which has table_type "TABLE".
On the third line you will find a public synonym defined for the agent table. Now the table owner for this synonym is "<Null>", but the table type has now changed to "SYSTEM TABLE".

Names with "table_type SYSTEM TABLE" are not visible when you try to attach them using MS-Access 2.0, so it is impossible to use them.

"TABLE_QUALIFIER", "TABLE_OWNER", "TABLE_NAME", "TABLE_TYPE", "REMARKS"
<Null>,            "CALLCENTER",  "AGENT",      "TABLE",        <Null>
<Null>,            <Null>,        "AGENT",      "SYSTEM TABLE", <Null>

(note: spaces have been added to align columns for readebility.)

I tried the ODBC driver (Oracle7) supplied by Oracle. Oracle support is not able to help. Does any one know a solution for the problem? Maybe ODBC drivers excist which can be configured to prevent name concatenation or pass all names with table type "TABLE"? Is there another solution?

TNX Ronald Received on Wed Dec 06 1995 - 00:00:00 CET