Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Class::DBI newbie

by rodbass63 (Novice)
on Feb 01, 2013 at 22:50 UTC ( #1016619=perlquestion: print w/ replies, xml ) Need Help??
rodbass63 has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I want to start learning Class::DBI and put the default DBI aside for a while. I created this method that does a simple select, I want to learn how to do it the Class::DBI way. Here's my method doing it the old fashion way.
sub test { # DATA SOURCE NAME my $dsn = "dbi:mysql:$dbname:localhost:3306"; # PERL DBI CONNECT my $connect = DBI->connect($dsn, $user, $pw); my $query = "SELECT username, password, created FROM TempAccountRegist +ry"; my $query_handle = $connect->prepare($query); # EXECUTE THE QUERY $query_handle->execute(); # BIND TABLE COLUMNS TO VARIABLES $query_handle->bind_columns(\$username, \$password, \$created); # LOOP THROUGH RESULTS while($query_handle->fetch()) { print DB "$username, $password, $created\n"; } }
I have another file with Class::DBI
use base 'Class::DBI'; DataStore::DAO::Base->connection("dbi:$source:$dbname", $user, $passwo +rd); # I would like to add the method doing it the Class::DbI # way here.
Where I work, they strive on Class:DBI, so I'm eager to learn it. Thanks for your help. usaims

Comment on Class::DBI newbie
Select or Download Code
Re: Class::DBI newbie
by 7stud (Deacon) on Feb 02, 2013 at 04:44 UTC

    The simplest setup employs one additional file next to your program file:

    some_dir/
       my_prog.pl
       Users.pm
    

    Here is the table I'm using:

    CREATE TABLE TempAccountRegistry ( 
        id INT(12) not null auto_increment primary key, 
        username VARCHAR(255), 
        password VARCHAR(50), 
        created TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
    

    1) Create the file Users.pm, and put this code in it:

    #This is file: Users.pm package Users; #Class name use strict; use warnings; use base 'Class::DBI'; #Users inherits from Class::DBI #Database specific info comes next: Users->connection('dbi:mysql:my_db', 'root', ''); #Format is: (dbi:<database_type>:<db_name>, <username>, <password>) #Connect your class to a table: Users->table('TempAccountRegistry'); #Declare the columns in the table(a primary key is required): Users->columns(All => qw/id username password created/); 1;

    2) Now you are ready to write a program that uses the features of Class::DBI. Create a program file called my_prog.pl in the same directory as Users.pm, and put this code in it:

    #This is file: my_prog.pl use strict; use warnings; use 5.012; #depending on your version of perl use Users; my $user1 = Users->insert( { username => 'Joe', password => '123', } ); say $user1->username; #Joe

    Here is the effect on your database:

    mysql> select * from TempAccountRegistry;
    +----+----------+----------+---------------------+
    | id | username | password | created             |
    +----+----------+----------+---------------------+
    |  1 | Joe      | 123      | 2013-02-02 00:12:00 |
    +----+----------+----------+---------------------+
    1 row in set (0.00 sec)

    You can retrieve a row like this:

    my $id = 1; my $user = Users->retrieve($id); say $user->username; #Joe

    If you insert another row:

    my $user2 = Users->insert( { username => 'Jim', password => '456', } );

    
    mysql> select * from TempAccountRegistry;
    +----+----------+----------+---------------------+
    | id | username | password | created             |
    +----+----------+----------+---------------------+
    |  1 | Joe      | 123      | 2013-02-02 00:12:00 |
    |  2 | Jim      | 456      | 2013-02-02 00:12:00 |
    +----+----------+----------+---------------------+
    2 rows in set (0.00 sec)
    
    

    ...you can retrieve all the rows like this:

    my @users = Users->retrieve_all; for my $user (@users) { say $user->username; say $user->password; say $user->created; } --output:-- Joe 123 2013-02-02 00:12:00 Jim 456 2013-02-02 00:12:00

    You can interpolate method calls into a string like this:

    for my $user (@users) { printf "%s %s %s \n", $user->username, $user->password, $user->created; } --output:-- Joe 123 2013-02-02 00:12:00 Jim 456 2013-02-02 00:12:00

    You can change the information in a row like this:

    $user2->username("Kathy"); $user2->update;

    mysql> select * from TempAccountRegistry;
    +----+----------+----------+---------------------+
    | id | username | password | created             |
    +----+----------+----------+---------------------+
    |  1 | Joe      | 123      | 2013-02-02 00:12:00 |
    |  2 | Kathy    | 456      | 2013-02-02 00:12:00 |
    +----+----------+----------+---------------------+
    2 rows in set (0.00 sec)
    
    

    Finally, you can delete a row like this:

    $user1->delete;

    mysql> select * from TempAccountRegistry;
    +----+----------+----------+---------------------+
    | id | username | password | created             |
    +----+----------+----------+---------------------+
    |  2 | Kathy    | 456      | 2013-02-02 00:12:00 |
    +----+----------+----------+---------------------+
    1 row in set (0.00 sec)
    
    

    Read the docs for the various ways you can search the database and update info.

    Also, according to the docs:

    It's usually wise to set up a "top level" class for your entire application to inherit from, rather than have each class inherit directly from Class::DBI. This gives you a convenient point to place system-wide overrides and enhancements to Class::DBI's behavior.

    If you want to do that, here are the files you'll need:

    /some_dir
        my_prog.pl
        MyDBIDatabase.pm
        Users.pm
    

    1) Create the file MyDBIDatabase.pm. Then put this code in MyDBIDatabase.pm:

    #This is file: MyDBIDatabase.pm package MyDBIDatabase; #Class name use strict; use warnings; use base 'Class::DBI'; #MyDBIDatabase inherits from Class::DBI #Database specific info comes next: MyDBIDatabase->connection('dbi:mysql:my_db', 'root', ''); #Format is: (dbi:<database_type>:<db_name>, <username>, <password>) 1; #Don't forget this!

    2) Create another file called Users.pm. Put this code in Users.pm:

    #This is file: Users.pm package Users; #Class name use strict; use warnings; use base 'MyDBIDatabase'; #Users inherits from MyDBIDatabase; #Connect your class to a table: Users->table('TempAccountRegistry'); #Declare the columns in the table(a primary key is required): Users->columns(All => qw/id username password created/); 1;

    Any additional classes that you want to map to tables will also inherit from MyDBIDatabase instead of Class::DBI.

    3) You can use the same code as above for my_prog.pl.

    If you have a larger project, you can use a more sophisticated directory structure:

    some_dir/
       my_prog.pl
       MyApp/
           MyDBIDatabase.pm
           Users.pm
    

    1) Create the subdirectory MyApp.

    2) cd into the MyApp directory, and create the file MyDBIDatabase.pm (can be any name with a .pm extension). Then put this code in MyDBIDatabase.pm:

    #This is file: MyApp/MyDBIDatabase.pm package MyApp::MyDBIDatabase; #Class name, which mimics MyApp/MyDBIDatabase.pm use strict; use warnings; use base 'Class::DBI'; #MyApp::MyDBIDatabase, inherits from Class::DBI #Database specific info comes next: MyApp::MyDBIDatabase->connection('dbi:mysql:my_db', 'root', ''); #Format is: (dbi:<database_type>:<db_name>, <username>, <password>) 1; #Don't forget this!

    3) Create another file called Users.pm (also in the MyApp directory, can be any name with a .pm extension). Put this code in Users.pm:

    #This is file: MyApp/Users.pm package MyApp::Users; #lass name, which mimics MyApp/Users.pm use strict; use warnings; use base 'MyApp::MyDBIDatabase'; #MyApp::Users inherits from MyApp::MyDBIDatabase; #Connect your class to a table: MyApp::Users->table('TempAccountRegistry'); #Declare the columns in the table(a primary key is required): MyApp::Users->columns(All => qw/id username password created/); 1;

    4) Change directories back to the higher directory from which you came( cd ..).

    5) Create a program file called my_prog.pl (can be any name), and put this code in it:

    #This is file: my_prog.pl use strict; use warnings; use 5.012; #depending on your version of perl use MyApp::Users; my $user1 = MyApp::Users->insert( { username => 'Joe', password => '123', } ); say $user1->username; #Joe #etc. #etc. #Same as my_prog.pl above with 'Users' replaced by 'MyApp::Users' #everywhere in the code. #

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1016619]
Approved by muba
Front-paged by Old_Gray_Bear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (11)
As of 2014-10-31 16:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (221 votes), past polls