Odd SQL Issue - Different Result Every Run
Posted: Wed Dec 08, 2010 12:19 pm
This is running PHP-CGI on Windows 2003. This is using Microsoft's SQL driver for PHP.
There may be a way to make SQL do the work, and if there is, please enlighten me. For now I have PHP doing the work. Here is the code:
This is kind of hard to explain, I'll do the best I can. This script was designed to fill an intermediate database with data for use on the web. The script resides in a non-web accessible part of the computer and is ran via Windows' Scheduled Task manager.
What determines an entry in the intermediate database is the 'Capias_Status' portion of the record. Each 'Capias_Case_Number' has multiple entries (either being issued, reissued, executed and so forth). The SQL statement sorts so that the newest entry comes first. It then looks to see if the last activity was "Issued". If it was it adds it to the array of active warrants. It then goes through the array and inserts the records into the intermediate database.
Now here's the weird part. I began to notice the amount of open warrants should be about 26K. And sometimes the result is between 1K and 16K... and sometimes the full 26K. I began to run the script via commandline and see what I could see.
This was ran side by side with two completely different results. What is going on?
*edit*
One more thing I forgot to mention. The place where the script drops off is odd as well. The SQL data it is sorting is sorted by last name alphabetically. The rows would stop getting processed in midstream (like at the letter 'J' or 'L'). Running the query in the Management Studio I don't see any flaws in the records that would cause this.
There may be a way to make SQL do the work, and if there is, please enlighten me. For now I have PHP doing the work. Here is the code:
Code: Select all
$result=sqlsrv_query($dbhandle, $query, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
$numrows = sqlsrv_num_rows($result);
echo $numrows . " Warrant Entries\n\r";
//Filter results to find out which are actually open warrants
$j=0;
for ($i=0; $i<$numrows; $i+=1) {
$row = sqlsrv_fetch_array($result);
if ($row['Capias_Case_Number'] <> $lastcase) {
$lastcase = $row['Capias_Case_Number'];
if ($row['Capias_Status'] == 'C') {
$capiasinfo['court'][$j] = $row['Capias_Which_Court'];
$capiasinfo['casenumber'][$j] = $row['Capias_Case_Number'];
$capiasinfo['spn'][$j] = $row['SPN'];
$capiasinfo['lastname'][$j] = $row['LastName'];
$capiasinfo['firstname'][$j] = $row['FirstName'];
$capiasinfo['middlename'][$j] = $row['MiddleName'];
$capiasinfo['suffix'][$j] = $row['Suffix'];
$capiasinfo['zip'][$j] = $row['ZipCode'];
$capiasinfo['address'][$j] = $row['StreetAddress'];
$capiasinfo['city'][$j] = $row['City'];
$capiasinfo['state'][$j] = $row['State'];
if ($row['Capias_Which_Court'] == 'G') {
$capiasinfo['chargedesc'][$j] = $row['GS_TCA_Charge_Desc'];
}
if ($row['Capias_Which_Court'] == 'C') {
$capiasinfo['chargedesc'][$j] = $row['Crim_TCA_Charge_Desc'];
}
$j+=1;
}
}
}
sqlsrv_close($dbhandle);
//Connect to local database
$dbhandle = sqlsrv_connect($sqlserver, array("UID"=>$sqlusername,"PWD"=>$sqlpassword,"Database"=>$sqldatabase));
if( $dbhandle === false ) {
die(FormatErrors( sqlsrv_errors() ) );
}
//begin insertion procedure.... *snicker*
$numrows = count($capiasinfo['casenumber']);
//Empty out table here
$query = "DELETE FROM OpenWarrants";
$result=sqlsrv_query($dbhandle, $query);
echo $numrows . " Active Warrants Found\n\r";
for ($i=0; $i<$numrows; $i+=1) {
$lastname = $capiasinfo['lastname'][$i];
$firstname = $capiasinfo['firstname'][$i];
$middlename = $capiasinfo['middlename'][$i];
$suffix = $capiasinfo['suffix'][$i];
$casenumber = $capiasinfo['casenumber'][$i];
$court = $capiasinfo['court'][$i];
$spn = $capiasinfo['spn'][$i];
$charge = $capiasinfo['chargedesc'][$i];
$city = $capiasinfo['city'][$i];
$state = $capiasinfo['state'][$i];
$zip = $capiasinfo['zip'][$i];
$address = $capiasinfo['address'][$i];
$query = "INSERT INTO " . $sqltable . " (LastName, FirstName, MiddleName, Suffix, CaseNumber, Court, SPN, Charge, City, State, Zip, Address)
VALUES ('$lastname', '$firstname', '$middlename', '$suffix', '$casenumber', '$court', '$spn', '$charge', '$city', '$state', '$zip', '$address')";
$result=sqlsrv_query($dbhandle, $query);
}
echo "Insertion Procedure Complete\n\r";
sqlsrv_close($dbhandle);What determines an entry in the intermediate database is the 'Capias_Status' portion of the record. Each 'Capias_Case_Number' has multiple entries (either being issued, reissued, executed and so forth). The SQL statement sorts so that the newest entry comes first. It then looks to see if the last activity was "Issued". If it was it adds it to the array of active warrants. It then goes through the array and inserts the records into the intermediate database.
Now here's the weird part. I began to notice the amount of open warrants should be about 26K. And sometimes the result is between 1K and 16K... and sometimes the full 26K. I began to run the script via commandline and see what I could see.
Code: Select all
C:\Program Files\PHP>php updatewarrants.php
366045 Warrant Entries
11716 Active Warrants Found
Insertion Procedure Complete
C:\Program Files\PHP>php updatewarrants.php
366045 Warrant Entries
26459 Active Warrants Found
Insertion Procedure Complete*edit*
One more thing I forgot to mention. The place where the script drops off is odd as well. The SQL data it is sorting is sorted by last name alphabetically. The rows would stop getting processed in midstream (like at the letter 'J' or 'L'). Running the query in the Management Studio I don't see any flaws in the records that would cause this.