On Jun 12, 6:34 pm, [EMAIL PROTECTED] (Chas Owens) wrote:
> On 6/12/07, Northstardomus <[EMAIL PROTECTED]> wrote:
> snip> $dbh->prepare('INSERT INTO area_status (areaID, survey_date,
> > update_time, status ) VALUES (?,?,?,?)');
> > $dbh->execute('$values[0]', '$values[1]', '$values[2]',
> > '$values[3]');
>
> snip
>
> You are getting an error because $dbh->prepare returns a statement
> handle that can be executed. The code should be written like this:
>
> my $sth = $dbh->prepare('
> INSERT INTO area_status (areaID, survey_date, update_time, status )
> VALUES (?,?,?,?)
> ');
> $sth->execute(@values[0 .. 3]);
>
> Also, connecting to the database every time you want to do something
> is expensive. You should connect once near the beginning of the
> script and use that connection for the rest of the script. The
> statement handle may also be reused, so you might want to name it with
> a better name than $sth* like this:
>
> my $dbh = DBI->connect(...);
>
> my $insert_area_status_sth = $dbh->prepare(...);
>
> while (<DATA>) {
> my @rec = split /,/;
> $insert_area_status_sth->execute(@rec) if $rec[0] eq 'Y';
>
> }
>
> * for handles that have a limited scope $sth is perfectly fine
Chas, thank you, I don't think I would have found that error any time
soon. I really appreciate it. Also, thanks for the advice on how I'm
using the database, I think it is spot on and I am going to impliment
your suggestions.
For reference here is what I ended up with, I had to do the
placeholders in the select statement as well.
Replaced the comment code with the uncommented code:
#$sql = "SELECT * FROM area_status WHERE areaID='$values[0]' AND
survey_date='$values[1]' AND status='$values[3]'";
#$sth = $dbh->prepare($sql);
$sql = "SELECT * FROM area_status WHERE areaID=? AND survey_date=?
AND status=?";
$sth = $dbh->prepare($sql);
$sth->execute($values[0], $values[1], $values[3]) or die $dbh-
>errstr;;
and
# if ($OK2INSERT) {
# $dbh = DBI->connect("DBI:SQLite:dbname=C:/Lanosrep/beW/Perl/
HelpPage/area.db", "", "", {'RaiseError' => 1});
# print "<br/>Inserting into Database , @values.";
# $dbh->do("INSERT INTO area_status (areaID, survey_date,
update_time, status ) VALUES ('$values[0]', '$values[1]',
'$values[2]', '$values[3]')");
# $dbh->disconnect();
# }
if ($OK2INSERT) {
#$dbh = DBI->connect("DBI:SQLite:dbname=C:/Lanosrep/beW/Perl/
HelpPage/area.db", "", "", {'RaiseError' => 1});
print "<br/>Inserting into Database , @values.";
my $sth = $dbh->prepare('INSERT INTO area_status (areaID,
survey_date, update_time, status ) VALUES (?,?,?,?)');
$sth->execute($values[0], $values[1], $values[2], $values[3])
or die $dbh->errstr;
$sth->finish;
$dbh->disconnect();
}
NSD
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
