[whatwg] Invalid SQL statements and SYNTAX_ERR

[Timothy Hatcher]

> 4.11.3. Executing SQL statements
>
> […]
>
> 1. If the syntax of sqlStatement is not valid (except for the use  
> of ? characters in the place of literals), or the statement uses  
> features that are not supported (e.g. due to security reasons), then  
> the the method must raise a SYNTAX_ERR exception and abort these  
> steps.

I think step one needs more clarification on what an invalid statement  
is. In the current WebKit implementation a SYNTAX_ERR is being thrown  
for all queries that sqlite3_prepare doesn't return SQLITE_OK. This  
means queries against tables that don't exist will throw a SYNTAX_ERR,  
among other things that are perfectly well-formed statements.

Take this example and assume WebKitNotes doesn't exist:

db.executeSql("CREATE TABLE WebKitNotes (note TEXT, timestamp REAL)",  
[], function(result) {});
db.executeSql("INSERT INTO WebKitNotes VALUES (?, ?)", [note,  
timestamp], function(result) {});

The first query will be queued for execution. It is a valid statement.  
The second query can come in before the first has been executed. Since  
the table hasn't been created yet, executeSql will throw a SYNTAX_ERR  
because sqlite3_prepare fails to find the table.

Now, run the code again and the create table will throw SYNTAX_ERR  
because the table already exists. Users could use CREATE TABLE IF NOT  
EXISTS, but SQLite has not always had this (for instance, the version  
shipped on Mac OS 10.4.)

So a developer needs to write their code like this:

try {
	db.executeSql("CREATE TABLE WebKitNotes (note TEXT, timestamp REAL)",  
[], function(result) {
		db.executeSql("INSERT INTO WebKitNotes VALUES (?, ?)", [note,  
timestamp], function(result) {});
	});
} catch(e) {
	db.executeSql("INSERT INTO WebKitNotes VALUES (?, ?)", [note,  
timestamp], function(result) {});
}

I think SYNTAX_ERR should only be thrown for non well-formed queries  
(missing quotes, clauses, etc.), and if a table doesn't exist it  
should not throw. Once the whole queue is executed, that table might  
exist. If the table still doesn't exist the developer will have an  
errorCode and a meaningful error string instead of a generic  
SYNTAX_ERR with no context.

>

— Timothy Hatcher


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.whatwg.org/pipermail/whatwg-whatwg.org/attachments/20071019/d65cf961/attachment.htm>