Hi Julie,
This is not specific to data.table (so you should rather post to another R list). If
you can, try to use RJDBC instead of RODBC along with MySQL official JDBC driver
(instead of DSN) at:
http://dev.mysql.com/downloads/connector/j/
Here is an example to connect to MySQL:
## Not run:
drv <- JDBC("com.mysql.jdbc.Driver",
"/etc/jdbc/mysql-connector-java-5.1.20-bin.jar", "`")
conn <- dbConnect(drv, "jdbc:mysql://localhost/test")
dbListTables(conn)
data(iris)
dbWriteTable(conn, "iris", iris)
dbGetQuery(conn, "select count(*) from iris")
d <- dbReadTable(conn, "iris")
If you're limited to DSN then make sure you're passing uid and pwd to odbcConnect(),
else check with your DB admin that you have the right credentials and permissions on
the database server.
--Mel.
On 6/19/2012 12:04 AM, bbgtrack1 wrote:
Hi all,
I am relative new to using r in conjunction with database.
I am trying to connect from RStudio in Win7 to MySQL database in Linux
Ubuntu 12.04.1 via DSN.
library("RODBC")
channel<-odbcConnect("test_mysql")
odbcGetInfo(channel)
DBMS_Name DBMS_Ver
"MySQL" "5.5.24-0ubuntu0.12.04.1"
Driver_ODBC_Ver Data_Source_Name
"03.51" "test_mysql"
Driver_Name Driver_Ver
"myodbc5.dll" "05.01.0011"
ODBC_Ver Server_Name
"03.80.0000" "***.***.*.** via TCP/IP"
sqlTables(channel)
[1] TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
<0 rows> (or 0-length row.names)
sqlPrimaryKeys(channel,func,errors=FALSE,as.is=TRUE,catalog=NULL,schema=NULL)
Error in sqlPrimaryKeys(channel, func, errors = FALSE, as.is = TRUE, catalog
= NULL, :
object 'func' not found
the connection seems to have gone through, and the table "func" exist and
non-empty, in fact the database has 20 tables but it returned non, yet it
couldn't locate one and return no value from the table. Is there something
obvious here that I don't see?
Can someone please advice.
Thanks,
Julie
--
View this message in context:
http://r.789695.n4.nabble.com/RODBC-able-to-connect-to-db-but-can-t-find-table-object-tp4633794.html
Sent from the datatable-help mailing list archive at Nabble.com.
_______________________________________________
datatable-help mailing list
[email protected]
https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help
_______________________________________________
datatable-help mailing list
[email protected]
https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help
