Skip to Content

Sender JDBC Query Dynamically

Hi Friends,

i have a requirement like, in SQL database we have 4 lakhs of records that consists last 30 days info.

from PI at first poll interval we need to fetch the current date - (minus) 30th day records

at second interval we need to fetch the current date - (minus)29th day records

current date - (minus) 28th day records

like wise i need to fetch up to current date - (minus) one day records.

NOTE: there is no flag field to update.

please provide any suggestions to achieve the same.

Regards,

Janardhan

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Jul 27, 2016 at 06:41 AM

    Hi Janardhan,

    I guess the best option is to make this scenario synchronous and as Sender JDBC cant be synchronous better to start the process from your receiving end. If your target system is an ERP system then make this a Proxy to JDBC synchronous scenario.

    Forget about the concept of 1st pool data, 2nd pool data etc as I guess your ultimate goal is to get last 30 days records from database.

    Design your outbound proxy in a fashion that it will pass sydatum-30 days in the first run and pass the date in your outbound SI . PI will query in your JDBC system and return the values. Once response is received report will go for the next date . If somehow any call fails, it will retry with the same date.

    So this will void you pooling concept hazards. Now for JDBC table query you can call stored procedure.

    Thanks,

    Apu

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 26, 2016 at 03:44 PM

    The better way to handle this is using stored procedure , ask the DB team to create the Stored procedure in which you can implement you required logic and from PI you can call this Stored procedure with execute command.

    Br,

    Manoj

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 27, 2016 at 03:49 AM

    Hi Janardhan!

    I think that you have to change your requirement first as it contains too many bottlenecks.

    Even if you use stored procedure at DB side, how does it store the needed date for each data polling or counter for your requests?

    What if your previous request was unsuccessfull for any reason?

    What if not all data for given time interval was polled in previous request?

    I guess that you should either consider using JDBC receiver scheme where the sender system provides required data for SQL query or modify source table in DB to add indicator field and change your requirement to something like: "to poll data for last 30 days starting from backwards with the oldest untaken date per each query".

    Regards, Evgeniy.

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Janardhan!

      Like Manoj has already mentioned above you could use Stored procedure call in sender adapter but in this case your DB team developers should apply some logic for your requirement (if they want to and if they can do it).

      As for me, if there are no other ways I would try with getting select statement of your DB source view (in most cases it's a main table with other tables joined to it by some conditions). There we can modify the "main" table to add indicator field to it and use it in update statement of JDBC sender.

      By the way, what system is the receiver of your JDBC data?

      Regards, Evgeniy.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.