Dear All,

I just wanted to follow up my question with an answer, which I owe to Robbie Bingler at UVA's IATH. The code chunk that bombed is here:

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         '"
The following works:

> sqlQuery(DRCch,paste('SELECT *
+                       FROM
+                      "tblCeramicWare"
+                      '))
   WareID                                   Ware
1       1       Coarse Earthenware, unidentified
2       2                     Red Agate, refined
3      97         Agate, refined (Whieldon-type)
4       4                                Redware
5       5                                Buckley
6       6                           Iberian Ware
7      87            North Devon Gravel Tempered

Note the double quote on the table name (a PostgreSQL feature) and the single quotes enclosing the SQL text-string
that is the argument to the paste() function.

Boolean operators often require single-quoted text strings and to prevent R from interpreting these as
the end of the  SQL string, one uses \ as an escape sequence:
> sqlQuery(DRCch,paste('SELECT * from "tblCeramicWare" WHERE "Ware" = \'Slip Dip\' '))
  WareID     Ware
1     93 Slip Dip


Thanks to Robbie and to all the folks on the R-Help list for their help.

Best, Fraser



From: Fraser D. Neiman
Sent: Friday, May 30, 2014 2:00 PM
To: r-help@r-project.org
Subject: RODBC and PosgreSQL problems


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


	[[alternative HTML version deleted]]

