CosmosDBInput binding of Azure Function is not using path Variable defined in HttpTrigger

Question

I have created a function with Kotlin using azure-functions-kotlin-archetype. I have created a Http Trigger and a cosmos input binding to read data from the cosmos. I have mentioned sql query also to fetch the data. I want to pass path variable to the http trigger which should be used as parameter in the query. As per Microsoft documentation I have defined the path parameter accordingly. https://learn.microsoft.com/en-us/azure/azure-functions/functions-bindings-cosmosdb-v2-input?tabs=java#http-trigger-get-multiple-docs-from-route-data-using-sqlquery-java

But I am getting blank response, means no data is fetched. If I hard code the parameter in the Sql query, I able to fetch the data. Can any one please tell me the issue here. The Function Code is as below

@FunctionName("GetData")
    fun run(
            @HttpTrigger(
                    name = "req",
                    methods = [HttpMethod.GET],
                    authLevel = AuthorizationLevel.ANONYMOUS,
            route = "/api/getItems/{id}/{qnt}"
            )
            request: HttpRequestMessage<Optional<String>>,
            @CosmosDBInput(
                name = "cosmosdb",
                databaseName = "item-db",
                collectionName = "item",
                sqlQuery = "SELECT * FROM ITEM n where n.id= {id} "
                             +"and n.qnt = {qnt}",
              connectionStringSetting = "Cosmos_DB_Connection_String"
            )
            rs: Array<String>,
            context: ExecutionContext): HttpResponseMessage {



            return request
                    .createResponseBuilder(HttpStatus.OK)
                    .body(rs)
                    .build()
        }

}

Hi, may I know if you still use `route` when you request function with hard code sqlQuery ? — Hury Shen, Aug 11 '20 at 05:55
@HuryShen Yes, I use route always. No change except instead of taking value from route, I hard coded it. — shoubhgh, Aug 11 '20 at 06:05
Ok, I will test it in my side. Could you please have a try to remove the `/` before `api/getItems/{id}/{qnt}` in your `route` ? — Hury Shen, Aug 11 '20 at 06:11
@HuryShen Thats a mistake in the typing, the / infront of api is not present in the actual code. — shoubhgh, Aug 11 '20 at 06:21
Could you please provide a sample of the value of `id` and `qnt`, also please provide a sample of `sqlQuery` when you request it hard code. — Hury Shen, Aug 11 '20 at 06:21
Sample Query: SELECT * FROM ITEM n where n.id= {'ID1'} " +"and n.qnt = {1} — shoubhgh, Aug 11 '20 at 06:23

Hury Shen · Accepted Answer · 2020-08-13T02:29:00.713

0

According to the sample query you provided: SELECT * FROM ITEM n where n.id= {'ID1'} " +"and n.qnt = {1}, it seems the id is string and qnt is a number. When we get the parameters from route, it will treat the parameters as string. So if you use the parameters from route in sqlQuery directly, it will select the data like this sql: SELECT * FROM ITEM n where n.id = 'ID1' and n.qnt = '1'. So no data is fetched.

You need to get the parameter qnt as a number but not as string from route, so please use route = "api/getItems/{id}/{qnt:int}".

===========================Update=========================

Add this update for other communities reference:

Use the method StringToNumber() in sql query like sqlQuery = "SELECT * FROM ITEM n where n.id= {id} " + "and n.qnt = StringToNumber({qnt})".

edited Aug 13 '20 at 02:29

answered Aug 11 '20 at 07:50

Hury Shen

14,948
1
9
18

I tried mentioning data type as well, but did not work. Is it working for you using Java or Kotlin – shoubhgh Aug 11 '20 at 09:31
1

@shoubhgh I did not test it with java. If it doesn't work in java, could you please have a try to use `sqlQuery = "SELECT * FROM ITEM n where n.id= {id} " + "and n.qnt = StringToNumber({qnt})"`. But I'm not sure if the method `StringToNumber()` will work in code, I just test this method in Data explorer of cosmos db. – Hury Shen Aug 11 '20 at 09:39
@shoubhgh According to my test, I think the problem was caused by the type of `qnt`. But in your side, I think you can remove `and n.qnt = {qnt}` in your `sqlQuery` for a test to make sure if the problem was caused by the type of `qnt`. – Hury Shen Aug 11 '20 at 09:41
StringToNumber() is the solution. You are spot on. Thanks a lot. So even though the data type is mentioned int, its still considered as String. – shoubhgh Aug 11 '20 at 17:55
Hi @shoubhgh So the method `StringToNumber()` works in your code ? If it works, could you please mark my answer as "accepted", thanks in advance~ – Hury Shen Aug 12 '20 at 01:37

CosmosDBInput binding of Azure Function is not using path Variable defined in HttpTrigger

1 Answers1