Getting Error on Running Time Based Flex Connector

[abhishekag]

Dear All,

I am facing the issue to run the Time Based DB Flex Connector.

version.order=0

version.id=1

version.query=SELECT AD Name,RoleName from Assign of permission

lastdate.query=SELECT max(UpdatedOn) from Assign of permission

query=SELECT User,AD Name,RoleName,UpdatedBy,UpdatedOn from Assign of permission   where UpdatedOn >= ? order by UpdatedOn

timestamp.field=UpdatedOn

uniqueid.fields=AD Name,RoleName

event.name=__stringConstant("Permission Assigned")

event.sourceHostName=AD Name

event.deviceReceiptTime=UpdatedOn

event.sourceUserName=User

event.deviceCustomString1=UpdatedBy

event.deviceCustomString1Label=__stringConstant("Permission Assigned By")

event.deviceCustomString2=RoleName

event.deviceCustomString1Label=__stringConstant("Type of Permission")

event.deviceVendor=__stringConstant("CRM")

event.deviceProduct=__stringConstant("CRM Applications")

Getting the following Error:

NFO   | jvm 1    | 2016/04/16 12:52:14 | [Sat Apr 16 12:52:14 IST 2016] [ERROR] Unable to get last date for [jdbc:sqlserver://x.x.x.x:1433;DatabaseName=DBxxx] with query [null] defaulting to system time [Sat Apr 16 12:52:14 IST 2016].

INFO   | jvm 1    | 2016/04/16 12:52:14 | [Sat Apr 16 12:52:14 IST 2016] [INFO ] Query will start at date [Sat Apr 16 12:52:14 IST 2016] for [jdbc:sqlserver://x.x.x.x:1433;DatabaseName=DBxxx]

Looking your Support.

[skrc851]

Hi Alex,

Thanks for that suggestion..however the "CAST" function returns the hour of the day as 00:00:00 (the date is perfect anyways). The DB team here mentioned that they use a PL/SQL client to connect to the Oracle DB and that retrieves the time details of the event for them. Not sure if we can try anything as such in the connector..any clue? please let me know, thanks in advance.

[yurlov.a]

Hi Saravanakumar,

All is right. You have date 18.11.2016, if you cast it to timestamp, you get 18.11.2016 00:00:00. It is normally, I have some databases with the same situation. Timestamp conversion is necessary for connector, because time-based connector works ONLY with this datatype. Date datatype can not contain time. Of course, you have events from the same date. In this case you will loose events with duplicates id.

I recommend you to analyze your DB with Oracle SQL Developer (Oracle SQL Developer Downloads ). Post your sample content from database to continue our work.

Regards,

Alexander

[skrc851]

Hi Alex..Thank you for the Oracle-SQL developer, attached is the sample content.

I've decided to go with "Time_login" as the unique (timestamp) field as there are hh:mm:ss within it. Please refer my attachment, the format of the "Time_login" and "As_of_Date" is in 'hh:mi:ss.00000', how do I map this to endTime (I got the error "verify the datatype" during my attempt). Kindly suggest.

[skrc851]

Hi Alex..thanks for your help..this is done now. I'm posting the below for the benefit of the community.

I had taken "Time_login" as the timestamp field and had cast'ed this field only at the end of the query (making it available in 'timestamp' format only for the connector's last read pointer). I mean, my query looks as below

"query=SELECT AS_OF_DATE, TIME_LOGIN, TIME_LOGOUT, Status_MSG, EMP_CODE from Assign of permission where CAST (TIME_LOGIN as TIMESTAMP) > ?"

This query provide the results of AS_OF_DATE, TIME_LOGIN, TIME_LOGOUT in the 'date' format itself (as declared in the oracle DB) and the "CAST (TIME_LOGIN as TIMESTAMP) > ?" in timestamp format. The timestamp formatted result is used for "timestamp.field" and all the other 3 date coloumns are mapped with endtime, deviceCustomDate1 & deviceCustomDate2. That solves the issue I faced.

[yurlov.a]

Hi Saravanakumar,

It is very interesting case, because I've never faced with this problem. If we read FlexCon_DevGuide.pdf we see the following:

What data types are supported in SQL server database?

These are the supported SQL server data types:

tinyint

bit

smallint

numeric

bigint

varbinary

float

char

varchar

nvarchar

ntext

timestamp

For other data types, the CASTing function might be required.

So, I have a little confuse. I always use CASTing in this case. I have many connectors which work this way...

Thanks for your answer, it's really helpful.

Regards,

Alexander