2.3 Executing a Query

---

Now that I’ve made a connection I’ll verify it with a simple GetDate() query.

dbGetQuery(trusted_connection,"Select GetDate()")

##                      
## 1 2019-04-27 10:43:55

dbDisconnect(trusted_connection)

I closed the connection by passing the connection variable to the dbDisconnect function. Generally, it is a good idea to close connections after use. Leaving connections open wastes memory and blocks resources for other query sessions. Too many open connections can overload the server and prevent new connections.

I’ll show what open connections look like on the back-end. First, I’ll make five connections using a for loop without invoking dbDisconnect.

for (i in 1:5)
{
trusted_connection <- dbConnect(odbc::odbc(),Driver= myDriver,Server = myServer
                                ,Database = myDatabase,Trusted_Connection='yes')
date <- dbGetQuery(trusted_connection,paste("Select GetDate() as mydate
                                            ,\'leakedquery\' as c1,",i))
}

Here’s a query that a system admin might use to watch database connections. It returns information about the five open connections. I’ve used this query to look for applications that might be leaking connections. If I a lot of sleeping connections then I contacted the app developer for a patch.

SELECT 
    DB_NAME(p.dbid) as DBName, 
    program_name as Program,CPU,memusage,status,SPID,nt_username
FROM sys.sysprocesses p
Cross apply sys.dm_exec_sql_text(p.sql_handle) d
WHERE  program_name = 'Rstudio'
and text like '%leakedquery%' and text not like '%DB_NAME%'

I didn’t issue a dbDisconnect, so the connections stays open until the client closes or R runs garbage collection.