Actually it is now getting worse. It has AutoCommit set to 0 so I am doing this:
$::sql = "UPDATE GLOBALSettings SET Lang = ?";
$::crt = $::lang_code;
&Debug("About to prepare: $::sql with criteria: $::crt");
$::sth = $::dbh->prepare($::sql) or &Error("Unable to prepare statement.");
$::rec = $::sth->execute ($::crt) or &Error("Unable to execute statement");
$::dbh->commit;
But it is now writing a <NULL> value on the DB. That &Debug line shows:
About to prepare: UPDATE GLOBALSettings SET Lang = ? with criteria: SP
So the value is fine but it is not updating right. What I found was that the
second field on the SQL table was set to no nulls so the update was failing on
the webserver log because of that. BTW responding to the "app bombs" ambiguity,
I use the value for an auto META REFRESH value to find out the directory of the
language I am running.
Regards,
Javier Moreno
-----Original Message-----
From: Michael A Chase [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 10, 2005 11:21 AM
To: Moreno, Javier
Cc: dbi-users@perl.org
Subject: Re: Value deletion
Quoting "Moreno, Javier" <[EMAIL PROTECTED]>:
> I have a script that writes a single record to a single row on a
> GLOBALSettings table. I have a web page that does that and it does so
> successfully. I go into the database with SQL Server Enterprise Manager and
> see the value on the table. Then I use another page that takes advantage of
> that value, it selects with no criteria the needed field and the value gets
> wiped out from the DB and of course the app bombs. I tried using a
> dbh->commit at the end of the update script but that didn't work either. Does
> anyone know what is wrong? Sample code below:
You need either $dbh->{AutoCommit} set to 1 or you must use $dbh->commit or the
value will be rolled back when your script exits.
You didn't show us your DBI->connect() call, so we don't know what it set.
> #### INSERT SCRIPT
>
> # Insert into DB
> my($sql) = "UPDATE GLOBALSettings SET Lang = '$::lang_code'";
# Bind variables should be used whenever possible.
my $sql = "UPDATE GLOBALSettings SET Lang = ?";
> my($criteria_var) = "";
> my($criteria) = "";
> my($reference) = &SQL_Update($sql, $criteria_var, $criteria);
my $reference = &SQL_Update( $sql, $criteria_var, $::lang_code, $criteria);
$dbh -> commit;
> # Disconnect from database
> $::rc = $::dbh->disconnect or
> &Error("Unable to disconnect from database.");
# If you are trapping individual errors, you should include
# the DBI error message or you won't have a clue what went wrong.
$::rc = $::dbh->disconnect or
&Error( "Unable to disconnect from database, $DBI::errstr." );
> sub SQL_Update {
> my ($sql, $criteria_field, $criteria) = @_;
# If you collect all the bind values in an array, you don't
# have to worry about a special case when there aren't any.
my ( $sql, $criteria_field, @value ) = @_;
> # Append criteria if present
> if ($criteria_field && $criteria) {
> $sql.= " WHERE $criteria_field = ?";
> }
>
> # Debug the SQL statement (uncomment for testing)
> &Debug("$sql");
>
> # Prepare a SQL statement
> my $sth = $::dbh->prepare( $sql ) or
> &Error("Unable to prepare statement.");
# Again, _always_ include the error text from DBI.
my $sth = $::dbh->prepare( $sql ) or
&Error( "Unable to prepare statement, $DBI::errstr." );
> # Execute the SQL statement prepared above, use with or without criteria
> depending on if it is present
> # or not
> if ($criteria) {
> my $rc = $sth->execute ( $criteria ) or
> &Error("Unable to execute statement", "$sql with criteria $criteria");
> } else {
> my $rc = $sth->execute or
> &Error("Unable to execute statement", $sql);
> }
# Because of the array, having criteria isn't a special case.
my $rc = $sth->execute( @value ) or
&Error( "Unable to execute statement, $DBI::errstr",
"$sql with: " . join " ", map { "'$_'" } @value );
> # Return a TRUE value just so that the variable which gets assigned to the
> query execution on the caller
> # program does not report an empty value. If we got this far then all went
> OK
> 1;
> }
--
Mac :})
