Closing ODBC connection doesn't write Excel file

Question

LS,

Basically my code opens a connection to the Excel file, performs an UPDATE query, and closes the connection again.

When I execute a SELECT on the file, and output it, it does reflect the updated data, but when I close the connection and send the file to the user, it doesn't have the data in it.

It seems that the data is written after the script is fully finished, because the (remote) file is correct after the request.

Kind regards,

Matthias Vance

Edit:

// Pseudo code
$excel = new Excel($path);
$excel->query("SELECT $columnNames, '' AS newCol1, '' AS newCol2 INTO [$newFile].[newSheet] FROM [$sheet]);
$excel->close();
$excel = new Excel($newFile);
$excel->query("UPDATE [newSheet] SET newCol1 = 'x'");
$excel->close();

Answer 1

In an external datarange's properties you can select to 'Remove external data from worksheet before saving'. Is that checked or unchecked in this case? The query may work but might act as you describe, not saving the data in the file.

Answer 2

Not 100% sure of this, but I'd guess that you probably need to add a COMMIT statement.

Answer 3

I don't have Perl background, so please be kind.

Try and call Save on Workbook, before calling Excel->Close.
You should be able to get reference to the workbook, using the Excel instance that you have, I suppose.

Answer 4

Microsoft ODBC Desktop Database Drivers UPDATE Statement Limitations Updated 2009

When the Microsoft Excel driver is used, it is possible to update values, but a row cannot be deleted from a table based on a Microsoft Excel spreadsheet. As a result, the UPDATE statement is not considered officially supported by the Microsoft Excel driver. Only the INSERT statement is considered supported.

More details here: Microsoft Excel Driver Programming Considerations

Depending on your version of ODBC driver, this may or may not be still the case.

Related pages:

• PHP Manual - odbc_connect
• W3Schools - PHP Database ODBC

Answer 5

Perhaps you should consider avoiding using the "old style" ODBC API and use the XML file format. This is a good tutorial showing how to read and write data from/to and Excel worksheet, from PHP, using XML:

Read and write Excel data with PHP

If you think XML is the way to go, have a look here:

• PHP Manual - SimpleXML
• PHP - Handling XML data

You can also consider using a dedicated PHP class for writing into an Excel file:

• Spreadsheet_Excel_Writer from PEAR
• PHPExcel from CodePlex
• php-excel

(a question on this topic already exists on StackOverflow)

Answer 6

Try turning autocommit on on using odbc-autocommit

Alternatively, ensure you use complete the transaction with odbc-commit or odbc-rollback before calling odbc_close

*Note: odbc_close function will fail if there are open transactions on this connection. The connection will remain open in this case.*

e.g.

$conn = odbc_connect($odbc,$user,$password)
    or die($error);

odbc_autocommit($conn, FALSE);

odbc_exec($conn, $query1);

if (!odbc_error())
      odbc_commit($conn);
else
      odbc_rollback($conn);

odbc_close($conn);

Answer 7

Perhaps a:

$excel = null;
unset($excel);

after the last close will fix the thing (I think excel->close() doesn't free the excel instance you use for the query)

Answer 8

I am still using the workaround I noted before, first, I request the file that processes the Excel files, and then do another request to a script that outputs them.

Answer 9

I am having the same issue. I am using direct ODBC API and can create a table in Excel using DDL and insert rows into the table and re-retrieve the inserted rows successfully, even after I disconnect and reconnect to the data source using ODBC; but as soon as my application exits, all the updates and created table in Excel are gone. Still searching for Solution.

Notes: Excel ODBC driver does not support transactions and is always in auto-commit mode.

OK. Found out why that was happening. Make sure all disconnect code paths call ::SQLDisconnect before freeing connection handle, otherwise the updates disappear. Example,

    if (!m_bConnectionDead) {

        if (IsTransactionCapable() && !IsAutoCommit())
            SQLTransact(m_bCommitOnDisconnect ? SQL_COMMIT : SQL_ROLLBACK);

        SQLRETURN rc = ::SQLDisconnect(m_hdbc);

        if (!RC_SUCCESS(rc) || rc == SQL_SUCCESS_WITH_INFO)
            CZDMsg::GetInstance()->DiagSQLError(this, NULL, rc, ROUTINE);
    }

    if (m_hdbc != NULL) {

        ::SQLFreeHandle(SQL_HANDLE_DBC, m_hdbc);
        m_hdbc = NULL;
    }

    if (m_henv != NULL) {

        ::SQLFreeHandle(SQL_HANDLE_ENV, m_henv);
        m_henv = NULL;
    }
}