Error with linking Azure Function hosted in APIM to SQL Database

hampton123 1,170 Reputation points
2023-08-31T16:06:57.66+00:00

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.

Azure API Management
Azure API Management
An Azure service that provides a hybrid, multi-cloud management platform for APIs.
2,296 questions
Azure SQL Database
Azure Functions
Azure Functions
An Azure service that provides an event-driven serverless compute platform.
5,419 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,484 questions
0 comments No comments
{count} votes

Accepted answer
  1. navba-MSFT 27,465 Reputation points Microsoft Employee
    2023-09-01T02:55:14.4+00:00

    @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)
    
        };
    
    

1 additional answer

Sort by: Most helpful
  1. hampton123 1,170 Reputation points
    2023-08-31T18:46:01.6033333+00:00

    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)
        };
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.