Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Red face Simple SELECT problem

    Sorry, folks...I finally tumbled to the meaning of AutoCommit.

    I'm a newbie with this stuff!

    I have a small MySQL database, properly populated.

    When I run the script below, it seems to execute and return a success code. However, the insert does not succeed, in the sense that the new line is not recorded in the table.

    I have included the script, the output, and the log of STDERR.

    It's driving me nuts, so help will be appreciated.

    D

    ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++

    This is the Perl script:

    1 #use strict;
    2 #use CARP;
    3 use FileHandle;
    4 open (TEMPOUT, ">logfile.txt");
    5 open (STDERR, ">errfile.txt");
    6 use DBI;
    7 my $server = "localhost";
    8 my $dsn = "recipes";
    9 my $user = "root";
    10 my $password = "dougm";
    11
    12 my $dbh = DBI->connect ("dbi:mysql:$dsn:$server", $user, $password,
    13 { RaiseError => 1, AutoCommit => 0}) or die "Unable to connect to Database ".$DBI::errstr;
    14 my $trace_level = 2;
    15 $dbh->trace($trace_level);
    16 my $success, $len;
    17 do{{
    18 print "Enter new ingredient, CR to exit\n";
    19 my $value = <STDIN>;
    20 print TEMPOUT ">".$value."<\n";
    21 chomp ($value);
    22 print ">".$value."<\n";
    23 $len = length($value);
    24 print TEMPOUT '$len: '.$len." \n";
    25 if ($len > 0)
    26 {
    27 $value = uc $value;
    28 #$value = "\'".$value."\'";
    29 print TEMPOUT $value."\n";
    30 my $query = "INSERT INTO ingreds (ingred_name) VALUES (?);";
    31 print TEMPOUT $query."\n";
    32 my $sth = $dbh->prepare($query);
    33 ($success = $sth->execute($value)) or print "Database access failure. Error: ".$DBD::errstr."\n";
    34 my $rows = $sth->rows;
    35 print TEMPOUT "Rows inserted = ".$rows."\n";
    36# $sth->finish;
    37 if ( $success){
    38 print TEMPOUT $value." added to ingredients\n";
    39 }
    40 }
    41 print "Loop to next ingredient\n";
    42 }} until $len == 0;
    43 $dbh->disconnect;

    This is the output to TEMPOUT, as logged in logfile.txt

    >vodka
    <
    $len: 5
    VODKA
    INSERT INTO ingreds (ingred_name) VALUES (?);
    Rows inserted = 1
    VODKA added to ingredients
    >
    <
    $len: 0


    This is the STDERR output as logged in errfile.txt. Note the line that implies a loop back to line 25 of the script

    DBI::db=HASH(0x293aac0) trace level set to 0x0/2 (DBI @ 0x0/0) in DBI 1.622-ithread (pid 11648)
    -> prepare for DBD::mysql::db (DBI::db=HASH(0x293a928)~0x293aac0 'INSERT INTO ingreds (ingred_name) VALUES (?);') thr#2ea518
    -> dbd_st_prepare MYSQL_VERSION_ID 50147, SQL statement: INSERT INTO ingreds (ingred_name) VALUES (?);
    >- dbd_st_free_result_sets
    <- dbd_st_free_result_sets RC -1
    <- dbd_st_free_result_sets
    >count_params statement INSERT INTO ingreds (ingred_name) VALUES (?);
    <- dbd_st_prepare
    <- prepare= ( DBI::st=HASH(0x293c580) ) [1 items] at Add Ingredients.pl line 32
    -> execute for DBD::mysql::st (DBI::st=HASH(0x293c580)~0x293c5e0 'VODKA') thr#2ea518
    Called: dbd_bind_ph
    -> dbd_st_execute for 027b8c90
    >- dbd_st_free_result_sets
    <- dbd_st_free_result_sets RC -1
    <- dbd_st_free_result_sets
    mysql_st_internal_execute MYSQL_VERSION_ID 50147
    >parse_params statement INSERT INTO ingreds (ingred_name) VALUES (?);
    Binding parameters: INSERT INTO ingreds (ingred_name) VALUES ('VODKA');
    <- dbd_st_execute returning imp_sth->row_num 1
    <- execute= ( 1 ) [1 items] at Add Ingredients.pl line 33
    -> rows for DBD::mysql::st (DBI::st=HASH(0x293c580)~0x293c5e0) thr#2ea518
    <- rows= ( '1' ) [1 items] at Add Ingredients.pl line 34
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x293c5e0)~INNER) thr#2ea518
    Freeing 1 parameters, bind 0 fbind 0
    <- DESTROY= ( undef ) [1 items] at Add Ingredients.pl line 25 <<<What?? How does it loop back to line 25???
    -> disconnect for DBD::mysql::db (DBI::db=HASH(0x293a928)~0x293aac0) thr#2ea518
    imp_dbh->pmysql: 29c83e8
    <- disconnect= ( 1 ) [1 items] at Add Ingredients.pl line 43
    -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x293aac0)~INNER) thr#2ea518
    <- DESTROY= ( undef ) [1 items]
    Last edited by DougMof; 12-31-12 at 13:59. Reason: Figured it out!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,906
    I think that you chomp()ed the newline. It shouldn't be in the table.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •