for a bigger cms i use frequently the following subroutine, which handles different sorts of sql queries, as my database days are still young and this part of code is essential for the cms i would like to your opionions about these lines.

sql_query handles different queries ranging from smaller and bigger select statements to update and insert like

my $chk_arid = "SELECT MAX( arid ) FROM $table where flag = ?";
my ($arid) = sql_query( $chk_arid , 1 );

#                 or
 my $cmd = "select aid, code from authors where name =  ? ";
 ( $aid, $code ) = sql_query( $cmd, $name );

#                   or even

my $cmd_up = "UPDATE SET flag = 0 where flag = 1 and arid = ?";
sql_query( $cmd_up, $pub{arid} ) or die("Error update flag");

     my $cmd_ins = "INSERT INTO $table ( arid, catid, filename, title,
        aid, teaser, text, links, comment, changeby, newsid,
  keywords, description, flag) VALUES ( ?, ?, ?, ?, ?,
        ?, ?, ?, ?, ?, ?, ?, ?, ?)";
sql_query( $cmd_ins, $arid, $file, $pub{title}, $pub{aid},
  $pub{teaser}, $pub{text}, $pub{links}, $comment, $aid, 
  $pub{newsid}, $pub{keywords}, $pub{description}, 1 )
or die("Failed to insert new changed sql data :( \n");

[download]
the subroutine itself :)) 

sub sql_query {

        my $cmd = shift;
        my @place_holders;

        if ( defined $_[0] ) {
                foreach my $i ( 0 .. $_ ) {
                        $place_holders[$i] = "$_[$i]";
                }
        }

       die("Received no sql cmd for database query") unless ( defined 
+$cmd);

        my $dbh = DBI->connect("DBI:mysql:$dbname","$username","$passw
+d");
        die "ERR: Couldn't open connection: ".$DBI::errstr."\n" unless
+ $dbh;

        my $sth = $dbh->prepare( $cmd );
        die "Couldn't prepare statement: $DBI::errstr stopped\n" unles
+s $sth;

        $sth->execute( @place_holders )
        or die "Couldn't execute statement: $DBI::errstr\n";

        my @row = $sth->fetchrow_array() if ( $cmd =~ /SELECT/i );
        $sth->finish;
        $dbh->disconnect;

        return @row if ( $cmd =~ /SELECT/i);
        return 1;
}

[download]

Well everything is working so far, but are there any (small or big) relevant contraindication speeking against the use of this snippet?
thx in advance for your opinion and interessting pointers or cleanups maksl
(ps speed is not so a big limitation as this piece of code is only use for the authors .. users see plain htm)

In reply to sql query subroutine check by maksl

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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! —
  • 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:
    & &amp;
    < &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.