cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar

Connecting to SQLite DB

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:

12825_pastedImage_1.png

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:

12850_pastedImage_5.png

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

12826_pastedImage_2.png

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:

12848_pastedImage_3.png

DSN:

12849_pastedImage_4.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
Eric_Hill
Staff

Re: Connecting to SQLite DB

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

View solution in original post

8 REPLIES 8
Phil_Brown
Super User (Alumni)

Re: Connecting to SQLite DB

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.

PDB

Re: Connecting to SQLite DB

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.

Eric_Hill
Staff

Re: Connecting to SQLite DB

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

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

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

-Bryan

Re: Connecting to SQLite DB

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

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!

12869_pastedImage_0.png

Re: Connecting to SQLite DB

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

-Bryan