|
Is the LIMIT statement supported? I'm trying to use the FuelPHP ORM library. It's generating a query that works with MySQL but doesn't seem to with SQLAnywhere. I've traced it down to the query below. Working MySQL example: SELECT * FROM SQLAnywhere example that doesn't work: SELECT * FROM EM.Users ORDER BY UserId ASC LIMIT 1 Similar SQLAnywhere example that does work (by removing LIMIT): SELECT * FROM EM.Users ORDER BY UserId ASC |
|
LIMIT is available with v12 but has to be "allowed" explicitly. From the docs:
SET OPTION PUBLIC reserved_keywords = 'LIMIT'; EDIT: As Reimer has noted, the doc sample is missing a period, it should read SET OPTION PUBLIC.reserved_keywords = 'LIMIT'; Thanks. Where or how is this setting applied?
(06 Dec '12, 10:30)
MMacdonald
Replies hidden
Use DBISQL (Interactive SQL) to connect as DBA to the database and run that statement. It is a public option, so it applies permantently to all further connections.
(06 Dec '12, 10:39)
Volker Barth
Thanks. I get "Syntax error near 'reserved_keywords' at line 1, column 19."
(06 Dec '12, 10:43)
MMacdonald
Replies hidden
What SQLA version and build are you using (e.g. 12.0.1.3797)
(06 Dec '12, 11:10)
Reimer Pods
12.0.1.3457
(06 Dec '12, 11:13)
MMacdonald
I had to double check twice until I saw the reason you've got an error: the period between PUBLIC and reserved_keywords was missing. SET OPTION PUBLIC.reserved_keywords = 'LIMIT';
(06 Dec '12, 11:38)
Reimer Pods
Replies hidden
Thanks, this worked.
(06 Dec '12, 11:53)
MMacdonald
Oops, I had just copied from the docs, and there's no period - I've made a DCX comment:)
(06 Dec '12, 12:02)
Volker Barth
|
|
I believe the equivalent SQL/Anywhere syntax is SELECT TOP 1 * FROM users ORDER BY id ASC 1
Just to clarify: The TOP / FIRST [START AT] clause is SQL Anywhere'c "classical" row limitation clause, available since v6 or v7, methinks. AFAIK it's not SQL standard, however supported by ASE and MS SQL Server, too. v12 has added the LIMIT [OFFSET] clause, presumably for MySQL compatibility.
(10 Dec '12, 04:09)
Volker Barth
|
