functions-bindings-azure-mysql-input.md

title	description	author	ms.topic	ms.custom	ms.date	ms.author	ms.reviewer	zone_pivot_groups
Azure Database for MySQL input binding for Functions	Learn to use the Azure Database for MySQL input binding in Azure Functions.	JetterMcTedder	reference	build-2023, devx-track-extended-java, devx-track-js, devx-track-python, devx-track-ts	9/26/2024	bspendolini	glenga	programming-languages-set-functions

Azure Database for MySQL input binding for Azure Functions (Preview)

When a function runs, the Azure Database for MySQL input binding retrieves data from a database and passes it to the input parameter of the function.

For information on setup and configuration details, see the overview.

::: zone pivot="programming-language-javascript,programming-language-typescript" [!INCLUDE functions-nodejs-model-tabs-description] ::: zone-end

Examples

::: zone pivot="programming-language-csharp"

[!INCLUDE functions-bindings-csharp-intro-with-csx]

Isolated worker model

More samples for the Azure Database for MySQL input binding are available in the GitHub repository.

This section contains the following examples:

• HTTP trigger, get row by ID from query string
• HTTP trigger, get multiple rows from route data
• HTTP trigger, delete rows

The examples refer to a Product class and a corresponding database table:

namespace AzureMySqlSamples.Common
{
    public class Product
    {
        public int? ProductId { get; set; }

        public string Name { get; set; }

        public int Cost { get; set; }

        public override bool Equals(object obj)
    }
}

DROP TABLE IF EXISTS Products;

CREATE TABLE Products (
	ProductId int PRIMARY KEY,
	Name varchar(100) NULL,
	Cost int NULL
);

HTTP trigger, get row by ID from query string

The following example shows a C# function that retrieves a single record. The function is triggered by an HTTP request that uses a query string to specify the ID. That ID is used to retrieve a Product record with the specified query.

Note

The HTTP query string parameter is case-sensitive.

using System.Collections.Generic;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Extensions.MySql;
using Microsoft.Azure.Functions.Worker.Http;
using AzureMySqlSamples.Common;

namespace AzureMySqlSamples.InputBindingIsolatedSamples
{
    public static class GetProductById
    {
        [Function(nameof(GetProductById))]
        public static IEnumerable<Product> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproducts/{productId}")]
            HttpRequestData req,
            [MySqlInput("select * from Products where ProductId = @productId",
                "MySqlConnectionString",
                parameters: "@ProductId={productId}")]
            IEnumerable<Product> products)
        {
            return products;
        }
    }
}

HTTP trigger, get multiple rows from route parameter

The following example shows a C# function that retrieves rows returned by the query. The function is triggered by an HTTP request that uses route data to specify the value of a query parameter. That parameter is used to filter the Product records in the specified query.

using System.Collections.Generic;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Extensions.MySql;
using Microsoft.Azure.Functions.Worker.Http;
using AzureMySqlSamples.Common;
 
namespace AzureMySqlSamples.InputBindingIsolatedSamples
{
    public static class GetProducts
    {
        [Function(nameof(GetProducts))]
        public static IEnumerable<Product> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproducts")]
            HttpRequestData req,
            [MySqlInput("select * from Products",
                "MySqlConnectionString")]
            IEnumerable<Product> products)
        {
            return products;
        }
    }
}

HTTP trigger, delete rows

The following example shows a C# function that executes a stored procedure with input from the HTTP request query parameter.

The stored procedure DeleteProductsCost must be created on the MySQL database. In this example, the stored procedure deletes a single record or all records depending on the value of the parameter.

DROP PROCEDURE IF EXISTS DeleteProductsCost;

Create Procedure DeleteProductsCost(cost INT)
BEGIN
	DELETE from Products where Products.cost = cost;
END

namespace AzureMySqlSamples.InputBindingSamples
{
    public static class GetProductsStoredProcedure
    {
        [FunctionName(nameof(GetProductsStoredProcedure))]
        public static IActionResult Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproducts-storedprocedure/{cost}")]
            HttpRequest req,
            [MySql("DeleteProductsCost",
                "MySqlConnectionString",
                commandType: System.Data.CommandType.StoredProcedure,
                parameters: "@Cost={cost}")]
            IEnumerable<Product> products)
        {
            return new OkObjectResult(products);
        }
    }
}

In-process model

More samples for the Azure Database for MySQL input binding are available in the GitHub repository.

This section contains the following examples:

• HTTP trigger, get row by ID from query string
• HTTP trigger, get multiple rows from route data
• HTTP trigger, delete rows

The examples refer to a Product class and a corresponding database table:

namespace AzureMySqlSamples.Common
{
    public class Product
    {
        public int? ProductId { get; set; }

        public string Name { get; set; }

        public int Cost { get; set; }

        public override bool Equals(object obj)
    }
}

DROP TABLE IF EXISTS Products;

CREATE TABLE Products (
	ProductId int PRIMARY KEY,
	Name varchar(100) NULL,
	Cost int NULL
);

HTTP trigger, get row by ID from query string

The following example shows a C# function that retrieves a single record. The function is triggered by an HTTP request that uses a query string to specify the ID. That ID is used to retrieve a Product record with the specified query.

Note

The HTTP query string parameter is case-sensitive.

using System.Collections.Generic;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Extensions.MySql;
using AzureMySqlSamples.Common;
 
namespace AzureMySqlSamples.InputBindingSamples
{
    public static class GetProductById
    {
        [FunctionName("GetProductById")]
        public static IActionResult Run(
            [HttpTrigger(AuthorizationLevel.Function, "get", Route = "getproducts/{productId}")] HttpRequest req,
            [MySql("select * from Products where ProductId = @productId",
                "MySqlConnectionString",
                parameters: "@ProductId={productId}")]
            IEnumerable<Product> products)
        {
            return new OkObjectResult(products);
        }
    }
}

HTTP trigger, get multiple rows from route parameter

The following example shows a C# function that retrieves documents returned by the query. The function is triggered by an HTTP request that uses route data to specify the value of a query parameter. That parameter is used to filter the Product records in the specified query.

using System.Collections.Generic;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Extensions.MySql;
using AzureMySqlSamples.Common;
 
namespace AzureMySqlSamples.InputBindingSamples
{
    public static class GetProducts
    {
        [FunctionName("GetProducts")]
        public static IActionResult Run(
            [HttpTrigger(AuthorizationLevel.Function, "get", Route = "getproducts")] HttpRequest req,
            [MySql("select * from Products",
                "MySqlConnectionString")]
            IEnumerable<Product> products)
        {
            return new OkObjectResult(products);
        }
    }
}

HTTP trigger, delete rows

The following example shows a C# function that executes a stored procedure with input from the HTTP request query parameter.

The stored procedure DeleteProductsCost must be created on the MySQL database. In this example, the stored procedure deletes a single record or all records depending on the value of the parameter.

DROP PROCEDURE IF EXISTS DeleteProductsCost;

Create Procedure DeleteProductsCost(cost INT)
BEGIN
	DELETE from Products where Products.cost = cost;
END

namespace AzureMySqlSamples.InputBindingSamples
{
    public static class GetProductsStoredProcedure
    {
        [FunctionName(nameof(GetProductsStoredProcedure))]
        public static IActionResult Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "getproducts-storedprocedure/{cost}")]
            HttpRequest req,
            [MySql("DeleteProductsCost",
                "MySqlConnectionString",
                commandType: System.Data.CommandType.StoredProcedure,
                parameters: "@Cost={cost}")]
            IEnumerable<Product> products)
        {
            return new OkObjectResult(products);
        }
    }
}

---

::: zone-end

::: zone pivot="programming-language-java"

More samples for the Azure Database for MySQL input binding are available in the GitHub repository.

This section contains the following examples:

• HTTP trigger, get multiple rows
• HTTP trigger, get row by ID from query string
• HTTP trigger, delete rows

The examples refer to a Product class and a corresponding database table:

package com.function.Common;

import com.fasterxml.jackson.annotation.JsonProperty;

public class Product {
    @JsonProperty("ProductId")
    private int ProductId;
    @JsonProperty("Name")
    private String Name;
    @JsonProperty("Cost")
    private int Cost;

    public Product() {
    }

DROP TABLE IF EXISTS Products;

CREATE TABLE Products (
	ProductId int PRIMARY KEY,
	Name varchar(100) NULL,
	Cost int NULL
);

HTTP trigger, get multiple rows

The following example shows a MySQL input binding in a Java function that is triggered by an HTTP request and reads from a query and returns the results in the HTTP response.

package com.function;

import com.function.Common.Product;
import com.microsoft.azure.functions.HttpMethod;
import com.microsoft.azure.functions.HttpRequestMessage;
import com.microsoft.azure.functions.HttpResponseMessage;
import com.microsoft.azure.functions.HttpStatus;
import com.microsoft.azure.functions.annotation.AuthorizationLevel;
import com.microsoft.azure.functions.annotation.FunctionName;
import com.microsoft.azure.functions.annotation.HttpTrigger;
import com.microsoft.azure.functions.mysql.annotation.CommandType;
import com.microsoft.azure.functions.mysql.annotation.MySqlInput;

import java.util.Optional;

public class GetProducts {
    @FunctionName("GetProducts")
    public HttpResponseMessage run(
            @HttpTrigger(
                name = "req",
                methods = {HttpMethod.GET},
                authLevel = AuthorizationLevel.ANONYMOUS,
                route = "getproducts}")
                HttpRequestMessage<Optional<String>> request,
            @MySqlInput(
                name = "products",
                commandText = "SELECT * FROM Products",
                commandType = CommandType.Text,
                connectionStringSetting = "MySqlConnectionString")
                Product[] products) {

        return request.createResponseBuilder(HttpStatus.OK).header("Content-Type", "application/json").body(products).build();
    }
}

HTTP trigger, get row by ID from query string

The following example shows a MySQL input binding in a Java function that is triggered by an HTTP request and reads from a query filtered by a parameter from the query string and returns the row in the HTTP response.

public class GetProductById {
    @FunctionName("GetProductById")
    public HttpResponseMessage run(
            @HttpTrigger(
                name = "req",
                methods = {HttpMethod.GET},
                authLevel = AuthorizationLevel.ANONYMOUS,
                route = "getproducts/{productid}")
                HttpRequestMessage<Optional<String>> request,
            @MySqlInput(
                name = "products",
                commandText = "SELECT * FROM Products WHERE ProductId= @productId",
                commandType = CommandType.Text,
                parameters = "@productId={productid}",
                connectionStringSetting = "MySqlConnectionString")
                Product[] products) {
 
        return request.createResponseBuilder(HttpStatus.OK).header("Content-Type", "application/json").body(products).build();
    }
}

HTTP trigger, delete rows

The following example shows a MySQL input binding in a Java function that is triggered by an HTTP request and executes a stored procedure with input from the HTTP request query parameter.

The stored procedure DeleteProductsCost must be created on the database. In this example, the stored procedure deletes a single record or all records depending on the value of the parameter.

DROP PROCEDURE IF EXISTS DeleteProductsCost;

Create Procedure DeleteProductsCost(cost INT)
BEGIN
	DELETE from Products where Products.cost = cost;
END

public class DeleteProductsStoredProcedure {
    @FunctionName("DeleteProductsStoredProcedure")
    public HttpResponseMessage run(
            @HttpTrigger(
                name = "req",
                methods = {HttpMethod.GET},
                authLevel = AuthorizationLevel.ANONYMOUS,
                route = "Deleteproducts-storedprocedure/{cost}")
                HttpRequestMessage<Optional<String>> request,
            @MySqlInput(
                name = "products",
                commandText = "DeleteProductsCost",
                commandType = CommandType.StoredProcedure,
                parameters = "@Cost={cost}",
                connectionStringSetting = "MySqlConnectionString")
                Product[] products) {

        return request.createResponseBuilder(HttpStatus.OK).header("Content-Type", "application/json").body(products).build();
    }
}

::: zone-end

::: zone pivot="programming-language-javascript,programming-language-typescript"

More samples for the Azure Database for MySQL input binding are available in the GitHub repository.

This section contains the following examples:

• HTTP trigger, get multiple rows
• HTTP trigger, get row by ID from query string
• HTTP trigger, delete rows

The examples refer to a database table:

DROP TABLE IF EXISTS Products;

CREATE TABLE Products (
	ProductId int PRIMARY KEY,
	Name varchar(100) NULL,
	Cost int NULL
);

HTTP trigger, get multiple rows

The following example shows a MYSQL input binding that is triggered by an HTTP request and reads from a query and returns the results in the HTTP response.

::: zone-end

::: zone pivot="programming-language-typescript"

Model v4

import { app, HttpRequest, HttpResponseInit, input, InvocationContext } from '@azure/functions';

const mysqlInput = input.generic({
    commandText: 'select * from Products',
    commandType: 'Text',
    connectionStringSetting: 'MySqlConnectionString',
});

export async function httpTrigger1(request: HttpRequest, context: InvocationContext): Promise<HttpResponseInit> {
    context.log('HTTP trigger and MySQL input binding function processed a request.');
    const products = context.extraInputs.get(mysqlInput);
    return {
        jsonBody: products,
    };
}

app.http('httpTrigger1', {
    methods: ['GET', 'POST'],
    authLevel: 'anonymous',
    extraInputs: [mysqlInput],
    handler: httpTrigger1,
});

Model v3

TypeScript samples aren't documented for model v3.

---

::: zone-end

::: zone pivot="programming-language-javascript"

Model v4

const { app, input } = require('@azure/functions');

const mysqlInput = input.generic({
    type: 'mysql',
    commandText: 'select * from Products where Cost = @Cost',
    parameters: '@Cost={Cost}',
    commandType: 'Text',
    connectionStringSetting: 'MySqlConnectionString'
})

app.http('GetProducts', {
    methods: ['GET', 'POST'],
    authLevel: 'anonymous',
    route: 'getproducts/{cost}',
    extraInputs: [mysqlInput],
    handler: async (request, context) => {
        const products = JSON.stringify(context.extraInputs.get(mysqlInput));

        return {
            status: 200,
            body: products
        };
    }
});

Model v3

The following example is binding data in the function.json file:

{
  "bindings": [
    {
      "authLevel": "function",
      "name": "req",
      "type": "httpTrigger",
      "direction": "in",
      "methods": [
        "get"
      ],
      "route": "getproducts"
    },
    {
      "name": "$return",
      "type": "http",
      "direction": "out"
    },
    {
      "name": "products",
      "type": "mysql",
      "direction": "in",
      "commandText": "select * from Products",
      "commandType": "Text",
      "connectionStringSetting": "MySqlConnectionString"
    }
  ],
  "disabled": false
}

The configuration section explains these properties.

The following example is sample JavaScript code:

module.exports = async function (context, req, products) {
    context.log('JavaScript HTTP trigger and MySQL input binding function processed a request.');
    
    context.res = {
        // status: 200, /* Defaults to 200 */
        mimetype: "application/json",
        body: products
    };
}

---

::: zone-end

::: zone pivot="programming-language-javascript,programming-language-typescript"

HTTP trigger, get row by ID from query string

The following example shows a MySQL input binding that is triggered by an HTTP request and reads from a query filtered by a parameter from the query string and returns the row in the HTTP response.

::: zone-end ::: zone pivot="programming-language-typescript"

Model v4

import { app, HttpRequest, HttpResponseInit, input, InvocationContext } from '@azure/functions';

const mysqlInput = input.generic({
    commandText: 'select * from Products where ProductId= @productId',
    commandType: 'Text',
    parameters: '@productId={productid}',
    connectionStringSetting: 'MySqlConnectionString',
});

export async function httpTrigger1(request: HttpRequest, context: InvocationContext): Promise<HttpResponseInit> {
    context.log('HTTP trigger and MySQL input binding function processed a request.');
    const products = context.extraInputs.get(mysqlInput);
    return {
        jsonBody: products,
    };
}

app.http('httpTrigger1', {
    methods: ['GET', 'POST'],
    authLevel: 'anonymous',
    extraInputs: [mysqlInput],
    handler: httpTrigger1,
});

Model v3

TypeScript samples aren't documented for model v3.

---

::: zone-end

::: zone pivot="programming-language-javascript"

Model v4

const { app, input } = require('@azure/functions');

const mysqlInput = input.generic({
    type: 'mysql',
    commandText: 'select * from Products where ProductId= @productId',
    commandType: 'Text',
    parameters: '@productId={productid}',
    connectionStringSetting: 'MySqlConnectionString'
})

app.http('GetProducts', {
    methods: ['GET', 'POST'],
    authLevel: 'anonymous',
    route: 'getproducts/{productid}',
    extraInputs: [mysqlInput],
    handler: async (request, context) => {
        const products = JSON.stringify(context.extraInputs.get(mysqlInput));

        return {
            status: 200,
            body: products
        };
    }
});

Model v3

The following example is binding data in the function.json file:

{
  "bindings": [
    {
      "authLevel": "function",
      "name": "req",
      "type": "httpTrigger",
      "direction": "in",
      "methods": [
        "get"
      ],
      "route": "getproducts/{productid}"
    },
    {
      "name": "$return",
      "type": "http",
      "direction": "out"
    },
    {
      "name": "products",
      "type": "mysql",
      "direction": "in",
      "commandText": "select * from Products where ProductId= @productId",
      "commandType": "Text",
      "parameters": "@productId={productid}",
      "connectionStringSetting": "MySqlConnectionString"
    }
  ],
  "disabled": false
}

The configuration section explains these properties.

The following example is sample JavaScript code:

module.exports = async function (context, req, products) {
    context.log('JavaScript HTTP trigger function processed a request.');
    context.log(JSON.stringify(products));
    return {
        status: 200,
        body: products
    };
}

---

::: zone-end

::: zone pivot="programming-language-javascript,programming-language-typescript"

HTTP trigger, delete rows

The following example shows a MySQL input binding that is triggered by an HTTP request and executes a stored procedure with input from the HTTP request query parameter.

The stored procedure DeleteProductsCost must be created on the database. In this example, the stored procedure deletes a single record or all records depending on the value of the parameter.

DROP PROCEDURE IF EXISTS DeleteProductsCost;

Create Procedure DeleteProductsCost(cost INT)
BEGIN
	DELETE from Products where Products.cost = cost;
END

::: zone-end

::: zone pivot="programming-language-typescript"

Model v4

import { app, HttpRequest, HttpResponseInit, input, InvocationContext } from '@azure/functions';

const mysqlInput = input.generic({
    commandText: 'DeleteProductsCost',
    commandType: 'StoredProcedure',
    parameters: '@Cost={cost}',
    connectionStringSetting: 'MySqlConnectionString',
});

export async function httpTrigger1(request: HttpRequest, context: InvocationContext): Promise<HttpResponseInit> {
    context.log('HTTP trigger and MySQL input binding function processed a request.');
    const products = context.extraInputs.get(mysqlInput);
    return {
        jsonBody: products,
    };
}

app.http('httpTrigger1', {
    methods: ['GET', 'POST'],
    authLevel: 'anonymous',
    extraInputs: [mysqlInput],
    handler: httpTrigger1,
});

Model v3

TypeScript samples aren't documented for model v3.

---

::: zone-end

::: zone pivot="programming-language-javascript"

Model v4

const { app, input } = require('@azure/functions');

const mysqlInput = input.generic({
    type: 'mysql',
    commandText: 'DeleteProductsCost',
    commandType: 'StoredProcedure',
    parameters: '@Cost={cost}',
    connectionStringSetting: 'MySqlConnectionString'
})

app.http('httpTrigger1', {
    methods: ['POST'],
    authLevel: 'anonymous',
    route: 'DeleteProductsByCost',
    extraInputs: [mysqlInput],
    handler: async (request, context) => {
        const products = JSON.stringify(context.extraInputs.get(mysqlInput));

        return {
            status: 200,
            body: products
        };
    }
});

Model v3

{
  "bindings": [
    {
      "authLevel": "function",
      "name": "req",
      "type": "httpTrigger",
      "direction": "in",
      "methods": [
        "get"
      ],
      "route": "DeleteProductsByCost"
    },
    {
      "name": "$return",
      "type": "http",
      "direction": "out"
    },
    {
      "name": "products",
      "type": "mysql",
      "direction": "in",
      "commandText": "DeleteProductsCost",
      "commandType": "StoredProcedure",
      "parameters": "@Cost={cost}",
      "connectionStringSetting": "MySqlConnectionString"
    }
  ],
  "disabled": false
}

The configuration section explains these properties.

The following example is sample JavaScript code:

module.exports = async function (context, req, products) {
    context.log('JavaScript HTTP trigger function processed a request.');
    context.log(JSON.stringify(products));
    return {
        status: 200,
        body: products
    };
}

---

::: zone-end

::: zone pivot="programming-language-powershell"

More samples for the Azure Database for MySQL input binding are available in the GitHub repository.

This section contains the following examples:

• HTTP trigger, get multiple rows
• HTTP trigger, get row by ID from query string
• HTTP trigger, delete rows

The examples refer to a database table:

DROP TABLE IF EXISTS Products;

CREATE TABLE Products (
	ProductId int PRIMARY KEY,
	Name varchar(100) NULL,
	Cost int NULL
);

HTTP trigger, get multiple rows

The following example shows a MySQL input binding in a function.json file and a PowerShell function that is triggered by an HTTP request and reads from a query and returns the results in the HTTP response.

The following example is binding data in the function.json file:

{
  "bindings": [
    {
      "authLevel": "function",
      "name": "Request",
      "type": "httpTrigger",
      "direction": "in",
      "methods": [
        "get"
      ],
      "route": "getproducts/{cost}"
    },
    {
      "name": "response",
      "type": "http",
      "direction": "out"
    },
    {
      "name": "products",
      "type": "mysql",
      "direction": "in",
      "commandText": "select * from Products",
      "commandType": "Text",
      "connectionStringSetting": "MySqlConnectionString"
    }
  ],
  "disabled": false
}

The configuration section explains these properties.

The following example is sample PowerShell code for the function in the run.ps1 file:

using namespace System.Net

param($Request, $TriggerMetadata, $products)

Write-Host "PowerShell function with MySql Input Binding processed a request."

Push-OutputBinding -Name response -Value ([HttpResponseContext]@{
    StatusCode = [System.Net.HttpStatusCode]::OK
    Body = $products
})

HTTP trigger, get row by ID from query string

The following example shows a MySQL input binding in a PowerShell function that is triggered by an HTTP request and reads from a query filtered by a parameter from the query string and returns the row in the HTTP response.

The following example is binding data in the function.json file:

{
  "bindings": [
    {
      "authLevel": "function",
      "name": "Request",
      "type": "httpTrigger",
      "direction": "in",
      "methods": [
        "get"
      ],
      "route": "getproducts/{productid}"
    },
    {
      "name": "response",
      "type": "http",
      "direction": "out"
    },
    {
      "name": "products",
      "type": "mysql",
      "direction": "in",
      "commandText": "select * from Products where ProductId= @productId",
      "commandType": "Text",
      "parameters": "MySqlConnectionString",
      "connectionStringSetting": "MySqlConnectionString"
    }
  ],
  "disabled": false
}

The configuration section explains these properties.

The following is sample PowerShell code for the function in the run.ps1 file:

using namespace System.Net

param($Request, $TriggerMetadata, $products)

Write-Host "PowerShell function with MySql Input Binding processed a request."

Push-OutputBinding -Name response -Value ([HttpResponseContext]@{
    StatusCode = [System.Net.HttpStatusCode]::OK
    Body = $products
})

HTTP trigger, delete rows

The following example shows a MySQL input binding in a function.json file and a PowerShell function that is triggered by an HTTP request and executes a stored procedure with input from the HTTP request query parameter.

The stored procedure DeleteProductsCost must be created on the database. In this example, the stored procedure deletes a single record or all records depending on the value of the parameter.

DROP PROCEDURE IF EXISTS DeleteProductsCost;

Create Procedure DeleteProductsCost(cost INT)
BEGIN
	DELETE from Products where Products.cost = 'cost';
END

{
  "bindings": [
    {
      "authLevel": "function",
      "name": "Request",
      "type": "httpTrigger",
      "direction": "in",
      "methods": [
        "get"
      ],
      "route": "deleteproducts-storedprocedure/{cost}"
    },
    {
      "name": "response",
      "type": "http",
      "direction": "out"
    },
    {
      "name": "products",
      "type": "mysql",
      "direction": "in",
      "commandText": "DeleteProductsCost",
      "commandType": "StoredProcedure",
      "parameters": "@Cost={cost}",
      "connectionStringSetting": "MySqlConnectionString"
    }
  ],
  "disabled": false
}

The configuration section explains these properties.

The following example is sample PowerShell code for the function in the run.ps1 file:

using namespace System.Net

param($Request, $TriggerMetadata, $products)

Write-Host "PowerShell function with MySql Input Binding processed a request."

Push-OutputBinding -Name response -Value ([HttpResponseContext]@{
    StatusCode = [System.Net.HttpStatusCode]::OK
    Body = $products
}

::: zone-end

::: zone pivot="programming-language-python"

More samples for the Azure Database for MySQL input binding are available in the GitHub repository.

This section contains the following examples:

• HTTP trigger, get multiple rows
• HTTP trigger, get row by ID from query string
• HTTP trigger, delete rows

The examples refer to a database table:

DROP TABLE IF EXISTS Products;

CREATE TABLE Products (
	ProductId int PRIMARY KEY,
	Name varchar(100) NULL,
	Cost int NULL
);

Note

Please note that Azure Functions version 1.22.0b4 must be used for Python .

HTTP trigger, get multiple rows

The following example shows a MySQL input binding in a function.json file and a Python function that is triggered by an HTTP request and reads from a query and returns the results in the HTTP response.

v2

The following example is sample python code for the function_app.py file:

import azure.functions as func
import datetime
import json
import logging
 
app = func.FunctionApp()
 
 
@app.generic_trigger(arg_name="req", type="httpTrigger", route="getproducts/{cost}")
@app.generic_output_binding(arg_name="$return", type="http")
@app.generic_input_binding(arg_name="products", type="mysql",
                           commandText= "select * from Products",
                           command_type="Text",
                           connection_string_setting="MySqlConnectionString")
def mysql_test(req: func.HttpRequest, products: func.MySqlRowList) -> func.HttpResponse:
    rows = list(map(lambda r: json.loads(r.to_json()), products))
 
    return func.HttpResponse(
        json.dumps(rows),
        status_code=200,
        mimetype="application/json"
    )

v1

The following example is binding data in the function.json file:

{
    "bindings": [
      {
        "authLevel": "function",
        "name": "req",
        "type": "httpTrigger",
        "direction": "in",
        "methods": [
          "get"
        ],
        "route": "getproducts/{cost}"
      },
      {
        "name": "$return",
        "type": "http",
        "direction": "out"
      },
      {
        "name": "products",
        "type": "mysql",
        "direction": "in",
        "commandText": "select * from Products",
        "commandType": "Text",
        "connectionStringSetting": "MySqlConnectionString"
      }
    ],
    "disabled": false
  }

The configuration section explains these properties.

The following is sample Python code:

import azure.functions as func
import json

def main(req: func.HttpRequest, products: func.MySqlRowList) -> func.HttpResponse:
    rows = list(map(lambda r: json.loads(r.to_json()), products))

    return func.HttpResponse(
        json.dumps(rows),
        status_code=200,
        mimetype="application/json"
    ) 

---

HTTP trigger, get row by ID from query string

The following example shows a MySQL input binding in a Python function that is triggered by an HTTP request and reads from a query filtered by a parameter from the query string and returns the row in the HTTP response.

v2

The following example is sample python code for the function_app.py file:

import azure.functions as func
import datetime
import json
import logging
 
app = func.FunctionApp()
 
 
@app.generic_trigger(arg_name="req", type="httpTrigger", route="getproducts/{cost}")
@app.generic_output_binding(arg_name="$return", type="http")
@app.generic_input_binding(arg_name="products", type="mysql",
                           commandText= "select * from Products where ProductId= @productId",
                           command_type="Text",
                           parameters= "@productId={productid}",
                           connection_string_setting="MySqlConnectionString")
def mysql_test(req: func.HttpRequest, products: func.MySqlRowList) -> func.HttpResponse:
    rows = list(map(lambda r: json.loads(r.to_json()), products))
 
    return func.HttpResponse(
        json.dumps(rows),
        status_code=200,
        mimetype="application/json"
    )

v1

The following is binding data in the function.json file:

{
    "bindings": [
      {
        "authLevel": "function",
        "name": "req",
        "type": "httpTrigger",
        "direction": "in",
        "methods": [
          "get"
        ],
        "route": "getproducts/{productid}"
      },
      {
        "name": "$return",
        "type": "http",
        "direction": "out"
      },
      {
        "name": "products",
        "type": "mysql",
        "direction": "in",
        "commandText": "select * from Products where ProductId= @productId",
        "commandType": "Text",
        "parameters": "@productId={productid}",
        "connectionStringSetting": "MySqlConnectionString"
      }
    ],
    "disabled": false
  }

The configuration section explains these properties.

The following example is sample Python code:

import azure.functions as func
import json

def main(req: func.HttpRequest, products: func.MySqlRowList) -> func.HttpResponse:
    rows = list(map(lambda r: json.loads(r.to_json()), products))

    return func.HttpResponse(
        json.dumps(rows),
        status_code=200,
        mimetype="application/json"
    ) 

---

HTTP trigger, delete rows

The following example shows a MySQL input binding in a function.json file and a Python function that is triggered by an HTTP request and executes a stored procedure with input from the HTTP request query parameter.

The stored procedure DeleteProductsCost must be created on the database. In this example, the stored procedure deletes a single record or all records depending on the value of the parameter.

DROP PROCEDURE IF EXISTS DeleteProductsCost;

Create Procedure DeleteProductsCost(cost INT)
BEGIN
	DELETE from Products where Products.cost = cost;
END

v2

The following example is sample python code for the function_app.py file:

import azure.functions as func
import datetime
import json
import logging
 
app = func.FunctionApp()
 
 
@app.generic_trigger(arg_name="req", type="httpTrigger", route="getproducts/{cost}")
@app.generic_output_binding(arg_name="$return", type="http")
@app.generic_input_binding(arg_name="products", type="mysql",
                           commandText= "DeleteProductsCost",
                           command_type="StoredProcedure",
                           parameters= "@Cost={cost}",
                           connection_string_setting="MySqlConnectionString")
def mysql_test(req: func.HttpRequest, products: func.MySqlRowList) -> func.HttpResponse:
    rows = list(map(lambda r: json.loads(r.to_json()), products))
 
    return func.HttpResponse(
        json.dumps(rows),
        status_code=200,
        mimetype="application/json"
    )

v1

    "bindings": [
      {
        "authLevel": "function",
        "name": "req",
        "type": "httpTrigger",
        "direction": "in",
        "methods": [
          "get"
        ],
        "route": "getproducts-storedprocedure/{cost}"
      },
      {
        "name": "$return",
        "type": "http",
        "direction": "out"
      },
      {
        "name": "products",
        "type": "mysql",
        "direction": "in",
        "commandText": "DeleteProductsCost",
        "commandType": "StoredProcedure",
        "parameters": "@Cost={cost}",
        "connectionStringSetting": "MySqlConnectionString"
      }
    ],
    "disabled": false
  }

The configuration section explains these properties.

The following is sample Python code:

import json
import azure.functions as func

# The input binding executes the `SELECT * FROM Products WHERE Cost = @Cost` query.
# The Parameters argument passes the `{cost}` specified in the URL that triggers the function,
# `getproducts/{cost}`, as the value of the `@Cost` parameter in the query.
# CommandType is set to `Text`, since the constructor argument of the binding is a raw query.
def main(req: func.HttpRequest, products: func.MySqlRowList) -> func.HttpResponse:
    rows = list(map(lambda r: json.loads(r.to_json()), products))

    return func.HttpResponse(
        json.dumps(rows),
        status_code=200,
        mimetype="application/json"
    )

---

::: zone-end

::: zone pivot="programming-language-csharp"

Attributes

The C# library uses the MySqlAttribute attribute to declare the MySQL bindings on the function, which has the following properties:

Attribute property	Description
CommandText	Required. The MySQL query command or name of the stored procedure executed by the binding.
ConnectionStringSetting	Required. The name of an app setting that contains the connection string for the database against which the query or stored procedure is being executed. This value isn't the actual connection string and must instead resolve to an environment variable name.
CommandType	Required. A CommandType value, which is Text for a query and StoredProcedure for a stored procedure.
Parameters	Optional. Zero or more parameter values passed to the command during execution as a single string. Must follow the format @param1=param1,@param2=param2. Neither the parameter name nor the parameter value can contain a comma (,) or an equals sign (=).

::: zone-end

::: zone pivot="programming-language-java"

Annotations

In the Java functions runtime library, use the @MySQLInput annotation on parameters whose value would come from Azure Database for MySQL. This annotation supports the following elements:

Element	Description
commandText	Required. The MySQL query command or name of the stored procedure executed by the binding.
connectionStringSetting	Required. The name of an app setting that contains the connection string for the database against which the query or stored procedure is being executed. This value isn't the actual connection string and must instead resolve to an environment variable name.
commandType	Required. A CommandType value, which is "Text" for a query and "StoredProcedure" for a stored procedure.
name	Required. The unique name of the function binding.
parameters	Optional. Zero or more parameter values passed to the command during execution as a single string. Must follow the format @param1=param1,@param2=param2. Neither the parameter name nor the parameter value can contain a comma (,) or an equals sign (=).

::: zone-end

::: zone pivot="programming-language-javascript,programming-language-typescript"

Configuration

Model v4

The following table explains the properties that you can set on the options object passed to the input.generic() method.

Property	Description
commandText	Required. The MySQL query command or name of the stored procedure executed by the binding.
connectionStringSetting	Required. The name of an app setting that contains the connection string for the database against which the query or stored procedure is being executed. This value isn't the actual connection string and must instead resolve to an environment variable name. Optional keywords in the connection string value are available to refine MySQL bindings connectivity.
commandType	Required. A CommandType value, which is Text for a query and StoredProcedure for a stored procedure.
parameters	Optional. Zero or more parameter values passed to the command during execution as a single string. Must follow the format @param1=param1,@param2=param2. Neither the parameter name nor the parameter value can contain a comma (,) or an equals sign (=).

Model v3

The following table explains the binding configuration properties that you set in the function.json file.

Property	Description
type	Required. Must be set to Mysql.
direction	Required. Must be set to in.
name	Required. The name of the variable that represents the query results in function code.
commandText	Required. The MySQL query command or name of the stored procedure executed by the binding.
connectionStringSetting	Required. The name of an app setting that contains the connection string for the database against which the query or stored procedure is being executed. This value isn't the actual connection string and must instead resolve to an environment variable name. Optional keywords in the connection string value are available to refine MySQL bindings connectivity.
commandType	Required. A CommandType value, which is Text for a query and StoredProcedure for a stored procedure.
parameters	Optional. Zero or more parameter values passed to the command during execution as a single string. Must follow the format @param1=param1,@param2=param2. Neither the parameter name nor the parameter value can contain a comma (,) or an equals sign (=).

---

::: zone-end

::: zone pivot="programming-language-powershell,programming-language-python"

Configuration

The following table explains the binding configuration properties that you set in the function.json file.

function.json property	Description
type	Required. Must be set to mysql.
direction	Required. Must be set to in.
name	Required. The name of the variable that represents the query results in function code.
commandText	Required. The MySQL query command or name of the stored procedure executed by the binding.
connectionStringSetting	Required. The name of an app setting that contains the connection string for the database against which the query or stored procedure is being executed. This value isn't the actual connection string and must instead resolve to an environment variable name. Optional keywords in the connection string value are available to refine MySQL bindings connectivity.
commandType	Required. A CommandType value, which is Text for a query and StoredProcedure for a stored procedure.
parameters	Optional. Zero or more parameter values passed to the command during execution as a single string. Must follow the format @param1=param1,@param2=param2. Neither the parameter name nor the parameter value can contain a comma (,) or an equals sign (=).

::: zone-end

[!INCLUDE app settings to local.settings.json]

Usage

The attribute's constructor takes the MySQL command text, the command type, parameters, and the connection string setting name. The command can be a MYSQL query with the command type System.Data.CommandType.Text or stored procedure name with the command type System.Data.CommandType.StoredProcedure. The connection string setting name corresponds to the application setting (in local.settings.json for local development) that contains the connection string to the Azure Database for MySQL.

If an exception occurs when a MySQL input binding is executed then the function code won't execute. This might result in an error code being returned, such as an HTTP trigger returning a 500 error code.

Next steps

• Save data to a database (Output binding)
• Run a function from a HTTP request (trigger)