HTTP API SQL db query issues
#1
I've been developing a remote for XBMC in Flash. I started the project a few months ago, and I'm just getting back to it. It seems that something has changed in the SQL database query processing in XBMC. I am now running Atlantis Beta 2 on the Xbox. The following SQL query used to work as expected, but now it returns every record in my music db (I do use encodeURI() in Flash to convert spaces to %20, but I'm displaying it with spaces for readability):

select song.strTitle, artist.strArtist, path.strPath, song.strFileName from song, path, artist where song.strTitle LIKE '%what%' AND song.idPath=path.idPath AND song.idArtist=artist.idArtist

This should filter results by songs with "what" in the title and return the song title, artist, path, and file name. I've tested the query using SQLite Database Browser and it returns the expected results. And Like I said, this same query string used to return correct results, although it did used to randomly lock up XBMC, which is what ended up discouraging me from continuing.

Any ideas what the problem may be?
Reply
#2
is that the full sql command. you don't use a LIMIT command in it?
For python coding questions first see http://mirrors.xbmc.org/docs/python-docs/
Reply
#3
No, I don't. SQL newb here, should I use LIMIT? I'm assuming that command will limit the results so that it doesn't lock up when somebody enters something like "%" into the search field, yes? If so, that's obviously a good idea, but it doesn't explain why I'm getting every songTitle record back when searching for songs with %what% in the song title.
Reply
#4
no need to use it, there is a bug in sqlite where if you use a LIMIT and a LIKE together it bugs.

'%%what%%' the % need to be doubled i believe
For python coding questions first see http://mirrors.xbmc.org/docs/python-docs/
Reply
#5
Quote:'%%what%%' the % need to be doubled i believe

You're right, that did it. Thanks!

On the subject of using LIMIT in SQL queries, you mentioned that there is a bug is SQLite that won't allow you to use LIMIT and LIKE in the same query, does XBMC use SQLite? So there's no way to limit the results? I suppose I should probably limit the search string to a minimum of 3 characters to ensure that every record isn't passed back, but I could still see problems with search strings like "the" in very large music databases. Is there any other way to limit the SQL results quantity?
Reply
#6
theres a bug in the version xbmc uses. but please try and see what results you get. i can't remember what error it was.

if you need that functionality, you can use GLOB instead of LIKE. GLOB is case sensitive though i think.
For python coding questions first see http://mirrors.xbmc.org/docs/python-docs/
Reply

Logout Mark Read Team Forum Stats Members Help
HTTP API SQL db query issues0