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.