topic Connecting to SQLite DB in Discussions

Connecting to SQLite DB

michaelaschmidt — Wed, 19 Oct 2016 04:11:41 GMT

Hi Everyone, I'm trying to use JMP to connect to a SQLite db and I'm running into a very funny problem where the text fields in the first few rows of data get mangled, while everything else looks fine.

I'm using JMP 11.2.1 for Mac, running Mac 10.11.6, and I'm using the ODBC available here: SQLite ODBC Driver

I've created a very simple database (attached) to test this issue. When I open JMP, and go to File > Database > Open Table I can see connect to the DSN that I've previously setup in the ODBC, and JMP finds the right table name and all.

When I view the db in the SQLite Manager add-on for Firefox, I see the following:

which is exactly what I would expect to find. I can also query the table directly using the sqlite3 db api for python, and I can confirm that the entries are as shown:

Yet, when I try to load this table into JMP, I instead find this:

Very strange! It's getting the integer and floating point fields correctly, but it only got the first two characters of the label column correctly for the first row, and it truncated the 'd' off the label in row 2.

For completeness, here's the info I have in my ODBC setup:

Driver:

DSN:

Any help on this would be much appreciated, I'd really love to be able to use JMP to run queries on SQLite databases.

Thanks,
Mike

Re: Connecting to SQLite DB

Phil_Brown — Tue, 27 Sep 2016 14:34:49 GMT

Hi michaelaschmidt0,

Not sure if its a JMP version issue, maybe Craige@JMP or someone else from JMP can weigh in.

I have JMP 13 and Mac 10.11.6 (El Capitan). I did notice there were multiple versions of the SQLite driver on the site you mentioned. I downloaded the following version:

sqliteodbc-0.9993.dmg

After installing and configuring as per the instructions, I was able to successfully retrieve your table, without the truncation.

Re: Connecting to SQLite DB

Eric_Hill — Tue, 27 Sep 2016 14:46:50 GMT

Hey, michaelaschmidt0,

I am able to replicate the problem you describe at JMP 11.2.1 on the Mac, but then I tried JMP 12, and the truncation did not occur. I would say your best bet at this point is to see if you can get to JMP 12 (or 13, just released last week!).

HTH,

Eric

Re: Connecting to SQLite DB

michaelaschmidt — Tue, 27 Sep 2016 16:06:49 GMT

Hi Eric, thanks for the feedback on this, I will request a site upgrade to JMP12 (or maybe 13) and will verify that it works after the upgrade. Once I'm able to verify, I'll mark your answer as correct.

As an aside, I'm curious if there's a work-around for JMP11 though, given that the issue goes away when upgrading I'm guessing it's a problem on the JMP side....or maybe the work-around is the upgrade.

Thanks again!

Mike

Re: Connecting to SQLite DB

michaelaschmidt — Tue, 27 Sep 2016 16:08:08 GMT

Hi PBZ - thanks for checking in on this, I'm using JMP 11.2.1 for Mac, running Mac 10.11.6, and I installed the version of the ODBC driver that you link to above. As per Eric's comment below, this looks like an issue with JMP 11, I will try to upgrade to confirm this.

Re: Connecting to SQLite DB

bryan_boone — Tue, 27 Sep 2016 17:22:39 GMT

I'll see where the problem is and check for a workaround.

-Bryan

Re: Connecting to SQLite DB

bryan_boone — Tue, 27 Sep 2016 17:53:32 GMT

Unfortunately, there doesn't seem to be a workaround in JMP 11.

Hopefully you can update to at least 12.

Re: Connecting to SQLite DB

michaelaschmidt — Wed, 19 Oct 2016 04:13:19 GMT

Hi Eric, I've just upgraded to JMP13 and I can confirm that this works. Thanks for the help to you and everyone on this thread!

Re: Connecting to SQLite DB

bryan_boone — Tue, 20 Mar 2018 01:48:07 GMT

As of JMP 14, you can now open SQLite files without using the ODBC driver.

-Bryan