[SQLiteJDBC] SQLException: no such column

[Markus Fuchs]

Hi,

I've got a problem when I try to execute the following statement:

PreparedStatement stmt = conn
		.prepareStatement("SELECT tags.tag "
				+ "FROM ( tags, has_tag ) WHERE has_tag.content_id=? "
				+ "AND has_tag.tag_id=tags.tag_id");
stmt.setLong(1, id);


The following exception is thrown which seems quite strange to me:

java.sql.SQLException: no such column: tags.tag
	at org.sqlite.DB.throwex(DB.java:288)
	at org.sqlite.NativeDB.prepare(Native Method)
	at org.sqlite.DB.prepare(DB.java:114)
	at org.sqlite.PrepStmt.<init>(PrepStmt.java:37)
	at org.sqlite.Conn.prepareStatement(Conn.java:231)
	at org.sqlite.Conn.prepareStatement(Conn.java:224)
	at org.sqlite.Conn.prepareStatement(Conn.java:213)
	at de.fxj.knofi.db.KDatabase.getTagsByEntry(KDatabase.java:183)
	at de.fxj.knofi.db.KDatabase.search(KDatabase.java:219)
	at de.fxj.knofi.gui.SearchDialog.search(SearchDialog.java:159)
	at de.fxj.knofi.gui.SearchDialog.access$0(SearchDialog.java:144)
	at de.fxj.knofi.gui.SearchDialog$2.insertUpdate(SearchDialog.java:121)
	at javax.swing.text.AbstractDocument.fireInsertUpdate(AbstractDocument.java:185)
	at javax.swing.text.AbstractDocument.handleInsertString(AbstractDocument.java:734)
	at javax.swing.text.AbstractDocument.insertString(AbstractDocument.java:693)
	at javax.swing.text.PlainDocument.insertString(PlainDocument.java:114)
	at javax.swing.text.AbstractDocument.replace(AbstractDocument.java:655)
	at javax.swing.text.JTextComponent.replaceSelection(JTextComponent.java:1351)
	at javax.swing.text.DefaultEditorKit$DefaultKeyTypedAction.actionPerformed(DefaultEditorKit.java:859)
	at javax.swing.SwingUtilities.notifyAction(SwingUtilities.java:1633)
	at javax.swing.JComponent.processKeyBinding(JComponent.java:2839)
	at javax.swing.JComponent.processKeyBindings(JComponent.java:2874)
	at javax.swing.JComponent.processKeyEvent(JComponent.java:2802)
	at java.awt.Component.processEvent(Component.java:6040)
	at java.awt.Container.processEvent(Container.java:2041)
	at java.awt.Component.dispatchEventImpl(Component.java:4630)
	at java.awt.Container.dispatchEventImpl(Container.java:2099)
	at java.awt.Component.dispatchEvent(Component.java:4460)
	at java.awt.KeyboardFocusManager.redispatchEvent(KeyboardFocusManager.java:1850)
	at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(DefaultKeyboardFocusManager.java:712)
	at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(DefaultKeyboardFocusManager.java:990)
	at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(DefaultKeyboardFocusManager.java:855)
	at java.awt.DefaultKeyboardFocusManager.dispatchEvent(DefaultKeyboardFocusManager.java:676)
	at java.awt.Component.dispatchEventImpl(Component.java:4502)
	at java.awt.Container.dispatchEventImpl(Container.java:2099)
	at java.awt.Window.dispatchEventImpl(Window.java:2475)
	at java.awt.Component.dispatchEvent(Component.java:4460)
	at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
	at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
	at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
	at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
	at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

Here is  a snippet of my database scheme:

CREATE TABLE tags ( tag_id INTEGER, tag UNIQUE NOT NULL, PRIMARY
KEY(tag_id ASC));
CREATE TABLE has_tag ( content_id, tag_id, FOREIGN KEY (content_id)
REFERENCES content ( content_id )  ON DELETE CASCADE ON UPDATE
CASCADE,FOREIGN KEY (tag_id) REFERENCES tags ( tag_id )  ON DELETE
CASCADE ON UPDATE CASCADE );

(I know FOREIGN KEY CONSTRAINTs are ignored)



If I execute the same statement via sqlite3 CLI, here with a fixed
value 3, it gives me the correct result
SELECT ( tags.tag ) FROM ( tags, has_tag) WHERE has_tag.content_id=3
and has_tag.tag_id=tags.tag_id

I really don't get it if it is my fault.
In my desperation I also tested the following:

PreparedStatement stmt = conn
		.prepareStatement("SELECT tag "
				+ "FROM ( tags, has_tag ) WHERE content_id=? "
				+ "AND has_tag.tag_id=tags.tag_id");

which complains about the unknown column "tags.tag_id", too.

Maybe somebody can help,

Best wishes,
Markus