sqlhelper (version 0.2.1)

read_sql: 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)

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

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 cascade=TRUE, execution parameters specified in the file will be cascaded to subsequent queries where that parameter is not specified. This enables you to set a parameter (e.g. the connection name) once, for the first query in a file, and use it for all the subsequent queries.

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.

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