Getting Data In

Sybase: Rising Column option produces error: "java.sql.SQLException: JZ0SA: Prepared Statement: Input parameter not set, index: 0."

anitaroseline
New Member

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 
0 Karma

akshatj2
Path Finder

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

0 Karma

vishaltaneja070
Motivator

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

0 Karma

anitaroseline
New Member

Stil lsame error

java.sql.SQLException: JZ0SA: Prepared Statement: Input parameter not set, index: 0.

0 Karma

FrankVl
Ultra Champion

Did you actually set an initial value for the checkpoint?

0 Karma

anitaroseline
New Member

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.

0 Karma

FrankVl
Ultra Champion

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.

0 Karma

anitaroseline
New Member

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.

0 Karma

FrankVl
Ultra Champion

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.

0 Karma

anitaroseline
New Member

Correct .Let Me check them and thanks for the quick response

0 Karma

anitaroseline
New Member

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

0 Karma

anitaroseline
New Member

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

0 Karma

FrankVl
Ultra Champion

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?

0 Karma

anitaroseline
New Member

Yes and it looks like Time and date format.

0 Karma

anitaroseline
New Member

2017-01-17 13:00:00.0 like this

0 Karma

anitaroseline
New Member

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

0 Karma

vishaltaneja070
Motivator

@anitaroseline I think the issue is Checkpoint field being null. Did you update the checkpoint value? or is it blank?

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...