Re: Setting Query Timeout through JDBC (CA Driver problem?)



I suggest (strongly) to you not using commands like ""set lockmode ..."
inside jdbc because they are not intended for being used like that nor
supported. So you can get unexpected and sometimes bizarre results
quite hard to follow. The type of everything is ok on tests and later
someday in production everything is collapsed by locks and nobody knows
really why (pooling, affairs of concurrency, etc).

After this warning, and if you are still thinking to go on, this piece
of code (not ours) should work (or does it some years ago) Use it at
your own risk.

try {
myStmt = myConn.createStatement();
myStmt.execute("set trace point rd020"); // Seems to disable
iisynonym locks (obsolete ?)
myStmt.execute("set lockmode session where timeout=40");
myConn.commit();
}

As mentioned, the pooling, the way your application is coded etc, (or
even if it will be supported in later versions of ingres through jdbc)
does not assure that it will work as you think.

I found missing in your code the transaction mode setting. If you plan
to read and make no lock it should be something like this

ingConn = getConnection(....)


ingConn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
ingStmt = ingConn.createStatement();
ingRset = ingStmt.executeQuery(aReadOnlyQuery);

About query timeout we have not used it, but I do not remember any
place that says that it is unsupported in ingres JDBC. But it could
not, I do not know.

We work too with uPortal (sometimes channels or checks inside Uportal
use ingres) . If you want I can give you more details offline.

Carlos

(losalo atsign unavarra dotsign es)

mark.berry@xxxxxxxxxxxxxxxx ha escrito:

I've been trying to set a timeout for my Ingres queries, but I can't
seem to get the necessary statements through the CA JDBC driver.

I'm using the CA driver (ca.edbc.jdbc.EdbcDriver), from a java channel
running inside uPortal, to connect to Ingres SPARC SOLARIS Version II
2.6/0201 (su4.us5/00) running on SunOS 5.9.

My first try was to pass through the following command just like a
regular query, immediately before the actual query:

set lockmode session where readlock=nolock, timeout=2

However, this produces a syntax error and says the correct syntax would
be
'OPEN CURSORID CURSOR FOR fullselect....'
I take this to be an error from within the JDBC driver rather than
coming from Ingres itself.

I've then tried to change my java code to call the query in various
different ways, but they all fail with syntax errors.

I've also tried the direct route through the JDBC command:
stmt.setQueryTimeout(2);
I've got this to work fine for connecting to SQL Server databases but
it doesn't seem to have any effect on my Ingres queries.

My java code for running the lockmode query is:
try
{
con = getConnection(jdbcDriver, jdbcUrl, jdbcUser,
jdbcPassword, RETRY_COUNT, prevErrorMsg);
stmt =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery("set lockmode session where
readlock=nolock, timeout=2");
}
catch (SQLException sqle)
{
etc


As I've been reading aound this subject, I've recently read comments
suggesting that my attempted method could in any case be problematic
because of connection pooling, which may mean that the timeouts I set
will be applied to any other queries sharing the same session. However
for now I can't even get these timeout settings through at all. If I
could, maybe I could deal with the connection pooling problems by
resetting the session timeout to 0 (unlimited) immediately after
running each query?

For now, though, the first question is simply whether the CA drivers
support setQueryTimeout(), and if not, how can I set a timeout for a
query? (I need to set different length timeouts for different queries
so a global setting wouldn't solve my problem).

Many thanks,

Mark Berry
ELVI Portal (Web) Developer
University of Nottingham

.

Relevant Pages

  • Re: why excute query through JDBC much slow than query analyzer?
    ... why excute query through JDBC much slow than query analyzer? ... The SendStringParametersAsUnicode is a connection string attribute that is ... If you capture the Execution Plan in a SQL Profiler trace while executing ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: database toolbox problem
    ... I have found a workaround for this problem. ... This bug is already listed in the Matlab bug reports and ... query = 'ALTER TABLE test ADD test_col VARCHARNOT NULL'; ... "The JDBC spec states that you must either use ...
    (comp.soft-sys.matlab)
  • Re: JDBC / ODBC problem
    ... surprised if you find bugs in databases, JDBC or ODBC drivers. ... Do the query differently. ...
    (comp.lang.java.programmer)
  • Re: SQL Developer - Selects
    ... JDBC offers to set the maximum number of rows returned by the query, and I assume that SQL Developer is using that feature - especially as you cannot see any limits when you trace the statement. ... My assumption is that the JDBC driver only sends the defined limit to the server, and the server is able to take this into account when returning the result. ...
    (comp.databases.oracle.server)
  • Re: number of rows in a resultset
    ... > JDBC allows the driver to read the results in chunks. ... > If your ResultSet isn't scrollable, then you have to walk through it: ... Also, maybe the first query could be an SQL "count", and the second query ... and as I am no SQL expert I have no idea if it would provide any ...
    (comp.lang.java.programmer)