CODEWITHSUNDEEP
rsqliterodbc
Anshu Gupta
Anshu Gupta

Reputation: 21

RODBC: able to connect to db but can't find table object

I am trying to connect SQLite database using RODBC in R. RODBC is able to connect to the database but is not able to get the list of tables in database using sqlTables, which returns "0 rows". The database has 20 tables.

System: R 3.1.2, Windows 7, Rstudio

Code snippet

> library(RODBC)
> odbcGetInfo(bbdb1)
                                                          DBMS_Name 
                                                           "SQLite" 
                                                           DBMS_Ver 
                                                            "3.8.6" 
                                                    Driver_ODBC_Ver 
                                                            "03.00" 
                                                   Data_Source_Name 
                                                            "bbdb1" 
                                                        Driver_Name 
                                                  "sqlite3odbc.dll" 
                                                         Driver_Ver 
                                                            "0.999" 
                                                           ODBC_Ver 
                                                       "03.80.0000" 
                                                        Server_Name 
"C:\\Users\\shals\\Documents\\R in a nutshell\\nutshell\\data\\bb1" 
> sqlListTables(bbdb1)
Error: could not find function "sqlListTables"
> sqlTables(bbdb1)
[1] TABLE_CAT   TABLE_SCHEM TABLE_NAME  TABLE_TYPE  REMARKS    
<0 rows> (or 0-length row.names)
> sqlPrimaryKeys(bbdb1,func,errors=FALSE,as.is=TRUE,catalog=NULL,schema=NULL)
Error in sqlPrimaryKeys(bbdb1, func, errors = FALSE, as.is = TRUE, catalog = NULL,  : 
  object 'func' not found

Can anyone please help why sqlTables returning 0 rows when there are 20 tables in database.

Upvotes: 1

Views: 1260

Answers (1)

Anshu Gupta
Anshu Gupta

Reputation: 21

changed the connection string as below after which the code worked fine. 

bbdb1 <- odbcConnect(dsn="bbdb",believeNRows = FALSE,rows_at_time = 1)

Upvotes: 1

Related Questions