Layer7 API Management

Expand all | Collapse all

disable jdbc resultset caching for external dbs

Jump to Best Answer
  • 1.  disable jdbc resultset caching for external dbs

    Posted 05-29-2017 08:46 AM

    Hi,

     

    We managed to get a strange behaviour in the api gateway (9.1) with an oracle jdbc connection if we do the following:

    1. Create a new jdbc connection to an oracle db with working username / pw (Driver Class: oracle.jdbc.OracleDriver, additional driver in: /opt/SecureSpan/Gateway/runtime/lib/ext/ojdbc6.jar)
    2. Click on test
    3. Create a new service with one assertion (Perform jdbc query) which performs a 'select 0 from dual' to this connection
    4. Click here on test as well
    5. Go back to the manage jdbc connection and set a wrong pw
    6. Click 3 times on test (to lock the oracle user)
    7. Set the right password again
    8. Click test againg to make sure the account is locked
    9. Go back to the perform jdbc query assertion
    10. Click here on test --> The test still passes !?!

     

    I assume that the resultsets are somehow cached (like these properties suggests it: JDBC Cluster Properties - CA API Gateway - 9.1 - CA Technologies Documentation).

    I understand that this is useful for the internal mysql db.

    But I would like to disable the caching for external dbs. How can I achieve this? Is it a special additional property?

     

    Kind regards,

    Andi



  • 2.  Re: disable jdbc resultset caching for external dbs

    Posted 05-30-2017 11:48 PM

    Hi,

     

    I tried replicating this scenario following the steps mentioned by you against oracle db, then i added this CWP

    jdbcQueryManager.cacheStaleTimeout  and reduced the value to 1 sec to check the behavior quickly and i see  the query test fails, but with default value which is 1800 sec's the query is successful even after the account is locked.

     

    Been through the url pointed out by you and my understanding is that the properties there are for any jdbc connection and not just for internal mysql db. 



  • 3.  Re: disable jdbc resultset caching for external dbs

    Posted 05-31-2017 09:40 AM

    Hi,

     

    Thanks for finding the right property.

    As you mentioned, this property will probably set the cache timeout for all jdbc connections and I think, that this will reduce the performance of the whole gateway, because it has to reload every second instead of every 30 seconds from the internal mysql db.

    That's why, I am looking for a way which can set this caching property only for a specific jdbc connection.

    Is this possible?

     

    Regards,

    Andi



  • 4.  Re: disable jdbc resultset caching for external dbs

    Posted 05-31-2017 09:58 AM

    Hi,

     

    I never meant to set it up as low as 1 sec, just to test it quickly so that i don't have to wait for long i set it up to 1 sec.



  • 5.  Re: disable jdbc resultset caching for external dbs

    Posted 06-01-2017 04:00 AM

    Hi,

     

    I know that this was only for testing.

    My original question was if there's a way to disable the caching for a specific jdbc connection...

     

    Regards,
    Andi



  • 6.  Re: disable jdbc resultset caching for external dbs

    Posted 06-01-2017 09:34 AM

    i don't think we can do it for a specific jdbc connection



  • 7.  Re: disable jdbc resultset caching for external dbs
    Best Answer

    Posted 06-01-2017 09:40 AM

    Thank you, added it as idea

    disable jdbc caching for a specific connection 

     

    Regards,

    Andi