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 :})
