DBD::ODBC and sp that inserts data (SQL2K)

[Steven Manross]

I want to use DBI/DBD with a stored procedure that calls 2 other stored
procedures.

-those other 2 stored procedures check tables and insert data if it did not
previously exist and then return the identity value to the first procedure
for purposes of normalization to insert into the main table.

When I invoke the stored procedure and it inserts data into one of the
helper tables, everything seems fine until the next "execute" in the foreach
loop, when perl returns an Invalid Cursor State (SQL-24000).

*I've tried disconnecting, reconnecting, preparing and executing from inside
the foreach loop and that seems to work, but doesn't seem like a fix to the
problem, and is pretty slow.

The stored procedure seems to invoke correctly from query analyzer.

This is very reproducable, and each time the script is invoked, it gets a
little further in processing the list of data (since it only fails when it
inserts data to one of the 2 [or both of the] helper tables).

I can truncate all of the tables, start over and get the same errors at the
same places, with the same behavior (fails after an insert to the helper
tables).

As well, I saw a similar post back in September, 2001 to this list and it
too was dealing with Inserts, but I didn't know if it would apply in my case
since my inserts are done from inside the stored procedure, and not strictly
within DBI. As well, I did employ the
finish() in case it helped.

Similar:
  Problem: http:[EMAIL PROTECTED]/msg05997.html
  Solution: http:[EMAIL PROTECTED]/msg06024.html

I wasn't able to find any more info from the archives that would lead me to
a solution.

The data is extracted from some log files for MAC addresses, IPs,
workstation names and usernames. Each time I encounter a new IP or new MAC
address the problem exerts itself.
Any help would be appreciated. 

I have DBD::ODBC 0.41.

Here's the code...
use DBI;
$dsn = 'driver={SQL
Server};server=servername;database=computer_info;uid=username;pwd=password';
$dbh = DBI->connect("dbi:ODBC:$dsn",{ 
                            PrintError => 1,
                            RaiseError => 1,
                            AutoCommit => 1,
                                    } 
                    ) or die "$DBI::errstr\n";

$sth = $dbh->prepare("exec sp_insert_computer_info ?,?,?,?,?,?");

#@dir_entries is a list of valid file names in "\\\\Server\\share"

foreach $file (@dir_entries) {
  print "$file\n";
  open (LOG_FILE,"\\\\server\\share\\$file") || die "Error reading file:
$file\n";
  foreach $line (<LOG_FILE>) {
    $x++;
    my @LINE = split (/ \| /,$line,9);
    my $return_val = 0;
    print " ($x) $LINE[2],$LINE[7],$LINE[4],$LINE[6],$LINE[5]\n";
    #the parameters look like this
    #$sth->bind_param(1, "DOM");
    #$sth->bind_param(2, "username");
    #$sth->bind_param(3, "workstat");
    #$sth->bind_param(4, "00-03-75-4A-CD-12");
    #$sth->bind_param(5, "172.17.242.19");
    #$sth->bind_param(6, $return_val);
    $return =
$sth->execute($LINE[2],$LINE[7],$LINE[4],$LINE[6],$LINE[5],$return_val);
    $sth->finish();
  }
  close (LOG_FILE);
}

$dbh->disconnect;
exit;