0 Replies Latest reply on May 25, 2016 9:40 AM by llicht RSS
    llicht Newbie

    Controlling fetch size of oracle sql connection



    We have a database Thing that connects to an oracle database via:

    JDBC Driver Class Name: oracle.jdbc.driver.OracleDriver

    JDBC Connection String: jdbc:oracle:thin:<....>


    By default, the fetch size used for this connection is very low, causing some queries that are meant to allow the user to export a large volume of data (e.g. 5000 rows) take 15 seconds (only 0.3 seconds of which are spent doing the query in the database).

    If I set a larger fetch size (e.g. 5000), the query can complete in as little as 0.7 seconds.


    However, I searched and haven't found a way to set the fetch size from Thingworx.

    Does anyone know how this can be done?


    (I found some connection strings online (in jdbc-fetch-size and extremely large dataset (35... | Oracle Community ) that would suggest you could add ";jdbc-fetch-size=<value>;" to the connection string in order to set the fetch size (at least for a jdbc:sqlserver connection string, so it may not be applicable here), but when I tried it on my jdbc:oracle:thin string in Thingworx, I got the following error:

    IO Error: Invalid connection string format, a valid format is: "host:port:sid")


    (JDBC documentation about fetch size: Result Set)