use constant SELECT_USER_QUERY => q(
    SELECT userid, username, password
    FROM user_table,
    WHERE userid=?
);

#...much later...

my $sth = $dbh->prepare( SELECT_USER_QUERY ) or die DBI::errstr;
$sth->execute( $userid ) or die DBI::errstr;
my @data = $sth->fetchrow_array( $sth );
[download]

Ah. Nice approach.

I would like to mention the possibility to use functions instead of constants, and move all these functions to a separate module.

This all in the spirit of McConnell's Code Complete. Paraphrased: If you don't like/ grog/ find strange/ consider to be difficult a certain library or structure or whatever, interface it in a separate module. That way, you don't clutter up your main code with things that are unclear and hard to maintain. As a spin-off, you can reuse that module.

In this case, you could write functions with very descriptive names just as wrappers for the SQL:

package SQL_functions;

sub select_picture_from_userID{
  my $userID = shift;
  "SELECT pict FROM users WHERE ID = $userID;"
}

sub select_some_nifty_case_with_contstraints{
  ... #hehe, perl6 only
}

#and in the "main" code:
use SQL_functions qw/:all/;
my $sth = $dbh->prepare( select_picture_from_userID( 666 ) ) or die...
[download]

Hope this helps,

Jeroen

The only problem that I have with this specific method is that you no longer use placeholders, which means you need to take appropriate care to quote and/or detaint all input into the SQL statements.

Now, let me offer to take this one step further, and cleaner: stick all SQL statements into values of a hash in a separate module:

package SQL_Helpers;

my %sql_statements = ( 
    SELECT_USER_INFO => q/SELECT userid, username, password FROM user_
+table WHERE userid=?/,
    UPDATE_USER_INFO => q/UPDATE user_table set password=? WHERE useri
+d=?/,
    ...yada yada yada...
    );

sub sql {
    my ( $dbh, $sql, @data ) = @_;
    return undef if !exists( $sql_statements{ $sql } );
    my $sth = $dbh->prepare( $sql_statements{ $sql } )
        or die DBI::errstr;
    $sth->execute( @data ) or die DBI::errstr;
    return $sth;
}

...

package MyPackage;
use SQL_Helpers qw/:all/;

my $sth = sql( $dbh, UPDATE_USER_INFO, $password, $userid );
[download]

I would even go a step farther, possibly being able to class-ify this so that one can make methods that are created at runtime that correspond to the various SQL statements:

my $sth = $sql_helper->UPDATE_USER_INFO( $password, $userid );
[download]

As well as incorporating prepare_cached details into this class such that things can be speeded up in the SQL side. Notice that there are no dies in the main block of code, and to me this is even more readable that typical cases. Now, these ideas seem rather simple that I would figure there's already a CPAN module for this, but I thought the same of both my Game::Life and Tie::Hash::Stack too...Maybe I'll take a look into developing such a class if nothing currently exists.

The only major problem with this is that sometimes the SQL statement and the code that calls it are closely nit. For example, one might try to do a SELECT listing all specific fields (more than 5, say), and using fetchrow_array with a long my() statement to collect them. If one then wanted to add another field, both the SQL statement and the perl code that called it would have to be changed at the same time; too much of a separation between the SQL statement and perl code could be confusing. But another option is to have pseudo variables in the SQL statement as well, and pass parameters by a hash; the class would be able to determine what order of parameters to pass based on placement of the placeholders in teh SQL statements.

Example SQL: UPDATE user_table SET password=?password WHERE userid=?us
+erid
Example call to sql:
my $sth = $sql_helper->UPDATE_USER_DATA( 
             { userid=>$userid, 
             password=>$password } 
          );
[download]

The only problem going this far is you are now losing some speed aspects for 'beauty' of code, which is always a plausable tradeoff. I might simply try this to see how bad the time difference is, though I suspect most of it is buried in the SQL server details.

---

Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain

    select  db_name (u.dbid),
            sum (2 * u.size),
            sum (2 * curunreservedpgs (u.dbid, u.lstart, unreservedpgs
+))
      from  sysusages u, sysdevices d, sysdatabases db   
     where  d.low  <= u.vstart
       and  d.high >= u.vstart + u.size - 1
       and  d.status & 2 = 2
       and  u.segmap & 4 = 4
       and  not (u.segmap & 2 = 2 or u.segmap & 1 = 1)
       and  u.dbid  = db.dbid
       and  db.name not like "dbcc_%"
     group  by u.dbid
[download]

You know, when I started working with SQL, I made myself such a function, I'd pass it a table name, fields, possible other information, and it build an SQL query for me. But soon I found that for anything that isn't a trivial select, writing the SQL yourself was far easier and gave cleaner code.

-- Abigail

Check out some of the new DBIx:: modules (SearchBuilder, Easy, Abstract, CGITables, etc.) on CPAN. I was going through them a couple months ago, many of them appeared to be headed in exactly the same direction you're describing.

Spud Zeppelin * spud@spudzeppelin.com

Problems with SQL in code:

• Your code has a dependancy on SQL, if you change to a different flavour of SQL you'll have to troll through all your code to fix any non portable SQL.
• If your database table structure changes (e.g. new not null columns in a table into which you are inserting data) your code may fall over at run time - having all the SQL in one place makes it easier to test.

• As mentioned earlier, very difficult to do for complex queries.
• You need to refer to several modules to understand what your code is doing. Having in line queries makes it very easy to quickly read and understand simple code.
• You limit what you can do with SQL to what your interface lets you do - some of the more powerful SQL features such as sub queries would be hard to build into an interface.

  e.g.
  
  select * 
    from rate 
   where id = ? 
     and rate.date_effective = 
    (select max(date_effective)
       from rate down_the_way
       where down_the_way.id = ?)
  [download]

My preference is that, for a large application, there should be a single module dealing with each data entity in your database (e.g. invoice.pm for invoices) and this module should provide interfaces to SQL to access data from that table. The perl and SQL are combined in the same module.

No outside module should access the data structures other than through this module. This is database centric however and can be tricky to map onto OO.

"The future will be better tomorrow."

i think what you describe is exactly the motive behind the DBIx:: set of modules: more perl, less sql, thank you very much. It has adherents and detractors, as will already be apparent. Sometimes they meet peacefully. Sometimes it gets a little more heated.

that crunchy texture you may have noticed underfoot and the napalm-in-the-morning smell are residues of last time this topic was aired.

My preferred way of doing things is to stick the SQL in the database as stored procedures (if the database supports it) or in .sql files which we can invoke through a commandline system call.

Jorg

"Do or do not, there is no try" -- Yoda

Masem hides his SQL statements by defining them elsewhere, whereas my problem is with the surrounding DBI code. Every time I want to look something up I have to do something like:

$sth=$dbh->prepare("select whatever from thetable where col=?");
$sth->execute($val);
my @list;
while(my $hashref=$sth->fetchrow_hashref) {
  push @list,$hashref;
}
$sth->finish;
return \@list;
[download]

my $hashref=$dbh->getall_hashref("select * from abc where col=?",$val)
+;
my $arrayref=$dbh->getrow_arrayref("select * from abc where col=?",$va
+l);
etc...
[download]

What do you mean selectall_arrayref is not cached?!?

my $res = $DBH->selectall_arrayref("
    SELECT employees.name, skills.name
    FROM employees,skills,employee_skills
    WHERE employees.id = ?
    AND skills.id = employee_skill.skill_id
",undef,$emp_id);
[download]

Correct me if i am wrong, but that statement is cached.

At any rate, please read (dkubb) Re: (2) Outputing data from mySQL query into format for use with HTML::Template if you are tired of building that hash data structure. :)

Jeff

R-R-R--R-R-R--R-R-R--R-R-R--R-R-R--
L-L--L-L--L-L--L-L--L-L--L-L--L-L--

This may or may not be useful, depending on what you want to do with the data once you get it. But you may want to look at the module Data::Table:

my $table = Data::Table::fromSQL($dbh,
                                 "select whatever from table where col
+ = ?",
                                 $val);
[download]

And the query result is stored in a Data::Table object. From there it can be extracted or manipulated at will. In a CGI program you can print it out as an HTML table easily: print $table->html;

buckaduck

So I can get a field or update arbitrary fields with this kind of thing:

my $oldpoints = &getfield("users",$id,"arigatopoints");
$ret = &updatefields($table,$id,\@fields,\@vals);
[download]

If you are doing lots of little things it might pay to abstract it out of the center of things and try to see how few subs you can have that are written with SQL. But fact is, I recently sped up a script from 3 minutes to <10 seconds just by doing a complex query instead of little ones interspersed by Perl. SQL engines are fast.

Let's step back a bit and look at what you're really trying to do.

Databases are about storing "things" in persistent storage. Those "things" are "rows", which loosely represent something.

But in OO programming, all "things" are Objects, so what we would like to do would be to store objects, not rows, in databases. There are a couple of approaches: use an OODBMS, or have some way of converting your objects into an RDBMS format.

For the latter approach, I strongly recommend the Tangram library in CPAN.

Here is a simple "movie database" in Tangram...

#!/usr/bin/perl

use LazyObject;
use warnings;

# "LazyObject" just gives you simple "new", "get", "set" members
package Movie;      @ISA = (LazyObject);
package Person;     @ISA = (LazyObject);
package Job;        @ISA = (LazyObject);
package Credit;     @ISA = (LazyObject);
package Location;   @ISA = (LazyObject);

package main;
use strict;

use Tangram;
use Tangram::Schema;
use Tangram::RawDateTime;
use Tangram::IntrSet;
use Tangram::mysql;

my $schema = new Tangram::Schema
{
    classes =>
 [
  'Credit' => {
      fields => {
                 # nothing, this is an association class with no data.
             },
         },
         'Movie' => {
             fields => {
                 string => [ qw(title) ],
                 int => [ qw(release_year) ],
                 # this means there is a set of 'Credit' objects
                 # related to this 'Movie' object.
                 iset =>
                 {
                     credits => 'Credit',
                 },
             },
         },
         'Person' => {
             fields => {
                 string => [ qw(name) ],
                 rawdatetime => [ qw(birthdate) ],
                 ref => [ qw(birth_location) ],
                 # This person also has a set of credits
                 iset =>
                 {
                     credits => 'Credit',
                 },
             },
         },
         'Job' => {
             fields => {
                 string => [ qw(job_title) ],
                 # As does this job
                 iset =>
                 {
                     credits => 'Credit',
                 },
             },
         },
         'Location' => {
             fields => {
                 string => [ qw(location) ],
                 ref => [ qw(parent_location) ],
             },
         },
         ],
};

my ($dsn, $user, $pass) =
    ("dbi:mysql:database=movies", "root", "");

print "Connecting to the database\n";
my $dbh = DBI->connect($dsn, $user, $pass);

print "Creating tables with SQL command:\n";
Tangram::mysql->deploy($schema);

print "Now creating tables...\n";
Tangram::mysql->deploy($schema, $dbh);

print "Disconnecting...\n";
$dbh->disconnect() or warn $DBI::errstr;;

# now connect to it as if we were a normal program
print "Connecting to Storage...\n";
my $storage = Tangram::Storage->connect($schema, $dsn, $user, $pass);

# Insert some data
do {
    print "Building data objects...\n";
    my @locations =
        (
         new Location( location => "Grappenhall",
                       parent_location => new Location
                       ( location => "Warrington",
                         parent_location => new Location
                         ( location => "Cheshire",
                           parent_location => new Location
                           ( location => "England",
                             parent_location => new Location
                             ( location => "United Kingdom" ) ) ) ) ),
         new Location( location => "Dallas",
                        parent_location => new Location
                       ( location => "Texas",
                         parent_location => new Location
                         ( location => "United States" ) ) ),
         );

    my @credits = ( map { new Credit } (1..5) );

    my @jobs =
        (
         new Job( job_title => "Dr. Frank-N-Furter",
                  credits => Set::Object->new( $credits[0] ) ),
         new Job( job_title => "Wadsworth",
                  credits => Set::Object->new( $credits[1] ) ),
         new Job( job_title => "Prosecutor",
                  credits => Set::Object->new( $credits[2] ) ),
         new Job( job_title => "Long John Silver",
                  credits => Set::Object->new( $credits[3] ) ),
         new Job( job_title => "Dr. Scott",
                  credits => Set::Object->new( $credits[4] ) ),
         );

    my @movies =
        (
         new Movie( title => "Rocky Horror Picture Show",
                    release_year => 1975,
                    credits => Set::Object->new( @credits[0, 4] ) ),
         new Movie( title => "Clue",
                    release_year => 1985,
                    credits => Set::Object->new( $credits[1] ) ),
         new Movie( title => "The Wall: Live in Berlin",
                    release_year => 1990,
                    credits => Set::Object->new( $credits[2] ) ),
         new Movie( title => "Muppet Treasure Island",   
                    release_year => 1996,
                    credits => Set::Object->new( $credits[3] ) ),
         );
    
    my @actors =
        (
         new Person( name => "Tim Curry",
                     birthdate => "1946-04-19 12:00:00",
                     birth_location => $locations[0],
                     credits =>
                         Set::Object->new( @credits[0..3] ) ),
         new Person( name => "Marvin Lee Aday",
                     birthdate => "1947-09-27 12:00:00",
                     birth_location => $locations[1],
                     credits =>
                         Set::Object->new( $credits[4] ) ),
         );

    $|=1;
    print "Inserting data objects into storage...";
    print "movies";    $storage->insert(@movies);
    print ", jobs";    $storage->insert(@jobs);
    print ", credits"; $storage->insert(@credits);
    print ", actors";  $storage->insert(@actors);
    print ", done!\n";
    
};

# first get the person in question
print "Getting Remote objects...\n";
my ($r_person, $r_movie, $r_job, $r_credit, $r_location) =
    $storage->remote( qw(Person Movie Job Credit Location) );

# turn on tracing of SQL
$Tangram::TRACE = \*STDOUT;

for my $name ("Tim Curry", "Marvin Lee Aday") {
    print "Selecting...\n";
    my ($who) = $storage->select($r_person, $r_person->{name} eq $name
+);

    # print the same header; but let's throw in the birth location too
+,
    # because it's so easy.
    print "Printing...\n";
    print ($who->{name}, ", born in ", $who->{birth_location}->{locati
+on},
           " was in the following films:\n\n");

    # now iterate through the credits desired
    for my $credit ($who->{credits}->members) {

        my ($movie) =
            $storage->select($r_movie,
                             $r_movie->{credits}->includes($credit) );

        my ($job)
            = $storage->select($r_job,
                               $r_job->{credits}->includes($credit) );

        if ($movie) {
            print( $movie->title, " released in ",
                   $movie->release_year, "\n");
        } else {
            print "Um, an unknown movie :-}\n";
        }

        if ($job) {
            print '  ', $job->job_title, "\n";
        } else {
            print "  Oh dear, no job\n";
        }
    }
}

# Select all people from a suburb of Warrington, who were in the movie
+ "Clue"
my ($clue) = $storage->select($r_movie, $r_movie->{title} eq "Clue");
my ($person) = $storage->select($r_person, ($r_person->{birth_location
+}->{parent_location}->{location} eq "Warrington"));

print $person->{name}, "\n";
[download]

As you can see, there is no SQL here and I'm still doing some quite complex queries.

A brief investigation of UML is probably worth the effort, too - so you can figure out how to think of concepts such as foreign keys in OO terms.