Hi All: I am using R with MySQL, following is my code: library(RMySQL) con <- dbConnect(MySQL(), user="user", password="password", dbname="db", host="localhost") rs1 <- dbSendQuery(con, "show tables like \"%padded\"") all_tables3<- fetch(rs1, n=-1) There are 25 tables in the database when I execute the above query on the MySQL prompt. However, the above R code giving me only 9 tables. It seems there is some cache from which R is getting 9 tables instead of 25 tables. Please help. Regards, Vivek Kumar Singh PhD student, Information Systems Decision Sciences, MUMA College of Business, USF Phone- (813) 5809131 Web: http://vivek4.myweb.usf.edu/ [[alternative HTML version deleted]]
On Mon, Oct 3, 2016 at 11:13 AM, Vivek Singh <vivek4 at mail.usf.edu> wrote:> Hi All: > > I am using R with MySQL, following is my code: > > library(RMySQL) > con <- dbConnect(MySQL(), user="user", password="password", dbname="db", > host="localhost") > rs1 <- dbSendQuery(con, "show tables like \"%padded\"") > all_tables3<- fetch(rs1, n=-1) > > There are 25 tables in the database when I execute the above query on the > MySQL prompt. However, the above R code giving me only 9 tables. It seems > there is some cache from which R is getting 9 tables instead of 25 tables. > Please help. >?Just to be sure that I am understanding you correctly. When you say there are "25 tables" do you mean a total of 25 tables, of all names, or a total of 25 tables which whose names end with the characters "padded". I ask because your SHOW TABLES is only looking for the names of tables which end in "padded". Please forgive me if this is a stupid question.?> > Regards, > > Vivek Kumar Singh > > PhD student, > Information Systems Decision Sciences, > MUMA College of Business, > USF > Phone- (813) 5809131 > Web: http://vivek4.myweb.usf.edu/ > >-- Heisenberg may have been here. Unicode: http://xkcd.com/1726/ Maranatha! <>< John McKown [[alternative HTML version deleted]]
You could try dbListTables() and do the subsetting afterwards. Also try dbGetQuery() instead of dbSendQuery() (There's no obvious reason that I can see to use dbSendQuery() followed by fetch. It would be interesting if the two ways return different lists of tables.) -- Don MacQueen Lawrence Livermore National Laboratory 7000 East Ave., L-627 Livermore, CA 94550 925-423-1062 On 10/3/16, 9:13 AM, "R-help on behalf of Vivek Singh" <r-help-bounces at r-project.org on behalf of vivek4 at mail.usf.edu> wrote:>Hi All: > >I am using R with MySQL, following is my code: > >library(RMySQL) >con <- dbConnect(MySQL(), user="user", password="password", dbname="db", >host="localhost") >rs1 <- dbSendQuery(con, "show tables like \"%padded\"") >all_tables3<- fetch(rs1, n=-1) > >There are 25 tables in the database when I execute the above query on the >MySQL prompt. However, the above R code giving me only 9 tables. It seems >there is some cache from which R is getting 9 tables instead of 25 tables. >Please help. > >Regards, > >Vivek Kumar Singh > >PhD student, >Information Systems Decision Sciences, >MUMA College of Business, >USF >Phone- (813) 5809131 >Web: http://vivek4.myweb.usf.edu/ > > [[alternative HTML version deleted]] > >______________________________________________ >R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >https://stat.ethz.ch/mailman/listinfo/r-help >PLEASE do read the posting guide >http://www.R-project.org/posting-guide.html >and provide commented, minimal, self-contained, reproducible code.