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]]
______________________________________________
R-help@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.
