thr3ads.net - R help - [R] R Function question, (repost to fix the messy work format) [Jul 2021]

If this information is useful, please help other people find it:
Share via:

Eric Berger

2021-Jul-02 19:05 UTC

[R] 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]]
>
> ______________________________________________
> R-help at 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]]

Kai Yang

2021-Jul-02 19:20 UTC

head link

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

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

______________________________________________
R-help at 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]]

Jeff Newmiller

2021-Jul-02 19:37 UTC

head link

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

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 <ericjberger at 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 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]]
>>
>> ______________________________________________
>> R-help at 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 at 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 - Jul 2021 - R Function question, (repost to fix the messy work format)

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

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

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