R DBI dbSendStatement not working for CREATE TABLE - Stack Overflow

admin管理员组

文章数量:1125480

Reading through the reference material- .html

Is there are reason the below code doesn't create the temp table?:

if (!require("pacman")
) install.packages("pacman")

pacman::p_load(
  #add list of libraries here
  DBI,
  odbc,
  RODBC,
  stringr
)

DBI:dbSendStatement(con, "CREATE TABLE #SURGEONS(PROV_ID VARCHAR(18),PROV_NAME VARCHAR(123),SERV_NAME VARCHAR(9));")

When I run the below:

DBI::dbGetQuery(con, "SELECT * FROM #SURGEONS")

It errors out with: Invalid object name '#SURGEONS'

But when I feed it into dbCreateTable it works?

dbCreateTable(
  conn = con,
  name = "#SURGEONS",
  fields = c(
    PROV_ID = "VARCHAR(18)",
    PROV_NAME = "VARCHAR(123)",
    SERV_NAME = "VARCHAR(9)"
    ),
  temporary = TRUE
  )

And I run:

DBI::dbGetQuery(con, "SELECT * FROM #SURGEONS")

Then I don't get an error that the table wasn't created-

[1] PROV_ID   PROV_NAME SERV_NAME
<0 rows> (or 0-length row.names)

Reading through the reference material- https://dbi.r-dbi.org/reference/dbSendStatement.html

Is there are reason the below code doesn't create the temp table?:

if (!require("pacman")
) install.packages("pacman")

pacman::p_load(
  #add list of libraries here
  DBI,
  odbc,
  RODBC,
  stringr
)

DBI:dbSendStatement(con, "CREATE TABLE #SURGEONS(PROV_ID VARCHAR(18),PROV_NAME VARCHAR(123),SERV_NAME VARCHAR(9));")

When I run the below:

DBI::dbGetQuery(con, "SELECT * FROM #SURGEONS")

It errors out with: Invalid object name '#SURGEONS'

But when I feed it into dbCreateTable it works?

dbCreateTable(
  conn = con,
  name = "#SURGEONS",
  fields = c(
    PROV_ID = "VARCHAR(18)",
    PROV_NAME = "VARCHAR(123)",
    SERV_NAME = "VARCHAR(9)"
    ),
  temporary = TRUE
  )

And I run:

DBI::dbGetQuery(con, "SELECT * FROM #SURGEONS")

Then I don't get an error that the table wasn't created-

[1] PROV_ID   PROV_NAME SERV_NAME
<0 rows> (or 0-length row.names)

• r
• create-table

Share Improve this question Follow edited Jan 9 at 6:54 Gingie asked Jan 9 at 6:48 GingieGingie 20122 silver badges77 bronze badges 2

• 1 The documentation details that "You must also call dbClearResult after calling [dbSendStatement]". Is there a reason why you don't use dbExecute? – Roland Commented Jan 9 at 6:56
• Yeah, that totally worked. I didn't even think to look for another function. Thank you so much for your help! – Gingie Commented Jan 9 at 7:20

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Roland answered in the comment-

Need to use dbExecute in this case and not dbSendStatement

本文标签： R DBI dbSendStatement not working for CREATE TABLEStack Overflow