Re: SQL API -- statement syntax

On Wed, 31 Oct 2007, Jim Jewett wrote:
> 
> I think the SQL API should be more explicit on the requirements for a 
> SQL statement.

Yeah, there's a note in the spec saying that in due course we'll add more 
precise definitions of the SQL language dialect expected.


> In particular, should the statement (before substitution) be something 
> that a SQL engine could prepare?  Are quote marks around the question 
> marks expected and/or required?  Is the replacement value restricted to 
> be a single token?  Is the final ";" expected or required?

As far as the question mark placeholders go, it's actually well-defined -- 
the spec says that the placeholders must be used in place of literals.


> To make that more concrete, which of the following are valid?
> 
> Statement = "?"
> Arguments = ["select * from mytable;"]

Invalid, a single literal isn't valid SQL.


> Statement = "sel?table;"
> Arguments = ["lect * from my"]

Invalid, no "sel" statement in SQL.


> Statement = "select * from mytable where x = ?;"
> Arguments = ["1 and y=2"]

Valid, will select rows with x equal to "1 and y=2".
(But see comment about semicolon below.)


> Statement = "select * from mytable where x = ?;"
> Arguments = ["1;select * from yourtable;"]

Valid, will select rows with x equal to "1;select * from yourtable;".
(But see comment about semicolon below.)


> /* note the lack of ";"  */
> Statement = "select * from mytable"

Valid.
(But see comment about semicolon below.)


> Statement = "select * from mytable where x='?';"
> Arguments = ["1';select * from yourtable where y='1"]

Invalid, the number of placeholders (0) doesn't match the number of 
arguments (1).


Regarding semicolons: I don't know if they should be required or not. I'd 
like implementation experience to guide us on that. It seems like we'd 
want them to be not allowed, so that we force the issue of there only 
being one statement per call.

-- 
Ian Hickson               U+1047E                )\._.,--....,'``.    fL
http://ln.hixie.ch/       U+263A                /,   _.. \   _\  ;`._ ,.
Things that are impossible just take longer.   `._.-(,_..'--(,_..'`-.;.'

Received on Thursday, 1 November 2007 09:56:37 UTC