Skip navigation.
Home
Your source for Perl tips, howto's, faq and tutorials

DBI: handling database errors

( categories: )

Basically, there are two ways of handling database errors, check (almost) every DBI call for errors or set 'RaiseError' attribute to '1ยด:

-- Manual checking

This way, you have to add code yourself to check for database error conditions, so after nearly every method call you should check if the operation completed successfully.

There are two DBI methods that are very helpful to manually check for database errors: 'err' and 'errstr'.


DBI: get the number of rows affected by a SQL statement

( categories: )

The number of rows must be obtained in different ways, depending of the type of SQL statement:

-- Non-SELECT statements

Use the 'rows' method. It returns the number of rows of the last executed command; in case the number of rows is unknown it returns -1.

This method is useful for SQL operations like "INSERT", "UPDATE", "DELETE", etc.

Example:

my $sth = $dbh->prepare("DELETE FROM table WHERE count < '?'");
$sth->execute(25);
 
print "Number of rows deleted: " . $sth->rows;

Please notice that the 'do' method combines 'prepare', 'execute' and 'rows' in a single function, so the above example can be rewritten as:


access a Microsoft SQL Server database from unix

( categories: )

To access Microsoft SQL server from unix, use the DBD:Sybase module.

In order to access SQL Server 2000 (and later) databases, you have to be sure that the DBD:Sybase module was built with the FreeTDS libraries.

Several *nix distributions have ready to install packages (for example, in Debian, you just need to install the packages 'libdbd-sybase-perl' and 'freetds-dev'); in case you need to install from sources, you need to do the following:

  1. install FreeTDS

    Get the package from www.freetds.org, unpack it and execute make and then make install


using the DBI module

( categories: )

The DBI module provides a high level layer to interact with databases. The advantage of using this module is that provides a uniform interface to handle different types of databases.

Using DBI to interact with a database usually consists of 4 steps:

  1. Connect to the database
  2. Execute a query and obtain the result set
  3. Process the result set
  4. Disconnect from the database

1. CONNECT TO THE DATABASE

my $dbh = DBI->connect("DBI:mysql:dbname:hostname:port","username","password");

where dbname is the name of the database, hostname is the server where the database is located, port is the port where the database server is listening, username is the database user and password is the user's password. (hostname and port can be omitted if the database is in localhost and listens in the default port).


Syndicate content