On June 20, 2012 08:43:31 PDT, Maury Markowitz wrote:
I'm working on a OSX10.7 ODBC query interface - type SQL, get
results. It uses the open-source iODBC library set. I've got this
working fairly well with MySQL (including major public servers on
the 'net, cool!) and Firebird.
I'd like to test it against SQLite as well, as this is obviously a
common DB on the Mac. I downloaded a version of the Northwind DB
that was converted to SQLite format, and to make sure that worked, I
also downloaded a wonderful (but SQLite-only) DB viewer known as
"Base". Everything is working well.
However, when I attempt to connect to the DB using the ODBC driver,
I connect fine but the database is not selected. This may be nothing
more than the driver reporting the connection wrong, but I can't be
sure...
So can anyone suggest any SQLite command I might send in through the
SQL interface that should return something even if there is no
active DB connection?
With MySQL I'd use "show databases" or "use mysql", is there an
equivalent in SQLite?
If you haven't already, install the ODBC Administrator tool. (It gets
installed to /Applications/Utilities.) Apple's been leaving it out
for some reason since 10.6 but provides it as a separate download at:
http://support.apple.com/kb/DL895
I have successfully used the SQLite ODBC Driver available from:
http://www.ch-werner.de/sqliteodbc/
I have it installed on my system as /usr/local/lib/
libsqlite3odbc.dylib but it can really go anywhere.
So I can do (from Terminal):
iodbctest 'DRIVER=/usr/local/lib/libsqlite3odbc.dylib;DATABASE=/tmp/
test.sq3'
And use the ODBC interface to manipulate the /tmp/test.sq3 SQLite
database successfully. If you set up a DSN using ODBC Adminstrator
(say named test3) that has the SQLite3 ODBC driver and database name
already set you can just do this:
iodbctest DSN=test3
I was never able to get SQLite pragmas that return values to work via
the ODBC driver. It seems to execute pragmas that set values okay
though. For example, you can execute 'pragma user_version=2;' But if
you execute 'pragma user_version;' expecting to get a row back from
ODBC you won't since it's not a select statement (and there's no
_pragma_value('user_version') or equivalent function that can be used
in a select statement instead).
If you're using the ODBC Administrator to set this up, first click the
Drivers tab and add a driver that uses the SQLite3 ODBC Driver, then
click one of the two DSN tabs and add a DSN that uses that driver and
then explicitly add a Database keyword with the value being the full
path to the SQLite3 database. The SQLite3 ODBC driver mostly only
cares about the database keyword.
It does, however, support several other keywords to control things
like foreign key support etc., but they don't seem to be well
documented other than by looking at the code. You can view the source
file at:
http://www.ch-werner.de/sqliteodbc/html/sqlite3odbc_8c-source.html
Then look at the source starting around line 10643.
Finally if you want to get an SQLite3 table list via ODBC, you can
always do something like:
SELECT name FROM sqlite_master WHERE type = 'table';
Kyle
P.S. As Simon already mentioned, 'SELECT sqlite_version();' will get
you the version if you're successfully connected to the SQLite driver.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
