[R] RODBC and PosgreSQL problems

[Paul Gilbert]

I'm not sure if this was due to mailer wrap, but I think you are putting 
a <return> between the end of the table name and the quote mark, and 
possibly that or a space is being included in the table name in the 
sqlQuery() statement. Do you have the same problem if you put the quote 
mark immediately after the table name, or if you put a ; after the table 
name?

(BTW, you will probably get better responses to this sort of question on 
the r-sig-db mailing list.)

Paul

> Message: 23 Date: Fri, 30 May 2014 18:00:06 +0000 From: "Fraser D.
> Neiman"<fneiman at monticello.org> To:"r-help at r-project.org"
> <r-help at r-project.org> Subject: [R] RODBC and  PosgreSQL problems
> Message-ID:
> <2176AD174D58CB4ABBDA99F3458C20171BE55EAA at GRANGER.monticello.org>
> Content-Type: text/plain
>
>
> Dear All,
>
> I am trying for the first time to run SQL queries against a remote
> PostgreSQL database via RODBC. I am able to establish a connection
> just fine, as shown by getting results back from the sqlTables(),
> sqlColumns() and sqlPrimary Key() functions in RODBC. However, when I
> try to run a SQL query using the sqlQuery() function I get
>
> [1] "42P01 7 ERROR: relation \"tblceramicware\" does not
> exist;\nError while executing the query" [2] "[RODBC] ERROR: Could
> not SQLExecDirect '\n         SELECT * \n         FROM
> tblCeramicWare
>
> What am I doing wrong?
>
> Here are the relevant snips from the R console.  What's puzzling is
> that "tblcermicWare" is recognized as an argument to sqlColumns() and
> sqlPrimaryKey() . But NOT in sqlQuery() .
>
> Thanks for any pointers.
>
> best, Fraser
>
>>> library(RODBC)
>>>
>>> # connect to DAACS and assign a name (DAACSch) to the connection
>>> DRCch <- odbcConnect("postgreSQL35W" , case= "nochange", uid
>>> ="XXXXXX",pwd="XXXXXX");
>>>
>>> #list the tables that are avalailabale sqlTables(DRCch, tableType
>>> = "TABLE")
> TABLE_QUALIFIER TABLE_OWNER                               TABLE_NAME
> TABLE_TYPE REMARKS 1   daacs-production      public
> TempSTPTable      TABLE 2   daacs-production      public
> activities      TABLE 3   daacs-production      public
> articles      TABLE 4   daacs-production      public
> schema_migrations      TABLE 5   daacs-production      public
> tblACDistance      TABLE 6   daacs-production      public
> tblArtifactBox      TABLE 7   daacs-production      public
> tblArtifactImage      TABLE 8   daacs-production      public
> tblBasicColor      TABLE 9   daacs-production      public
> tblBead      TABLE
>
>
>>> sqlColumns(DRCch, "tblCeramicWare")
> TABLE_QUALIFIER TABLE_OWNER     TABLE_NAME COLUMN_NAME DATA_TYPE
> TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE 1 daacs-production
> public tblCeramicWare      WareID         4      int4        10
> 4     0    10        0 2 daacs-production      public tblCeramicWare
> Ware        -9   varchar        50    100    NA    NA        1
> REMARKS                         COLUMN_DEF SQL_DATA_TYPE
> SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION 1
> nextval('global_id_seq'::regclass)             4               NA
> -1                1 2                                       <NA>
> -9               NA               100                2 IS_NULLABLE
> DISPLAY_SIZE FIELD_TYPE AUTO_INCREMENT PHYSICAL NUMBER TABLE OID BASE
> TYPEID TYPMOD 1        <NA>           11         23              1
> 1     27441           0     -1 2        <NA>           50       1043
> 0               2     27441           0     50
>>> sqlPrimaryKeys(DRCch, "tblCeramicWare")
> TABLE_QUALIFIER TABLE_OWNER     TABLE_NAME COLUMN_NAME KEY_SEQ
> PK_NAME 1 daacs-production      public tblCeramicWare      WareID
> 1 tblCeramicWare_pkey
>
>>> sqlQuery(DRCch,paste("
> +          SELECT * +          FROM tblCeramicWare +          ")) [1]
> "42P01 7 ERROR: relation \"tblceramicware\" does not exist;\nError
> while executing the query" [2] "[RODBC] ERROR: Could not
> SQLExecDirect '\n         SELECT * \n         FROM tblCeramicWare \n
> '"
>>>
>
>
> Fraser D. Neiman Department of Archaeology, Monticello (434) 984
> 9812
>
>