Visokio website     Downloads     Video tutorials     KnowledgeBase  
'Refresh from Source' task of Database block(ODBC) fails to execute. - Visokio Forums
'Refresh from Source' task of Database block(ODBC) fails to execute.
  • Aruna November 8, 2011 12:24PM
    I have a ODBC Source that points to an Oracle Database in my iok file. The "Test SQL" button on the Source always results in 'SQL is valid. Query returned 92131 rows and 4 columns'.

    When I refresh the Source manually from the Omniscope window, (Tools -> Update Data) the data is refreshed correctly.
    When I use the "Refresh" Button on the Omniscope window, it also refreshes correctly. When I use the "Update All" button, Sometimes the data is refreshed, sometimes the Database error occurs. When I save and Reopen the file, then it works.
    Qn: Why is the Data Refresh from the same source show such inconsistency?

    When the iok file is Scheduled to be refreshed (Refresh from Source), it always fails and throws an ODBC Error.
    This is the Error message from the Scheduler
    ..
    CAUSE: [Microsoft][ODBC Driver Manager] Invalid string or buffer length
    java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid string or buffer length
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)...

    I use 64 bit versions of Oracle Drivers and 64 bit ODBC, Java and Omniscope Applications.

    Qn:Why does the Scheduler always fails to execute this Task? How can I overcome this issue?

    Thanks.
  • 5 Comments
  •     steve November 9, 2011 2:13AM
    This error is due to a fault in either the Windows or Java part of the ODBC connection, on 64-bit. We don't understand the nature of the problem exactly, but have seen it before solely on 64-bit with ODBC.

    You should avoid using ODBC for this unreliability but also for performance reasons; ODBC is generally considered to be sub-standard, and should only be used for databases which don't have a dedicated choice.

    The "Oracle" option in the database is a much better choice and won't exhibit this problem. Using this option, Omniscope will connect directly to your database via networking, bypassing ODBC.

    If you must use ODBC, you may find adjusting the connection caching parameters resolve the problem. Please try reducing maxper to 1, documented here, and post back if this is resolved:
    http://www.visokio.com/kb/dbcc
  • Aruna November 9, 2011 4:11AM
    Hi Steve,
    Thanks for your suggestions.

    I tried the Maxper setting in the Config file. Still the same issue persists.

    I tried accessing the database directly. However, I got the following error.
    Unable to connect to the database: I/O Exception: The Network Adapter could not establish the connection
    Please check the following possible causes: 1) Ensure the IP/hostname "hostname" is correct. 2) Ensure the port "xxxx" is correct. 3) Database server doesn't accept TCP/IP connections. 4) Firewall is blocking connections. 5) Network is down.

    Our Database server is behind the Firewall. So the only option is ODBC.

    Please advice.
  •     chris November 9, 2011 4:35AM
    Hi Aruna,

    We strongly recommend that you use the Oracle thin driver.

    A couple of quick questions:

    - Can you try setting the maxper setting to 0, does this work?

    - Does the error ever occur in Omniscope desktop? Does it ONLY occur in the scheduler?

    - Is there any way for you to use the Oracle thin driver? If there are problems with the firewall is this something you can resolve? For example, you can try setting:

    ADDITIONAL_JVM_ARGS=-Dvisokio.disableCustomProxySelector=true -DenableProxyDebug=true -Dvisokio.disableSystemProxies=true

    in the installconfig.properties file in the installation directory.
    For more information see:://www.visokio.com/kb/proxysettings

    - If this does not work is it possible to speak to your system admin to see if there is any way around this?

    Regards

    Chris
  • Aruna November 9, 2011 5:15AM
    Hi Chris,

    The following are the settings I have changed for the ODBC Cache.
    wrapper.java.additional.4=-Dvisokio.dbcc.maxall=0
    wrapper.java.additional.5=-Dvisokio.dbcc.maxper=0

    We are unable to connect using Oracle Thin client. And there are no other issues with Firewall. I am able to connect via ODBC. Still, I am trying to get in touch with the Admins to get the Oracle connection working.

    The error occurs sometimes in the Omniscope Desktop. It is resolved when I Close and Restart the Omniscope. Manual Refresh and Update Works well.

    Thanks.
  •     mustafa November 9, 2011 5:55AM
    Can I suggest that you try the setting Chris has suggested by editing the installconfig.properties rather than wrapper.conf.

    The reason why I suggest making the changes to the installconfig.properties rather than wrapper.conf is because it makes it easier to verify the problem is still present using the installed Omniscope application rather than having to use the Service.

    Can you post back on here if the error still occurs after making these changes to installconfig.properties.

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership