1

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?

Frank
  • 31
  • 8
  • If i use ticks instead of double quotes for field names and table names that have spaces I get syntax errors near the first tick. Still get the tick syntax error if i use the ticks and double quotes instead of single quotes for the general string assignment. – Frank Oct 18 '17 at 19:26
  • 1
    THIS IS NOT MYSQL. – Frank Oct 18 '17 at 19:32
  • The syntax error is being thrown by your sql database. Show your full error message. Or try brackets around your column and table names: https://stackoverflow.com/questions/12696259/mdb-tools-sql-query-table-name-space-separated – aynber Oct 18 '17 at 19:45
  • root:/var/www/html/tops# php order.php Error at Line : syntax error near . syntax error near . Got no result for 'SELECT "Order No", "Ship To File"."Cust Name" FROM "Order File" INNER JOIN "Ship To File" ON "Order File"."Customer No" = "Ship To File"."Cust No" WHERE "Order No"='RV2002-001'' command Segmentation fault (core dumped) – Frank Oct 18 '17 at 19:53
  • Definitely try the brackets instead of double quotes, and see if that helps. – aynber Oct 18 '17 at 19:53
  • used brakets before, tried them again and syntax error near [ instead of . – Frank Oct 18 '17 at 19:57
  • For database.table names, you'll need to put the bracket around the whole thing, not each part. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58572 – aynber Oct 18 '17 at 19:57
  • `$sql = 'SELECT [Order No] '; $sql = $sql . 'FROM [Order File] '; $sql = $sql . 'WHERE [Order No]=\'RV2002-001\''; Error at Line : syntax error near [ syntax error near [ Got no result for 'SELECT [Order No] FROM [Order File] ' command Segmentation fault (core dumped)` – Frank Oct 18 '17 at 20:00
  • side note, can't seem to get line breaks in comments for `code` – Frank Oct 18 '17 at 20:02
  • Because comments aren't meant for code. I'm stumped. I'm not all that familiar with Access or MDB, so couldn't tell you beyond the links I found via Google. – aynber Oct 18 '17 at 20:03
  • Yeah, i've done my homework in google/stackoverflow, but still can't make sense of the syntax errors... seg fault because of trying to display records without any results... – Frank Oct 18 '17 at 20:05
  • Perhaps this is a more relevant link: https://stackoverflow.com/questions/9719869/what-is-the-difference-between-the-backtick-and-the-square-bracket-in-sql-statem If you can't manage to quote or bracket your tables/columns/keywords after modifying the `sql_mode` (I don't have experience with MS Access), perhaps you will have to (warn your colleagues that you are going to turn their IT world upside down and) spend the time to write the aforementioned values in a way that doesn't require quoting... no spaces, no keywords as tables/columns, etc. – mickmackusa Oct 19 '17 at 04:36
  • Found a real answer at https://github.com/brianb/mdbtools/blob/master/src/sql/lexer.l MDBTools does not support JOIN or ORDER BY, etc. – Frank Oct 19 '17 at 11:17
  • 1
    You can add it as your own answer now – Your Common Sense Oct 19 '17 at 11:24

1 Answers1

1

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

Frank
  • 31
  • 8