CODEWITHSUNDEEP
sqlite
bolts
bolts

Reputation: 31

Sqlite rejecting quoted value as ambiguous column name

I'm using Sqlite 3.7.11 with Windows x64 Visual Studio 2010. The example below can be run from the sqlite3.exe tool. I get the same failures from code.

Given this table:

PRAGMA foreign_keys=ON;

CREATE TABLE hashes( id INTEGER PRIMARY KEY, hash CHARACTER(4) NOT NULL UNIQUE );
CREATE TABLE sources( id INTEGER PRIMARY KEY, source VARCHAR(64) NOT NULL UNIQUE );
CREATE TABLE files( hash_id INTEGER, source_id INTEGER, filename VARCHAR(2048), extension VARCHAR(16),  
FOREIGN KEY(hash_id) REFERENCES hashes(id) 
FOREIGN KEY(source_id) REFERENCES sources(id) 
UNIQUE( hash_id, source_id, filename ) );

And this initial data:

INSERT INTO hashes VALUES( ?, "abcd" );
INSERT INTO sources VALUES( ?, "mysource" );

I'm adding rows like this. The files tables is referencing the hash and source tables per the constraints above.

INSERT INTO files ( hash_id, source_id, filename, extension ) 
SELECT hashes.id, sources.id, "filename.ext", "ext" 
FROM hashes, sources 
WHERE hashes.hash = "abcd" AND sources.source = "mysource";

Everything works fine until 'id' shows up in either the filename or extension field. This doesn't make any sense -- it's quoted. Both of these inserts will fail:

INSERT INTO files ( hash_id, source_id, filename, extension ) 
SELECT hashes.id, sources.id, "id", "" 
FROM hashes, sources 
WHERE hashes.hash = "abcd" AND sources.source = "mysource";

INSERT INTO files ( hash_id, source_id, filename, extension ) 
SELECT hashes.id, sources.id, "filename.id", "id" 
FROM hashes, sources 
WHERE hashes.hash = "abcd" AND sources.source = "mysource";

Error: ambiguous column name: id

Do I need to further escape the quoted value somehow?

Upvotes: 0

Views: 781

Answers (1)

bolts
bolts

Reputation: 31

Well ... that was a rookie mistake. This works once I used single quotes to indicate string literals:

INSERT INTO files ( hash_id, source_id, filename, extension ) 
SELECT hashes.id, sources.id, "id", "" 
FROM hashes, sources 
WHERE hashes.hash = "abcd" AND sources.source = "mysource";

INSERT INTO files ( hash_id, source_id, filename, extension ) 
SELECT hashes.id, sources.id, 'filename.id', 'id' 
FROM hashes, sources 
WHERE hashes.hash = "abcd" AND sources.source = "mysource";

Upvotes: 2

Related Questions