JDBC adapter doubts

[Former Member]

I am working  JDBC sender.

I have Microsoft sql databse.  I have to select ten fields from the database. There Is a field CurrentValue in the data structure, and it is the last field.

Every one hour, I have to select data from database. If the CurrentValue has not changed I don't have to pick data. There are two primary keys fr selecting the data. Employee name and employee number.

Is this possible with sql jdbc adapter?

Can this done by sql queerry in communication channel?

Any more inputs I need to ask?

Also telnet is disabled in my desktop, how may I check if the port given by the database guy is correct ? I want to make sure there is no firewall also

Please suggest some methods

[Muniyappan · Accepted solution]

you can do this with jdbc adapter.

in the table add one more column and give the values as "changed" if the currentValue is changed. you can use extra column in where condition to pick the data.

run the interface once in hour, you can use ATP to schedule sender channel.

for checking firewall do a telnet check. better to check with basis on this.

else run this command in your PI system (Os level)

telnet server/host port (telnet 11.242.141.11 5001)

if there is no firewall then it will show message "connected"

[Former Member · Accepted solution]

I spoke with the Databse people. They cannot add one more field in it.

So what is the option to avoid duplicate records now?

The data picked from the database will contain only the fields to be picked to the target system

[Muniyappan · Accepted solution]

without extra status column it will look tricky to select the rows from table.

i am not sure if there is any sql query which can select the row based on column value change. this is important even if you are going for stored procedure.

[Former Member · Accepted solution]

Hello,

Can your database team develop a stored procedure which will provide you with only the updated data?

regards,

Diptee

[suman_saha]

Hi,

You need to update a field to check whether the data row is already processed. Which field are you updating?Are you using <TEST> in update ?

If you are using any flag field,you may ask to reset the flag whenever CurrentValue  is changed.

Suman