MySQL $dbh->do VS $dbh->prepare

[Ultimater]

Assuming that $sql equals the following:

my $sql = "SELECT name, telephone FROM table1";


What is the difference between:

my $sth = $dbh->prepare($sql); $sth->execute();

and

$dbh->do($sql);


What is the point of preparing $sql? Are there more options at hand than just the execute function or something?

[Nedals]

The do() method returns the number of lines affected, not the resulting array.

[Ultimater]

Thanks for the reply Nedels!
I see. So are there any other functions available except for execute taking the prepare route? Like to catch errors and stuff?

[Jeff Mott]

As a rule of thumb: if you are SELECTing then use prepare/execute; if you are doing any other SQL command multiple times (such as in a loop) then use prepare/execute (this saves from having to recompile the SQL command for each iteration); and finally for any non-SELECT statement that is not in a loop use do.

If you're interested in seriously learning the full capabilities then you'd have to read the DBI documentation (http://search.cpan.org/~timb/DBI-1.48/DBI.pm). If you would like all that information presented in a more tutorial fashion (the DBI is a *big* module) then you may be interested in the book Programming the Perl DBI (http://www.amazon.com/exec/obidos/tg/detail/-/1565926994/qid=1128639356/sr=1-1/ref=sr_1_1/102-7410802-0268106?v=glance&s=books). I haven't actually read it so I can't comment on its quality, but the DBI module author is one of the book authors so I think it is a safe bet that this book is among the best you'll find.

[Ultimater]

Thanks for the reply Jeff!
I found what I was looking for from your CSPAN link:

$rc = $sth->bind_param($p_num, $bind_value);
$rc = $sth->bind_param($p_num, $bind_value, $bind_type);
$rc = $sth->bind_param($p_num, $bind_value, \%attr);

$rv = $sth->execute;
$rv = $sth->execute(@bind_values);
$rv = $sth->execute_array(\%attr, ...);

$rc = $sth->bind_col($col_num, \$col_variable);
$rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);

@row_ary = $sth->fetchrow_array;
$ary_ref = $sth->fetchrow_arrayref;
$hash_ref = $sth->fetchrow_hashref;

$ary_ref = $sth->fetchall_arrayref;
$ary_ref = $sth->fetchall_arrayref( $slice, $max_rows );

$hash_ref = $sth->fetchall_hashref( $key_field );

$rv = $sth->rows;

(first block of code in the link)

[Nedals]

One other you might find very useful where you only expect a single row to be returned.

my @row = $dbh->selectrow_array("SELECT......");
# combines prepare() and execute()

Pick up a copy of 'MySQL' by Paul DuBois. It's my SQL bible :)

[Nedals]

Hijacking this thread for a second :)
(I'm sure this will be of interest)

Jeff,
Do you know of any clever way to do this..

if (<row does not exist>) {
$dbh->do("INSERT.....);
## with no unique key so I cannot use REPLACE
}

Current I do a "SELECT COUNT(*)...." to determine if the row exists.

[Jeff Mott]

'MySQL' by Paul DuBois. It's my SQL bibleMore precisely, it is your MySQL bible. The commands you have available in MySQL are very different from those you have available in Oracle, which are very different from those in DB2, which are very different from those proposed in the SQL standard. It is very difficult to learn SQL in general. You really have to learn it for each particular database.

if (<row does not exist>) {
$dbh->do("INSERT.....);
## with no unique key so I cannot use REPLACE
}So, what you're saying is a certain field value can only occur once? If so then it *should* be a unique key. In fact, from the MySQL documentation...Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.And even though you technically are not using REPLACE here, you are almost mimicking its behavior. So, unfortunately, the clever way you were looking for is probably to restructure your table a bit.

[Nedals]

More precisely, it is your MySQL bible.Tying too quickly. :)


RE: INSERT; Let me explain further (which I should have done in the first place.)

Database table: group_id | customer_id | etc
Where the combination of group_id and customer_id is unique.

So here's what I currently do..

my $exists = $dbh->selectrow_array("SELECT COUNT(*) FROM table WHERE group_id=$groupID AND customer_id=$customerID");
if ($exists) {
$dbh->do("UPDATE table SET cola=$data WHERE group_id=$groupID AND customer_id=$customerID)");
} else {
$dbh->do("INSERT INTO table (group_id,customer_id, etc) VALUES ($groupID,$customerID,etc)");
}

This is used to update the customer list from a user-uploaded .CSV file which may contain both updates and additions. (The user defines the customer_id so I cannot control it, leading to duplication.)

This code is within a loop and I want to reduce the number of calls to the database.
I've shown it this way for explanation purposes. The actual code 'prepares' the SQL statements outside the loop, utilizing placeholders, and executes within the loop.