Seeking Beta-testers for SQLite Pass-through engine

StatNoodle — Fri, 14 Nov 2014 22:32:44 GMT

I am seeking beta testers for a SAS SQL Pass-through engine for SQLite v3.

I can test the software for 32-bit Windows. I would be particularly interested in 64-bit Windows users and other O/S users. However, anyone interested is welcome.

This engine does not require SAS/ACCESS for ODBC, etc. or the wonderful SQLite ODBC driver. 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. I can supply this DLL for 32-bit and 64-bit Windows and it is also available from any number of locations and platforms. I have no access to SAS on any other operating system so I may draw on your expertise on other systems.
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 SAS 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 -- this is just promotion-ware).

Edit 1:

I should also mention that I have not worked out the UTF-8, UTF-16le, and UTF-16be encoding/locale stuff yet. This will be an active area during beta so any non-English/non-US locales would be great.

Edit 2:

I should clarify. I would love to develop the driver for Unix (where SQLite is available) but I do not have access to a Unix machine with SAS, SAS/Toolkit, or SQLite on the box. So if anyone has these features available, I would love to discuss compiling the engines for that platform. Or of SAS can give me access to these, I would love to do that. I can debug on Windows, SAS 32bit. I can compile the driver on Windows, SAS 64bit. The engine will run under any version of SAS v9. It can be backward compiled to SAS v8. It can be adapted for SQLite v2 (though the API calls would need to be modified...that is relatively simple.

Re: Seeking Beta-testers for SQLite Pass-through engine

Reeza — Fri, 14 Nov 2014 22:47:21 GMT

I'll test as available. Windows 7, SAS 64 Bit SAS 9.3 TS1M2

Re: Seeking Beta-testers for SQLite Pass-through engine

StatNoodle — Sat, 15 Nov 2014 00:16:17 GMT

I hope to be ready for beta before Thanksgiving. It will take that long to solve the data typing issues. I'll contact you then.

Re: Seeking Beta-testers for SQLite Pass-through engine

An3 — Mon, 24 Sep 2018 19:32:29 GMT

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

topic Seeking Beta-testers for SQLite Pass-through engine in SAS Programming

Seeking Beta-testers for SQLite Pass-through engine

Re: Seeking Beta-testers for SQLite Pass-through engine

Re: Seeking Beta-testers for SQLite Pass-through engine

Re: Seeking Beta-testers for SQLite Pass-through engine