0

I have twelve MySQL database connections created using:

mydb1 = dbConnect(MySQL(), user='user', password=password, dbname='db',host='domain')
mydb2...  
mydb3...  
...
mydb12...

I have a script where I want to execute the same query on all 12 databases and loop through them. How do I pass the dbConnect objects successfully to a dbSendQuery?

items <- ls()[grep("mydb",ls())]
query <- dbSendQuery(items[1], "SELECT * FROM table")

gives me the error:

Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbSendQuery’ for signature ‘"character", "character"’

Scarabee
  • 5,437
  • 5
  • 29
  • 55
Hedekar
  • 33
  • 5

1 Answers1

0

You cannot pass the textual representation of a connection object to the database functions. Your call is analogous to dbSendQuery("mydb1", "select * from table"), which I'm guessing you would not have typed in literally.

Ultimately you want to deal with a list of connections, which you can form manually with

conns <- list(mydb1, mydb2, ...)

but if that is difficult or you want to be more programmatic about it, try

conns <- lapply(ls()[grep("mydb",ls())], get)

and then

results <- lapply(conns, function(con) dbSendQuery(con, "select * from ..."))
r2evans
  • 141,215
  • 6
  • 77
  • 149
  • So after trying this `items <- list(mydb1,mydb2,mydb3,mydb4,mydb5,mydb6,mydb7,mydb8,mydb9,mydb10,mydb11,mydb12) result1 <- dbSendQuery(items[1], "SELECT...` I get Error in (function (classes, fdef, mtable) : unable to find an inherited method for function ‘dbSendQuery’ for signature ‘"list", "character"’ . Is there something in your last statement that you're doing that I'm missing? – Hedekar Dec 09 '17 at 21:44
  • Please read the whole answer, I never suggested your `result1 <- ...` command. – r2evans Dec 09 '17 at 22:13
  • r2evans I did read the whole answer and where I'm confused is how your `results <-` line differs from my `result1 <-` line. R is obviously treating the data types differently across these two seemingly identical operations and this is where I'm needing assistance. – Hedekar Dec 09 '17 at 22:26
  • Oh, I see. Have you used double-brackets for indexing? Take a look at `items[1]` and then `items[[1]]` and see the difference. (See [`?Extract`](https://stat.ethz.ch/R-manual/R-devel/library/base/html/Extract.html).) – r2evans Dec 09 '17 at 22:30
  • If you want to do the same thing across multiple otherwise-identical objects, I urge you to consider using `lapply` (or a related vectorizing function). What's your reason for `lapply`-aversion, if I may ask? – r2evans Dec 09 '17 at 22:32
  • I'm averse to the lapply function primarily because of it's strange behaviours that I see when compared to using the for loops that I am used to from other programming languages. I know there's performance loss, but it's so negligible in my usage that for() is a much more straightforward command. – Hedekar Dec 09 '17 at 22:34
  • Are you able to explain to me why the double brackets are used to index a single-dimension list of connections? I think I understand the concept, but as you can probably tell, I'm not the strongest R programmer yet. – Hedekar Dec 09 '17 at 22:35
  • (For brackets, see https://stackoverflow.com/questions/46541269/difficulty-in-understanding-the-difference-between-double-and-single-bracket-sub, https://stackoverflow.com/questions/1169456/the-difference-between-and-notations-for-accessing-the-elements-of-a-lis, and https://stackoverflow.com/questions/36777567/is-there-a-logical-way-to-think-about-list-indexing.) `for` versus `lapply` is no longer about speed (it's improved in last few years), it's mostly (to me) about code flow and methodology. To me, it is very simple to process a list of identically-structured results. – r2evans Dec 09 '17 at 22:38