[datatable-help] RODBC: able to connect to db but can't find table object

[Bacou, Melanie]

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
> datatable-help at lists.r-forge.r-project.org
> https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help