Connecting to SQLite DB

[#1]

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

[#2]

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.

[#3]

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.

[#4 · Accepted solution]

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

[#5]

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

[#6]

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

-Bryan

[#7]

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

Hopefully you can update to at least 12.

[#8]

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!

[#9]

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

-Bryan