IO::DB - Database convenience object.
use IO::DB; my $db = new IO::DB( { db_dsn => 'dbi:Sybase:db01', db_user => 'web', db_pass => 'password' } ); # Note the lack of a connect! my $rows = $db->sql_rows( 'select count(*) from mytable' ); foreach my $row (@$rows) { print $row->{value}, "\n"; } my $hash = $db->sql_hash( 'select name, count(*) from mytable group by name' ); foreach my $key (keys(%$hash)) { print "$key is $hash{$key}\n"; }
The IO::DB library was created and is intended as a convenience library. It works by reducing clutter in your code caused by using the same redundant code. It also works under the philosophy of intelligent code. That is, let me tell you to do something and let the code figure out the prerequisits. This is in part responsible for the lack of an explicit connect function. It is also responsible for the currently incomplete quote function. Those who are adventurous may poke around and try it out.
Back to the topic of connecting, the library will automatically connect to the database the first time you issue a sql statement. If for some reason you need some functionality tied to dbh, you can access it through the dbh member like this:
$db->{dbh}->quote( $mycolumndata );
Note that if you haven't executed any sql, this will not work. If you find a case where you do need an explicit connect, simply call the private _connect function like so:
$db->_connect();
Features eventually slated include an improved 'quote' function which looks at the table and determines if a field needs quoting or not. All of this information will of course be cached to limit the additional load on the database that this will inevidably cause.
The new function creates a new instance of the IO::DB object. It should be passed a configuration parameter either through a hash or through a configuration object resembling a hash. The three parameters in the configuration are: db_dsn - the DSN string you would normally pass to DBI db_user - the username to log into the database with db_pass - the password to use or undef if no password
The sql_do function simply privides a wrapper around the DBI 'do' statement. Other than this, it does nothing. Simply pass the sql to execute as the first parameter. $db->sql_do("delete from mytable where inactive='Y'");
The sql function works by wrapping around a prepare statement and executing the passed sql. It supports using paramaters (1, 2, 3, ... or ?, ?, ?,...) for the sql. Coming enhancements include caching sth handles to improve performance. my $sth = $db->sql( "select count(*) from mytable" ); -or- my $sth = $db->sql( "select count(*) from mytable where a=? and b=?", $a, $b );
The sql_rows function behaves much like the sql statement, except that instead of returning the $sth object, it will instead return a reference to an array containing the result set. Each row will have it's values contained within a hash. Care should be taken with this function as large result sets will undoubtedly kill the performance of your computer. my $rows = $db->sql_rows( 'select foo, bar from mytable' ); foreach my $row (@$rows) { print "$row->{foo}, $row->{bar}\n"; }
The sql_hash function is useful for returning two-column result sets as a hash rather than as a set of rows. It again behaves in much the same manner as the sql function does.
WARNING: THIS FUNCTION MAY NOT FUNCTION PROPERYLY FOR YOU. It is currenty specific to SQL Server and Sybase. The quote function is useful for quoting data prior to insertion. It has the nice trate that it does datatype lookups on tables so you don't have to know what to quote and what not to. HOWEVER it currently will quote functions as though they are strings with the notable exception of the getdate() function.
David Bialac <dbialac@yahoo.com>
IO::DB version 0.1, released on 22 June 2005.
Copyright (C) 2003-2005 David Bialac. All Rights Reserved.
This module is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser Public License or the Perl Artistic License at your discression.
To install IO::DB, copy and paste the appropriate command in to your terminal.
cpanm
cpanm IO::DB
CPAN shell
perl -MCPAN -e shell install IO::DB
For more information on module installation, please visit the detailed CPAN module installation guide.