Search code examples
phpms-accesspdoodbcmdbtools

Why is my inner join throwing syntax errors?

Why is my sql string throwing syntax errors? Answer: MDBTools does not support JOIN or ORDER BY or other SEE http://github.com/brianb/mdbtools/blob/master/src/sql/lexer.l

This code does not work (syntax error near .):

$db = "access.mdb";
$conn = new PDO("odbc:DRIVER=MDBTools; DBQ=$db; Uid=; Pwd=;");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql =        'SELECT "Order No", "Ship To File"."Cust Name" ';
$sql = $sql . 'FROM "Order File" ';
$sql = $sql . 'INNER JOIN "Ship To File" ';
$sql = $sql . 'ON "Order File"."Customer No" = "Ship To File"."Cust No" ';
$sql = $sql . 'WHERE "Order No"=\'RV2002-001\'';

Longer/Other info:
Pretty certain it's the "Ship To File"."Cust Name" . throwing the near . error because omitting "Ship To File"."Cust Name" changes the error to near INNER, which makes me think the issue is a namespace problem for fields and tables with spaces.

Replacing double quotes with ticks for tables and fields with spaces will break even the simplest queries (syntax error near `). INNER JOIN query seems to work fine in MS-Access but with brackets instead of double quotes.

This code does not work either (syntax error at `):

$db = "access.mdb";
$conn = new PDO("odbc:DRIVER=MDBTools; DBQ=$db; Uid=; Pwd=;");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql =        "SELECT `Order No` ";
$sql = $sql . "FROM `Order File` ";

This code gives me a record/result:

$db = "access.mdb";
$conn = new PDO("odbc:DRIVER=MDBTools; DBQ=$db; Uid=; Pwd=;");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql =        'SELECT "Order No" ';
$sql = $sql . 'FROM "Order File" ';
$sql = $sql . 'WHERE "Order No"=\'RV2002-001\'';

Windows Subsystem for Linux, PHP Version 5.5.9-1ubuntu4.22, apache2, MDBTools, can't change database tables or fields (accessing database via symbolic link at the moment while others are still using MS Access for their interface).

"Ship To File"."Cust No" is type (autonumber) "Order File."Customer No" is type(number)

Comments or Suggestions?

Solution

  • MDBTools does not support JOIN or ORDER BY. SEE http://github.com/brianb/mdbtools/blob/master/src/sql/lexer.l