Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

hits() method NOT working for do_query(), update(), modify() methods in GT::SQL::Table!

Quote Reply
hits() method NOT working for do_query(), update(), modify() methods in GT::SQL::Table!
It seems the hits() method is not working for do_query(), update(), modify() methods!
Only the rows() method works for update().

Do the following with do_query():
Code:
my $links_sth = $links_db->do_query("UPDATE apro_links SET col = 1 WHERE col IS NULL);
my $hits = $links_db->hits;
my $rows = $links_sth->rows; # will result error Unknown method 'rows'
The result of $hits will be 0, even if there was a lot hits.
hits() not working. rows() not available, error.
It seems do_query result number can not counted at all.
A workaround would be to create a condition obkect with same where clause, and then count with $links_db->count ($cond); But this means an additional, unnecessary query.


Do the following with update():
Code:
my $cond1 = GT::SQL::Condition->new( "col", 'IS', \'NULL' );
my $links_sth = $links_db->update ( {"col" => 1}, $cond1);
my $hits = $links_db->hits;
my $rows = $links_sth->rows;
The result of $hits will be 0, even if there was a lot hits. $rows has correct result.
hits() not working. rows() works.


Did not test modify(), but very likely will get the same result as update().


hits() method reads the hits number from $self->{last_hits}.
Final conclusion is: do_query(), update(), modify() are not updating the $self->{last_hits}, therefore the hits() method can not read the hits number from it.

Would be fine to correct these bugs (if are bugs) in GT::SQL::Table module!
I checked the GT::SQL::Table module (Table.pm v1.220), which is used in LSQL v2.1.2, and GCommunity v0.9.0 and this feature is still not missing.

IMHO, hits() should be added (corrected?) to before the final release of GCommunity (of course you know, if it fits the time until final release or not).

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [webmaster33] hits() method NOT working for do_query(), update(), modify() methods in GT::SQL::Table! In reply to
It's been buggy for awhile.

GT changed how they test for hits, if you check the code, and the older methods were unfinished.

I was hitting this, but never banged on it like you did.

Maybe Alex/GT has a point to start with now :)


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [webmaster33] hits() method NOT working for do_query(), update(), modify() methods in GT::SQL::Table! In reply to
Hi,

hits() only applies for selects. I'll update the docs to make this more clear.

A lot of databases don't provide the information required to make hits meaningful on updates and deletes (i.e. they don't tell you how many rows were updated, only if the query was successful).

If you are passing raw sql via do_query, then you are on your own. =)

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] hits() method NOT working for do_query(), update(), modify() methods in GT::SQL::Table! In reply to
Thanks. However I thought every database passes back the info, that how many rows were affected (i.e. updated).

Can not be this handled through drivers? I.e. provide support just for MySQL, and those databases, which still supports that feature... Also in docs can be made clear, by listing in method description, which databases supports that feature. This way, the problem may be solved through docs... Just an idea, tough.

BTW: I found this in DBI docs:
Quote:
The do() method can be used for non repeated non-SELECT statement (or with drivers that don't support placeholders):
$rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 1")

or

rows description:
$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.
Also I checked GT::Base. You use count() method in hits(). The count() use the COUNT(*) in select, and the doc said.

Now for UPDATE, I would be curious how the following change in GT:SQL:Table update() method would work:
Code:
# Execute the query.
my $sth = $self->do_query($sql, $ph) or return;
$self->{last_hits} = $sth->rows || 0;
Did not tried, just thinking... But basically should work when we call using $cat_db->hits() after $cat_db->update().
Same code, should make hits() working on do(), do_query(), modify(), if the red code above would be added.
Also did not digged deep info the code, just quickly checked the main parts.

Alex? Opinion?

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [Alex] hits() method NOT working for do_query(), update(), modify() methods in GT::SQL::Table! In reply to
Did anyone work out how to get a number from the hits of an UPDATe query? I'm playing with this in one of my plugins...for some debuggin info...but can't see anything Unsure I dind't really find anything conclusive in this thread (or others) either :(

Thanks in advance...

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] hits() method NOT working for do_query(), update(), modify() methods in GT::SQL::Table! In reply to
An $sth object is returned for an update so try dumping it to see what it contains. You'll probably be able to calls rows() or hits().

Last edited by:

Paul: Feb 25, 2003, 7:49 AM
Quote Reply
Re: [Paul] hits() method NOT working for do_query(), update(), modify() methods in GT::SQL::Table! In reply to
I got around it by using 'die' after the UPDATE query...to see what SQL query was being run...and then calling the same command via MySQLMonitor...

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] hits() method NOT working for do_query(), update(), modify() methods in GT::SQL::Table! In reply to
Code:
my $rows = $DB->table('Links')->update({ Status => 'Administrator' })->rows;
Quote Reply
Re: [Andy] hits() method NOT working for do_query(), update(), modify() methods in GT::SQL::Table! In reply to
Andy,

In my first post, I wrote rows() method works for update() method. Tough did not try, likely rows() works for other GT::SQL query methods, too.

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...