DBX - How to connect to Composite Information Server

[tsunamii]

Just installed the latest Splunk DB connect (DBX) app, and attempting to create a custom database connection to Composite Information Server using the following database_types.conf stanza:

[composite]
displayName = CompositeSoftware
jdbcDriverClass = cs.jdbc.driver.CompositeDriver
defaultPort = 9401
connectionUrlFormat = jdbc:compositesw:dbapi@{0}:{1}?domain=composite&dataSource={2}
testQuery = SELECT 1 as test

The following errors were thrown when trying to validate database connection:

Encountered the following error while trying to update: In handler 'dbx-databases': Error validating database: java.sql.SQLException: An exception occurred when executing the following query: "SELECT 1 as test". Cause: Unable to parse query text: Incorrect syntax near "null". On line 1, column 17. [parser-2904201] Cause: Incorrect syntax near "null" com.compositesw.cdms.services.parser.ParserException: Unable to parse query text: Incorrect syntax near "null". On line 1, column 17. [parser-2904201] at com.compositesw.cdms.services.parser.ParserImpl.parseSql(ParserImpl.java:172) at com.compositesw.cdms.services.parser.ParserImpl.parseSql(ParserImpl.java:120) at com.compositesw.server.qe.QueryEngine.a(SourceFile:979) at com.compositesw.server.qe.QueryEngine.a(SourceFile:133) at com.compositesw.server.qe.QueryEngine.execute(SourceFile:93) at com.compositesw.server.qe.QESqlRequest.execute(SourceFile:144) at com.compositesw.server.request.HookSqlRequest.execute(HookSqlRequest.java:62) at com.compositesw.server.dbchannel.CsExec.exec(CsExec.java:100) at com.compositesw.server.dbchannel.CsExec.exec(CsExec.java:52) at com.compositesw.server.dbchannel.ServerCommand.exec(ServerCommand.java:645) at com.compositesw.server.dbchannel.ServerCommand.dispatchCommand(ServerCommand.java:166) at com.compositesw.server.dbchannel.ServerCommand.run(ServerCommand.java:82) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) Caused by: line 1:17: Incorrect syntax near "null" at com.compositesw.cdms.services.parser.SqlParser.createException(SqlParser.java:77) at com.compositesw.cdms.services.parser.SqlParser.createException(SqlParser.java:57) at com.compositesw.cdms.services.parser.SqlParser.from_clause(SqlParser.java:5954) at com.compositesw.cdms.services.parser.SqlParser.table_expression(SqlParser.java:2790) at com.compositesw.cdms.services.parser.SqlParser.query_specification(SqlParser.java:2583) at com.compositesw.cdms.services.parser.SqlParser.simple_table(SqlParser.java:2497) at com.compositesw.cdms.services.parser.SqlParser.non_join_query_primary(SqlParser.java:2461) at com.compositesw.cdms.services.parser.SqlParser.non_join_query_term(SqlParser.java:2315) at com.compositesw.cdms.services.parser.SqlParser.non_join_query_expression(SqlParser.java:1775) at com.compositesw.cdms.services.parser.SqlParser.query_expression(SqlParser.java:1238) at com.compositesw.cdms.services.parser.SqlParser.direct_select_statement_multiple_rows(SqlParser.java:847) at com.compositesw.cdms.services.parser.SqlParser.direct_sql_data_statement(SqlParser.java:766) at com.compositesw.cdms.services.parser.SqlParser.directly_executable_statement(SqlParser.java:259) at com.compositesw.cdms.services.parser.SqlParser.start_rule(SqlParser.java:193) at com.compositesw.cdms.services.parser.ParserImpl.parseSql(ParserImpl.java:159) ... 14 more [jdbc-1906510]

[splunkIT]

This is either a bug or limitation of the Composite jdbc driver (csjdbc.jar), as it does not support the query syntax SELECT 1 AS TEST. Either try to see if their latest jdbc driver will support this syntax or use the following workaround:

1> In Composite Studio, publish one of the Composites example views such as ViewOrder under /shared/examples/ into /services/databases/examples

2> modify your validation query to like: "SELECT 1 FROM ViewOrder"

testQuery = SELECT 1 from ViewOrder

Validate database connection should be successful this time.