Error in my MS SQL script

mecrazycoder has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I wrote a sample DB program to insert values into a table.I am using MS SQL server. While executing below script

use warnings;
use strict;
use DBI;

my $dsn="XYZ";
my $user="XXXX";
my $password="XXXX";

my $dbh=DBI->connect("dbi:ODBC:$dsn", $user, $password,
                      { RaiseError => 1, AutoCommit => 0 });

my $query = "INSERT INTO temp (SNO, Name, Institute) VALUES (5,'Ram','
+IIT')";
my $query_handle = $dbh->prepare($query);

$query_handle->execute();
[download]

I am getting error as: Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

Comment on Error in my MS SQL script Download Code

Replies are listed 'Best First'.
Re: Error in my MS SQL script by Corion (Patriarch) on Oct 05, 2009 at 10:24 UTC
In your `->connect` call, you give DBI the setting `AutoCommit => 0`, which means that you will tell DBI later when to commit. But you never issue a `->commit` call, so DBI assumes that your program encountered an error and tells you that it is discarding all your changes, as likely was your intention. All of this information is found in the DBI documentation for AutoCommit ~~including the error message~~.	[reply] [d/l] [select]
Re^2: Error in my MS SQL script by mecrazycoder (Sexton) on Oct 05, 2009 at 11:01 UTC
Thanks	[reply]
Re: Error in my MS SQL script by marto (Cardinal) on Oct 05, 2009 at 10:29 UTC
You don't do a `$query_handle->commit() or die $query_handle->errstr;` after executing the query. As previously suggested it's worth while reading and understanding the DBI documentation, including the `AutoCommit` attribute and the `commit` method. Martin	[reply] [d/l] [select]
Re: Error in my MS SQL script by jakobi (Pilgrim) on Oct 05, 2009 at 10:25 UTC
~~Why do you say `AutoCommit => 0`, but don't commit later on? This should explain it, if the code shown contains all of the relevant logic and statements (does it?).~~ Considering the first two answers, I prefer Corion's answer with the extra background over this one :) (and I really should start to remember checking the thread between preview and node create). Update: I was initially a bit confused by the upvotes. After some thinking, I'd rather take & accept it as my fellow monks' congrats to overcoming the initial monk's level-rushing xp-whore-phase :).	[reply] [d/l]