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"<fnei...@monticello.org> To:"r-help@r-project.org"
<r-help@r-project.org> Subject: [R] RODBC and PosgreSQL problems
Message-ID:
<2176ad174d58cb4abbda99f3458c20171be55...@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
______________________________________________
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.
