Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
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

Replies are listed 'Best First'.
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?
[stevieb]: well, what happens is the Arduino 'listens' for requests r/w, and does the appropriate thing when it's interrupted based on the 'register' address sent in. It's ugly as it was my first attempt, but I've got great new ideas I'm just sitting.
[stevieb]: ...down to implement now. Here's the sketch as it currently sits
[shmem]: well I use I2C and SPI and stuff, but creating a pseudo chip looks to me like lot of indirection and memory clutter... not?
[choroba]: Are you going to use the device soon? Related to your comment about "not having much time to do a lot of coding"...
[stevieb]: sure, but I'm just learning ;) I consider it practice to get a good understanding of what goes on *after* an I2C/SPI request is made
[shmem]: ah ok. Gonna read that. but now....
shmem compiles himself into his template
[stevieb]: this is my RPi:: distribution overall automation build system physical layout. It doesn't encompass all of my RPi dists yet, ...
[stevieb]: ...but I'm getting close. I've been soldering my own PCBs to gain that experience as well, before I have my own manufactured.
[stevieb]: heh, nice *template* ;) 'night shmem!

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (9)
As of 2017-06-25 22:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How many monitors do you use while coding?















    Results (572 votes). Check out past polls.