Search code examples
phpms-accessubuntuunixodbc

unixODBC PHP Update statement error

I'm using Ubuntu+php+unixodbc+mdbtools for working with .mdb file.
Every thing(connection+select) works good, but Insert or Update statements.
My code is something like this : 

$mdbConnection = new \PDO("odbc:mdbdriver",$user , $password , array('dbname' =>$FileName) ); 
$SelectResult = $mdbConnection->query("Select * from Zone");

$UpdateResult = $mdbConnection->query("Update Zone Set ShahrCode = 99");

$SelectResult returns correct result, but the second one throws an error that cause apache to segfault error.
I test it with isql command.Running Select statement is successful but Update is not. 

#isql mdbdriver
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>Update Zone Set ShahrCode = 99
Error at Line : syntax error near Update
syntax error near Update
Got no result for 'Update Zone Set ShahrCode = 99' command
[08001][unixODBC]Couldn't parse SQL
[ISQL]ERROR: Could not SQLExecute

Or 

SQL> Update [Zone] Set ShahrCode = 99
Error at Line : syntax error near Update
syntax error near Update
Got no result for 'Update [Zone] Set ShahrCode = 99' command
[ISQL]ERROR: Could not SQLExecute

How should I fix this error ? Thanks all

Solution

  • At last I found a solution :
    mdbtools can not write to mdb files yet.

    MDB Tools currently has read-only support for Access 97 (Jet 3) and Access 2000/2002 (Jet 4) formats. Write support is currently being worked on and the first cut is expected to be included in the 0.6 release.

    Using simple compiled java application is our solution.

    1. Create a simple java project with Jackcess library.
    2. Enable CLI params for java application and do what you want with mdb file.
      • You can even get mdb file path with CLI params.
    3. Compile java project.
    4. In PHP you can use exec('cd path/to/javaproject;java -cp . YourJavaProject "mdbfilepath" "insert|update|or select"',$output);