topic Re: cannot use DB connect app with H2 database in All Apps and Add-ons

cannot use DB connect app with H2 database

jzhong_splunk — Mon, 28 Sep 2020 15:33:21 GMT

I tried to use DB Connect to get data from H2 database.

My H2 database files are at

/usr/share/tomcat7/prodDb.h2.db
/usr/share/tomcat7/prodDb.h2.db.bak
/usr/share/tomcat7/prodDb.lock.db
/usr/share/tomcat7/prodDb.trace.db

(This database is automatically created by Grails framework for Java)

I cannot use DB Connect app to connect to this existing database. Instead, it has problem to locate the database.

The correct JDBC connection string should be "jdbc:h2:/usr/share/tomcat7/prodDb;MVCC=TRUE;LOCK_TIMEOUT=10000"

But DB Connect doesn't allow me to set the full JDBC string. Instead, it asks me about the database file.

If I set /usr/share/tomcat7/prodDb, then it will create folder at /opt/splunk/var/dbx/usr/share/tomcat7/prodDb.h2

Damn! In the web page, it reads

"You can either specify the absolute
path to the database or place it under
$SPLUNK_HOME/var/dbx/.h2".

It seems that the absolute path doesn't really work.

Then I tried to use relative path:
../../../../usr/share/tomcat7/prodDb.h2

Then DB connect will create folder ../../../../usr/share/tomcat7/prodDb.h2/db

This drives me crazy. Then I have to decompile the dbx.jar to see what kind of code logic it uses.

in dbx.jar/com.splunk.dbx.sql.type.impl.H2

  public java.sql.Connection connect(String host, int port, String username, String password, String database, java.util.Map params, com.splunk.config.SplunkConfigStanza configStanza) throws java.sql.SQLException {
    return connect(host, port, "sa", "", database, params, configStanza);
  }

  public String getConnectionString(String host, int port, String database, java.util.Map params) {
    return String.format("jdbc:h2:%s;database_to_upper=false", {getDatabasePath(database)};);
  }

  private String getDatabasePath(String database) {
    java.io.File parentDir;
    parentDir = com.splunk.env.SplunkContext.getEnvironment().getSplunkPath(0, 1, {"var","dbx",String.format("%s.h2", {database};)};);
    return new java.io.File(parentDir, "db").getAbsolutePath();
  }

According to this code. DB connect will also connect H2 with user "sa", and JDBC path with template jdbc:h2:%s;database_to_upper=false. For the database, it always use %SPLUNK/var/dbx/%db%, and automatically created folder, %db.h2/db.

I would suggest the DB connect team just opens a JDBC string for us to specify. Don't be too smart.

Re: cannot use DB connect app with H2 database

jcoates_splunk — Wed, 01 Jan 2014 01:01:08 GMT

Hi,

you should be able to specify anything by editing the configuration file directly; would that work better for you?

Re: cannot use DB connect app with H2 database

ziegfried — Fri, 03 Jan 2014 17:58:25 GMT

I'd generally advice against accessing a H2 db via the filesystem from multiple processes. The safest way would be to copy over the database files into a folder $SPLUNK_HOME/var/dbx/<dbname>.h2 or to enable the server mode of H2 in the host application and add a new database type to connect to it via TCP.

For the server approach see http://www.h2database.com/html/tutorial.html#using_server on how to start the TCP server of H2. Here's an example of a new database type definition that should work for connecting to such a server:

database_types.conf:

[h2tcp]
displayName = H2 (TCP Server)
jdbcDriverClass = org.h2.Driver
connectionUrlFormat = jdbc:h2:tcp://{0}/{1}
testQuery = SELECT 1
defaultSchema = PUBLIC

It's also possible to add a custom database type for connecting to an H2 db located at an arbitrary path (again, not recommend if multiple processes are accessing the same files):

[h2abs]
displayName = H2 (absolute path)
jdbcDriverClass = org.h2.Driver
connectionUrlFormat = jdbc:h2:{1};MVCC=TRUE;LOCK_TIMEOUT=10000
testQuery = SELECT 1
defaultSchema = PUBLIC
local = true

Re: cannot use DB connect app with H2 database

jzhong_splunk — Mon, 28 Sep 2020 15:34:54 GMT

Thank you, sigi.

I end up with using H2 AUTO_SERVER mode.
In the grails project, I set jdbc:h2:prodDb;MVCC=TRUE;LOCK_TIMEOUT=10000;AUTO_SERVER=TRUE

Then use the same in the Splunk DB Connect.

By doing so, H2 will use in-process call for Grails webapp, and use a tcp port for DB connect to read data.

In my project, the Grails app will generate log and also create/update data in H2. I'd like to create splunk dashboard to show data from log and database. So copying the H2 database to somewhere else is not an option.

After checking database_types.conf.spec, I know how to set JDBC string. Thx.