sqlGetQuery: Gets result from sql query

In danielfm123/dftools: Data Science Tools

Description

Does query to a DB in a server from sqlServers and return its result as a data frame or data table. It calls dbGetQuery() from DBI package so the result is always free-d. Provide the option to make general queries and change its parameters easier, as in sqlGsub() from this package.

Usage

sqlGetQuery(server_name, query, param=c(), dt=FALSE, key=c(), ...)

Arguments

server_name	character, name of the DB server from sqlServers list.
query	character, query structure with variant parameters between @'s, if they are.
param	character vector with the value of the parameters that would be used in query.
dt	boolean, If true, the results would be data table class.
key	character vector, it contains the names of the columns to be used as keys if return a data table.
...	inherit parameters used in dbGetQuery().

Details

It ends the connection inmediately after getting the results. sqlServer is a list built-in sqlGetConn().

Value

A data frame or data table with query results.

See Also

"sqlServerConn","setSqlServers()" and "sqlGsub()" documentation in toolkitEntel and "dbGetQuery()" from DBI for more details.

Examples

sqlWriteTable("local",mtcars,"mtcars") #create mtcars table in local server
sqlListTables("local")
q1="SELECT * FROM mtcars WHERE mpg>20" #decalrate query

sqlGetQuery("local",q1) #do a simple query

q2="SELECT @col@ FROM @table@ WHERE @condition@" #decalrate a mutable query
p1=c(table="mtcars",col="*",condition="hp>100")
p2=c(table="mtcars",col="wt",condition="wt>2.600") #and declarate the parameter to use in mutable query

sqlGetQuery("local",q2,p1)

sqlGetQuery("local",q2,p2,dt=T) # as data table

danielfm123/dftools documentation built on July 17, 2021, 1 p.m.