4

I am trying to connect to a sqlite database via the RODBC package.

1.) I have installed the SQLite ODBC driver from http://www.ch-werner.de/sqliteodbc/ and set it up using the ODBC Data Source Administrator in Windows 7. Settings are Lock Timeout 20ms, Sync Mode NORMAL, and "Don't Create Database" checked. I can see my data source in the "User DSN" tab as a SQLite3 ODBC Driver.

2.) In R I am running the following commands to connect to the database. No problems so far. Looks like it is set up correctly. 

library(RODBC)
con <- odbcConnect("dbss")
odbcGetInfo(con)
                                   DBMS_Name 
                                   "SQLite" 
                                   DBMS_Ver 
                                    "3.8.2" 
                            Driver_ODBC_Ver 
                                    "03.00" 
                           Data_Source_Name 
                                     "dbss" 
                                Driver_Name 
                          "sqlite3odbc.dll" 
                                 Driver_Ver 
                                    "0.996" 
                                   ODBC_Ver 
                               "03.80.0000" 
                                Server_Name 
                               "U:\\Research\\data\\smartsystemtic\\db.sqlite"

3.) However if I want to query a table or just show the tables I am running into problems. I can see (using SQLite Studio) that I have a table called "School" with 4 columns and 3 rows.

> sqlQuery(con, paste("SELECT * FROM School"))
[1] SchID     Location  Authority SchSize  
<0 rows> (or 0-length row.names)

where I know that there are 3 rows looking at SQLite Studio.

4.) Also I get

> sqlTables(con)
[1] TABLE_CAT   TABLE_SCHEM TABLE_NAME  TABLE_TYPE  REMARKS    
<0 rows> (or 0-length row.names)

while in SQLite Studio I see 4 tables for the database.

Could you give me any pointers in what I am doing wrong? Thank you.

Wolfgang Wu
  • 834
  • 6
  • 16
  • 1
    The usual way of connecting to SQLite from R is using the `RSQLite` package. Can you successfully return results using that package? – Richie Cotton Feb 14 '14 at 10:33
  • Yes, I can. Maybe this will be the way to go. I am more familiar with the RODBC package and I was hoping that I might just have a small setting wrong. The RODBC vignette/documentation says it supports SQLite. – Wolfgang Wu Feb 14 '14 at 10:45
  • Wild guess: Is it a 32/64-bit problem? Have you tried it in both 32-bit and 64-bit R? – Richie Cotton Feb 14 '14 at 11:30
  • On a similar system I was not able to get it work ... use the RSQLite package ... RODBC and DBI based package functions are not sooooo different: It might be more efficient to simply switch to the package that perfectly works with SQLite and learn slightly different functions. – petermeissner Mar 05 '14 at 13:35
  • 1
    Wolfgang Wu, did you solve the problem? I have the exact same trouble. Since I am operating with another mysql database through odbc, I wish to only use one type of drivers.. – Nemis May 19 '14 at 12:18
  • Unfortunately not. I used the RSQLite package instead. Sorry. – Wolfgang Wu May 22 '14 at 08:18

2 Answers2

3

Wolfgang,

Tommy O'Dell's answer here worked for me.

I included believeNRows = FALSE, rows_at_time = 1 when opening the ODBC connection to SQLite.

Community
  • 1
  • 1
Felix Lechner
  • 468
  • 5
  • 11
  • I found I needed the believeNRows = FALSE, but not the rows_at_time = 1. I'm glad for the latter, as it would seem to be a big performance hit. – dsz Jun 17 '15 at 00:03
  • I can confirm that `rows_at_a_time=1` is not required. Testing environment: Ubuntu + unixODBC + SQlite 3.7.16.2 + Driver version 0.992 + ODBC version 3.52 + RODBC 1.3-14 – R Yoda Feb 24 '17 at 21:21
0

I have been able to access my SQLite db using the RODBC package. I have at least 5.4 million rows in each of my 10 tables in the db. The main difference I see from your (@Wolfgang Wu) setup and code is that the SQLite 3 Datasource driver I used was accessed within the System DSN tab. I installed the 64-bit driver linked from: http://www.ch-werner.de/sqliteodbc/

Here are my commands and results.

################################################
# Create SQL tables from same-name r dataframes
################################################

  db <- dbConnect(SQLite(), dbname = "./slds.sqlite")

 # student record - stu, crs, dis, enr, prog, sped, addr
 # assessments - crct, crctm, eoct

  for (i in 1:dim(r)[1]) {
    dbWriteTable(conn = db, name = paste0(r[i, 1]), value = get(r[i, 1]),      
                 row.names = FALSE, overwrite = TRUE)
  }

# FYI - the r matrix is as follows:

# > r
#       [,1]    [,2]            
#  [1,] "stu"   "Student"       
#  [2,] "crs"   "Course"        
#  [3,] "dis"   "Discipline"    
#  [4,] "enr"   "Enroll"        
#  [5,] "addr"  "Address"       
#  [6,] "prog"  "Programs"      
#  [7,] "sped"  "Sp. Ed. Events"
#  [8,] "crct"  "CRCT(-M)"      
#  [9,] "crctm" "CRCT(-M)"      
# [10,] "eoct"  "EOCT" 


  ################################
  # Connect, access, show results
  ################################

  slds <- odbcConnect("slds_dews", believeNRows = FALSE, rows_at_time = 1)
  table_list<-sqlTables(slds)

table_list[, "TABLE_NAME"]
 [1] "stu"   "crs"   "dis"   "enr"   "addr"  "prog"  "sped"  "crct"  "crctm"     
     "eoct" 

odbcGetInfo(slds)
                       DBMS_Name 
                        "SQLite" 
                       DBMS_Ver 
                       "3.8.7.4" 
                 Driver_ODBC_Ver 
                         "03.00" 
                Data_Source_Name 
                     "slds_dews" 
                     Driver_Name 
               "sqlite3odbc.dll" 
                      Driver_Ver 
                        "0.9991" 
                        ODBC_Ver 
                    "03.80.0000" 
                     Server_Name 
               "H:\\slds.sqlite"
Yossarian
  • 1