table names v_* not readable; Error: no such table
chrisdane opened this issue · 6 comments
Hi
I can read all tables from a database successfully except the tables starting with v_
(packageVersion("DBI")
= 1.1.3):
fin <- "/path/to/sqlite_db" # in shell: `file $fin` = SQLite 3.x database
db <- DBI::dbConnect(RSQLite::SQLite(), fin)
print(DBI::dbListTables(db))
# ... "v_n" "v_nitrate_ts" "v_pCO2"
tab <- DBI::dbGetQuery(db, "SELECT * FROM v_n LIMIT 5")
#Error: no such table: n
DBI::dbGetQuery(db, "SELECT * FROM v_nitrate_ts LIMIT 5")
#Error: no such table: main.v_nitrate
DBI::dbGetQuery(db, "SELECT * FROM v_pCO2 LIMIT 5")
#Error: no such table: pCO2
The file is 45G. Sorry, I don't know how to give a better example.
Thanks a lot for any help and kind regards,
Chris
Thanks. Can you please try:
tab <- DBI::dbGetQuery(db, "SELECT * FROM `v_n` LIMIT 5")
Same errors for all 3 tables named v_*
.
Weird. Can the tables be queried from the sqlite command-line interface?
Exact same errors from command line (sqlite3 3.36.0). So that means the file is probably broken?
update:
Those v_*
tables were added to the file as a schema, e.g.
sqlite> .schema v_n
CREATE VIEW v_n as select n.* where n.pqf1>2 and n.pqf2>2 and sqf>-1;
Sorry about my non-existing knowledge about sqlite. What is the problem? Why cant these schemes not be handled as a table?
Thanks a lot!
Thanks, this is weird indeed. A reproducible example would help here.