Skip to Content
avatar image
Former Member

SQL query for JDBC adapter

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jun 07, 2011 at 01:43 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 07, 2011 at 01:49 PM

    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

    Add comment
    10|10000 characters needed characters exceeded