Perl / DBI / FreeTDS / SQLAzure Some inserts are ignored

Question

I have a problem with Perl using DBI and FreeTDS (on Ubuntu) to insert some data into Azure SQL. A problem may occur and some lines are ignored, and then I can just run it again without any problems.

Script:

print "Importing File $file: to Staging Table\n"; open my $info, $file or die "Could not open $file: $!"; $dbh->do("EXEC DWTOOLS.InitStage;") or die $DBI::errstr ; #truncates the table my $sth = $dbh->prepare("INSERT INTO DWSTAGE.CDRImport (LINE) VALUES(?);") or die $DBI::errstr ; my $counter = 0; while( my $line = <$info>) { $line =~ s/\r?\n$//; $counter++; print "Loading line $counter: $line\n" ; my $rc = $sth->execute($line) or die $DBI::errstr ; print "Result: $rc\n"; } close $info; print "\nChecking Data Warehouse: $counter lines expected\n" ; my $checksth = $dbh->prepare("EXEC DWTOOLS.CheckStage ?;") or die $DBI::errstr ; my $checkrc = $checksth->execute($counter) or die $DBI::errstr ; my @row; while ( @row = $checksth->fetchrow_array( ) ) { print "Row: @row\n"; } 

gives the result:

 Importing File filename: to Staging Table Loading line 1: data redacted Result: 1 Loading line 2: data redacted Result: 1 etc. etc. with no indication of errors Loading line 165: data redacted Result: 1 Loading line 166: data redacted Result: 1 Checking Data Warehouse: 166 lines expected Row: 35 166 Row: 35 166 Loading to Data Warehouse 

Thus, when I look at the table, it shows that all the initial lines are missing until a certain moment when it started working - reliably - to the end - so basically the last 35 lines of the file are downloaded, and they start from 1 to 35, where line 1 is a line (166-35 + 1) or something else. There is a PK column in the table in Azure, clustered IDENTITY, and it starts with 1, and there are no spaces, so he, like the first, inserted as many inserts without any signs of error. This happens in different files, different sizes and in different places of the file.

Files are processed in a loop, with each file being opened, processed, and closed if this has anything to do with this odd behavior. The application is reinstalled once for each file, but the Azure SQL connection is maintained throughout the life of the program if this can cause a problem. I still expected the program to die if the connection fails, but judging by the absence of error codes returning from execution, I am not sure that I am getting any indication of an error.

If I just continue and restart the program, all the lines will go in and everything will be fine.

I am not sure which conclusion to draw. Right now, my conclusion is that FreeTDS is buggy and unreliable.

Answer 1

It looks like the DB is pasting before the truncation process is complete. If this is a stored procedure, you can add some kind of flag so that it is filled, and check this before moving on. Or better yet, put the whole set of db calls into a transaction.