SPUG: Two errors I need hashing out

Parr, Ryan Ryan.Parr at wwireless.com
Tue Jan 15 17:08:41 CST 2002


Maybe you could post the results of a "describe reports;" command so we can
see what all your field types are.

MySQL supports (through emulation) using placeholders in your SQL
statements. With placeholders you never need to comment your stuff, as the
DBI module will take care of that for you. Using placeholders would turn:

my $query3 = "INSERT into reports (uid, system_name, last_success_run, id)
values ('$uid', '$system_name', '$datestamp at $timestamp', '')";
$buffer3 .= "===> Executing Query: $query3\n\n";
my $sth = $dbh->prepare($query3);
$sth->execute;

Into:

my $query3 = "INSERT INTO reports (uid,system_name,last_success_run) VALUES
(?,?,?)";
my $sth = $dbh->prepare($query3);
$sth->execute($uid,$system_name,"$datestamp at $timestamp");

The main benefit of this is that you can _re-use_ the statement handle,
which is being used in a loop. Then instead of recreating the handle you can
simply create the handle outside of the loop and re-execute inside the loop.
This can be a major bonus if you loop a bunch of times. And it's (IMHO) much
cleaner. In databases other than MySQL it can have huge performance
increases in terms of database processing speed. ButMySQL doesn't do that.

-- Ryan


-----Original Message-----
From: Susanne Bullo [mailto:sweetsue at sweethomes.com] 
Sent: Tuesday, January 15, 2002 1:30 PM
To: Seattle Perl User's Group
Subject: RE: SPUG: Two errors I need hashing out


The uid is being called up from another table in order to be placed in the
reports table.  The uid can be used over and over.  It's the id that should
be unique and is setup as such.  Running those commands showed me that id is
the primary and the non-unique field is set to 0.  I can't seem to figure
out why it tries to go with 127 as the next id when that's already in place.
Scratching my head still.

Susanne Bullo - Sweet Homes

-----Original Message-----
From: Parr, Ryan [mailto:Ryan.Parr at wwireless.com]
Sent: Tuesday, January 15, 2002 12:40 PM
To: 'Daryn Nakhuda'; Susanne Bullo
Cc: Dan Ebert; Seattle Perl User's Group
Subject: RE: SPUG: Two errors I need hashing out


MySQL auto_increment fields can take an empty or NULL value and still work,
so I don't think it's choking on that. However it should be left out since
you're explicitly defining the fields/order you are inputting data.

Why would you have two separate accounts with the same UID though? Did you
set up the database? If not then maybe whoever did set this to be a unique
index. From the mysql command line:

mysql> show keys from reports;
-- and --
mysql> show index from reports;

This will show you whether or not the UID field is able to be non-unique. So
if the Non-Unique field is set to 0, then that's why you have a problem.

-- Ryan


-----Original Message-----
From: Daryn Nakhuda [mailto:daryn at marinated.org]
Sent: Tuesday, January 15, 2002 11:12 AM
To: Susanne Bullo
Cc: Dan Ebert; Seattle Perl User's Group
Subject: RE: SPUG: Two errors I need hashing out



if it's an auto-increment, don't try and explicitly set it to '', that's
probably where it is choking.

change your query to "INSERT into reports
(uid,system_name,last_success_run) values ('$uid','$system_name','$datestamp
at $timestamp')";



On Tue, 15 Jan 2002, Susanne Bullo wrote:

> Ok, I will place that in there to see if it fails - thanks.
>
> The weird thing about the number being called out is it's an id number 
> not a uid.  The uid is assigned by the previous line and it doesn't 
> need to be unique.  However, the id for this is supposed to be unique 
> but is set to automatically increment.  So, I don't know why it's 
> trying to overwrite something that should automatically increment. 
> Guess I'll look at the properties of that table.
>
> Thanks for your help!
>

>
> -----Original Message-----
> From: Dan Ebert [mailto:mathin at mathin.com]
> Sent: Tuesday, January 15, 2002 10:21 AM
> To: Susanne Bullo
> Cc: Seattle Perl User's Group
> Subject: Re: SPUG: Two errors I need hashing out
>
>
>
> The print on close file handle error could be cause by the failure of 
> the open command.  You might try
>
> open(FILE,">>$filename") or die $!;
>
> to make sure the file is opened.  (The die $! will print the error if 
> it
> fails.)
>
> The second SQL error looks like you are attempting to insert a value 
> into a table field which must be unique ... and there is already an 
> entry
with
> '127' in that field.   The 'key 1' doesn't mean the first value in the
> list of values to insert.  It refers to the field in the table which 
> is in conflict with the value you are attempting to insert.
>
> If you do want to update the table (or insert if it doesn't exist) you 
> might try the 'replace into' mysql command instead of the 'insert 
> into'
>
> Hope that helps.
>
> Dan.
> ----------------------------------------------------------
> Immigration is the sincerest form of flattery.
> 	- Unknown
> ----------------------------------------------------------
>
>
> On Tue, 15 Jan 2002, Susanne Bullo wrote:
>
> > Being new, I still haven't a complete clue as to what I'm doing. The
> listed
> > program is giving the following two errors:
> >
> > print on closed filehandle main::FILE at sunday line 61. 
> > DBD::mysql::st execute failed: Duplicate entry '127' for key 1 at 
> > sunday line 79.
> >
> > I'm at a loss as to what the first one is.  The second one I'm 
> > thinking
> has
> > to do with number assigning within the database but I'm not 
> > completely
> sure
> > (there is an id of 127 within the database but that is under id and 
> > not under uid which seems to be what "key 1" is referencing).
> >
> > If anyone has a soft spot for a newbie and wants to help me out, I'd 
> > appreciate it!
> >
> > #!/usr/bin/perl -w
> > use Time::Local;
> > use POSIX qw(mktime);
> > use DBI;
> > use LWP::UserAgent;
> > use HTTP::Request;
> > use HTTP::Response;
> > &get_date;
> > sub get_date {
> > @days = ('Sunday','Monday','Tuesday','Wednesday',
> > 'Thursday','Friday','Saturday');
> > @months = ('January','February','March','April','May','June','July',
> > 'August','September','October','November','December');
> > ($sec,$min,$hour,$mday,$mon,$year,$wday) =
> (localtime(time))[0,1,2,3,4,5,6];
> > $time = sprintf("%02d:%02d",$hour,$min);
> > if ($year < 50){
> > $year += 2000;
> > }
> > else {
> > $year += 1900;
> > }
> > $date = "$days[$wday], $months[$mon] $mday, $year at $time"; $mon2 = 
> > $mon + 1; $date2 = "$mon2/$mday/$year"; $date3 = 
> > "$mon2.$mday.$year"; }
> > my $buffer = '';
> > my $buffer2 = '';
> > my $buffer3 = '';
> > my $host = 'localhost';
> > my $database = 'safelist';
> > my $username = 'admin';
> > my $password = 'srb6653';
> > my $ua = new LWP::UserAgent();
> > my $dbh = DBI->connect("DBI:mysql:$database", $username, $password) ||
die
> > "Failed DBI connect(): $!\n";
> > my $query = "SELECT safelists.email_to, safelists.username, 
> > safelists.password, email_txt.email_subject, email_txt.email_text, 
> > safelists.uid, members.email from safelists, schedule, email_txt,
members
> > where email_txt.id = schedule.SUN and schedule.system_name = 
> > safelists.system_name and members.id = safelists.uid and 
> > email_txt.email_subject != ''"; $buffer .= "===> Executing Query: 
> > $query\n\n"; my $sth = $dbh->prepare($query);
> > my $rc = $sth->execute();
> > while
> >
> (my($email_to,$username,$password,$email_subject,$email_text,$system_n
> ame,$u
> > id,$email) = $sth->fetchrow_array) {
> > my $url = "http://$email_to?action=Send This
> >
> Message&username=$username&password=$password&subject=$email_subject&m
> essage
> > =$email_text";
> > $email_text =~ s/\\//g;
> > my $req = HTTP::Request->new(GET=>$url);
> > my $file_path = "./members/data/$username/$system_name/$date3.txt";
> > my $resp = $ua->request($req, $file_path);
> > my $sec_file_path = "members/data/$username";
> > my $outfile = "index.html";
> > my $filename = "$sec_file_path/$outfile";
> > open (FILE,">>$filename");
> > Line 61 -->{ print FILE "<a href=$sec_file_path>Problem on 
> > $date3</a>"; } close (FILE); } my $query2 = "SELECT safelists.uid, 
> > schedule.system_name from safelists, schedule where 
> > schedule.system_name = safelists.system_name and
> schedule.SUN
> > != '0'";
> > $buffer2 .= "===> Executing Query: $query2\n\n";
> > my $sth = $dbh->prepare($query2);
> > my $rc = $sth->execute();
> > while (my($uid,$system_name) = $sth->fetchrow_array) {
> > my $datestamp = $date2;
> > my $timestamp = $time;
> > $buffer3 .= "===> Executing Query: $query3\n\n";
> > my $sth = $dbh->prepare($query3);
> > Line 79 -->my $rc = $sth->execute();
> > }
> > undef $sth;
> > $dbh->disconnect;
> > 0;
> >
> > Susanne Bullo - Sweet Homes
> > DirectTV DSL
> > Get Paid to Read Email!
> >
> >
>
>
>  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>      POST TO: spug-list at pm.org       PROBLEMS: owner-spug-list at pm.org
>       Subscriptions; Email to majordomo at pm.org:  ACTION  LIST  EMAIL
>   Replace ACTION by subscribe or unsubscribe, EMAIL by your 
> Email-address  For daily traffic, use spug-list for LIST ;  for 
> weekly,
spug-list-digest
>      Seattle Perl Users Group (SPUG) Home Page: 
> http://zipcon.net/spug/
>
>

--



 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     POST TO: spug-list at pm.org       PROBLEMS: owner-spug-list at pm.org
      Subscriptions; Email to majordomo at pm.org:  ACTION  LIST  EMAIL
  Replace ACTION by subscribe or unsubscribe, EMAIL by your Email-address
For daily traffic, use spug-list for LIST ;  for weekly, spug-list-digest
     Seattle Perl Users Group (SPUG) Home Page: http://zipcon.net/spug/


 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     POST TO: spug-list at pm.org       PROBLEMS: owner-spug-list at pm.org
      Subscriptions; Email to majordomo at pm.org:  ACTION  LIST  EMAIL
  Replace ACTION by subscribe or unsubscribe, EMAIL by your Email-address
For daily traffic, use spug-list for LIST ;  for weekly, spug-list-digest
     Seattle Perl Users Group (SPUG) Home Page: http://zipcon.net/spug/


 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
     POST TO: spug-list at pm.org       PROBLEMS: owner-spug-list at pm.org
      Subscriptions; Email to majordomo at pm.org:  ACTION  LIST  EMAIL
  Replace ACTION by subscribe or unsubscribe, EMAIL by your Email-address
 For daily traffic, use spug-list for LIST ;  for weekly, spug-list-digest
     Seattle Perl Users Group (SPUG) Home Page: http://zipcon.net/spug/





More information about the spug-list mailing list