Virtual Table Problem

by cyntacks » Mon, 10 Nov 2008 20:42:52 GMT


Sponsored Links
 Hi All,

I Am experiencing a strange issue regarding the joining of a SQLite3
Virtual Table with a "regular" table. MY result allways pull back 0
records, but if i run the same sql on the 2 reular tables it pulls
back all records. After hours or debugging this I am starting to
believe I may not fully comprehend what this sql does on the backend.
This returns now rows:

SELECT v.name, o.year
FROM NAMES_VIRTUAL v
JOIN SOME_OTHER o ON o.test_id = v._id

Now, the STRANGE part of all this is using the "regular" table which
was used to create the virtual table works fine and BOTH the "test_id"
and "_id' values ARE in each table:

SELECT n.name, o.year
FROM NAMES n
JOIN SOME_OTHER o ON o.test_id = n._id

What am I missing, is it possible to join a Vitual and "regular"
table, sqlite3 doc claims it is, but..

Thanks for the help,

Kevin

--~--~---------~--~----~------------~-------~--~----~



Virtual Table Problem

by cyntacks » Mon, 10 Nov 2008 22:40:33 GMT


 GOT IT!!! This was driving me nuts for HOURS!!!!

Long story short, SQLite does not validate fields, that is, you can
stick an INTEGER into a VARCHAR. My code was inserting an INTEGER into
a VARCHAR "assuming" it would be cast into a VARCHAR but in actuality
no conversion takes place, it was just being treated as an INTEGER,
thus my join failed becuase SQLite was seeing the entry as in INTEGER!

Wow, that took WAY too long to figure out...





--~--~---------~--~----~------------~-------~--~----~


Sponsored Links


Virtual Table Problem

by Mark Murphy » Mon, 10 Nov 2008 22:47:38 GMT


 


That's a SQLite-ism. Types expressed in table schemas are hints more so 
than mandates.

-- 
Mark Murphy (a Commons Guy)
 http://commonsware.com 

Android Training on the Ranch! -- Mar 16-20, 2009
 http://www.bignerdranch.com/schedule.shtml 

--~--~---------~--~----~------------~-------~--~----~