Re: Affected Rows
If you are using oracle count(*) is best. According to oracle anyways. -Original Message- From: amonotod[EMAIL PROTECTED] Date: Mon, 24 Jan 2005 21:47:00 To:dbi-users@perl.orgdbi-users@perl.org Cc:Hernan Arredondo[EMAIL PROTECTED] Subject: Re: Affected Rows From: amonotod [EMAIL PROTECTED] Date: 2005/01/24 Mon PM 02:03:18 CST From: Hernan Arredondo [EMAIL PROTECTED] Date: 2005/01/24 Mon PM 12:41:56 CST anyone knows other easy method ? FROM THE DOCS! snip One alternative method to get a row count for a SELECT is to execute a ``SELECT COUNT(*) FROM ...'' SQL statement with the same ``...'' as your query and then fetch the row count from that. /snip I guess I should have pointed out that the part you really need to take notice of was the last paragraph, in which it is suggested that you run a select count() before you run your real query. One extra piece of advice, it says select count(*), but I suggest you use just select count(first_column) instead. Also, keep in mind that you're not guaranteed that the return from the select count() is the actual number of rows you'll get back from your select ... statement. The accuracy obviously depends on level of activity in your database... HTH, amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_| Sent wirelessly via BlackBerry from T-Mobile.
Re: Affected Rows
From: Hernan Arredondo [EMAIL PROTECTED] Date: 2005/01/24 Mon PM 12:41:56 CST of rows affected by a select statement, use the $var = $st-rows but it no works in select statements, the other way is making a while statement to get the result in an array, but I think is a easy way to do it , anyone knows other easy method ? rows FROM THE DOCS! snip $rv = $sth-rows; Returns the number of rows affected by the last row affecting command, or -1 if the number of rows is not known or not available. Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement. For SELECT statements, it is generally not possible to know how many rows will be returned except by fetching them all. Some drivers will return the number of rows the application has fetched so far, but others may return -1 until all rows have been fetched. So use of the rows method or $DBI::rows with SELECT statements is not recommended. One alternative method to get a row count for a SELECT is to execute a ``SELECT COUNT(*) FROM ...'' SQL statement with the same ``...'' as your query and then fetch the row count from that. /snip Regards Hernan Dario Arredondo HTH, amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_|
Re: Affected Rows
From: amonotod [EMAIL PROTECTED] Date: 2005/01/24 Mon PM 02:03:18 CST From: Hernan Arredondo [EMAIL PROTECTED] Date: 2005/01/24 Mon PM 12:41:56 CST anyone knows other easy method ? FROM THE DOCS! snip One alternative method to get a row count for a SELECT is to execute a ``SELECT COUNT(*) FROM ...'' SQL statement with the same ``...'' as your query and then fetch the row count from that. /snip I guess I should have pointed out that the part you really need to take notice of was the last paragraph, in which it is suggested that you run a select count() before you run your real query. One extra piece of advice, it says select count(*), but I suggest you use just select count(first_column) instead. Also, keep in mind that you're not guaranteed that the return from the select count() is the actual number of rows you'll get back from your select ... statement. The accuracy obviously depends on level of activity in your database... HTH, amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_|
Re: Affected Rows
From: amonotod [EMAIL PROTECTED] From: amonotod [EMAIL PROTECTED] From: Hernan Arredondo [EMAIL PROTECTED] Date: 2005/01/24 Mon PM 12:41:56 CST FROM THE DOCS! snip One alternative method to get a row count for a SELECT is to execute a ``SELECT COUNT(*) FROM ...'' SQL statement with the same ``...'' as your query and then fetch the row count from that. /snip I guess I should have pointed out that the part you really need to take notice of was the last paragraph, in which it is suggested that you run a select count() before you run your real query. One extra piece of advice, it says select count(*), but I suggest you use just select count(first_column) instead. Maybe this depends on the database server, but I do believe count(*) will be quicker than COUNT(first_column) under e.g. MS SQL. Besides the two do not have to return the same number. COUNT(forst_column) doesn't count the rows in which the first_column is null. Eg this query returns 5, 4, 3 select count(*), count(foo), count(distinct foo) from ( select 1 as Foo union all select 2 union all select 1 union all select 3 union all select null ) as Tbl If you want the count of rows, use COUNT(*), if you want the count of Foos use Count(Foo). Jenda = [EMAIL PROTECTED] === http://Jenda.Krynicky.cz = When it comes to wine, women and song, wizards are allowed to get drunk and croon as much as they like. -- Terry Pratchett in Sourcery
Re: Affected Rows
From: Jenda Krynicky [EMAIL PROTECTED] Date: 2005/01/24 Mon PM 06:02:03 CST COUNT(first_column) doesn't count the rows in which the first_column is null. snippage If you want the count of rows, use COUNT(*), if you want the count of Foos use Count(Foo). Hard to argue with that, indeed it is. Myself, I never put a NULLable column in the first position of a query. You know, since that is usually a KEYed column... Well... Usually... For me... Jenda Point taken, amonotod -- `\|||/ amonotod@| sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo _|_|_|_|_|_|_|_|
