Is there a way to read a SQLite database using SAS?

[LoriGoldman]

Is there a way to read a SQLite database using SAS?  Is there an ACCESS Interface available?  There is an R application that writes to a SQLite database and I'm trying to figure out a way to read the data generated in the app.  Any ideas?  I'm running SAS 9.3.2 in Windows 7.  We have SAS Access for ODBC.  With the right driver, can it be used to read SQLite?

[jakarman · Accepted solution]

as you can  access SQLite and having sas/access that should be able to be used.

SQLite ODBC Driver (www.ch-werner.de/sqliteodbc) take care of the bitness. With a 32-bit SAS you are needing a 32-bit driver with a 64-bit SAS the 64-bit driver.

If you have IML you could connect that one to R. 

[Reeza]

Yes, simply download the SQLITE ODBC driver as indicated by

THere are 3 types though, besides bitness, so you'll need to know if the database was created as SQLITE 3 or another source. Or try each one 🙂 until one works.

You probably need admin privileges on your computer to accomplish this.

[StatNoodle]

Funny you should ask.  I am working on a SQL Pass-through engine for SQLite v3 right now. I am recruiting anyone interested in beta-testing the engine.  If you are interested, reply.  The engine does not require SAS/ACCESS for ODBC, etc.  This is an internal SAS pass-through engine (see below for the LIBNAME engine, information).

Current status on SQLite Pass-Through engine (SQLitePT):

• The engine connects/disconnects with the SQLITE3.DLL.
• The engine opens/closes the file you tell it to with ACCESS=READONLY | READWRITE | CREATE.
• The engine executes SELECT queries but currently returns everything as a 128 byte character string (see below).
• The engine can perform an EXECUTE (sqlite-sql-code) BY command.
• The EXECUTE () BY also includes an option to return results to the LOG or not.  This is needed because of SQLite's love of PRAGMA commands to retrieve information and the SQL-Pass-Through requirement that information may only be retrieved from the DB via a SELECT query.  EXECUTE has no facility to return information to SAS (except via LOG or OUTPUT).  I will get around this later by exploiting commented hints in the query.
• Currently working on data type evaluation which is tricky in SQLite since the engine does not really type columns, at all.  This will follow three methods: Hints, Declared types, and Guess (sample with type promotion, sample with type TEXT, sample with type MAJORITY).
• The options parser needs work -- I thought I would not need so many options but they keep spiraling and my simple state-machine is becoming cumbersome.

The Pass-through engine was my first effort because it is relatively simple [except for data typing, the SQLitePT engine is almost a direct mapping between the SAS Pass-through API requests and SQLite API fulfiller].  This is my introduction to the SQLite API.

It will be followed by a SQLite LIBNAME engine later this year or early next year.  This is partially written but nowhere near beta-testing.  Most likely January before a beta version would be available since I have a day-job and this ain't it :smileygrin: -- this is just promotion-ware).

I'm toying with a Cassandra interface.

[whiteray]

Dear Ms/Mr Noodle,

We are interested in your "SQL Pass-through engine for SQLite v3".

We are running Red Hat Linux on our server (2.6.32-431.20.3.el6.x86_64).  Would that be compatible with your DLL?

Thanks.