[R] R Function question, (repost to fix the messy work format)

[Eric Berger]

Hard for me to tell without more details but it looks like the following
has several bugs

for (i in dbtable$Tot_table)
{
  Tabname <- as.character(sqldf(sprintf("SELECT Tot_table FROM dbtable",
i)))
  summ(Tabname)
}

Your sprintf() statement seems to use 'i' but actually does not.
You probably want to rewrite/rearrange this code. More like

x <- sqldf("SELECT Tot_table FROM dbtable")
for ( Tabname in x )
summ(Tabname)

no doubt this is wrong but put a browser() call after the x <- sqldf(...)
line and inspect x and go from there




On Fri, Jul 2, 2021 at 10:20 PM Kai Yang <yangkai9999 using yahoo.com> wrote:

> Hello Eric,
>
> Following your suggestion, I modified the code as:
>
> summ <- function(Tabname){
>
>   query <- sprintf(" SELECT * FROM %s",Tabname)
>
>   res <- dbGetQuery(con, query)
>
>   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)
>
> }
>
> after submitted the work, I got the error message below:
>
>
>  Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC Driver 17 for
> SQL Server][SQL Server]Invalid object name 'c'.  [Microsoft][ODBC Driver 17
> for SQL Server][SQL Server]Statement(s) could not be prepared.
> <SQL> ' SELECT * FROM c("BIODBX.MECCUNIQUE2", "BIODBX.QDATA_HTML_DUMMY",
> "BIODBX.SET_ITEMS", "BIODBX.SET_NAMES", "dbo.sysdiagrams",
> "GEMD.ASSAY_DEFINITIONS", "GEMD.ASSAY_DISCRETE_VALUES",
> "GEMD.ASSAY_QUESTIONS", "GEMD.ASSAY_RUNS", "GEMD.BIODBX_DATABASE_SEED",
> "GEMD.BIODBX_USER_SEEDS", "GEMD.BIODBX_USERS", "GEMD.DATA_ENTRY_PAGES",
> "GEMD.DISC_SESSION_QID", "GEMD.DISC_SESSION_STATUS",
> "GEMD.DISC_SESSION_TYPE", "GEMD.DISCREPANCIES",
> "GEMD.DISCREPANCY_QUERY_TEMP", "GEMD.DISCRETE_VALUES",
> "GEMD.ENTERED_DATA_ENTRY_PAGES", "GEMD.ENTRY_GROUPS",
> "GEMD.ExportSampleListNames", "GEMD.FORM_STATUS_BY_SUBJECT",
> "GEMD.GEMD_CODELIST_GROUPS", "GEMD.GEMD_CODELIST_VALUES",
> "GEMD.GEMD_LOT_DEFINITIONS", "GEMD.GEMD_SAMPLES", "GEMD.GEMD_STUDIES",
> "GEMD.MECCUNIQUE", "GEMD.MECCUNIQUE2", "GEMD.MISSING_DI
>
>
> One more question,  in the code of "*view(dfSummary(res), file =
> "W:/project/_Joe.B/MSSQL/try/summarytools.Tabname.html")*",
>
> can Tabname part be replacted automatic also?
>
> Thank you,
>
> Kai
> On Friday, July 2, 2021, 12:06:12 PM PDT, Eric Berger <
> ericjberger using 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 using 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 using 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 using 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]]