R Mysql Not reading all table names

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

R Mysql Not reading all table names

Vivek Singh-2
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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: R Mysql Not reading all table names

John McKown
On Mon, Oct 3, 2016 at 11:13 AM, Vivek Singh <[hidden email]> 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: R Mysql Not reading all table names

MacQueen, Don
In reply to this post by Vivek Singh-2
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"
<[hidden email] on behalf of [hidden email]> 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]]
>
>______________________________________________
>[hidden email] 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.

______________________________________________
[hidden email] 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.