On Sat, 21 Sep 2002 02:16:56 -0500 Kevin <[EMAIL PROTECTED]> wrote:
> My question is, I'm trying to build a 'custom' querier for a survey
> program... based on the information submitted, the program will generate
> an SQL Query statement. The command that my program works absolutely
> find if I copy/paste it into MySQL, however, DBI won't pass it... if I
> pass the QUERY Along to DBI typed in manually it works...
Run 'perldoc DBI' and 'perldoc mysql' and read them completely at least
once. The parts in DBI on placeholders and error reporting may be
particularly interesting now, but everything is worth reading.
> Here is a copy of the block of code
> #Attempt to build an artificial SQL Query
> #########################################
> $i=0;
> if($vars{'f_name'} ne "") {
> @query[0] = "AND `f_name` = \'$vars{'f_name'}\'";
> $i++;
> }
> if($vars{'l_name'} ne "") {
> @query[$i] = "AND `l_name` = \'$vars{'l_name'}\'";
> $i++;
> }
> if($vars{'stud_num'} ne "") {
> @query[$i] = "AND `stud_num` = \'$vars{'stud_num'}\'";
> $i++;
> }
> if($vars{'grade'} ne "") {
> @query[$i] = "AND `grade` = \'$vars{'grade'}\'";
> $i++;
> }
>
> foreach $item(@query) {
> $dastring = join(" ",$dastring,$item);
> #$end = "\"";
> #$dastring = join("",$dastring,$end);
> }
You don't appear to be using '-w' in the '#!' line or 'use strict;'. Both
help you find minor problems before the become big.
It would be more efficient to use placeholders. I would have done the part
above like this:
my ( @query, @val );
foreach ( qw( f_name l_name stud_num grade ) ) {
push( @query, "$_ = ?" ), push( @val, $vars{$_} )
if exists $vars{$_} && defined $vars{$_} && length $vars{$_};
}
> #print "Dum Dum Dum... The query is <b>$dastring</b>\n";
>
> #Build the remaining query string
> $first_part = "SELECT
> `level`,`f_name`,`l_name`,`stud_num`,`grade`,`last_survey` FROM `users`
> WHERE 1";
> $finalstring = join(" ", $first_part,$dastring);
> print "The Final Product <b>$finalstring</b>\n";
Unless you typed this in (don't do that) instead of cutting and pasting
from the real code, those '`' characters make the SQL invalid.
my $finalstring = join "\n",
"SELECT level, f_name, l_name, stud_num, grade, last_survey",
" FROM users"
" WHERE " . join( " AND ", @query );
print "SQL: $finalstring\n";
> #############################################
> #End of Artificial Query
>
> #Connect to the SQL Server specified in the %sql hash
> $dbh = DBI->connect("dbi:mysql:$sql{'database'}", "$sql{'username'}",
> "$sql{'password'}");
You are not checking for errors.
my $dbh = DBI -> connect( "dbi:mysql:$db, $usr, $pwd,
{ AutoCommit => 1, PrintError => 0, RaiseError => 0 } )
or die "Can't connect to $db as $usr, $DBI::errstr\n";
# $dbh -> {RaiseError} = 1;
# You can avoid this and the 'or die's by using 'RaiseError => 1'
# You must either set RaiseError or check the result of each
# DBI method call.
> #Pepare an SQL Query for execution
> $sth = $dbh->prepare($finalstring);
my $sth = $dbh -> prepare( $finalstring )
or die "Prepare failed, $DBI::errstr\n$finalstring\n";
> #Executes the SQL Command in the previous statement
> $rc = $sth->execute; #executes the search
# Execute using values to fill placeholders
$sth -> execute( @val ) or die "Execute failed, $DBI::errstr\n";
# Tell DBI to automatically put column values into hash elements
$sth -> bind_columns(
\( @vars{qw( level f_name l_name stud_num grade l_survey )} ) )
or die "Bind_columns failed, $DBI::errstr\n";
# I often like custom error messages as shown above,
# but, for fetch() in a loop, RaiseError is mush easier
$dbh -> {RaiseError} = 1;
while ( $sth -> fetch ) {
# All the column values are automatically put in %vars
}
> #my $row_hash; #defines $row_hash as a local variable.
>
> #Grab and breakup the row hash
> #while($row_hash = $sth->fetchrow_hashref) {
> $vars{'f_name'}=$row_hash->{f_name}; #Loads the Username
> $vars{'l_name'}=$row_hash->{l_name}; #Loads the password
> $vars{'stud_num'}=$row_hash->{stud_num}; #Loads the Student #
> $vars{'grade'}=$row_hash->{grade}; #loads the grade
> $vars{'l_survey'}=$row_hash->{last_survey}; #loads the date of the
> last
> $vars{'level'}=$row_hash->{level}; #Grabs the user level
>
>
> print <<endofhtml;
> <tr>
> <td>$vars{'f_name'}</td>
> <td>$vars{'l_name'}</td>
> <td>$vars{'stud_num'}</td>
> <td>$vars{'grade'}</td>
> <td>$vars{'l_survey'}</td>
> <td>$vars{'level'}</td>
> </tr>
> endofhtml
> }
> $sth->finish; #finishes the SQL Query
finish() is only useful if you are not fetching all the rows from the
query.
> $dbh->disconnect(); #Disconnect the database to save connections
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
