Changeset 231
- Timestamp:
- 08/13/08 15:42:11
- Files:
Legend:
- Unmodified
- Added
- Removed
- Modified
- Copied
- Moved
obskml/trunk/QAQC/CalcPlatformUptimePercentage.pl
r228 r231 1 1 ####################################################################################################### 2 2 #Revisions 3 # Rev: 1.3.0.0 4 # Author: DWR 5 # Changes: Fixed the database error handling. On the database connect statement, changed RaiseError to 0 so the errors don't cause us to exit out. 6 # 7 # Sub: AddRecordToDatabase(...) 8 # Changes: Added retry ability for INSERTS if the database is locked. Added timeout value for database when retrying to keep CPU cycles down. 9 # 3 10 #Rev: 1.2.0.0 4 11 #Author: DWR … … 42 49 43 50 #1 enables the various debug print statements, 0 turns them off. 44 use constant USE_DEBUG_PRINTS => 0;51 use constant USE_DEBUG_PRINTS => 1; 45 52 46 53 use constant SECONDS_PER_DAY => ( 24 * 60 * 60 ); … … 197 204 #Try and connect to the database. 198 205 my $DB = DBI->connect("dbi:SQLite:dbname=$strDBName", "", "", 199 { RaiseError => 1, AutoCommit => 1 });206 { RaiseError => 0, AutoCommit => 1 }); 200 207 if(!defined $DB) 201 208 { … … 272 279 if( USE_DEBUG_PRINTS ) 273 280 { 274 print( "TestProfileID: $test_profile \n");281 print( "TestProfileID: $test_profile_id\n"); 275 282 } 276 283 my %SensorIDs; … … 387 394 # m_date >= '$strStart' AND m_date < '$strEnd' AND 388 395 396 389 397 #DWR v1.2.0.0 390 398 #Added time zone adjustment into WHERE clause for start/end date range. … … 398 406 datetime(m_date) < datetime('$strEnd','$iTimeZoneShift hours');"; 399 407 400 my $hSt = $DB->prepare( $strSQL ); 401 if( !defined $hSt ) 402 { 403 print( "ERROR: Unable to prepare SQL statement; $strSQL.\n"); 404 return( -1 ); 405 } 406 if( !$hSt->execute( ) ) 407 { 408 print( "ERROR: Failed execute: $hSt->errstr()\n SQLStatement: $strSQL\n"); 409 return( -1 ); 410 } 411 my $iCnt = $hSt->fetchrow_array(); 412 my $strDay = substr( $strStart, 0, 10); 413 %$PlatformIDs->{Platform}{$strPlatformID}{Sensor}{$strSensorName}{Day}{$strDay}{Count} = $iCnt; 414 %$PlatformIDs->{Platform}{$strPlatformID}{Sensor}{$strSensorName}{UpdateInterval} = $iUpdateInterval; 408 my $iCnt = -1; 409 410 my $hSt = $DB->prepare( $strSQL ); 411 if( defined $hSt ) 412 { 413 if( $hSt->execute( ) ) 414 { 415 $iCnt = $hSt->fetchrow_array(); 416 my $strDay = substr( $strStart, 0, 10); 417 %$PlatformIDs->{Platform}{$strPlatformID}{Sensor}{$strSensorName}{Day}{$strDay}{Count} = $iCnt; 418 %$PlatformIDs->{Platform}{$strPlatformID}{Sensor}{$strSensorName}{UpdateInterval} = $iUpdateInterval; 419 } 420 else 421 { 422 my $strErr = $hSt->errstr; 423 print( "QueryPlatformSensorReportCount::ERROR: Failed execute: $strErr\n SQLStatement: $strSQL\n"); 424 } 425 } 426 else 427 { 428 print( "QueryPlatformSensorReportCount::ERROR: Unable to prepare SQL statement: $strSQL.\n"); 429 } 430 415 431 return( $iCnt ); 432 416 433 } 417 434 … … 445 462 else 446 463 { 447 print( "ERROR: Failed execute: $hSt->errstr()\n SQLStatement: $strSQL\n"); 464 my $strErr = $hSt->errstr; 465 print( "ERROR: Failed execute: $strErr\n SQLStatement: $strSQL\n"); 448 466 } 449 467 return( $strURL ); … … 698 716 my ( $DB, $strPlatformID, $Sensor, $strSensorAvg, $strDate ) = @_; 699 717 718 719 # DWR v1.3.0.0 720 # Set the timout value to 2 seconds. This is applicable to the execute statement if the database is locked for instance, we'll wait 2 seconds 721 # waiting on the lock before we give up. 722 $DB->func( 2000, 'busy_timeout' ); 723 700 724 #We have to lookup the sensor id for the platform. 701 725 my $strSQL = "SELECT sensor_id … … 705 729 LIMIT 1;"; 706 730 731 my $iSuccess = 0; 732 my $iRetry = 0; 733 my $strSensorID; 707 734 my $hSt = $DB->prepare( $strSQL ); 708 if( !defined $hSt ) 709 { 710 print( "ERROR: Unable to prepare SQL statement; $strSQL.\n"); 735 if( defined $hSt ) 736 { 737 if( $hSt->execute( ) ) 738 { 739 $strSensorID = $hSt->fetchrow_array(); 740 } 741 else 742 { 743 my $strErr = $hSt->errstr; 744 print( "AddRecordToDatabase::ERROR: Failed execute: $strErr\n SQLStatement: $strSQL\n"); 745 return( -1 ); 746 } 747 } 748 else 749 { 750 print( "AddRecordToDatabase::ERROR: Unable to prepare SQL statement: $strSQL.\n"); 711 751 return( -1 ); 712 752 } 713 if(! $hSt->execute( ) ) 714 { 715 print( "ERROR: Failed execute: $hSt->errstr()\n SQLStatement: $strSQL\n"); 716 return( -1 ); 717 } 718 my $strSensorID = $hSt->fetchrow_array(); 753 754 $iSuccess = 0; 755 $iRetry = 0; 756 719 757 if( defined $strSensorID ) 720 758 { … … 726 764 VALUES('$strDate', $strSensorID, $strSensorAvg);"; 727 765 728 $hSt = $DB->prepare( $strSQL ); 729 if( !defined $hSt ) 730 { 731 print( "ERROR: Unable to prepare SQL statement; $strSQL.\n"); 732 return( -1 ); 733 } 734 if( !$hSt->execute( ) ) 735 { 736 print( "ERROR: Failed execute: $hSt->errstr()\n SQLStatement: $strSQL\n"); 737 return( -1 ); 738 } 739 return( 1 ); 766 #DWR v1.3.0.0 767 # Added retry capability if the database is locked. 768 while( !$iSuccess ) 769 { 770 $hSt = $DB->prepare( $strSQL ); 771 if( defined $hSt ) 772 { 773 if( $hSt->execute( ) ) 774 { 775 return( 1 ); 776 } 777 else 778 { 779 my $strErr = $hSt->errstr; 780 if( $strErr =~ 'locked' ) 781 { 782 print( "AddRecordToDatabase::ERROR: Failed execute: $strErr\n SQLStatement: $strSQL\n Retry Attempt: $iRetry\n"); 783 } 784 else 785 { 786 print( "AddRecordToDatabase::ERROR: Failed execute: $strErr\n SQLStatement: $strSQL\n"); 787 return( -1 ); 788 } 789 } 790 } 791 else 792 { 793 print( "AddRecordToDatabase::ERROR: Unable to prepare SQL statement: $strSQL.\n DBI::errstr()\n"); 794 return( -1 ); 795 } 796 $hSt->finish(); 797 undef $hSt; 798 799 $iRetry++; 800 } 740 801 } 741 802 return( 0 );
