I am attempting to walk a directory (using PHP) and get the modified and created time of the files, updating or inserting as needed to an SQL Server 2003 table.
I am using PHP's DirectoryIterator in order to do this, and it works flawlessly if I only use the object's getCTime() function. If I move to include or instead use the objects getMTime() method, SQL Server throws out an error at me on insert/update:
22003 : [Microsoft][SQL Server Native Client 10.0][SQL Server]Arithmetic overflow error converting expression to data type smalldatetime.
This error ONLY occurs when using the object getMTime() method. Testdir has two files in it, solely for testing obviously.
I have searched for help with this via Google, and especially on Stackoverflow (One, Two, Three). Those StackOverflow questions are for a SELECT statement however, and say to use CONVERT function of SQL. The only semi-relevant item I have found was a blog post which says to ensure the SQL user has the default language set appropriately. In my user case, it is set properly to 'English'.
Here is the entire function and subsequent call:
function fillArrayWithFileNodes( DirectoryIterator $dir )
{
$data = array();
require('connecttodbfile');
clearstatcache();
foreach ( $dir as $node )
{
if ( $node->isDir() && !$node->isDot() )
{
$data[$node->getFilename()] = fillArrayWithFileNodes( new DirectoryIterator( $node->getPathname() ) );
}
else if ( $node->isFile() )
{
$filename = $node->getFilename();
//Comment this out below and use CTime, Update works!
//$file_time = date('Y-d-m H:i:s', $node->getMTime());
//Comment this out and use MTime, Update DOES NOT WORK!
$file_time = date('Y-d-m H:i:s', $node->getCTime());
//Debug here, make sure I'm getting expected values
echo sprintf("Mtime (%s): %s - %s<br />Ctime (%s): %s - %s<br /> <br />", gettype($node->getMTime()), $node->getMTime(), gettype($node->getMTime()), date('Y-d-m H:i:s', $node->getMTime()), $node->getCTime(), date('Y-d-m H:i:s', $node->getCTime()));
$data[] = array('time'=>$file_time);
try {
$sQuery = <<<EOD
BEGIN TRAN
UPDATE files SET file_time=:ftime WHERE file_name=:fname
IF @@ROWCOUNT = 0 and @@ERROR = 0
BEGIN
INSERT INTO files (file_name,file_time) VALUES (:f_name, :f_time)
END
COMMIT TRAN
EOD;
$statm = $conn->prepare($sQuery);
//PDO doesn't like it if we name repeat variables the same. So even if you use the same variable more than once in your query, PDO says you need the exact number of variables in the execute array as is in your statement, all unique names.
$statm->execute(array(':ftime'=> $file_time, ':fname'=>$filename,':f_time'=> $file_time, ':f_name'=>$filename));
} catch(PDOException $err) {//SQL got mad at us. Figure out why.
echo $statm->errorCode() . " : ";
$error = $statm->errorInfo();
print_r( $error[2]);
exit(1);
}
}
}
return $data;
}
$fileData = fillArrayWithFileNodes( new DirectoryIterator( 'C:\testdir' ) );
echo "<pre>";
print_r($fileData);
echo "</pre>";
When this code is run with mtime as the $file_time (through web browser) I get the following output:
Mtime (integer): 1311599068 - 2011-25-07 08:04:28
Ctime (integer): 1346941012 - 2012-06-09 09:16:52
22003 : [Microsoft][SQL Server Native Client 10.0][SQL Server]Arithmetic overflow error converting expression to data type smalldatetime.
If I change out $file_time to use getCTime() instead, the output completes with no errors:
Mtime (integer): 1311599068 - 2011-25-07 08:04:28
Ctime (integer): 1346941012 - 2012-06-09 09:16:52
Mtime (integer): 1265983000 - 2010-12-02 07:56:40
Ctime (integer): 1346941018 - 2012-06-09 09:16:58
Array (
[0] => Array
(
[time] => 2012-06-09 09:16:52
)
[1] => Array
(
[time] => 2012-06-09 09:16:58
)
)
Even if that method specified in the StackOverflow question (linked above) work to have both cases of mtime and ctime upload properly, my question is still Why does using ctime work without any modification but mtime does not?
