2

In CodeIgniter 2, I'm generating a CSV file from fetching data from my Oracle Database (volume of data can be a less than 10 lines to hundreds of thousands of lines, this is why I "unset" each $line) using the following code :

        $conn = $this->db;

        $stid = oci_parse($conn->conn_id, $sql);
        oci_execute($stid);

        // Header extraction
        $headers = "";
        $values = "";

        $row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS );

        foreach($row as $key => $value) {
            $headers = $headers.$key.';';
            $values = $values.$value.';';
        }
        // Remove of last comma and adding in a line break
        $headers = substr($headers,0,-1). "\r\n";
        $values = substr($values,0,-1). "\r\n";
        $data = $headers . $values;

        $values = "";

        // Parsing all data to concatenate the values
        while (($row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) != false) {
            $line = "";
            foreach ($row as $key => $value) {
                $line .= $value . ';';
            }
            $line = substr($line, 0, -1) . "\n";
            $data = $data . $line;
            // Freeing memory for the line
            unset($line);
        }
        return $data;

Using this piece code, my CSV file is properly generated except for one thing.

When I'm exporting just a small number of lines, everything is fine. I have all the values, for ALL lines, comma seperated. However, when I'm exporting a few hundred or thousand of lines, the LAST value from the LAST line is always truncated by 5 characters (data in the database is OK).

HEADER1;HEADER2;HEADER3;HEADER4;HEADER5
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
[.......]
1-XXXXXX;F1;IDX1;ERR_IDX_CAX_0001
1-XXXXXX;F1;IDX1;ERR_IDX_CAX

I thought maybe it was some cache limitation or something, but it happens after a few hundred lines exported and wether it's just a few hundred or several hundred thousand lines.

I can't figure this one out ...

Can anybody help ?

Thanks in advance.

Kavatah
  • 43
  • 5
  • 1
    I'd suggest using PHP's `fputcsv()` function rather than outputting it yourself with that foreach loop. – Simba Sep 09 '15 at 15:00
  • Also, given the fairly simple dump you're doing, isn't it possible to use Oracle to output the CSV directly? (maybe [like this](http://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer)? I don't know Oracle that well, but it's the sort of thing I'd expect to be able to do with a decent DB engine these days) – Simba Sep 09 '15 at 15:04
  • Exporting Query result into CSV format from SQL Dev or TOAD or any Oracle Client is easy. Here I need to export from a web browser using PHP interface. I'm not sure how to implement fputcsv() in my code as I need to unset each line to avoid have my Cache full. – Kavatah Sep 09 '15 at 15:32
  • `fputcsv()` will output to a file. However, if you're struggling with memory space (which I can see you would be doing the above), consider exporting the CSV to a temp file on disk; then all PHP has to do is `readfile()` to send it to the browser and then delete it. If you can get Oracle to output the CSV file for itself, then PHP's memory limits never need to be worried about. And it'll be a *lot* quicker. – Simba Sep 09 '15 at 15:42
  • Actually, using Codeigniter built in csv functions and file generator, I managed to generate my csv file on the server. After I have done this, I forced the download of the generated file. This I noticed the following : my file on the server is complete... Last value of last line is not truncated. However, the file that I download as that truncated last line. So my above code isn't the issue... I tried with Firefox and chrome and I have the same behavior... Very strange... – Kavatah Sep 09 '15 at 19:42
  • Okay, so it sounds like the problem is actually something else entirely from what you originally thought it was. At this point, I'd advise abandoning this question and starting a new one describing the problem afresh. It would be worth linking back to this question in the new one to help give people some background, but it's a sufficiently different problem now that most of the original details you gave won't be particularly useful any more. – Simba Sep 10 '15 at 08:05
  • Indeed :) thanks anyway for your help and quick replies :) – Kavatah Sep 10 '15 at 08:53

1 Answers1

2

Original issue described in this post not related to the code.

The issue comes from the Header Content Length passed by CodeIgniter force_download() function (from CI download helper).

header("Content-Length: ".strlen($data));

strlen($data) doesn't return (in my case) the correct length of data and my browser then only downloads the amount of data that was passed in the header, hence truncating a few characters at the end.

Not finding anything on this "issue", I decided to comment out the header Content length line

Kavatah
  • 43
  • 5