Statement Handle Methods | Mastering MySQL 4

These methods work on the statement handle, obtained by calling a database handle method such as prepare().

bind_col

$rc = $sth->bind_col($column_number, \$column_variable);

Binds a field (starting at 1) in the result from a SELECT statement to a variable. See bind_ columns for more information.

bind_columns

$rc = $sth->bind_columns(@list_of_refs_to_vars_to_bind);

Calls the bind_col() method for each field from the SELECT statement. The number of references must match the number of fields.

For example:

# set RaiseError to 1 to avoid having to check each method call $dbh->{RaiseError} = 1; $sth = $dbh->prepare(q{SELECT first_name,surname FROM customer}); $sth->execute; my ($first_name, $surname); # Bind Perl variables to columns: $rv = $sth->bind_columns(\$first_name, \$surname); while ($sth->fetch) {     print "$first_name $surname\n"; }

bind_param

$rv = $sth->bind_param($bind_num, $bind_value [\%attributes | $bind_type]);

Used to bind a value with a placeholder, indicated with a question mark (?). A placeholder is used where you're planning to run a similar query multiple times, where only a parameter changes each time.

For example:

$sth = $dbh->prepare("SELECT fname, sname FROM tname WHERE sname LIKE ?"); $sth->bind_param(1, "Vusi%");  # placeholders begin from 1 $sth->execute;

You can't use placeholders to replace a table name or fieldname, or to replace anything but a single scalar value. For example, the following are incorrect usages of placeholders:

SELECT fname, ? FROM tname WHERE sname LIKE 'Vusi%' SELECT fname, sname FROM ? WHERE sname LIKE 'Vusi%' SELECT fname, sname FROM tname WHERE sname IN (?)

You can also use the optional bind type parameter to indicate what type the placeholder should have. For example:

$sth->bind_param(1, $bind_value, {TYPE => SQL_INTEGER});

or the equivalent shortcut (which requires you to import DBI with use DBIqw(:sql_types):

$sth->bind_param(1, $bind_value, SQL_INTEGER);

Alternatively, you can use the \%attributes parameter, as follows:

$sth->bind_param(1, $bind_value, {TYPE => SQL_INTEGER});

This returns an integer.

For example:

my $hostname = 'localhost'; my $database = 'firstdb'; my $username = 'guru2b'; my $password = 'g00r002b';   # Connect to the database my $dbh = DBI->connect("dbi:mysql:$database:$hostname", $username,–  $password) or die $DBI::errstr; # Create the query, with a ? to indicate the placeholder my $query = 'SELECT first_name,surname FROM customer WHERE id=?'; # Prepare the query my $sth = $dbh->prepare($query); # Create an array of id's to use to replace the placeholder my @ids = (1,4,5,6); # Loop through the array and execute the query for(@ids) {   $sth->bind_param(1, $_, SQL_INTEGER);   $sth->execute();   my( $first_name, $surname);   $sth->bind_columns(undef, \$first_name, \$surname);   # Loop through the rows returned and display the results   while( $sth->fetch()) {     print "$first_name $surname\n";   } } $sth->finish();

bind_param_array

$rc = $sth->bind_param_array($p_num, $array_ref_or_value    [, \%attributes | $bind_type])

Used to bind an array to a placeholder set in the prepared statement, ready for execution with the execute_array() method.

For example:

# set RaiseError to 1 to avoid having to check each method call $dbh->{RaiseError} = 1; $sth = $dbh->prepare("INSERT INTO customer(first_name, surname) VALUES(?, ?)"); # Each array must have the same number of elements $sth->bind_param_array(1, [ 'Lyndon', 'Nkosi', 'Buhle' ]); $sth->bind_param_array(2, [ 'Khumalo', 'Battersby', 'Lauria' ]); my %tuple_status; $sth->execute_array(\%tuple_status);

bind_param_inout

$rv = $sth->bind_param_inout($p_num, \$bind_value,  $max_len [, \%attributes | $bind_type])     or ...

The same as the bind_param() method, but you can update values (for stored procedures). MySQL does not currently support this.

dump_results

$rows = $sth->dump_results($max_len, $lsep, $fsep, $fh);

Outputs all rows from the statement handle to $fh (default STDOUT) after calling DBI::neat_ list for each row. $lsep is the row separator (with a default of \n), $fsep the field separator, with a default of comma (,), and the $max_len defaults to 35.

execute

$rv = $sth->execute([@bind_values]);

Executes a prepared statement and returns the number of rows affected (for a query that doesn't return data, such as INSERT or UPDATE). Returns 0E0 (treated as true) if no rows are affected or undef if an error occurs. Use one of the fetch methods to process the data.

This returns an integer.

For example:

my $hostname = 'localhost' my $database = 'firstdb'; my $username = 'guru2b'; my $password = 'g00r002b';   # Connect to the database my $dbh = DBI->connect("dbi:mysql:$database:$hostname", $username, $password); # Create the query, with a ? to indicate the placeholder my $query = 'SELECT first_name,surname FROM customer WHERE id=2'; # Prepare and execute the query my $sth = $dbh->prepare($query); $sth->execute(); my( $first_name, $surname); $sth->bind_columns(undef, \$first_name, \$surname); # Loop through the rows returned and display the results while( $sth->fetch()) {   print "$first_name $surname\n"; } $sth->finish();

execute_array

$rv = $sth->execute_array(\%attributes[, @bind_values]);

Executes a prepared statement for each parameter set with bind_param_array() or in @bind_ values and returns the total number of rows affected.

fetch

An alias for fetchrow_arrayref().

fetchall_arrayref

$table = $sth->fetchall_arrayref [[($slice[, $max_rows])];

Returns all rows returned from the query as a reference to an array containing one reference per row.

If no rows are returned, it returns a reference to an empty array. If an error occurs, it returns the data fetched until the error, if any.

The optional $slice can be an array reference or a hash reference. If it's an array reference, the method uses fetchall_arrayref to fetch each row as an array ref. If an index is specified, then it returns fields (starting at 0). If there are no parameters, or if $slice is undefined, the method works as if passed an empty array ref.

If $slice is a hash reference, the method uses fetchall_hashref to fetch each row as a hash reference. The fields returned will be based upon the hash keys. The hash value should always be 1.

Some examples will make this clearer. The first two examples will return references to an array of array references. First, to return just the second field of every row, use the following:

$tbl_ary_ref = $sth->fetchall_arrayref([1]);

To return the third last and last field of every row, use the following:

$tbl_ary_ref = $sth->fetchall_arrayref([-3,-1]);

The next two examples return a reference to an array of hash references. First, to fetch all fields of all rows as a hash ref, use this:

$tbl_ary_ref = $sth->fetchall_arrayref({});

To fetch only the fields called fname and sname of each row as a hash ref, with the keys named as FNAME and sname, use the following:

$tbl_ary_ref = $sth->fetchall_arrayref({ FNAME=>1, sname=>1 });

If the optional $max_rows is defined as a positive integer (it can be zero), the number of rows returned are limited to this number. You can call fetchall_arrayref again to return more rows. You'd use this if you don't have enough memory to return all the rows at once but still want the performance benefit of fetchall_arrayref.

fetchall_hashref

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

Returns a reference to a hash that contains one entry per row at most. If the query returns no rows, the method returns a reference to an empty hash. If an error occurs, it returns the data fetched until the error, if any.

The $key_field parameter specifies the fieldname that holds the value to be used for the key for the returned hash, or it can be a number corresponding to a field (note that this starts at 1, not 0). The method returns an error if the key does not match a field, either as a name or a number.

You'd normally only use this when the key field value for each row is unique; otherwise the values for the second and subsequent rows overwrite earlier ones of the same key.

For example:

$dbh->{FetchHashKeyName} = 'NAME_lc'; $sth = $dbh->prepare("SELECT id, fname, sname FROM tname"); $hash_ref = $sth->fetchall_hashref('id'); print "The surname for id 8: $hash_ref->{8}->{sname}";.

fetchrow_array

@row = $sth->fetchrow_array;

Returns an array of field values from the next row of data, using a previously prepared statement handle. The elements of the row can then be accessed as $row[0], $row[1], and so on. This moves the row pointer so that the next call to this method will return the following row.

fetchrow_arrayref

$row_ref = $sth->fetchrow_arrayref

Returns a reference to an array of field values from the next row of data, using a previously prepared statement handle. The elements of the row can then be accessed as $row_ ref->[0],
$row_ref->[1], and so on.

This moves the row pointer so that the next call to this method will return the following row.

fetchrow_hashref

$hash_ref = $sth->fetchrow_hashref[($name)];

Returns a reference to a hash table with the fieldname as key and field contents as value, using a previously prepared statement handle. The elements of the row can then be accessed as $hash_ref->{fieldname1}, $hash_ref->{fieldname2}, and so on.

This moves the row pointer so that the next call to this method will return the following row.

The optional name parameter specifies the name the attributes will be given. It defaults to NAME, though NAME_uc or NAME_lc (uppercase or lowercase) is suggested for portability.

The fetchrow_arrayref and fetchrow_array methods are markedly quicker.

finish

$rc = $sth->finish;

Frees system resources associated with a statement handle, indicating that no more data will be returned from it.

Returns true if successful or false if not.

rows

$rv = $sth->rows;

Returns the number of rows changed by the last SQL statement (after an UPDATE or INSERT statement, for example) or –1 if the number is unknown.