Re: Storing binary data in mySQL

Ok, not sure if handling this right, but, pretty much still seems to be corrupting at least the .wav files - here's both the code for data insertion, and, below that retrieval:: $i_insert_id = 0; $pdoO = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", "root", "r00t", array(PDO::FETCH_ASSOC)); $pdoO->setAttribute(PDO::ATTR_ERRMODE, $db_error_mode); $s_file_name = strval($_FILES["fil_upload"]["name"]); $s_mime_type = strval($_FILES["fil_upload"]["type"]); $i_file_size = intval($_FILES["fil_upload"]["size"]); $stream = fopen($_FILES["fil_upload"]["tmp_name"], "rb"); $b_blob = stream_get_contents($stream); fclose($stream); $b_blob64 = base64_encode($b_blob); $stmt = $pdoO->prepare("insert into tbl_uploads (v_file_name, v_mime_type, i_file_size, b_file) values (:file_name, :mime_type, :file_size, :blob);"); $stmt->bindParam(":file_name", $s_file_name); $stmt->bindParam(":mime_type", $s_mime_type); $stmt->bindParam(":file_size", $i_file_size); $stmt->bindParam(":blob", $b_blob64, PDO::PARAM_LOB); if ($stmt->execute()===true) {

    $i_insert_id = intval($pdoO->lastInsertId());

} And, it's not returning any errors, and does insert the record. I retrieve the insert_id to return it from the function being called, as form of proof that it ran through. When I then output the file's data, I am running code that just opens a pdo statement, binds output parameters, fetches the data, and then outputs header data for file name, content disposition, etc., and then just echoes the binary data, now decoded using base64_decode(), and, while the code works fine without that on other file types, but, not with the .wav files, here is the new version: $pdoO = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", "root", "r00t", array(PDO::FETCH_ASSOC)); $pdoO->setAttribute(PDO::ATTR_ERRMODE, $db_error_mode); $s_sql = "select v_file_name, v_mime_type, i_file_size, b_file from tbl_uploads where id = :i_id and b_file IS NOT NULL;"; $stmt = $pdoO->prepare($s_sql); $stmt->bindParam(":i_id", intval($_GET["id"]); if ($stmt->execute()===true) {

    $stmt->bindColumn(1, $s_file_name);
    $stmt->bindColumn(2, $s_mime_type);
    $stmt->bindColumn(3, $i_file_size);
    $stmt->bindColumn(4, $b_file, PDO::PARAM_LOB);
    $stmt->fetch(PDO::FETCH_BOUND);
    $stmt = NULL;
    $pdoO = NULL;
    header("Content-Type: " . $s_mime_type);
    header("Content-Disposition: attachment; filename=\"$s_file_name\"");
    header("Content-Length: " . strval($i_file_size));
    //ob_clean(); //not sure if need these
        //flush(); //but, on or off, makes no difference
    $b_file64 = base64_decode($b_file);
    echo $b_file64;
    exit;

} The above code is what I have taken out of my actual .php files, so, yes, there are parts above, etc. that are 'missing', but, these are the parts that I think are relevant. Anyway, unless am missing something here, think will just have to avoid .wav files completely here. One other odd thing just tested now, in case is a word .doc file, and it also works fine without base64 encoding, but, not with it, so think must be missing something, except .mp3 files still work...? Who knows...not me...<smile> Stay well Jacob Kruger Blind Biker Skype: BlindZA "Resistance is futile, but, acceptance is versatile..." On 2016-10-31 8:29 PM, Christoph M. Becker wrote:

On 27.10.2016 at 02:40, Jacob Kruger wrote:

Next question then - how did you read the file's contents into a variable before encoding it? Ask since, base64_encode requires a string parameter, and, if I read the files in using fopen($s_file_name, "rb"), then it returns a resource,

fopen() isn't supposed to read a file, but it returns a resource which can be passed to fread(), for instance, to read the contents of the file. Probably the simplest solution: <?php $stream = fopen($file_name, 'rb'); $contents = stream_get_contents($stream); fclose($stream); After that, $contents is supposed to hold the exact contents of the file.

whereas if I use file_get_contents, to read the audio clips contents in as a string, and then encode them, they still get decoded as a corrupted version, most likely due to reading in binary data the wrong way?

I'm not sure if there are any issues with file_get_contents() wrt. automatic line ending conversion – at least I've never noticed any. But you can easily check that for yourself: read the file with file_get_contents(), and compare the strlen() of the return value with the file size.