Re: Connection getMetaData() does not throw SQL Exception
- From: "Joe Weinstein" <bigdog@xxxxxxx>
- Date: 26 Jun 2006 15:40:32 -0700
Hi Richard!
The reason the getMetaData() call doesn't fail is because all the
driver needs to do to satisfy the call is to give you a local Java
object. Only if you used it in a way that required DBMS connectivity
would it fail. For instance if you called getDriverVersion() it would
still work with a dead DBMS because the answer is coded in
the driver object. If you called getDatabaseProductVersion()
it might fail, or it might have already gotten that info when it
first logged in, and cached it.
Some calls like getTables() will surely go to the DBMS, but
these are going to be less efficient than a "select 1". There is
no simpler, faster way to test the condition of the connection,
and in any case it could fail the instant you got your 1 back ;)
but that's the best you can do. If you can cache a Prepared
Statement with that test query, and rerun the same statement,
that might save some time.
For Oracle, you can do a "select 1 from DUAL" but
"BEGIN NULL; END;" is even better. Use execute(),
not executeQuery().
I would do this:
test_ps.execute(); // doesn't have to be a query!
ResultSet r = test_ps.getResultSet();
if (r != null) r.close();
Joe Weinstein at BEA Systems
Richard wrote:
Angel,
I am modifying the data access layer (middle tier) that is database
independent. We support MySQL, Oracle, and SQL Server.
The java code does not use connection pooling. I have one JDBC connection
per thread. The connection pre-test, is used just before fullfilling a
client request. Also the pre-test is used to verify the connection the
database still exists. Usually every 60 seconds.
Originally, I was using IBM's example of "Select 1 from mytable". I
thought by using the getMetaData() method would be a more efficient test,
because I would have to create a Statment object. Is this true?
So why did the getMetaData() not throw a SQL Exception?
We will eventually move to the SQL Server 2005 driver, but since it our
current product line, I can't change the driver class name and URL format.
Most of our software is distributed.
Does the 2005 driver behave different, for my current issue?
"Angel Saenz-Badillos[MS]" wrote:
I am not condoning this necyessarily (pretesting connections defeats the
purpose of pooling for Sql Server, it does make sense for other Databases
like Oracle where establishing a connection is much more expensive) but it
sounds like you are trying to build your own pooling connection pre-testing.
IBM recommends something along the lines of executing a "select 1" (the from
table [TESTABLE] is not necessary for Sql Server) to get this done:
http://publib.boulder.ibm.com/infocenter/wasinfo/v5r1//index.jsp?topic=/com.ibm.websphere.base.doc/info/aes/ae/tdat_pretestconn.html
Have you considered moving over to the 2005 Jdbc driver?
http://msdn.microsoft.com/data/ref/jdbc/
We have just shipped the June community tech preview of this driver if you
want to play with the latest and greatest:
http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en
--
Angel Saenz-Badillos [MS] Data Programmability
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging: http://blogs.msdn.com/angelsb/
"Richard" <Richard@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F0222B76-0DC6-49BB-9802-C89DFBECD4A7@xxxxxxxxxxxxxxxx
I am trying to impliment a method, to check if the current database
connection still exists.
The problem is that Connection.getMetaData() does not throw a SQL
exception
when I shut down SQL Server 2000 instance.
I am using the latest SQL Server 2000 JDBC driver (SP3)
try
{
DatabaseMetaData dbmd = con.getMetaData();
// connection still valid
return true;
}
catch(SQLException se)
{
return false;
}
.
- References:
- Re: Connection getMetaData() does not throw SQL Exception
- From: Angel Saenz-Badillos[MS]
- Re: Connection getMetaData() does not throw SQL Exception
- From: Richard
- Re: Connection getMetaData() does not throw SQL Exception
- Prev by Date: Re: Connection getMetaData() does not throw SQL Exception
- Next by Date: RE: Login failed for user 'root'
- Previous by thread: Re: Connection getMetaData() does not throw SQL Exception
- Next by thread: Re: Connection getMetaData() does not throw SQL Exception
- Index(es):
Relevant Pages
|
Loading
