JDBC check for database existing and SQLState codes

[Andy]

Is there anyway to check to see if a database exists before I try and
establish a connection to it using JDBC? (I'm trying this out in MySQL
but I would like to understand it for any database system so I want to
avoid vendor specific info).
It seems like the only two options are to either make a call to the
system database and see if the requested database is in its list, which
seems a little ugly, or to make the call to getconnection and then
respond to the different SQLStates within the exception generated. My
concern/question about this latter option (which seems to be more
appropriate) is what are the various SQLState values? I have tried
searching the net for a list of the state values for XOPEN and SQL99
but it seems every web reference consists of the description about
SQLState and how it uses the standard XOPEN or SQL99 specs. I am unable
to find any reference that actually gives me those specs :-(
While I can run the program and build a list of State values for
various conditions, I am loathe to doing it this way as I have no way
of knowing whether I am missing certain key conditions and whether the
responses are universal to different DBMS.
Surely this is such a basic common process that thousands of people
must have addressed this problem already. Most source code examples I
have come across seem to just assume that the database exists and that
the username and password are correct.

regards

Andy

 

[Bjorn Abelli]

...

Is there anyway to check to see if a database exists before
I try and establish a connection to it using JDBC? (I'm trying
this out in MySQL but I would like to understand it for any
database system so I want to avoid vendor specific info).
It seems like the only two options are to either make a call
to the system database and see if the requested database is
in its list, which seems a little ugly, or to make the call
to getconnection and then respond to the different SQLStates
within the exception generated.

You wouldn't gain much to make a database call in order to see if a database
call is possible, so the second option seems more adequate. Especially as
the possibilities regarding the first option is vendor dependent.

My concern/question about this latter option (which seems
to be more appropriate) is what are the various SQLState
values? I have tried searching the net for a list of the
state values for XOPEN and SQL99 but it seems every web
reference consists of the description about SQLState and
how it uses the standard XOPEN or SQL99 specs. I am unable
to find any reference that actually gives me those specs :-(

Unfortunately, even if the SQLStates are standardized, not all vendors have
implemented it. Even when they have implemented it, they haven't always
implemented the full set of codes...

Your best bet is to target some specific databases, and see what SQLState
codes they use.

Here's some of what you could find with a simple google on "SQLState":

DB2:
http://tinyurl.com/nu8nx

MySQL (implemented set listed in share/errmsg.txt)
http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html

Mimer:
http://tinyurl.com/ftrjq

Microsoft - ODBC (downloadable file):
http://support.microsoft.com/default.aspx?scid=kb;en-us;110769

While I can run the program and build a list of State values for
various conditions, I am loathe to doing it this way as I have no way
of knowing whether I am missing certain key conditions and whether the
responses are universal to different DBMS.
Surely this is such a basic common process that thousands of people
must have addressed this problem already. Most source code examples I
have come across seem to just assume that the database exists and that
the username and password are correct.

Well, most source code is built for existing databases... ;-)

// Bjorn A

 

[Thomas Fritsch]

I have tried
searching the net for a list of the state values for XOPEN and SQL99
but it seems every web reference consists of the description about
SQLState and how it uses the standard XOPEN or SQL99 specs. I am unable
to find any reference that actually gives me those specs :-(

Quoted from <http://en.wikipedia.org/wiki/SQL#Scope>:
"The SQL standard is not freely available. SQL:2003 may be purchased
from ISO or ANSI."

 

[steve]

Is there anyway to check to see if a database exists before I try and
establish a connection to it using JDBC? (I'm trying this out in MySQL
but I would like to understand it for any database system so I want to
avoid vendor specific info).
It seems like the only two options are to either make a call to the
system database and see if the requested database is in its list, which
seems a little ugly, or to make the call to getconnection and then
respond to the different SQLStates within the exception generated. My
concern/question about this latter option (which seems to be more
appropriate) is what are the various SQLState values? I have tried
searching the net for a list of the state values for XOPEN and SQL99
but it seems every web reference consists of the description about
SQLState and how it uses the standard XOPEN or SQL99 specs. I am unable
to find any reference that actually gives me those specs :-(
While I can run the program and build a list of State values for
various conditions, I am loathe to doing it this way as I have no way
of knowing whether I am missing certain key conditions and whether the
responses are universal to different DBMS.
Surely this is such a basic common process that thousands of people
must have addressed this problem already. Most source code examples I
have come across seem to just assume that the database exists and that
the username and password are correct.

regards

Andy

it is fairly easy.

you have the ip address & the port. start by opening a port , if you get a
low level connection, then something is at the other end.

you do not need any passwords/usernames because you are just opening a port.
it is the same way a "port" scanner works for a hackers tool.


I have the code (messy, never got round to tidying it up), but there is a
known bug on windows xp (related to how xp resolves dns)

Steve

 

[Monique Y. Mudama]

Is there anyway to check to see if a database exists before I try and
establish a connection to it using JDBC? (I'm trying this out in MySQL
but I would like to understand it for any database system so I want to
avoid vendor specific info).

I guess I'm not sure what problem this solves. What if you make the
check, connect to the database, and then the database's hard drive
bursts into flame? Your next DB call would still fail ...

 

[Scott Ellsworth]

I guess I'm not sure what problem this solves. What if you make the
check, connect to the database, and then the database's hard drive
bursts into flame? Your next DB call would still fail ...

A number of HA packages actually do this, strange as it may seem.
Essentially, a fast 'select 1' before pumping a complicated sql string
down the line will detect a lot of misconfiguration problems that would
otherwise flatten your production system.

The value comes from the time a failure has to exist in. If you open
connections to the db when your app starts up, then use a pool, you can
easily end up in a state where your connection is hours or days old.
Many possible changes can bork that connection up, so if you know that
the connection is old, you might try to send a fast sql command first to
detect problems that came up since your 'old' connection was opened.

You are quite correct that if the db bursts into flames, you have a
problem. This can insulate your app from problems that took place more
than a few minutes ago.

(I know - a connection pool that has a three day old connection with no
activity might best close the darn thing, but many apps use the same HA
code for frequently and rarely used connections.)

Scott

 

[Monique Y. Mudama]

The value comes from the time a failure has to exist in. If you
open connections to the db when your app starts up, then use a pool,
you can easily end up in a state where your connection is hours or
days old. Many possible changes can bork that connection up, so if
you know that the connection is old, you might try to send a fast
sql command first to detect problems that came up since your 'old'
connection was opened.

Ah. That explains it. Thank you.

 

[steve]

I guess I'm not sure what problem this solves. What if you make the
check, connect to the database, and then the database's hard drive
bursts into flame? Your next DB call would still fail ...

if you have a number of databases, you can quickly scan for the closest most
available one in less than 3 seconds.
If you attempt to connect before doing this , then a time out can cost you
3-15 minutes per database.

An example is an app I have. , it runs on portable computers.
It scans 3 different address ranges, so that if it is in our home office it
gets a connection to 192.***, it the user is on the road , then it connects
to either 211.* or 52.*

That is why it is useful.

Steve