Need error message to indicate reserved words in SQLite

[cmbarton]

I just tried v.net.centrality so I could present it to class tomorrow. Apparently it is broken.

I got the error:

v.net.centrality input=roads_nodes@spatialtech2012 alayer=1 nlayer=2 output=roads_cent degree=degree closeness=close betweenness=between eigenvector=eigenvect DBMI-SQLite driver error: Error in sqlite3_prepare(): near "between": syntax error ERROR: Unable to create table: 'create table roads_cent(cat integer, degree double precision, close double precision, between double precision, eigenvect double precision)' (Thu Oct 4 22:10:04 2012) Command finished (0 sec)

[martinl]

Replying to cmbarton:

near "between": syntax error ERROR: Unable to create table: 'create table roads_cent(cat integer, degree double precision,

'between' is a reserved word for SQLite and other DB backends.

ps: please could you use preformatted text in your reports, otherwise it's hard to read - WikiFormatting

[marisn]

There is an old request to quote column names that would allow to use SQL reserved words as column names. See more: #578

Second option would be to enhance parser to discard SQL reserved words as valid column parameters. AFAIK SQL-92 reserved word list isn't so long. Yes, I know, somebody will scream that PG, MySQL etc. have extra reserved words etc., still even blocking only the most common ones would make day for a user like Michal much better as he would be able to finish his task instantly instead of writing a bug report.

[cmbarton]

I relooked at the error and I'm not as unobservant as I initially assumed. There is nothing to say that "between" is an SQLite reserved word. So thanks for the clue about what is wrong.

This is not a bug. But maybe an enhancement request for better error messages here. Changing the field name to "betw" makes it work fine. I've changed the summary too. I don't know if the component should stay vector or shift to something else.

Michael

[neteler]

For SQL reserved word collisions, see also #578

[martinl]

Milestone renamed

[neteler]

Ticket retargeted after milestone closed

[martinl]

All enhancement tickets should be assigned to 7.6 milestone.

[neteler]

See also #3563

[cmbarton]

Not sure where this is at (maybe nowhere yet). I very much like the idea suggested in #3563 to add an underscore and give a warning. This seems the least disruptive and provides information for future table design.

[martinl]

Ticket retargeted after milestone closed

[martinl]

Ticket retargeted after milestone closed