Error with linking Azure Function hosted in APIM to SQL Database

Question

Hi, I've been trying to link my Azure Function hosted in APIM to my company's SQL database. Below is the Azure Function's code; I added "SqlConnectionString" to my application settings as well. For some reason, it hasn't been working. My Azure Function returns status 200, but nothing is returned. I'm trying to return everything within the table "TestTable". Why is this not working?

// index.js
module.exports = async function (context, req, toDoSchools) {
    context.log('JavaScript HTTP trigger and SQL input binding function processed a request.');

		mimetype: "application/json",        
		body: toDoSchools    
	};
}

// function.json
{
  "bindings": [
    {
      "authLevel": "anonymous",
      "type": "httpTrigger",
      "direction": "in",
      "name": "req",
      "methods": [
        "get",
        "post"
      ]
    },
    {
      "type": "http",
      "direction": "out",
      "name": "res"
    },
    {
      "type": "sql",
      "direction": "out",
      "name": "toDoSchools",
      "commandText": "SELECT * FROM dbo.TestTable",
      "connectionStringSetting": "SqlConnectionString"
    }
  ]
}

Thank you in advance.

Answer 1

@hampton123 I'm glad to see you were able to resolve your issue. Thanks for posting your solution so that others experiencing the same thing can easily reference this.

Since the Microsoft Q&A community has a policy that the question author cannot accept their own answer, they can only accept answers by others, I'll repost your solution in case you'd like to Accept the answer.

Issue: You're to link your Azure Function hosted in Azure API Management (APIM) to your company's SQL database. You have added the "SqlConnectionString" to your application settings, but the function is not returning any data from the "TestTable" table. The Azure Function returns status 200, but nothing is returned.

Resolution:
You changed direction to "in" for the SQL binding, and for the Azure Function and also changed context.res to the following:

status: 200,

        headers: {

            "Content-Type": "application/json"

        },

        body: JSON.stringify(toDoSchools, null, 2)

    };

Answer 2

It's fixed now! I changed direction to "in" for the SQL binding, and for the Azure Function I also changed context.res to the following:

        status: 200,
        headers: {
            "Content-Type": "application/json"
        },
        body: JSON.stringify(toDoSchools, null, 2)
    };