MS SQL + Perl = Phantom rows

[Rebles]

Hi, I just posted this in the MS SQL Section, but maybe my problem is rooted in Perl, so it's more appropriate to post here instead (sorry for the double post)


I'm writing a PERL script to access and insert rows into a Microsoft SQL. i'm using MS SQL Server Management Studio Express (2005) to architect tables and queries.

I've inserted two records into my "Pools" table
One from MS SQL Manager as a test, and another from my PERL script.

The one inserted by MS SQL Manager shows up everytime. The row from my PERL script doesn't show up on MS SQL Manager and ONLY shows up on my PERL script and ONLY if the INSERT query isn't commented out.

For me, this is the second day of troubleshooting . Does anybody have any insight?

$dbHandle = S_dbConnect($hostname, $database, $username, $password, $port);
 
$query = qq{INSERT INTO Pools (site_ID, pool_name, last_job_ID)
    VALUES(1, 'CSE_IAL_intnb6', 0)};
print "$query\n";
 
$dbHandle->do($query);
 
$query = qq{SELECT * FROM Pools};
print "$query\n";
 
$m_sth = S_dbExecute($dbHandle, $query);
 
while(my $row = $m_sth->fetchrow_hashref()) {
    print "$row->{pool_ID}\t$row->{site_ID}\t$row->{pool_name}\t$row->{last_job_ID}\n";
}
print "END\n";
 
######################################################################
# Subroutine: S_dbConnect
# Function:
#       Creates a database connection to the database specified by the global
#       variables in the database initialization section at the top of this
#       file.
# Variables:
#       $g_serverName contains the name of the database server.
#       $g_dbName contains the name of the database on the server.
#       $g_interfacesFile contains the path to the interfaces file which is
#               required by Perl::DBI.
#       $g_dbUsername contains the username which will be used to access the DB.
#       $g_dbPassword contains the password used to authenticate with the DB.
# Returns:
#       $H_DB is the database handle established by the database connection
#
######################################################################
 
sub S_dbConnect{
        my ($server,$db,$user,$password,$port) = @_;
        print "Trying to connect to Database...   " if ($debug) ;
        $H_DB = DBI->connect("dbi:Sybase;server=$server:$port;database=$db",$user,$password, 'Sybase' )
        || die "DBI: Unable to connect to the database - $DBI::errstr\n";
        $H_DB->{'RaiseError'} = 1;
        print "Connected to the Database\n" if ($debug) ;
        return $H_DB;
}
 
######################################################################
# Subroutine: S_dbExecute
# Function:
#       Executes a SQL query on an already established database connection.
# Variables:
#       $m_sql contains the SQL query to be executed on the database.
#       $H_DB is the database handle the sql query is to be executed on.
# Returns:
#       $m_sth is the handle for the data returned by the database.
#
######################################################################
 
sub S_dbExecute{
        my ($H_DB, $m_sql) = @_;
        my $m_sth = $H_DB->prepare($m_sql);
        # if prepare fails, sth is undefined
        if (!defined $m_sth){
                print "Error preparing sql record: " . $H_DB->errstr .my @ret_val = (1,$m_sth);
                return @ret_val; "\n";
        }
 
# sth returns true if it succeeds
        if ($m_sth->execute()){
                return $m_sth;
        }
# sth returns false if it fails
        else{
                die "Error quering Database\n";
        }
}

The two functions above are not mine, they are borrowed

Here are the results:

filc0201> ./test_db2.pl
Trying to connect to Database...   Connected to the Database
INSERT INTO Pools (site_ID, pool_name, last_job_ID)
        VALUES(1, 'CSE_IAL_intnb6', 0)
SELECT * FROM Pools
22      1       test    0
30      1       CSE_IAL_intnb6  0
END

Here is my server's info:

filc0201> uname -a
Linux filc0201 2.6.5-7.276.PTF.196309.1-smp #1 SMP Mon Jul 24 10:45:31 UTC 2006 x86_64 x86_64 x86_64 GNU/Linux
filc0201> cat /etc/issue
 
Welcome to SUSE LINUX Enterprise Server 9 (x86_64) - Kernel \r (\l).

[Rebles]

Ah, nevermind,
I needed to add a
$dbHandle->commit(); for the changes to take effect.

Also, Does anybody know if there is a way to retrieve the auto incremented ID in the same SQL statement? It would help cut down the over head in my script.