• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

why would somebody do that??

 
Ranch Hand
Posts: 583
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
mister krabs
Posts: 13974
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
To see if the connection is alive?
 
High Plains Drifter
Posts: 7289
Netbeans IDE VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
maybe they want just one record
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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).
 
Ranch Hand
Posts: 5399
1
Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
To get the number of rows in a table without worrying abt any column name. (to execute fast)
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why would somebody do that?
It's about Being Happy....Making Friends.....
 
Michael Ernest
High Plains Drifter
Posts: 7289
Netbeans IDE VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Leave it to Matola to take the question seriously...
 
R K Singh
Ranch Hand
Posts: 5399
1
Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Michael Matola:

select count(*) from ...


Ummm....
then why would somebody do that??
 
gautham kasinath
Ranch Hand
Posts: 583
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 13974
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
how bout !isClosed() ?
 
Michael Ernest
High Plains Drifter
Posts: 7289
Netbeans IDE VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Moving this to General Computing.
 
Thomas Paul
mister krabs
Posts: 13974
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
gautham kasinath
Ranch Hand
Posts: 583
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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..
 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 583
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 583
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
well maite!! whatsup??
 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 ]
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Ranch Hand
Posts: 1170
Hibernate Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic