read_sql {sqlhelper} | R Documentation |
Read a sql file and return it's contents as a tibble
Description
Read a sql file and return it's contents as a tibble
Usage
read_sql(file_name, cascade = TRUE)
Arguments
file_name |
Full name and path of a file to read |
cascade |
Parameters for executing each query may be specified as
comments in the SQL file. If |
Details
Multiple SQL queries in files should be terminated by semi-colons
(;
), as usual.
The values of qname
, quotesql
, interpolate
, execmethod
, geometry
,
and conn_name
in the output may be specified with comments immediately
preceding each query (see examples).
With the exception of qname
, the value of each interpreted comment is
cascaded to subsequent queries (assuming cascade=TRUE
). This means you
may set values once for the first query in the file and they will apply to
all the queries thereafter.
See run_queries()
for the implications of setting execution parameters.
See prepare_sql()
for the treatment of missing values in the output and
their defaults. The article vignette("execution")
has further examples of
using these parameters to control execution.
Value
A tibble containing 1 row per query with the following fields:
- qname
character. A name for this query
- quotesql
"yes" or "no". Should parameterized character values be quoted for this query?
- interpolate
"yes" or "no". Should this query be parameterized with values from R?
- execmethod
The method to execute this query. One of "get" (
DBI::dbGetQuery()
), "execute" (DBI::dbExecute()
), "sendq" (DBI::dbSendQuery()
), "sends" (DBI::dbSendStatement()
) or "spatial" (sf::st_read()
)- geometry
character. If
execmethod
is "spatial", which is the geometry column?- conn_name
character. The name of the database connection to use for this query. Must be the name of a configured sqlhelper connection.
- sql
The sql query to be executed
- filename
The value of
file_name
Examples
library(sqlhelper)
fn <- system.file( "examples/read_sql_execution_params.SQL",
package="sqlhelper" )
readLines( fn ) |> writeLines()
sql_tibble <- read_sql(fn)
sql_tibble
sql_tibble$sql
fn <- system.file( "examples/read_sql_comments.SQL", package="sqlhelper" )
readLines( fn ) |> writeLines()
sql_tibble <- read_sql(fn)
sql_tibble
sql_tibble$sql