[R] Dealing with schema in RODBC

[Prof Brian Ripley]

On Fri, 9 Nov 2007, Mark Lyman wrote:

> Thanks for your suggestion Marc. I saw that on some Oracle-related
> web-sites, but something in the way RODBC functions verify the existance of
> a table does not accept that naming structure. For example:
>
>> sqlColumns(eids, "EIDS.TEST_ARTCL_INST")
> Error in sqlColumns(eids, "EIDS.TEST_ARTCL_INST") :
>        'EIDS.TEST_ARTCL_INST': table not found on channel

Yes, and AFAICS it is not RODBC but Oracle's ODBC driver that is 
restricting you. Oracle-style schema are not supported that way: as Marc 
hinted, I believe you can set the schema and then use unqualified names.

>
>
> On 11/7/07, Marc Schwartz <marc_schwartz at comcast.net> wrote:
>>
>> On Wed, 2007-11-07 at 22:15 +0000, Mark Lyman wrote:
>>> Is there a way to get a table in a certain schema? The Oracle database I
>> am
>>> using has a table by the same name in two different schemas. This
>> creates
>>> problems in sqlUpdate because to sqlUpdate there are duplicate columns.
>> The
>>> following is part of the output of sqlColumns:
>>>
>>> sqlColumns(eids, "TEST_ARTCL_INST")[,1:4]
>>>    TABLE_CAT TABLE_SCHEM      TABLE_NAME        COLUMN_NAME
>>> 1                   EIDS TEST_ARTCL_INST         CHANNEL_ID
>>> 2                   EIDS TEST_ARTCL_INST    ARTICLE_TEST_ID
>>> 3                   EIDS TEST_ARTCL_INST     CHANNEL_OLD_ID
>>> 4                   EIDS TEST_ARTCL_INST        FREQ_FM_CNT
>>> 5                   EIDS TEST_ARTCL_INST      RANGE_MAX_CNT
>>> 6                   EIDS TEST_ARTCL_INST      RANGE_MIN_CNT
>>> 7                   EIDS TEST_ARTCL_INST       TYPE_GAGE_ID
>>> 8                   EIDS TEST_ARTCL_INST    DRAWING_TYPE_ID
>>> 9                   EIDS TEST_ARTCL_INST         DRAWING_ID
>>> 10                  EIDS TEST_ARTCL_INST RATE_SPECIFIED_CNT
>>> 11                  EIDS TEST_ARTCL_INST  ACCURACY_RQRD_CNT
>>> 12                  EIDS TEST_ARTCL_INST        UNIT_MSR_ID
>>> 13              EIDS_APP TEST_ARTCL_INST         CHANNEL_ID
>>> 14              EIDS_APP TEST_ARTCL_INST    ARTICLE_TEST_ID
>>> 15              EIDS_APP TEST_ARTCL_INST     CHANNEL_OLD_ID
>>> 16              EIDS_APP TEST_ARTCL_INST        FREQ_FM_CNT
>>> 17              EIDS_APP TEST_ARTCL_INST      RANGE_MAX_CNT
>>> 18              EIDS_APP TEST_ARTCL_INST      RANGE_MIN_CNT
>>> 19              EIDS_APP TEST_ARTCL_INST       TYPE_GAGE_ID
>>> 20              EIDS_APP TEST_ARTCL_INST    DRAWING_TYPE_ID
>>> 21              EIDS_APP TEST_ARTCL_INST         DRAWING_ID
>>> 22              EIDS_APP TEST_ARTCL_INST RATE_SPECIFIED_CNT
>>> 23              EIDS_APP TEST_ARTCL_INST  ACCURACY_RQRD_CNT
>>> 24              EIDS_APP TEST_ARTCL_INST        UNIT_MSR_ID
>>>
>>> Mark Lyman
>>
>> Typically, with a schema in Oracle, you use:
>>
>> schema.object
>>
>> syntax. So something like (in SQL):
>>
>> select * from EIDS.TEST_ARTCL_INST;
>>
>> would be different than:
>>
>> select * from EIDS_APP.TEST_ARTCL_INST;
>>
>>
>> So in RODBC, prefix any occurrence of a table name with 'SchemaName.' as
>> may be appropriate. The same syntax is used for views.
>>
>> The nuance is that in Oracle, all users typically have a schema that is
>> their UserID. When you login to Oracle and just use the table name, your
>> current UserID schema prefix is 'implied'.
>>
>> However, if you want to access other objects within schema created by
>> other users, you need to explicitly use the schema prefix. You of course
>> also need appropriate access privileges for other schema that you have
>> not created.
>>
>> HTH,
>>
>> Marc Schwartz
>>
>>
>>
>
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595