Re: [sqlite] ? placeholder not allowed in LIMIT or OFFSET clause of SELECT statement?
Eric Scouten wrote: James, thanks for that update. I've filed a new ticket for this issue, #1096 (http://www.sqlite.org/cvstrac/tktview?tn=1096). I hope you don't mind that I quoted your response and implementation suggestion in the body of that ticket. There you express some concerns about performance. However, it seems to me that the chief use case for LIMIT ... OFFSET is paging results for interactive display, in which case the repeated queries are going to occur at human-speed intervals, no more than once every few seconds. It's not as if the query is going to be recompiled inside a tight loop, so I think any speed gain would be meaningless. That said, allowing placeholders there would make for slightly easier coding. I'm not sure, though, that that's a big enough concern to warrant complicating SQLite's own code.
Re: [sqlite] ? placeholder not allowed in LIMIT or OFFSET clause of SELECT statement?
James, thanks for that update. I've filed a new ticket for this issue, #1096 (http://www.sqlite.org/cvstrac/tktview?tn=1096). I hope you don't mind that I quoted your response and implementation suggestion in the body of that ticket. -Eric James Berry wrote: Eric, No, you're not missing anything. I asked this same question about a month ago. Dr. Hipp replied that argument substitution is not allowed in those cases, because it's allowed only where any of the datatypes allowed for substitution would be legal (blob, int, string, null). As limit and offset take only integers, the prepared statement parser wouldn't know ahead of time that the syntax was correct. While I understand that argument, I, too, think this is unfortunate. I wonder if this could be relaxed such the arguments would simply be coerced at execution time into integer, no matter what the actual argument type. -jdb On Feb 1, 2005, at 8:41 PM, Eric Scouten wrote: When I attempt to prepare the following statement using sqlite3_prepare: SELECT id FROM testEntity LIMIT 5 OFFSET ?; I get the following error back from SQLite: near "?": syntax error Is this really not allowed? If so, that seems a bit odd to me. This seems like a classic use-case for prepared statements (re-running the same query with different offsets). Or is there something else I'm missing? -Eric (FWIW, I'm using SQLite 3.0.8 on Mac OS X.)
Re: [sqlite] ? placeholder not allowed in LIMIT or OFFSET clause of SELECT statement?
Eric, No, you're not missing anything. I asked this same question about a month ago. Dr. Hipp replied that argument substitution is not allowed in those cases, because it's allowed only where any of the datatypes allowed for substitution would be legal (blob, int, string, null). As limit and offset take only integers, the prepared statement parser wouldn't know ahead of time that the syntax was correct. While I understand that argument, I, too, think this is unfortunate. I wonder if this could be relaxed such the arguments would simply be coerced at execution time into integer, no matter what the actual argument type. -jdb On Feb 1, 2005, at 8:41 PM, Eric Scouten wrote: When I attempt to prepare the following statement using sqlite3_prepare: SELECT id FROM testEntity LIMIT 5 OFFSET ?; I get the following error back from SQLite: near "?": syntax error Is this really not allowed? If so, that seems a bit odd to me. This seems like a classic use-case for prepared statements (re-running the same query with different offsets). Or is there something else I'm missing? -Eric (FWIW, I'm using SQLite 3.0.8 on Mac OS X.)
[sqlite] ? placeholder not allowed in LIMIT or OFFSET clause of SELECT statement?
When I attempt to prepare the following statement using sqlite3_prepare: SELECT id FROM testEntity LIMIT 5 OFFSET ?; I get the following error back from SQLite: near "?": syntax error Is this really not allowed? If so, that seems a bit odd to me. This seems like a classic use-case for prepared statements (re-running the same query with different offsets). Or is there something else I'm missing? -Eric (FWIW, I'm using SQLite 3.0.8 on Mac OS X.)
