Having a hard time querying a mySQL db

[PsY4]

Using 1.0.1-beta2 here ! I just wanna be able to ask informations about my Kodi movie list : how many movies, is a movie present, ... My Kodi mySQL database is accessed using the homeassistant-mysql_query integration,

In my initial prompt, I've added : If I ask you about my Kodi movie collection you can call the query_kodi_movies_list function.

In functions, I've added :

- spec:
    name: query_kodi_movies_list
    description: >-
      Use this function to query the movie list from Kodi mySql database
      Example:
        Question: How many movies are in Kodi?
        Answer: SELECT COUNT(m.c00) AS count FROM movie m 
        Question: Is Avatar in the Kodi movie list?
        Answer: SELECT m.c00 AS movie_name FROM movie m WHERE m.c00 LIKE "%Avatar%"
    parameters:
      type: object
      properties:
        query:
          type: string
          description: A fully formed SQL query.
  function:
    type: script
    sequence:
    - service: mysql_query.query
      data: 
        query: "{{ query }}"

In Home Assistant, I can get results just right :

service: mysql_query.query
data: 
  query: SELECT m.c00 as movie_name FROM movie m WHERE m.c00 LIKE "%A Star Is Born%"

Here is the response of the service call

result:
  - movie_name: A Star Is Born

But when I ask Assist about my Kodi movies, il fails with "Unexpected error during intent recognition"

However it's welle aware of the function :

I could use any idea, any help on how to debug this, or where to start looking for more specific error messages ! Thx a lot!

[jekalmin]

Can you add loggings and see how function is called?

Or is there error logs?

Also, if it continues to fail, an alternative way is to create a function for each query.

- spec:
    name: get_kodi_movie_count
    description: Get kodie movie count
    parameters:
      type: object
      properties:
        dummy:
          type: string
          description: Nothing
  function:
    type: script
    sequence:
    - service: mysql_query.query
      data: 
        query: "SELECT COUNT(m.c00) AS count FROM movie m"

- spec:
    name: search_kodi_movie
    description: Search movie from kodi
    parameters:
      type: object
      properties:
        title:
          type: string
          description: The title of movie
  function:
    type: script
    sequence:
    - service: mysql_query.query
      data: 
        query: "SELECT m.c00 AS movie_name FROM movie m WHERE m.c00 LIKE "%{{title}}%"

[PsY4]

OK, that helped a lot ! Now it's working great !

When I added loggings, I had a message Script requires 'response_variable' for response data for service call mysql_query.query...

As i saw in the calendar example, i just added response_variable: _function_result in the function service, and it worked immediately !

\o/

Here are the final form of the functions :

- spec:
    name: search_kodi_movie
    description: Use this function to search a movie from Kodi
    parameters:
      type: object
      properties:
        title:
          type: string
          description: The title of movie
  function:
    type: script
    sequence:
    - service: mysql_query.query
      data: 
        query: SELECT m.c00 AS movie_name FROM movie m WHERE m.c00 LIKE "%{{title}}%"
      response_variable: _function_result

- spec:
    name: get_kodi_movie_count
    description: Use this function to get Kodi movie count
    parameters:
      type: object
      properties:
        dummy:
          type: string
          description: Nothing
  function:
    type: script
    sequence:
    - service: mysql_query.query
      data: 
        query: SELECT COUNT(m.c00) AS movie_count FROM movie m
      response_variable: _function_result