Issue Description : Configured Sybase to Connect with Splunk and works Fine. While using Rising Column option for the query below we receive this error :
"java.sql.SQLException: JZ0SA: Prepared Statement: Input parameter not set, index: 0."
SELECT "DBA"."f_Day_ComponentMetrics"."Day",
"DBA"."f_Day_ComponentMetrics"."Node Name",
"DBA"."f_Day_ComponentMetrics"."Node Location",
"DBA"."f_Day_ComponentMetrics"."Node Family",
"DBA"."f_Day_ComponentMetrics"."Node Availability (avg)",
"DBA"."f_Day_ComponentMetrics"."Node Availability (min)",
"DBA"."f_Day_ComponentMetrics"."Node Availability (max)",
"DBA"."f_Day_ComponentMetrics"."lt_TimeStamp"
FROM "DBA"."f_Day_ComponentMetrics" WHERE "Node Name" > ?
ORDER BY "Node Name" ASC
The issue is mainly due to rising column value being null. The rising column field should be unique for splunk to identify any new data.
You can check for the with your database administrator to add a key value to the current tables or use a timestamp filed if already available. if not you can also use cast to create a new field, example below and use this field as your rising column. Ensure you put some checkpoint value.
cast(
cast(datepart(year, eventtime) as varchar) +
right('0' + cast (datepart(month, eventtime) as varchar),2) +
right('0' + cast (datepart(day,eventtime) as varchar),2) +
right('0' + cast (datepart(hour,eventtime) as varchar),2) +
right('0' + cast (datepart(minute,eventtime) as varchar),2) +
right('0' + cast (datepart(second,eventtime) as varchar),2) +
right('0' + cast (datepart(millisecond,eventtime) as varchar),3)
as numeric(18,0)) as numtime
Try this
SELECT "DBA"."f_Day_ComponentMetrics"."Day",
"DBA"."f_Day_ComponentMetrics"."Node Name" as Node,
"DBA"."f_Day_ComponentMetrics"."Node Location",
"DBA"."f_Day_ComponentMetrics"."Node Family",
"DBA"."f_Day_ComponentMetrics"."Node Availability (avg)",
"DBA"."f_Day_ComponentMetrics"."Node Availability (min)",
"DBA"."f_Day_ComponentMetrics"."Node Availability (max)",
"DBA"."f_Day_ComponentMetrics"."lt_TimeStamp"
FROM "DBA"."f_Day_ComponentMetrics" WHERE Node > ?
ORDER BY Node ASC
Stil lsame error
java.sql.SQLException: JZ0SA: Prepared Statement: Input parameter not set, index: 0.
Did you actually set an initial value for the checkpoint?
Correct its blank.Thanks for bringing up.
Please can you advise me what could be set.For SQL inputs i have give Date and time.
But here for Sybase input i entered the same and i get same error.
You selected "Node Name" as the rising column. That doesn't sound like a timestamp field?
So either you need to use a different column, or find a suitable value from that column to use as the initial checkpoint. You could run the query without the WHERE part, to see what value shows up on top.
Yeah without WHERE works perfect.I wanted to use rising column here as i wanted to index only when there are some new events.
Date is not supported for Sybase.
Yeah, but the Name column is not going to enable you to find new records right? You'll need to find a column that actually increases for newly inserted entries. So either an auto increment row ID column or a column that provides a timestamp of when the respective line was added to the database.
Correct .Let Me check them and thanks for the quick response
Hi ,
I found a column timestamp from sybase.
But when i choose rising column input its Character set seems to be blank (meaning it will display the format of respective column like Varchar, real, timestamp).In My case its blank.Any ideas.
SELECT "DBA"."f_Day_ComponentMetrics"."Day",
"DBA"."f_Day_ComponentMetrics"."Node Name",
"DBA"."f_Day_ComponentMetrics"."Node Location",
"DBA"."f_Day_ComponentMetrics"."Node Family",
"DBA"."f_Day_ComponentMetrics"."Node Availability (avg)",
"DBA"."f_Day_ComponentMetrics"."Node Availability (min)",
"DBA"."f_Day_ComponentMetrics"."Node Availability (max)",
"DBA"."f_Day_ComponentMetrics"."lt_TimeStamp"
FROM "DBA"."f_Day_ComponentMetrics" WHERE lt_TimeStamp > ?
ORDER BY lt_TimeStamp
Helo All,
I have found another column and similar error below:
SELECT "DBA"."f_Day_ComponentMetrics"."Hour",
"DBA"."f_Day_ComponentMetrics"."Node Name",
"DBA"."f_Day_ComponentMetrics"."Node Location",
"DBA"."f_Day_ComponentMetrics"."Node Family",
"DBA"."f_Day_ComponentMetrics"."Node Vendor",
"DBA"."f_Day_ComponentMetrics"."Node Availability (avg)",
"DBA"."f_Day_ComponentMetrics"."Node Availability (max)",
"DBA"."f_Day_ComponentMetrics"."Node Availability (min)",
"DBA"."f_Day_ComponentMetrics"."Day"
FROM "DBA"."f_Day_ComponentMetrics"
WHERE "DBA"."f_Day_ComponentMetrics"."Hour" > '2018-08-26'
ORDER BY 'Hour' ASC
if you don't sort and filter by that column, can you see any data in that and if so, what does that look like?
Yes and it looks like Time and date format.
2017-01-17 13:00:00.0 like this
But one Question per splunk docs, varchar int, float, real,
bigint, number are supported with rising columns.
Hence i do not have proper timestamp filed i choose Node name.
So how could we use them in combination with checkpoint
@anitaroseline I think the issue is Checkpoint field being null. Did you update the checkpoint value? or is it blank?