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

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

[[alternative HTML version deleted]]