[SOLVED] R and RMySQL, how do I get the database name of the connection?

R and RMySQL, how do I get the database name of the connection?

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.

Solution

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"