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

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.