2
votes

La fonction SQL ne fonctionne pas lors de la tentative d'écriture d'une table dans un schéma autre que celui par défaut

J'essaie d'écrire une table dans un schéma non par défaut dans SQL Server 2017. J'utilise la documentation de RStudio pour savoir quelles sont les meilleures pratiques pour faire cela: https://db.rstudio.com/best-practices/schema/ # tables-en-écriture-non-temporaires .

Cependant, lorsque je suis leurs directives, j'obtiens l'erreur suivante: Error: Can't unquote EXISTING_SCHEMA.newTbleIris

ÉDITER

Après quelques recherches, j'ai fait des progrès. Mais le problème n'est pas complètement résolu. J'ai trouvé que la fonction Id () me permet de lire mais pas d'écrire. Exemple ci-dessous. L'erreur que j'obtiens en essayant d'écrire est:

Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in% : nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

Nouvel exemple reproductible

- Session info --------------------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.5.2 (2018-12-20)
 os       Windows Server >= 2012 x64  
 system   x86_64, mingw32             
 ui       RStudio                     
 language (EN)                        
 collate  English_United States.1252  
 ctype    English_United States.1252  
 tz       America/New_York            
 date     2019-03-07                  

- Packages ------------------------------------------------------------------------------------------------------------------------
 package     * version date       lib source        
 assertthat    0.2.0   2017-04-11 [1] CRAN (R 3.5.2)
 backports     1.1.3   2018-12-14 [1] CRAN (R 3.5.2)
 bit           1.1-14  2018-05-29 [1] CRAN (R 3.5.2)
 bit64         0.9-7   2017-05-08 [1] CRAN (R 3.5.2)
 blob          1.1.1   2018-03-25 [1] CRAN (R 3.5.2)
 callr         3.1.1   2018-12-21 [1] CRAN (R 3.5.2)
 cli           1.0.1   2018-09-25 [1] CRAN (R 3.5.2)
 crayon        1.3.4   2017-09-16 [1] CRAN (R 3.5.2)
 DBI         * 1.0.0   2018-05-02 [1] CRAN (R 3.5.2)
 dbplyr      * 1.3.0   2019-01-09 [1] CRAN (R 3.5.2)
 desc          1.2.0   2018-05-01 [1] CRAN (R 3.5.2)
 devtools      2.0.1   2018-10-26 [1] CRAN (R 3.5.2)
 digest        0.6.18  2018-10-10 [1] CRAN (R 3.5.2)
 dplyr       * 0.8.0.1 2019-02-15 [1] CRAN (R 3.5.2)
 fs            1.2.6   2018-08-23 [1] CRAN (R 3.5.2)
 glue          1.3.0   2018-07-17 [1] CRAN (R 3.5.2)
 hms           0.4.2   2018-03-10 [1] CRAN (R 3.5.2)
 htmltools     0.3.6   2017-04-28 [1] CRAN (R 3.5.2)
 httpuv        1.4.5.1 2018-12-18 [1] CRAN (R 3.5.2)
 jsonlite      1.6     2018-12-07 [1] CRAN (R 3.5.2)
 later         0.8.0   2019-02-11 [1] CRAN (R 3.5.2)
 magrittr      1.5     2014-11-22 [1] CRAN (R 3.5.2)
 memoise       1.1.0   2017-04-21 [1] CRAN (R 3.5.2)
 mime          0.6     2018-10-05 [1] CRAN (R 3.5.2)
 odbc        * 1.1.6   2018-06-09 [1] CRAN (R 3.5.2)
 pillar        1.3.1   2018-12-15 [1] CRAN (R 3.5.2)
 pkgbuild      1.0.2   2018-10-16 [1] CRAN (R 3.5.2)
 pkgconfig     2.0.2   2018-08-16 [1] CRAN (R 3.5.2)
 pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.5.2)
 prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.5.2)
 processx      3.2.1   2018-12-05 [1] CRAN (R 3.5.2)
 promises      1.0.1   2018-04-13 [1] CRAN (R 3.5.2)
 ps            1.3.0   2018-12-21 [1] CRAN (R 3.5.2)
 purrr       * 0.3.0   2019-01-27 [1] CRAN (R 3.5.2)
 R6            2.4.0   2019-02-14 [1] CRAN (R 3.5.2)
 Rcpp          1.0.0   2018-11-07 [1] CRAN (R 3.5.2)
 remotes       2.0.2   2018-10-30 [1] CRAN (R 3.5.2)
 rlang         0.3.1   2019-01-08 [1] CRAN (R 3.5.2)
 rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.5.2)
 rstudioapi    0.9.0   2019-01-09 [1] CRAN (R 3.5.2)
 sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.5.2)
 shiny       * 1.2.0   2018-11-02 [1] CRAN (R 3.5.2)
 tibble        2.0.1   2019-01-12 [1] CRAN (R 3.5.2)
 tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.5.2)
 usethis       1.4.0   2018-08-14 [1] CRAN (R 3.5.2)
 withr         2.1.2   2018-03-15 [1] CRAN (R 3.5.2)
 xtable        1.8-3   2018-08-29 [1] CRAN (R 3.5.2)

Exemple reproductible

library(odbc)
library(dbplyr)
library(dplyr)
library(DBI)

con <- dbConnect(odbc(),
                 Driver = "SQL SERVER",
                 Server = "SERVER_NAME",
                 Database = "DB_NAME",
                 UID = "USER_NAME",
                 PWD = "PASSWORD")

# this works, writing to default schema
dbWriteTable(con, SQL("newTbleIris"), iris)

# this does NOT work and gives error mentioned above
dbWriteTable(con, SQL("EXISTING_SCHEMA.newTbleIris"), iris)

Informations sur la session

library(odbc)
library(dbplyr)
library(dplyr)
library(DBI)

con <- dbConnect(odbc(),
                 Driver = "SQL SERVER",
                 Server = "SERVER_NAME",
                 Database = "DB_NAME",
                 UID = "USER_NAME",
                 PWD = "PASSWORD")

# Works assuming you already have iris table in EXISTING SCHEMA.
# I did this not in R because I cannot currently write to a non-default schema in R.
dbReadTable(con, Id(schema = "EXISTING_SCHEMA", name = "iris"))

# Still cannot write to non-default schema Throws an error.
dbWriteTable(con, Id(schema = "nycDoe", name = "iris"), iris)

sql-server r r-dbi

6 commentaires

Vérifiez ceci: github.com/r-dbi/odbc/issues/197

Alors j'ai vu ça, et ça marche en quelque sorte. Par exemple, dbReadTable(con, Id(schema = "EXISTING_SCHEMA", name = "iris")) fonctionne (en supposant que vous ayez déjà une table dans ce schéma). Cependant, dbWriteTable(con, Id(schema = "EXISTING_SCHEMA", name = "iris"), iris) ne fonctionne pas et renvoie une erreur Error in connection_sql_tables(conn@ptr, catalog_name = if ("catalog" %in% : nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

Pouvez-vous essayer avec les nouveaux dbCreateTable() et dbAppendTable() ?

Utilisé dbCreateTable () et dbAppendTable () pour essayer d'écrire une table dans un schéma autre que celui par défaut. Mais cela a échoué. ##### Tables are created, but they are all empty dbCreateTable(con, SQL("EXISTING_SCHEMA.iris3"), iris) dbAppendTable(con, SQL("EXISTING_SCHEMA.iris3"), iris) iris3 <- dbReadTable(con, SQL("EXISTING_SCHEMA.iris3")) queryIris3 <- dbGetQuery(con, "SELECT * FROM EXISTING_SCHEMA.iris3")

J'ai essayé de la même manière avec Id () plutôt qu'avec SQL (), mais le résultat est le même. La table est vide. dbAppendTable () ne semble pas être en train d'ajouter. dbCreateTable(con, Id(schema = "EXISTING_SCHEMA", table = "iris4"), iris) dbAppendTable(con, Id(schema = "EXISTING_SCHEMA", table = "iris4"), iris) iris4 <- dbReadTable(con, Id(schema = "EXISTING_SCHEMA", table = "iris4"), iris) queryIris4 <- dbGetQuery(con, "SELECT * FROM EXISTING_SCHEMA.iris4")

Étrangement, j'obtiens un problème similaire même lorsque j'essaye de travailler avec des tables dans le schéma par défaut. dbAppendTable () n'ajoute toujours pas. dbCreateTable(con, "iris2", iris) dbAppendTable(con, "iris2", iris) iris2 <- dbReadTable(con, "iris2") queryIris2 <- dbGetQuery(con, "SELECT * FROM iris2")

3 Réponses :

1
votes

J'ai trouvé une solution de contournement facile pour cela. Suivez simplement ces étapes:

Tout d'abord, écrivez votre table avec un schéma par défaut: dbWriteTable(con, "newTbleIris", iris)

Et insérez-le dans un nouveau avec un schéma autre que celui par défaut, mais cette fois en utilisant la fonction dbGetQuery:

dbGetQuery(con, "SELECT * INTO [schema].newTbleIris FROM newTbleIris")

Et c'est tout! Votre table apparaîtra désormais avec un schéma autre que celui par défaut.

Vous pouvez maintenant supprimer la première table que vous avez créée avec un schéma par défaut en effectuant dbGetQuery(con, "DROP TABLE newTbleIris")

4 commentaires

C'est une solution de contournement très utile! Espérons qu'à l'avenir, les responsables du paquet résoudront ce problème.

J'ai donc rencontré un problème en utilisant votre méthode et je me demande si vous avez le même problème. Tout d'abord, j'écris la table dbWriteTable(con, "iris", iris) . Ensuite, je copie la table dbExecute(con, "SELECT * INTO [schema].iris FROM iris"). Finally, I drop the table I initially added into the default schema dbExecute (con, "DROP TABLE iris"). Cependant, lorsque j'exécute `dbExistsTable (con," iris "), la valeur de retour que j'obtiens est TRUE.

Cela signifie que la table existe dans votre serveur SQL avec le nouveau schéma

Droite. Mais dbExistsTable() ne doit renvoyer true que pour [schema].iris . Dans mon appel de fonction, je ne spécifie pas le schéma, et j'aurais pensé que cela ne vérifierait que le schéma par défaut. Ce n'est pas le cas, cependant, d'où la raison pour laquelle cela revient vrai. Cependant, cela conduit à un comportement problématique sur toute la ligne car si j'essaie d'écrire dans le schéma par défaut en utilisant le nom de table iris cela ne me le permettra pas. Parce qu'il pense que la table existe déjà. Espérons que ce soit clair.

0
votes

Dans mon cas, faire dbWriteTable(con, "[schema].newTbleIris", iris) sans envelopper aucune fonction a fonctionné.

3 commentaires

Ouais ça ne marche pas pour moi. Pouvez-vous publier plus de détails sur la façon dont votre environnement? J'ai essayé avec les caractères "[" "]" et sans ces caractères.

Hey drizzle, la syntaxe exacte que j'utilise est sans "[]" autour du nom du schéma. Avez-vous également essayé cela? dbWriteTable(con, "schema.tablename", dt)

Ouaip. J'ai essayé avec les caractères "[]" et sans eux, et rien ne fonctionne. Ce qui finit par arriver, c'est qu'une table est écrite dans le schéma par défaut sous le nom "schema.tablename".

0
votes

DBI 1.1.0 ce problème.

dbWriteTable(con, SQL("schema.newTbleIris"), iris)

1 commentaires

J'ai dû mettre à niveau DBI vers 1.1.0 et aussi odbc vers 1.3.0, puis utiliser la fonction SQL, par exemple dbWriteTable (con, SQL ("SCHEMA.TABLE"), value = table, row.names = FALSE, overwrite = FALSE, ajouter = TRUE). Ça a marché.