All Apps and Add-ons

DBConnect 3.1.4 Input

willadams
Contributor

I have setup and configured DB Connection (version 3.1.4) and have been able to get my identities and connections setup. However I am having a problem with configuring an input.

If I do the following as a "Batch" type

SELECT * FROM "mydatabase"."dbo"."mytable"

it works as expected and I get data


If I then change the input type to "Rising" and do the following:

SELECT * FROM "mydatabase"."dbo"."mytable" and chose rising_column (in this instance it has to be TIME_OPENED) I notice that the Checkpoint Value defaults to 01/01/1970 00:00:00.000

I receive the error "com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1. No results found"


If I edit the query in the Editor as follows:

SELECT * FROM "mydatabase"."dbo"."mytable"
WHERE TIME_OPENED > ?

This fails with the same error "com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1. No results found"


if I run the following command directly in search

|dbxquery query="SELECT * FROM \"mydatabase\".\"dbo\".\"mytable\"" connection="myconnection" maxrows=1000 timeout=30

it works


if I run the following command directly in search

|dbxquery query="SELECT * FROM \"mydatabase\".\"dbo\".\"mytable\" WHERE TIME_OPEN > ? " connection="myconnection" maxrows=1000 timeout=30

I get the same error (e.g. * ....The value is not set for the parameter number 1. No results found*"


However I run this query directly in search (changed "?" to "0"

|dbxquery query="SELECT * FROM \"mydatabase\".\"dbo\".\"mytable\" WHERE TIME_OPEN > 0 " connection="myconnection" maxrows=1000 timeout=30

I get data and results


If I then go back to the "Data Lab" in DB Connect and do the following:

1) Cancel current editor
2) Open a new input
3) Set my "batch type" as SELECT * FROM "mydatabase"."dbo"."mytable" **
4) Run my query and get results
5) Select Rising Column and change the Checkpoint value (single field) and set to "0" and then select the appropriate rising column (i.e. TIME_OPENED)
6) Run the search and it fails as expected
7) If I then go into "search" in the DB Connect app and run the following search **|dbxquery query="SELECT * FROM \"mydatabase\".\"dbo\".\"mytable\" WHERE TIME_OPEN > ? " connection="myconnection" maxrows=1000 timeout=30
this generates a result
8) If I then go back to the data lab with rising configuration and amend the SQL Editor with SELECT * FROM "mydatabase"."dbo"."mytable" WHERE OPEN_TIME > ? ** this gives me data
9) However I can't save the Rising Column configuration as I get an error "One or more fields are invalid, please fix them before go next".

10) I then notice that under "step 4" of the Input Type it is marked as Red telling me that I **must
have a WHERE STATEMENT and an ORDER BY statement
11) I update my query to now include a ORDER BY TIME_OPENED ASC and Execute
12) The execution then starts but never finishes (despite having a query timeout of 30 seconds) and I have to abandon the search
13) I tried to click Next while it was running and then noted that some results start showing up (strangely)
14) When I click next again I then get told by DBConnect that not step 3 in the Rising input type is not set. The error relates to the checkpoint value which is now showing as "checkpoint is invalid.
15) If I then "execute SQL" again I am back to the query running without any timeout

I have an older version of SPLUNK with DB2 on it and it doesn't need to do this. Thoughts? I may have to look for an alternative way to get this data. I also can't go back to DB2 as its not supported under 7.2.6.

0 Karma
1 Solution

willadams
Contributor

Learnt that yhis was a bug in 3.1.4. Downgraded db connect to 3.1.2 which resolved my issue

View solution in original post

0 Karma

paola92
Explorer

The correct form to solve the issue without downgrade the app is using the bulk action. In the inputs tab click Bulk Actions and select create or edit and continue the process.

0 Karma

willadams
Contributor

Learnt that yhis was a bug in 3.1.4. Downgraded db connect to 3.1.2 which resolved my issue

View solution in original post

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.