0

I am accessing MySQL connections from R via RMySQL. I found a MySQL command with which to get the database/schema name 

SELECT DATABASE();

So I can call this via dbGetQuery() to get it from within my R scripts.

However, I also found that calling summary(connection) automatically gets the database, among other info.

> summary(connection)
<MySQLConnection:(6746,0)> 
  User: root 
  Host: localhost 
  Dbname: my_database
  Connection type: Localhost via UNIX socket 
  No resultSet available

Because of the quickness of the result, I assume the connection stores that information within R, but I don't know how to access its contents.

I tried checking its environment, and even looking in the source of RMySQL to understand it, however I don't have enough experience... yet.

So how do I get the Dbname as a variable (not printed)? Thanks and greetings from Mx.

Diego-MX
  • 2,279
  • 2
  • 20
  • 35

2 Answers2

0

While, I don't quite understand why you want the database name as a variable, you can capture it as follows:

db_info <- capture.output(mysqlDescribeConnection(MySQLcon, verbose = T))

Note the use of mysqlDescribeConnection(), rather than summary.

The dbname can be accessed as the fourth element of the output vector:

db_info[4]
[1] "  Dbname: my_database "

If you really have plans to use it as a variable, some string manipulation will be necessary.

db <- strsplit(db_info[4], ' ')

final_dbname <- db[[1]][4]
[1] "my_database"
Minnow
  • 1,733
  • 2
  • 26
  • 52
  • While not a strict necessity, I want to do this is because I want a script to cache the query results from MySQL which are taking longer each time, this is a function of the form `makeCacheQuery(conn, script_or_file)` and inside I check the last time the database from `conn` was modified. =) – Diego-MX Feb 28 '15 at 18:56
0

This is straightforward: dbname <- db.con@info$dbname

For context, the @ operator is used to access the "slots" of S4 objects in R.

Patrick
  • 653
  • 1
  • 6
  • 17