The Perl Toolchain Summit 2025 Needs You: You can help 🙏 Learn more

 / 
SysAdmin-0.11
River stage zero No dependents
/ SysAdmin::DB

NAME

SysAdmin::DB - Perl DBI/DBD wrapper module..

SYNOPSIS


            

              
              
 ## Example using PostgreSQL Database
         
 use SysAdmin::DB;
         
 my $db = "dbd_test";
 my $username = "dbd_test";
 my $password = "dbd_test";
 my $host = "localhost";
 my $port = '5432';
 my $driver = "pg"; ## Change to "mysql" for MySQL connection
         
 ### Database Table
 ##
 # create table status(
 # id serial primary key,
 # description varchar(25) not null);
 ##
 ###
         
 my $dbd_object = new SysAdmin::DB(db          => $db,
                                   db_username => $username,
                                   db_password => $password,
                                   db_host     => $host,
                                   db_port     => $port,
                                   db_driver   => $driver);
 ###
 ## DB and DB_DRIVER are always required!
 ###
         
 ###
 ## For databases that need username and password (MySQL, PostgreSQL),
 ## DB_USERNAME and DB_PASSWORD are also required
 ###
         
 ### For SQLite, simply declare DB and DB_DRIVER, example:
 ##
 ## my $db = "/tmp/dbd_test.db";
 ## my $dbd_object = new SysAdmin::DB(db        => $db,
 ##                                   db_driver => sqlite);
 ##
 ###
         
         
 ###
 ## Work with "$dbd_object"
 ###
         
 ### Insert Data
         
 ## SQL Insert statement
 my $insert_table = qq(insert into status (description) values (?));
         
 ## Insert Arguments, to subsitute "?"
 my @insert_table_values = ("DATA");
         
 ## Insert data with "insertData"
         
 $dbd_object->insertData("$insert_table",\@insert_table_values);
         
 ## By declaring a variable, it returns the last inserted ID
         
 my $last_insert_id = $dbd_object->insertData("$insert_table",\@insert_table_values);
         
 ## The insertData Method could also be expressed the following ways
   
 my $insert_table_values = ["Data"];
 $dbd_object->insertData("$insert_table",$insert_table_values);
   
 # or
   
 $dbd_object->insertData("$insert_table",["Data"]);
         
 ### Select Table Data
         
 ## SQL select statement
 my $select_table = qq(select id,description from status);
         
 ## Fetch table data with "fetchTable"
 my $table_results = $dbd_object->fetchTable("$select_table");
         
 ## Extract table data from $table_results array reference
 foreach my $row (@$table_results) {
         
my ($db_id,$db_description) = @$row;
         
## Print Results
print "DB_ID $db_id, DB_DESCRIPTION $db_description\n";
         
 }
         
         
 ### Select Table Row
         
 ## SQL Stament to fetch last insert
 my $fetch_last_insert = qq(select description 
                            from status 
                            where id = $last_insert_id);
         
 ## Fetch table row with "fetchRow"
 my $row_results = $object->fetchRow("$fetch_last_insert");
         
 ## Print Results
 print "Last Insert: $row_results\n";

DESCRIPTION

This is a sub class of SysAdmin. It was created to harness Perl Objects and keep code abstraction to a minimum.

SysAdmin::DB uses perl's DBI and DBD to interact with database.

Currently DBD::Pg, DBD::mysql and DBD::SQLite are supported.

METHODS

new()


            

              
              
my $dbd_object = new SysAdmin::DB(db          => $db,
                                     db_username => $username,
                                     db_password => $password,
                                     db_host     => $host,
                                     db_port     => $port,
                                     db_driver   => $driver);

Creates SysAdmin::DB object instance. Used to declare the database connection information.


            

              
              
db => $db

State database name to connect to


            

              
              
db_username => $username

State a privileged user to connect to the DB database


            

              
              
db_password => $password,

State a privileged user's password to connect to the DB database


            

              
              
db_host => $host

State the IP address/Hostname of the database server


            

              
              
db_port => $port

State the listening port of the database server. PostgreSQL 5432, MySQL 3306


            

              
              
db_driver => $driver

State the database driver to use. Currently supported: Pg, mysql and SQLite

insertData()


            

              
              
## SQL Insert statement
my $insert_table = qq(insert into status (description) values (?));
## Insert Arguments, to subsitute "?"
my @insert_table_values = ("DATA");
## Insert data with "insertData"
$dbd_object->insertData("$insert_table",\@insert_table_values);
## By declaring a variable, it returns the last inserted ID
my $last_insert_id = $dbd_object->insertData("$insert_table",\@insert_table_values);

fetchTable()


            

              
              
## Select Table Data
## SQL select statement
my $select_table = qq(select id,description from status);
## Fetch table data with "fetchTable"
my $table_results = $dbd_object->fetchTable("$select_table");
## Extract table data from $table_results array reference
foreach my $row (@$table_results) {
        my ($db_id,$db_description) = @$row;
        ## Print Results
        print "DB_ID $db_id, DB_DESCRIPTION $db_description\n";
}

fetchRow()


            

              
              
## Select Table Row
         
## SQL Stament to fetch last insert
my $fetch_last_insert = qq(select description 
                              from status 
                              where id = $last_insert_id);
         
## Fetch table row with "fetchRow"
         
my $row_results = $object->fetchRow("$fetch_last_insert");
         
## Print Results
print "Last Insert: $row_results\n";

SEE ALSO

DBI - Database independent interface for Perl

DBD::Pg - PostgreSQL database driver for the DBI module

DBD::MySQL - MySQL driver for the Perl5 Database Interface (DBI)

DBD::SQLite - Self Contained RDBMS in a DBI Driver

AUTHOR

Miguel A. Rivera

COPYRIGHT AND LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.