Not all advice received on the Internet is safe. https://xkcd.com/327
https://db.rstudio.com/best-practices/run-queries-safely It is not that much more difficult to do it right. On July 2, 2021 12:05:43 PM PDT, Eric Berger <ericjber...@gmail.com> wrote: >Modify the summ() function to start like this > >summ <- function(Tabname){ > query <- sprintf(" SELECT * FROM %s",Tabname) > res <- dbGetQuery(con, query) > >etc > >HTH, >Eric > >On Fri, Jul 2, 2021 at 9:39 PM Kai Yang via R-help ><r-help@r-project.org> >wrote: > >> Hello List, >> >> The previous post look massy. I repost my question. Sorry, >> >> >> I need to generate summary report for many tables (>200 tables). For >each >> table, I can use the script to generate report: >> res <- dbGetQuery(con, "SELECT * FROM BIODBX.MECCUNIQUE2") >> view(dfSummary(res), file = >> "W:/project/_Joe.B/MSSQL/try/summarytools.BIODBX.MECCUNIQUE2.html") >> rm(res) >> BIODBX.MECCUNIQUE2 is the name of table. >> >> I have all of tables' name in a data frame. So, I'm trying to write a >> function to do this: >> summ <- function(Tabname){ >> res <- dbGetQuery(con, "SELECT * FROM Tabname") >> view(dfSummary(res), file = >> "W:/project/_Joe.B/MSSQL/try/summarytools.Tabname.html") >> rm(res) >> } >> for (i in dbtable$Tot_table) >> { >> Tabname <- as.character(sqldf(sprintf("SELECT Tot_table FROM >dbtable", >> i))) >> summ(Tabname) >> } >> >> 1. I created a function summ, the argument is Tabname. I put the >Tabname >> in the function. I hope it can be replaced one by one >> 2. the table dbtable contents all tables' name (>200 rows), the field >name >> is Tot_table >> 3. I want use "for" to establish a loop, which can automatic generate >a >> summary report for each table >> >> but I got error message below: >> Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC Driver 17 >for >> SQL Server][SQL Server]Invalid object name 'Tabname'. >> [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) >could >> not be prepared. >> >> <SQL> 'SELECT * FROM Tabname' >> 10. stop(structure(list(message = "nanodbc/nanodbc.cpp:1655: 42000: >> [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid >> object name 'Tabname'. [Microsoft][ODBC Driver 17 for SQL >Server][SQL >> Server]Statement(s) could not be prepared. \n<SQL> >> 'SELECT * FROM Tabname'", >> call = NULL, cppstack = NULL), class = c("odbc::odbc_error", >> "C++Error", "error", "condition"))) >> 9.new_result(connection@ptr, statement, immediate) >> 8.OdbcResult(connection = conn, statement = statement, params = >params, >> immediate = immediate) >> 7..local(conn, statement, ...) >> 6.dbSendQuery(conn, statement, params = params, ...) >> 5.dbSendQuery(conn, statement, params = params, ...) >> 4..local(conn, statement, ...) >> 3.dbGetQuery(con, "SELECT * FROM Tabname") >> 2.dbGetQuery(con, "SELECT * FROM Tabname") >> 1.summ(Tabname) >> >> it seems the tables' name is not successfully pass into query. can >someone >> give me an instruction for this? >> many thanks, >> Kai >> >> >> [[alternative HTML version deleted]] >> >> ______________________________________________ >> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see >> 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. >> > > [[alternative HTML version deleted]] > >______________________________________________ >R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see >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. -- Sent from my phone. Please excuse my brevity. ______________________________________________ R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see 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.
