SQL query for JDBC adapter

[Former Member]

Hello Everyone,

I am using a simple SELECT to fetch data from sql DB. The only SELECT it seems to fetch data is when it is getting data from sys tables. It does not work for fetching data from application tables created in the sql DB. Could this be a authorization issue? Listed below are the different SELECT statements used in PI and that works in SQL too.

1. SELECT Query : SELECT * from sys.tables

UPDATE : <TEST>

The above SELECT works and I can see all the xml messages in SXMB_MONI.

2. SELECT Query : SELECT * from sql_ztable

UPDATE : <TEST>

The above SELECT does not show any xml messages. There are records in the sql_ztable. When checking the CC monitoring I get the following message below.

Polling interval started. Length: 60.0 seconds

Processing finished successfully

Any suggestions?

Thanks,

Teresa

[baskar_gopalakrishnan2 · Accepted solution]

Check with DBA and see whether you have application access userid/password... I think you are using system access credentials. The log message shows that connection string is valid. Only thing you need to check application access password.

I dont see any problem with your query. In addition , also set logSQLStatement parameter and value true in the advanced tab of jdbc channel. This might help for the log tracing.

[Former Member]

Hi Teresa,

You have to maintain the select and update statements like below.

Select statement should be like

Select * from TableName where fieldname = ''

The field name should be either status field or date field or anything. You will be having some unique field in the table. This field name will ignore the unneccesary fetching records those are already fetched.

Update statement should be like

Update TableName set fieldname = '' where filedname = ''

Here the field name should be same as the select statement filed name. What ever you have used in the filed name in the select statement you have to use same fieldname in the update fieldname.

Let me know if you have any doubts. If your issue has resolved close this thread.

Thanks,

Raju

[Former Member]

Thanks for the feedback. Let me try the suggestions.

Teresa