How to set schema for Redshift w/o throwing error?

[Jim Porzak]

packageVersion("dplyr")  ‘0.5.0’   or  packageVersion("dplyr") ‘0.5.0.9004’ give same result

schema <- "tep_mart"

db_ret <- DBI::dbExecute(dsa_con, paste0("set search_path to ", schema))

Throws error:

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", : Unable to retrieve JDBC result set for set search_path to tep_mart ([JDBC Driver]com.amazon.dsi.dataengine.impl.DSISimpleRowCountResult cannot be cast to com.amazon.dsi.dataengine.interfaces.IResultSet)

But subsequent

DBI::dbListTables(dsa_con)

lists tables in the schema and

 tbl(dsa_con, "<some table name>")

works as expected.

Error clobbers R Notebook execution (in addition to being annoying)

Am I missing something???

TIA

-Jim

Full code:

driver <- JDBC("com.amazon.redshift.jdbc42.Driver",
               "C:\\Drivers\\database\\RedshiftJDBC42-1.2.1.1001.jar",
               identifier.quote="`")


dsa_user <- "jim"
dsa_password <- "...."
dsa_host <- "jdbc:redshift://.....redshift.amazonaws.com"
dsa_port <- "5432"
dsa_dbname <- "dsa"
url <- paste0(dsa_host, ":",
              dsa_port, "/",
              dsa_dbname, "?",
              "user=", dsa_user,
              "&password=", dsa_password)
dsa_con <- DBI::dbConnect(driver, url)


## following throws errors, but actually functions
schema <- "tep_mart"
db_ret <- DBI::dbExecute(dsa_con, paste0("set search_path to ", schema))

## following lists tables in "tep_mart" correctly
DBI::dbListTables(dsa_con)

[Hadley Wickham]

There currently isn't any way - but I'm planning on adding something
tomorrow. Possibly

tbl(dsa_con, schema("my_schema", "my_table")

Hadley

> --
> You received this message because you are subscribed to the Google Groups
> "manipulatr" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to manipulatr+...@googlegroups.com.
> To post to this group, send email to manip...@googlegroups.com.
> Visit this group at https://groups.google.com/group/manipulatr.
> For more options, visit https://groups.google.com/d/optout.



--
http://hadley.nz

[Jim Porzak]

Cool!  -Jim

> email to manipulatr+unsubscribe@googlegroups.com.

[Hadley Wickham]

I ended up calling the function `in_schema()`. Please try it out and
let me know if it works for you!

Hadley

>> > email to manipulatr+...@googlegroups.com.

>> > To post to this group, send email to manip...@googlegroups.com.
>> > Visit this group at https://groups.google.com/group/manipulatr.
>> > For more options, visit https://groups.google.com/d/optout.
>>
>>
>>
>> --
>> http://hadley.nz
>
>

--
http://hadley.nz