Get rows from a table

  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, = TRUE,
  origin = "1970-01-01", = FALSE,
  where.sql = NULL, = FALSE,
  n = -1



dbi database connection


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.


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 = TRUE to construct a correct WHERE clause.


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.


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.


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.


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


if FALSE only return parametrized SQL string


a table schema that can be used to convert values


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


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


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")

shall NULL values be converted to NA values?


the origin date for DATE and DATETIME conversion

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


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

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


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