Get rows from a table

dbGet(
  db,
  table = NULL,
  params = NULL,
  sql = NULL,
  fields = NULL,
  joinby = NULL,
  jointype = c("inner", "left", "right")[1],
  run = TRUE,
  schema = if (length(table) == 1) schemas[[table]] else NULL,
  schemas = get.db.schemas(db),
  rclass = schema$rclass,
  convert = !is.null(rclass),
  convert.param = FALSE,
  orderby = NULL,
  null.as.na = TRUE,
  origin = "1970-01-01",
  where.in = FALSE,
  where.sql = NULL,
  empty.as.null = FALSE,
  n = -1
)

Arguments

db

dbi database connection

table

name of the table. If you specify more than one table the later tables will be joined. You then should specify the joinby argument and possible the fields argument if you want to select fields also from the later tables.

params

named list of values for key fields. If you don't use a custom SQL statement the list will be used to construct a WHERE clause. E.g. params = list(age=30,gender="male") would be translated to the WHERE clause WHERE age = 30 AND gender="male". If you want to match several values, e.g. params = list(age = c(30,40)) you need to set the argument where.in = TRUE to construct a correct WHERE clause.

sql

optional a parameterized custom sql string Can contain parameters passed with the param arguments. E.g. if you have param = list(myname="Seb") you could use myname in an SQL statement as follows:

select * from mytable where name = :myname

To avoid SQL injection you should provide all values that can be provided by a user as such parameters or make sure that you escape them.

fields

If not NULL can be used to specify fields that shall be selected as character. For joined tables, you must enter fields in the format "tablename.field". E.g. fields = "*, table2.myfield" would select all columns from the first table and the column myfield from the joined 2nd table.

joinby

If you specify more than one table the later tables shall be joined by the variables specified in joinby with the first table. For more complicated joins where the names of the join variables differ you have to write custom SQL with the sql argument instead.

jointype

The type of the join if you specify a joinby argument. Default is "inner" but can also be set to "left" or "right"

run

if FALSE only return parametrized SQL string

schema

a table schema that can be used to convert values

rclass

the r class of the table columns, is extracted from schema

convert

if rclass is given shall results automatically be converted to these classes?

orderby

names of columns the results shall be ordered by as character vector. Add "DESC" or "ASC" after column name to sort descending or ascending. Example: orderby = c("pos DESC","hp ASC")

null.as.na

shall NULL values be converted to NA values?

origin

the origin date for DATE and DATETIME conversion

where.in

Set TRUE if your params contain sets and therefore a WHERE IN clause shall be generated.

where.sql

An optional SQL code just for the WHERE clause. Can be used if some parameters will be checked with inequality.

empty.as.null

if TRUE return just NULL if the query returns zero rows.

n

The maximum number of rows that shall be fetched. If n=-1 (DEFAULT) fetch all rows.