HTTP API SQL Query - Printable Version

HTTP API SQL Query - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Development (https://forum.kodi.tv/forumdisplay.php?fid=32)
+--- Forum: Add-ons (https://forum.kodi.tv/forumdisplay.php?fid=26)
+--- Thread: HTTP API SQL Query (/showthread.php?tid=99109)

Pages: 1 2

HTTP API SQL Query - Manromen - 2011-04-11

Hi

I have some trouble with the http api.
I'm trying to get the idFile from the Video Database:

Code:
sql_data = "select movie.idFile from movie where movie.c09=%s" % str(imdb_id)

xml_data = xbmc.executehttpapi( "QueryVideoDatabase(%s)" % urllib.quote_plus( sql_data ), )

print ("XML DATA: " + xml_data)

I get this Error:

Code:
20:07:22 T:2696246592 M:1673220096   DEBUG: HttpApi Start command: QueryVideoDatabase  paras: QueryVideoDatabase; select movie.idFile from movie where movie.c09=tt1323594

20:07:22 T:2696246592 M:1673220096   ERROR: SQL: SQL error or missing database

                                            Query: select movie.idFile from movie where movie.c09=tt1323594

20:07:22 T:2696246592 M:1673220096   ERROR: GetArbitraryQuery failed

20:07:22 T:2956091392 M:1673220096  NOTICE: XML DATA: <li>Error:SQL error or missing database

                                            Query: select movie.idFile from movie where movie.c09=tt1323594

Any Idea someone Huh

- giftie - 2011-04-12

You require Quotes around a string(IMDB numbers are strings) so you would need to change your sql_data line to:

Code:
sql_data = "select movie.idFile from movie where movie.c09='%s'" % str(imdb_id)

I always have tried my SQL searches in SQLite Spy first.

Manromen Wrote:Hi

I have some trouble with the http api.
I'm trying to get the idFile from the Video Database:

Code:
sql_data = "select movie.idFile from movie where movie.c09=%s" % str(imdb_id) xml_data = xbmc.executehttpapi( "QueryVideoDatabase(%s)" % urllib.quote_plus( sql_data ), ) print ("XML DATA: " + xml_data)

I get this Error:

Code:
20:07:22 T:2696246592 M:1673220096 DEBUG: HttpApi Start command: QueryVideoDatabase paras: QueryVideoDatabase; select movie.idFile from movie where movie.c09=tt1323594 20:07:22 T:2696246592 M:1673220096 ERROR: SQL: SQL error or missing database Query: select movie.idFile from movie where movie.c09=tt1323594 20:07:22 T:2696246592 M:1673220096 ERROR: GetArbitraryQuery failed 20:07:22 T:2956091392 M:1673220096 NOTICE: XML DATA: <li>Error:SQL error or missing database Query: select movie.idFile from movie where movie.c09=tt1323594

Any Idea someone

- Manromen - 2011-04-12

Ah okay,

thank you very much :-)

- Manromen - 2011-04-12

Is there a way to update some data in the video library ?
I want to update the playcount field, but xbmc tells me that httpapi only allows select statements...

- topfs2 - 2011-04-12

Manromen Wrote:Is there a way to update some data in the video library ?
I want to update the playcount field, but xbmc tells me that httpapi only allows select statements...

It allows everything, you have full and complete access via it, also why its going away Smile

- Manromen - 2011-04-12

Ah okay,
then I'm missing something again:

Quote:sql_data = "update files set files.playcount=%s where files.idFile=%s" % (str(playcount), match[0])
xml_data = xbmc.executehttpapi( "QueryVideoDatabase(%s)" % urllib.quote_plus( sql_data ), )
print ("XML DATA: " + str(xml_data))

It tells me this:

Quote:18:25:15 T:2953850880 M:1151631360 NOTICE: MATCH: ['5986']
18:25:15 T:2696246592 M:1151631360 DEBUG: HttpApi Start command: QueryVideoDatabase paras: QueryVideoDatabase; update files set files.playcount=1 where files.idFile=5986
18:25:15 T:2696246592 M:1151631360 ERROR: SQL: MUST be select SQL!
18:25:15 T:2696246592 M:1151631360 ERROR: GetArbitraryQuery failed
18:25:15 T:2953850880 M:1151631360 NOTICE: XML DATA: <li>Error:Successful result
Query: SELECT idVersion FROM version

- topfs2 - 2011-04-12

oh, weird. perhaps I'm wrong. The webinterface XWMM uses the SQL stuff for updating afaik.

EDIT: Looking at the source there is nothing stopping access afaict. SQL isn't my strong suite so I can't say if its right or not Smile

- Manromen - 2011-04-12

Hehe thanks anyway. Smile

Do you know any timeframe when I can use json rpc for updating the playcount?

I'm using sqlite for updating the playcount, but some people ask for mysql support...

I wanted to wait till json rpc supports it, but i thought i could just use httpapi till then. Smile

- topfs2 - 2011-04-12

Please add feature requests about it, there is none afaik. The more detailed the better.

Exactly what the name of the method should be, what parameters it should contain, the expected behavior in all cases and the permissions it needs. If you provide all this it is more likely someone goes out and implementing it as the less a dev have to think about such stuff the more incentive they have (as its usually easier to just implement than to think about all that stuff).

I'd say a full on edit which can edit everything (including playcount) is the way you want to go.

- Manromen - 2011-04-12

Okay i will do it.

Thank you Smile

- spiff - 2011-04-12

query != exec. use execvideodatabase

- Manromen - 2011-04-12

I'll try this,

Thank you. Smile

Edit
That was the solution.
Thank you very much Smile

- jmarshall - 2011-04-13

Please make the feature request for JSON-RPC to do it.

That functionality WILL be going away as soon as JSON-RPC can handle what you want.

- Manromen - 2011-04-13

Yep,

I'll create the feature request later this day.

- pieh - 2011-04-13

topfs2 Wrote:It allows everything, you have full and complete access via it, also why its going away

This isn't actually like this

XBMC checks if there is 'select' string inside of query - propably to deny modification of database but this can be workarounded:

You can't execute: "UPDATE table_name SET column1=value WHERE id=X"
But You can do : "UPDATE table_name SET column1=value WHERE id=X or id=select"