No rows in ODBC query to SQLite db

[iamed2]

julia> using ODBC

julia> dsn = ODBC.DSN("Driver=/usr/local/lib/libsqlite3odbc.so;Database=/Users/ericdavies/Dropbox/comp4190/A4/movies.db")
ODBC.DSN(DSN=;Database=/Users/ericdavies/Dropbox/comp4190/A4/movies.db;StepAPI=;Timeout=;SyncPragma=;NoTXN=;ShortNames=;LongNames=;NoCreat=;NoWCHAR=;FKSupport=;Tracefile=;JournalMode=;LoadExt=;BigInt=;JDConv=;PWD=)

julia> source = ODBC.Source(dsn, "SELECT * FROM movies")
ODBC.Source:
	DSN: ODBC.DSN(DSN=;Database=/Users/ericdavies/Dropbox/comp4190/A4/movies.db;StepAPI=;Timeout=;SyncPragma=;NoTXN=;ShortNames=;LongNames=;NoCreat=;NoWCHAR=;FKSupport=;Tracefile=;JournalMode=;LoadExt=;BigInt=;JDConv=;PWD=)
	status: 100
	schema: Data.Schema{true}:
rows: 0	cols: 6
Columns:
 "id"            NullableArrays.NullableArray{Int32,1}
 "name"          NullableArrays.NullableArray{String,1}
 "budget"        NullableArrays.NullableArray{Int32,1}
 "us_gross"      NullableArrays.NullableArray{Int32,1}
 "world_gross"   NullableArrays.NullableArray{Int32,1}
 "release_date"  NullableArrays.NullableArray{Int32,1}

It just doesn't seem to be able to see any rows.

A related SO question from someone else at Invenia who has their ODBC set up a bit differently: http://stackoverflow.com/questions/42767373/julia-odbc-jl-unable-to-connect-properly-to-my-sqlite-database-and-retrieve-dat

[iamed2]

               _
   _       _ _(_)_     |  A fresh approach to technical computing
  (_)     | (_) (_)    |  Documentation: http://docs.julialang.org
   _ _   _| |_  __ _   |  Type "?help" for help.
  | | | | | | |/ _` |  |
  | | |_| | | | (_| |  |  Version 0.5.2-pre+0 (2017-03-05 13:26 UTC)
 _/ |\__'_|_|_|\__'_|  |  Commit 9f40107618* (8 days old release-0.5)
|__/                   |  x86_64-apple-darwin16.4.0

julia> Pkg.status("ODBC")
 - ODBC                          0.5.1

[quinnj]

Hmmmm....I wonder if the SQLite driver is doing something funny here. Thanks for the link to the SO question, I'll try to repro and see what's going on here.

[iamed2]

Thanks!

[quinnj]

Unfortunately, it seems this is an sqlite driver problem. For some reason, it's inaccurately always returning the SQL_NO_DATA return code after any query execution. It looks like other languages have found a way around this, perhaps by just trying to fetch results anyway. I'll have to keep digging around to figure out if we can do some kind of similar work-around.

[quinnj]

Hmmmm.........even if I blindly force a resultset fetch (even though the driver has returned the SQL_NO_DATA return code and that the row count of the resultset is 0), the driver still doesn't provide any type information, so everything defaults to binary blobs.

At this point, I'm tempted to just close as wont fix and point people to the SQLite.jl package, which appropriately handles all of sqlite's weirdness with respect to resultset handling.

[iamed2]

The type information appears to be retrieved above in my example?