query using odbc on access db error: Couldn't parse SQL

[raphael75]

I'm using Ubuntu 24.04, PHP 8.3, and an Access database with ODBC. I have this:

$user = '';
$password = '';

$conn = odbc_connect('my_db', $user, $password);
echo 'conn: ';var_dump($conn);

$sql = 'select id, name from Objects limit 3';
$q = odbc_prepare($conn, $sql);
$success = odbc_execute($q, array());
echo 'success: ';var_dump($success);

$res = odbc_exec($conn, $sql);
echo 'res: ';var_dump($res);

while($row = odbc_fetch_array($res)){
	var_dump($row);
}

exit;

which works perfectly. However, if I try any of the following:

$sql = 'select * from Objects limit 3';

$sql = 'select objects.id, objects.name, sets.name from Objects inner join sets on sets.id = objects.setid limit 3';

$sql = 'select `objects`.`id`, `objects`.`name`, `sets`.`name` from `Objects` inner join `sets` on `sets`.`id` = `objects`.`setid` limit 3';

$sql = 'select [objects].[id], [objects].[name], [sets].[name] from [Objects] inner join [sets] on [sets].[id] = [objects].[setid] limit 3';

I get this error:

Error at Line : syntax error
syntax error
Got no result for 'select objects.id, objects.name, sets.name from Objects inner join sets on sets.id = objects.setid limit 3' command
PHP Warning:  odbc_exec(): SQL error: Couldn't parse SQL
, SQL state  in SQLExecDirect

Each attempt results in the "Couldn't parse SQL" error. I don't know how to format queries that have joins, "*", etc. for Access/ODBC/etc. to be happy.

Note: I am able to run all of these queries in DBeaver and they all return correctly with no errors.

[requinix]

I don't think backticks are the right quoting style, but brackets should have been. Or maybe not. Try single and double quotes too.

Been a while but I think the LIMIT could be a problem too. Does SELECT TOP 3... instead work?

[gizmola]

Microsoft access does not support Limit. It has a "TOP" keyword that does the same thing.   So something like:

SELECT TOP 3 * from Objects.

 

It also does not support backtics.  That is a MySQL feature.  You use the square brackets, as you did in the final example.  However, like MySQL's backtics you only need brackets if you have a non standard table or column name with perhaps spaces in it, or used a SQL keyword for the name.  

So your problem is most likely the use of LIMIT, which is not a keyword supported by Access.  

[raphael75]

I don't think the "limit 3" is an issue, because this query does work:

select id, name, type from Objects limit 3

I tried both single- and double-quotes and neither worked.

[gizmola]

2 minutes ago, raphael75 said:

I don't think the "limit 3" is an issue, because this query does work:

select id, name, type from Objects limit 3

I tried both single- and double-quotes and neither worked.

What do you mean it works? 

It's not even in the list of Access SQL keywords:  https://support.microsoft.com/en-us/office/sql-reserved-words-b899948b-0e1c-4b56-9622-a03f8f07cfc8

I also never mentioned single or double quotes.   You can use either when you are working with string constants, but you aren't doing that.  You either use [name] or the name by itself for table and columns.  So table.column or [table].[column].  

[requinix]

3 hours ago, gizmola said:

I also never mentioned single or double quotes.

I did. I can never remember which systems allow for quoting identifiers with apostrophes or quotation marks.

[gizmola]

afaik, you can intermix them as you need, but there is no quoting used around table/fieldnames other than the [name] syntax.