Build a file from MSSQL content

[PeteOC]

Hi All,

I'm having problems when trying to create a file from a database. The original system was developed in ASP and the developers decided that storing the files in the database was the best way forward........further down the line and a 30GB database later we're not happy and as such was to move away from this.

This is where I come in, I'm trying to move to a new system and would like to generate the files physically on the hard disk.

I've got the original filename available, the content type and the file data, when querying the server MSSQL 2000 reports the data is binary. If I output it on screen on a browser I can see gibberish which is what I expected. I've tried to write this content to a file with the filename however when I attempt to open the file it does not open.

Any ideas?

<?php

ini_set('display_errors',1);
error_reporting(E_ALL);
date_default_timezone_set("Europe/London") ;
$server = "#*$!";
$sqldatabase = "#*$!";
$user2 = "#*$!";
$password = "#*$!";
$connection = odbc_connect("#*$!", $user2, $password);

$query = "SELECT TOP 1 * FROM LogAttach ORDER BY LogAttachID DESC";

$exec = odbc_exec($connection, $query);
$result = odbc_fetch_object($exec);

echo $result->FileName;

$fp = fopen($result->FileName, 'w');
fwrite($fp, $result->FileData);
fclose($fp);

?>

[rocknbil]

Welcome about PeteOC, $result is the resource object and you probably have to fetch from that with odbc_fetch_array [us2.php.net] (or odbc_fetch_row [us2.php.net].) Try


$exec = odbc_exec($connection, $query);
$result = odbc_fetch_object($exec);
while ($row = odbc_fetch_array($result)) {
$fp = fopen($row['FileName'], 'w');
fwrite($fp, $row['FileData']);
fclose($fp);
echo 'wrote data to ' . $row['FileName'] . '<br>';
}

For 30GB of data though, you should do this at first:

$query = "SELECT TOP 1 * FROM LogAttach ORDER BY LogAttachID DESC limit 1";

(never used top 1, maybe that's the same effect.)

Personally I don't think dumping to static files is the solution, seems like you'd be better off optimizing the database somehow.

[PeteOC]

Hi,

LIMIT 1 does not work with MSSQL, it is TOP 1 to only return 1 row.

The code itself works perfectly fine, it's more of "#*$! is going on?!" when creating the file from the database.....

I cannot optimise the database as we are moving away from this system and as such need to create the physical files.

[coopster]

Be certain your permissions are correct for the directory and file to which you are attempting to write. I assume this is already so as you are are indeed writing something to the file system because you are attempting to open it again later.

Next, use the binary switch when opening the file to which you are attempting to write. See the details on the PHP fopen() [php.net] online manual page. In particular, pay close attention to the giant Note: on that page which includes this sentence:

If you do not specify the 'b' flag when working with binary files, you may experience strange problems with your data, including broken image files and strange problems with \r\n characters.