Executes a SQL query and returns the results as a data.frame
dkuSQLQueryToData(connection, query, preQueries = NULL, postQueries = NULL, findConnectionFromDataset = FALSE, dbType = "sql")
| connection | Name of the SQL connection to use. This can also be a dataset name. In that case, findConnectionFromDataset must be set to TRUE |
|---|---|
| query | A SQL query that may or may not return results |
| preQueries | A list of SQL queries to execute before the main query |
| postQueries | A list of SQL queries to execute after the main query |
| findConnectionFromDataset | Set this to TRUE if the "connection" you passed was actually a dataset name |
| dbType | Internal usage, do not modify |
A data.frame with the query results, if any. Else, an empty dataframe
In most cases, DSS connections do not automatically commit transactions. When this is the case, in particular, write operations performed as part of the query will not actually be committed. For example, if you insert or delete records. In that case, you need to add a 'COMMIT' statement as a post query.
# NOT RUN {
# Identify a connection directly
dkuSQLQueryToData('my-postgresql-connection', 'SELECT COUNT(*) FROM mytable')
# Identify a connection by a dataset name
dkuSQLQueryToData('my-dataset', 'SELECT COUNT(*) FROM mytable', findConnectionFromDataset=TRUE)
# Insert data and commit
dkuSQLQueryToData('my-connection', 'INSERT INTO mytable VALUES (42, 'stuff')', postQueries=c("COMMIT"))
# }