aspose file tools*
The moose likes JDBC and the fly likes why would somebody do that?? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "why would somebody do that??" Watch "why would somebody do that??" New topic
Author

why would somebody do that??

gautham kasinath
Ranch Hand

Joined: Dec 01, 2000
Posts: 583
Hi! All!
I would like to know why some one would query a database as "select 1 from <tablename>"
and then see if the query executed properly.
Regds
Gautham Kasinath


"In the country of the blind, the one eyed man is the King"
Gautham Kasinath CV at : http://www.geocities.com/gkasinath
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
To see if the connection is alive?


Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
Michael Ernest
High Plains Drifter
Sheriff

Joined: Oct 25, 2000
Posts: 7292

maybe they want just one record
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1752
    
    2
If you want only 1 record then you should query
select distinct 1 from ...
unless you're certain that the table has only one row (like DUAL table in Oracle).
R K Singh
Ranch Hand

Joined: Oct 15, 2001
Posts: 5371
To get the number of rows in a table without worrying abt any column name. (to execute fast)
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1752
    
    2
Originally posted by Ravish Kumar:
To get the number of rows in a table without worrying abt any column name. (to execute fast)

Wouldn't
select count(*) from ...
be a clearer way of doing the same thing? I'd suspect most databases don't actually perform a count for count(*), but rather just check some table metadata that knows the current number of rows.
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
Why would somebody do that?
It's about Being Happy....Making Friends.....
Michael Ernest
High Plains Drifter
Sheriff

Joined: Oct 25, 2000
Posts: 7292

Leave it to Matola to take the question seriously...
R K Singh
Ranch Hand

Joined: Oct 15, 2001
Posts: 5371
Originally posted by Michael Matola:

select count(*) from ...

Ummm....
then why would somebody do that??
gautham kasinath
Ranch Hand

Joined: Dec 01, 2000
Posts: 583
Well thats exactky what I m wondering.
Finding if the connection is still alive does seem a good possibility though.. but I would rather do a isAlive() on the connection object than execute a query.. wudnt you??
Regds
Lupo
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
Originally posted by gautham kasinath:
Well thats exactky what I m wondering.
Finding if the connection is still alive does seem a good possibility though.. but I would rather do a isAlive() on the connection object than execute a query.. wudnt you??
Regds
Lupo

I would love to use isAlive()... to bad there is no such method on a Connection object!
I have found that the only sure way to find out if a Connection is connected is to run a query.
swaroop shastri
Ranch Hand

Joined: Aug 15, 2001
Posts: 65
how bout !isClosed() ?
Michael Ernest
High Plains Drifter
Sheriff

Joined: Oct 25, 2000
Posts: 7292

Moving this to General Computing.
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
Originally posted by swaroop shastri:
how bout !isClosed() ?

isClosed() only tells you if someone ran the close() method on the Connection object. It does not actually check to see if the Connection is still open to the database.
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
I was looking at some connection pooling software the other day and part of its configuration allows you to specify a query that the connect pool software will run when it wants to check that a connection is still alive before it hands the connection out to the app.
So I would say doing a query is the main way of checking.
Adam


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
gautham kasinath
Ranch Hand

Joined: Dec 01, 2000
Posts: 583
I still maintain it surely is of no use to be FORCED to execute a query ( without any sense ) just to check if the connection is alive..
It really doesnt make sense to me..
Braj Prasad
Greenhorn

Joined: Apr 08, 2002
Posts: 16
This query is executed to ensure a row exists in the table for the select statement being executed. If such a row exists it returns '1', which can be used to evaluate for certain boolean conditions, else the result set returned is empty.
Its perceived to be an efficient way, when performing DB checks, from database load point-of-view.
gautham kasinath
Ranch Hand

Joined: Dec 01, 2000
Posts: 583
Thanks for that maite!!
But can you elaborate on how such a thing is an efficient way?? esp. on DB load point of view??
Regds
Gautham Kasinath
gautham kasinath
Ranch Hand

Joined: Dec 01, 2000
Posts: 583
well maite!! whatsup??
Michael Zalewski
Ranch Hand

Joined: Jun 10, 2002
Posts: 30

The database must scan the table. The database can stop looking and return a '1' as soon as it finds 'SOMETHING'.

The database must scan the whole table. It cannot stop after it finds the first match, because it must do more work to count all the matching rows.
If no match exists, both forms will scan the entire table. But the first query will return zero rows. (ResultSet.next() returns false). The second query will return a single row with the number '0'.
[ June 11, 2002: Message edited by: Michael Zalewski ]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Michael Zalewski:

The database must scan the table. The database can stop looking and return a '1' as soon as it finds 'SOMETHING'.

The database must scan the whole table. It cannot stop after it finds the first match, because it must do more work to count all the matching rows.
If no match exists, both forms will scan the entire table. But the first query will return zero rows. (ResultSet.next() returns false). The second query will return a single row with the number '0'.
[ June 11, 2002: Message edited by: Michael Zalewski ]

I think this answer is only partially true.
This will do a complete table scan returning a 1 for every column that matches the criteria( unless it is an indexed column ). It doesn't stop when it finds the first one.
The count function does a different kind of search. From what I understand it uses a binary algorithm of some sort to count the number of matches. This is exponentially more efficient than a regular query.
But I'm sure that it also depends on the database implementation of the count function, and other unique database properties.
Jamie
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

I do this to get new IDs to use in my table
"SELECT MAX(ID) FROM table_connectors;"
then I add 1 to the value returned
a "SELECT COUNT(*)" will get the NULLs as well but I dont think the "SELECT 1" will get the NULLs.
But since you say the only check on the data is to see if it worked correctly, then we must assume it is a check to see if
A. the table exists
on a database that likely has no other way to check if the table exists. Let us not all assume this person used the best most efficient way to achieve his objective.
 
wood burning stoves
 
subject: why would somebody do that??