PivotalR (version 0.1.18.3.1)

db.q: Execute a SQL query

Description

This function sends SQL queries into the connected database to execute, and then extracts the result if there is any.

Usage

db(..., nrows = 100, conn.id = 1, sep = " ", verbose = TRUE)
.db(..., nrows = 100, conn.id = 1, sep = " ", verbose = TRUE)
db.q(..., nrows = 100, conn.id = 1, sep = " ", verbose = TRUE)

Arguments

…

One or multiple SQL query strings. Multiple strings will be concatenated into one SQL query string.

nrows

An integer, default is 100. How many rows should be extracted? If it is NULL, "all" or non-positive value, all rows in the result will be loaded into R. For big dataset, you may not want to do this.

conn.id

An integer, default is 1. The ID of the connection. See db.list for how to list the existing database connections.

sep

A string, default is a space character " ". If multiple strings are used in …, this string is used to separate them in the concatenation.

verbose

A logical, default is TRUE. Whether to output the SQL query that you are executing.

Value

A data.frame that contains the result if the result is not empty. Otherwise, it returns a logical value, which indicates whether the SQL query has been sent to the database successfully.

See Also

db.connect, db.objects, db.list,

Examples

# NOT RUN {
<!-- %% @test .port Database port number -->
<!-- %% @test .dbname Database name -->
## set up the database connection
## Assume that .port is port number and .dbname is the database name
cid <- db.connect(port = .port, dbname = .dbname, verbose = FALSE)

db("show search_path", conn.id = cid)
.db("drop table if exists tr;",
    "create temp table tr (idx integer,
                           val double precision);",
    "insert into tr values (1, 2.3), (2, 3.4)", conn.id = cid)
db.q("select * from tr", conn.id = cid)

db.disconnect(cid, verbose = FALSE)
# }