I'm trying to get Splunk to login to a MS SQL database and execute a stored procedure based upon data in the events. This is currently running on a Windows 2008 server (but may be moving to Linux shortly).
Based on what read here (http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Setupadatabaselookuptable), I should be able to specify the datatype for each field. How do I determine if my JDBC driver supports parameter metadata?
The definition for the lookup is:
[Bit9_Local_Approval]
advanced = 1
database = Bit9Alpha
input_fields = host_id,hash,file_state,ret_val
query = DECLARE @ret INTEGER\
EXEC @ret = bit9_public.ChangeLocalFileState @computerId=$host_id:INTEGER$, @fileHash = $hash:NVARCHAR$, @fileState=$file_state:INTEGER$,\
SELECT ret_val = @ret
I see the folloiwng error in the dbx.log:
2013-07-22 12:56:43.741 dbx7931:ERROR:BridgeSession - Exception occured while executing command: java.lang.AbstractMethodError: net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setNString(ILjava/lang/String;)V
java.lang.AbstractMethodError: net.sourceforge.jtds.jdbc.JtdsPreparedStatement.setNString(ILjava/lang/String;)V
at com.splunk.util.Utils$SQL.applyArgumentAsSqlType(Utils.java:1305)
at com.splunk.dbx.sql.util.NamedPreparedStatement.set(NamedPreparedStatement.java:108)
at com.splunk.dbx.sql.util.NamedPreparedStatement.set(NamedPreparedStatement.java:118)
at com.splunk.dbx.lookup.DatabaseLookupExecutor.performAdvancedLookup(DatabaseLookupExecutor.java:174)
at com.splunk.dbx.lookup.DatabaseLookupExecutor.performLookup(DatabaseLookupExecutor.java:39)
at com.splunk.runtime.SplunkLookup.invoke(SplunkLookup.java:26)
at com.splunk.bridge.session.BridgeSession.call(BridgeSession.java:92)
at com.splunk.bridge.session.BridgeSession.call(BridgeSession.java:30)
Looks like the JDBC driver doesn't support the NVARCHAR datatype for the prepared statement. You can try VARCHAR instead:
... @fileHash = $hash:VARCHAR$, ...
The database connection was set to read-only.
I tried the same lookup on a RHEL 6 box and got the same exception.