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

[Kai Yang]

 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]]