getSFtable: Extract table from connection to Sustainable Farms SQL...

In sustainablefarms/sflddata: Data Extraction Methods for the Linking Data Project

Extract table from connection to Sustainable Farms SQL database

Description

Extraction using the column order given in the database does not always work. I suspect this is due to the issue https://github.com/r-dbi/odbc/issues/112.

Usage

getSFtable(
  tablename,
  schema,
  con,
  last = NULL,
  tryperm = FALSE,
  verbose = tryperm,
  n = -1,
  params = NULL,
  ...
)

colsfromtbl(cols, tablename, schema, con, n = -1, params = NULL, ...)

Arguments

tablename	The name of table to extract columns from
schema	The schema containing the table
con	A connection to the SQL database. Created, for example, by DBI::dbConnect
last	Column names to put last when querying the database
tryperm	TRUE means try different permutations of the column ordering until table successfully extracted.
verbose	If TRUE prints each permutation of column names before it is tried.
n	Passed to dbGetQuery. Maximum number of records to retrieve per fetch. Use n = -1 or n = Inf to retrieve all pending records. Some implementations may recognize other special values.
params	Query parameters to pass to dbBind() via dbGetQuery. See dbBind() for details.
cols	A list of column names to extract

Details

Best guesses so far is that all columns of data type 'varchar' must be read last (credit to https://github.com/r-dbi/odbc/issues/86#issuecomment-314492486). The bug report here https://github.com/r-dbi/odbc/issues/10 also suggests 'varbinary' types should be places last. The names of columns of these types are appeneded to last in getSFtable.

Functions

• colsfromtbl: Extract specific columns from a table in the SQL database

Examples

out <- getSFtable("tblPost-fireAcaciaBasal", "VicBirdsVegetation", con, last = "VisitCode2")

sustainablefarms/sflddata documentation built on April 19, 2022, 11:19 a.m.