sqlExecute: excecutes query on SQL

In danielfm123/dftools: Data Science Tools

Description

It uses dbExecute() function from DBI to execute a statement and return the number of rows affected and provide the option to make general queries and change its parameters easier, as in sqlGsub() from this package.

Usage

sqlExecute(server_name, query, param=c(), ...)

Arguments

server_name	character, name of the server where DB is alocated. It must be in sqlServers.
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.
debug	boolean to show excecuted query.
...	inherit parameters used in dbExecute().

Details

This function ends the connection with DB inmediately after it execute and update the statement. sqlServers is a list that contain the expressions necessary to connect DB's.

sqlServers is a list built-in sqlGetConn().

Author(s)

Daniel Fischer

See Also

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

Examples

  connection=sqlGetConn("local")
  dbWriteTable(connection, "cars",head(cars, 3))
  dbReadTable(connection, "cars")   # there are 3 rows
  dbDisconnect(connection) #end connection tu see the use of sqlExecute
  sqlExecute("local",
         "INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)") #can omite "param" to make a simple query

  connection=sqlGetConn("local")
  dbReadTable(connection, "cars")   # there are now 6 rows
  dbDisconnect(connection) #end connection again

  q="INSERT INTO @table@ (@col1@, @col2@) VALUES (4, 0), (5, 0), (6, 0)" #use this form to make mutable queries
  p1=c(table="cars",col1="speed",col2="dist")
  p2=c(table="cars",col1="dist",col2="speed")

  sqlExecute("local",q,p1)

  connection=sqlGetConn("local")
  dbReadTable(connection, "cars")
  dbDisconnect(connection) #end connection again

  sqlExecute("local",q,p2)

  connection=sqlGetConn("local")
  dbReadTable(connection, "cars")
  dbDisconnect(connection) #end connection again

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