topic Re: How do I connect to a local SQLite database? in Splunk Search

How do I connect to a local SQLite database?

wcooper003 — Tue, 29 Sep 2020 10:41:52 GMT

Hi all,

This has been asked before, but I have yet to find an answer on Splunk Answers that details the actual steps to connect to a local SQLite database (e.g. embedded within a custom app). The responses i've seen either (1) point directly to the Splunk Documentation (Install drivers for other databases) which I find has minimal specifics on actually getting it working; (2) show that it works but without detailing the connection specifics (e.g., how to set up DB Connect connection and/or edit db_connection_types.conf?); or (3) say that it should 'just work' (but how do i set up a DB Connect connection if there isn't a SQLite connection type defined, since you have to pick an existing connection?).

From what I can tell, I need to define a SQLite type in the db_connection_types.conf file in order for it to work no matter what. If so, what is the serviceClass -- do I also need to write a Java class for each custom connection type? Here's what I currently have:

[sqlite]
displayName = SQLite
serviceClass = ???
jdbcUrlFormat = jdbc:sqlite:<database>
jdbcDriverClass = org.sqlite.JDBC

Any specifics on how to get this set up - either defining a connection in DB Connect or setting up the db_connection_types.conf - would be much appreciated.

Thanks

Re: How do I connect to a local SQLite database?

acharlieh — Fri, 19 Aug 2016 21:08:00 GMT

Referencing an answer on a similar question would com.splunk.dbx2.DefaultDBX2JDBC work as a generic serviceClass?

Re: How do I connect to a local SQLite database?

jcoates_splunk — Tue, 29 Sep 2020 10:42:52 GMT

this works. here's my db_connection_types.conf

[sqlite]
displayName = SQLite
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcDriverClass = org.sqlite.JDBC
jdbcUrlFormat = jdbc:sqlite:<database>
ui_default_catalog = $database$

and here's my db_connections.conf

[kegbot]
connection_type = sqlite
database = /Users/jcoates/Downloads/kegbot.sqlite
host = localhost
identity = jigglypuff
jdbcUrlFormat = jdbc:sqlite:<database>
jdbcUseSSL = 0

Re: How do I connect to a local SQLite database?

wcooper003 — Tue, 29 Sep 2020 10:40:41 GMT

Thanks for the response.

I copied your properties into apps/splunk_app_db_connect/local/db_connection_types.conf -- here is the file contents:

[local]

[sqlite]
displayName = SQLite
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcDriverClass = org.sqlite.JDBC
jdbcUrlFormat = jdbc:sqlite:<database>
ui_default_catalog = $database$

I then restarted Splunk.

However - when I go to the DB Connect v2 app and click to add a new Connection, I still don't see an option for SQLite in the Database Types - Select Database Type dropdown menu. Am I missing something?

Thanks

Re: How do I connect to a local SQLite database?

mblauw — Mon, 27 Mar 2017 14:05:38 GMT

I currently have got this same issue.. Were you already able to resolve this?

Re: How do I connect to a local SQLite database?

wcooper003 — Mon, 27 Mar 2017 14:09:27 GMT

I never got it working, I ended up using a KV store within our app instead to keep persistent data that we need.

Re: How do I connect to a local SQLite database?

mblauw — Mon, 27 Mar 2017 14:27:45 GMT

I've just replaced the file in the /default/ directory. My database type is now showing up in the list..