Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??

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");
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; }

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":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (3)
As of 2024-06-21 10:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.