DBI Number of Rows Changed

[FeiLung]

Quick question on DBI.

Using the following example code.

my $sql = qq{ UPDATE table set field1 = "A" 
		WHERE field2 = "" };

$dbh->do( $sql );

What would I need to do to return the total number of records that were changed?

Thank you.

FL

 

[MillerH]

The do method returns the number of rows affected:

my $sql = qq{UPDATE table set field1 = "A" WHERE field2 = ""};

my $rows_affected = $dbh->do($sql);

- Miller

 

[FeiLung]

I pulled the same info from the CPAN docs. I thought I might have done something wrong since the only output I get is a 1.

When I do

print "$rows_affected\n";

I get a 1

FL

 

[MillerH]

If you need a sanity check, always go back to the SQL prompt and take a look at your data.

You can also use the execute method, which is generally my preference:

my $sql = qq{UPDATE table set field1="A" WHERE field2=""};

my $sth = $dbh->prepare($sql);
$sth->execute or die $dbh->errstr;
my $rows_affected = $sth->rows;

Also, just to point out something obvious, "table" is a reserved word. I don't know if that is just a placeholder for your real table name, but this might be an issue for you.

- Miller

 

[FeiLung]

My sanity has been checked.

Running the SQL by hand produces the results I am looking for. I have a table with 10 records. When run by hand the records are updated.

I am aware of the reserved word conflict. This is just an example swapping out the table and field names.

One thing I did notice is when I use:

my $sql = qq{ UPDATE table set field1 = "A" 
        WHERE field2 = "" };

$dbh->do( $sql );

The records are updated. But I do not get a true count back from $rows_affected. It returns a '1' whether it updated any records or not. But when I use:

my $sql = qq{UPDATE table set field1="A" WHERE field2=""};

my $sth = $dbh->prepare($sql);
$sth->execute or die $dbh->errstr;
my $rows_affected = $sth->rows;

It does not update the records at all, and still returns a '1'.

Let me explain a little of what I am trying to accomplish, as there may be a better way to approach this.

I have a table that gets populated from a 3rd party app. Every 5 or 10 minutes I want to go out and check to see if any new rows have been added. If they have I am going to dump them out and email them to a new accounts manager.

My thinking was along these lines:

1. Update the table setting the field filler1 = "A" where filler1 = "". If the table returns any results, then there are new records.
2. Query the table for all records that have field filler1 = "A". Dump them out to a text file.
3. Update the table setting the field filler1 = "B" where filler1 ="A".

So I am on step 1, trying to determine if there are any results or affected rows from the first update. If there are not then I can break out of the script. If there are then I can proceed and produce the output needed to email.

I am running through this process in an attempt to prevent missing any records that may be entered while the script is running. This way I can find all records that were entered since the last time the script ran.

Any help is greatly appreciated.

- FL

 

[MikeLacey]

Hi FL,

Your statement:

$sth->execute or die $dbh->errstr;

Does it execute the die command?


Mike

Hardware is that part of a computer which, when you remove electrical power, doesn't go away.

Want great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[FeiLung]

No it does not execute the die command. It simply returns a 1, but does not update the table.

So that being said, If I do the following:

select count(*), filler1
from table
where filler1=""
group by filler1

How would I go about testing the results? If the resulting count is 0, then I can quit. If there is any number greater then 0, then I can continue the rest of the script.

 

[MillerH]

Hi FeiLung,

I'm sorry ... my DBI skills are a little rusty, as all of my tables are accessed through classes, and all database operations are handled through inheritance.

Anyway, to adjust what I suggested at first:

my $sql = qq{UPDATE table set field1="A" WHERE field2=""};

my $sth = $dbh->prepare($sql);
my $rows_affected = $sth->execute or die $dbh->errstr;

But that doesn't matter. Now that you've spelled out exactly what you're trying to do, I would suggest a different approach.

First off, my preferred approach would be to keep track of a primary key that would indicate the last record reported to the accounts manager. This would prevent the need of a key to indicate that a record had been seen. But then again, that would still require that you save the old max primary key in the database somewhere, so maybe it's not that much cleaner of a method.

Anyway, given that you've already added a field to your table, I would suggest that you simply select all the new records from the very beginning and count the rows. Then either update the indicator column record by record or by using an update with an "IN" clause. This would neetly avoid your concern about new records being added during this process.

PS. I keep on wanting to assume that your_table has a primary key, as that would change how I would do the queries. But given that you haven't told us that, I'll assume you don't. This will make for 3 queries instead of just 2.

Your code would look something like this:

sub processNewAccounts {
	# Set to Intermediate State
	my $sth = $dbh->prepare(q{UPDATE your_table SET hasBeenReported=1 WHERE hasBeenReported=0});
	$sth->execute or die $dbh->errstr;
	$sth->finish; undef $sth;
	
	# Select all rows
	my $sth = $dbh->prepare(q{SELECT * FROM your_table WHERE hasBeenReported=1});
	$sth->execute or die $dbh->errstr;

	if ($sth->rows == 0) {
		# Nothing to do.
		$sth->finish; undef $sth;
		return;
	}
	
	my $output = '';
	while (my $record = $sth->fetchrow_arrayref) {
		### Accumulate your output here
		
	}
	$sth->finish; undef $sth;
	
	# Set to Final State
	my $sth = $dbh->prepare(q{UPDATE your_table SET hasBeenReported=2 WHERE hasBeenReported=1});
	$sth->execute or die $dbh->errstr;
	$sth->finish; undef $sth;

	### Send output in email.
	
}

Anyway, I personally would design this differently on my own system, but given what you've told us so far, this is the best approach I can suggest. As for why your counting of rows of the update's not working. I can't say. Try doing the same thing using different methods and see if you can figure out exactly where it's going wrong. Database problems are very annoying, which is why I personally try to generalize all database operations in such a way so that I never have to do them again.

Good Luck,
- Milller