If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > Perl and the DBI > Simple SELECT problem

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,788
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On