Geek asks and answers

Connect to SQLite via RODBC

I am trying to connect to sqlite database through RODBC package.

1.) I installed the SQLite ODBC driver from http://www.ch-werner.de/sqliteodbc/ and configured it using the ODBC Data Source Administrator in Windows 7. The timeout lock is set to 20 ms, the synchronization mode is NORMAL and "Do not create database data. " I see the data source in the "Custom DSN" tab as a SQLite3 ODBC driver.

2.) In R, I run the following commands to connect to the database. There are no problems at the moment. It seems to be configured 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.) , , . ( SQLite Studio), "" 4 3 .

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

, SQLite Studio 3 .

4.)

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

SQLite Studio 4 .

- , ? .

+3
2

,

Tommy O'Dell .

believeNRows = FALSE, rows_at_time = 1 ODBC SQLite.

+2

SQLite db RODBC. 5,4 10 db. , (@Wolfgang Wu), - , SQLite 3 Datasource, , System DSN. 64- , : http://www.ch-werner.de/sqliteodbc/

.

################################################
# 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"
0

More articles:


All Articles