Connect to SQLite via RODBC

Question

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.

Answer 1

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.