Azure Function SQL Binding: UPDATE statement possible?

Question

Azure Function SQL Binding: UPDATE statement possible?

Joe Pawlowski (DHL Supply Chain) 30

I've been wracking my brain and the internet to understand the cababilities of this newer feature but can't get a straight answer.

Should the below excerpt from my function.json file work?

  {
    "type": "sql",
    "direction": "out",
    "name": "sqlUpdate",
    "connectionStringSetting": "SqlConnectionString",
    "tableName": "dbo.ToDo", 
    "commandText": "UPDATE dbo.ToDo SET url = @url WHERE title = @title"
 
  }

Joe Pawlowski (DHL Supply Chain) 30 Reputation points

2023-10-03T20:00:35+00:00

BTW, it doesn't for me. I've tried a million different things. Currently getting this error in VS Code:

System.Private.CoreLib: Exception while executing function: Functions.HttpTrigger1_update092523. Microsoft.Azure.WebJobs.Host: Error while handling parameter _binder after function returned:. Microsoft.Azure.WebJobs.Extensions.Sql: Encountered error(s) while parsing schema and object name: Incorrect syntax near UPDATE.

Accepted answer

1 additional answer

Your answer

Joe Pawlowski (DHL Supply Chain) 30 Reputation points

2023-10-03T20:00:35+00:00

BTW, it doesn't for me. I've tried a million different things. Currently getting this error in VS Code:

System.Private.CoreLib: Exception while executing function: Functions.HttpTrigger1_update092523. Microsoft.Azure.WebJobs.Host: Error while handling parameter _binder after function returned:. Microsoft.Azure.WebJobs.Extensions.Sql: Encountered error(s) while parsing schema and object name: Incorrect syntax near UPDATE.

Answer 1

MuthuKumaranMurugaachari-MSFT 22,441 Moderator

Joe Pawlowski (DHL Supply Chain) Thanks for posting your question in Microsoft Q&A. From the code snippet above, you are using SQL output binding to write the data to the table. As per doc: Azure SQL output binding for Azure Functions, the CommandText property should point to the name of the table not SQL statement. The output binding actually uses MERGE statement (T-SQL) to write the data and UPDATE statement is not supported (hence you faced the syntax error).

We do have a lot of samples available here for different languages such as Python, C# for output bindings and I suggest you referring that for usage. Specifically on UPDATE functionality, check out this discussion and let me know if you face any issues or have any other questions with the samples.

For any feedback on SQL extension, please submit it via Issues or Discussions and our product team will assist you with that. I hope this helps with your questions.

If you found the answer to your question helpful, please take a moment to mark it as "Yes" for others to benefit from your experience. Or simply add a comment tagging me and would be happy to answer your questions.

Joe Pawlowski (DHL Supply Chain) 30 Reputation points

2023-10-03T22:34:16.6266667+00:00

Still looking for a definite answer. Unfortunately, the above isn't at all useful in answering my question.

MuthuKumaranMurugaachari-MSFT 22,441 Moderator

Joe Pawlowski (DHL Supply Chain) To answer your questions,

#1 No, you cannot put UPDATE statement in the CommandText property.

#2 If you are using JavaScript (as an example), check out the example: https://github.com/Azure/azure-functions-sql-extension/blob/main/samples/samples-js-v4/src/functions/AddProduct.js and you need to specify the table name (Products in the example) in the CommandText property. This will insert the data into the table if it doesn't exists or update if it already exists.

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

const sqlOutput = output.generic({
    type: 'sql',
    commandText: 'Products',
    connectionStringSetting: 'SqlConnectionString'
})

// Upsert the product, which will insert it into the Products table if the primary key (ProductId) for that item doesn't exist.
// If it does then update it to have the new name and cost.
app.http('AddProduct', {
    methods: ['POST'],
    authLevel: 'anonymous',
    extraOutputs: [sqlOutput],
    handler: async (request, context) => {
        // Note that this expects the body to be a JSON object or array of objects which have a property
        // matching each of the columns in the table to upsert to.
        const product = await request.json();
        context.extraOutputs.set(sqlOutput, product);

        return {
            status: 201,
            body: JSON.stringify(product)
        };
    }
});

I hope this helps.

Joe Pawlowski (DHL Supply Chain) 30 Reputation points

2023-10-04T12:42:52.1666667+00:00

Thanx that was helpful. I've built many AF using SQL Bindings to populate Azure SQL Tables and retrieve the data out using HTTP Triggers in my web solutions so, I'm well versed there and that's not a problem.

I wanted to now implement record edit functionality and thought I could use the UPDATE statement in a SQL Binding which, I now believe cannot be done.

Thank you.

Does anyone know if this a feature that's on the product roadmap?
MuthuKumaranMurugaachari-MSFT 22,441 Reputation points Moderator

2023-10-04T13:49:57.4366667+00:00

Joe Pawlowski (DHL Supply Chain) Glad it helped! Please consider marking it as helpful to benefit the community.

Can you elaborate about recording edit functionality? Are you looking to add loggers somewhere that UPDATE occurred?
Joe Pawlowski (DHL Supply Chain) 30 Reputation points

2023-10-04T17:03:04.8+00:00

So, I just want to add functionality that allows my end-users to edit an record:

Original

Name: Joe Brown

ID: 1234

DOB: 10/25/77

DOB was entered correctly. Record needs to be updated to:

Revised

Name: Joe Brown

ID: 1234

~~DOB: 10/25/77~~

DOB: 10/25/67

Again, just allowing an end user to update and edit any record.
MuthuKumaranMurugaachari-MSFT 22,441 Reputation points Moderator

2023-10-04T19:48:30.1433333+00:00
Joe Pawlowski (DHL Supply Chain) You can edit the existing record via SQL output binding.

Let's consider Products table as in the GitHub example (Table definition) with the empty rows and I am using the same code snippet reference above.

Then I made POST method to create a new row with the below request body from Postman

Successfully saved the row in the SQL DB as follows:

Then make an edit record with updated request body (Cost value now 900) and make POST method. (since ProductId 123 already exists, it will update the existing record in DB)

To summarize, if the row already exists in the database (via Primary Key here), it will update the row with the new request body info. Otherwise, it will just insert the row into the database.
Joe Pawlowski (DHL Supply Chain) 30 Reputation points

2023-10-04T20:19:40.4+00:00

Hmm. I see. It seems to behave more like a 'replace' where the whole record is updated (some fields change, some might not). This is a little different than how the UPDATE statement works but, it might work for me. Let me explore this. I will report back here with more questions or successful results. Thanx again.
MuthuKumaranMurugaachari-MSFT 22,441 Reputation points Moderator

2023-10-04T20:21:09.68+00:00

Joe Pawlowski (DHL Supply Chain) sure, you are welcome!
Joe Pawlowski (DHL Supply Chain) 30 Reputation points

2023-10-06T19:44:07.6766667+00:00

I got it to work. All good for now.
MuthuKumaranMurugaachari-MSFT 22,441 Reputation points Moderator

2023-10-06T19:47:54.37+00:00

Awesome!
Gireesh Annepu 0 Reputation points

2024-02-13T07:19:34.4133333+00:00

@Joe Pawlowski (DHL Supply Chain) Will you be able to post how exactly you have did it? I am struggling to do the same thing again and again.

Answer 2

Joe Pawlowski (DHL Supply Chain) 30

Ok, so I've gotten through Create, Read, Update...Delete is next.

From what I can gather, there currently isn't any way to use the SQL Bindings to delete a record form a table.

MuthuKumaranMurugaachari-MSFT 22,441 Reputation points Moderator

2023-10-09T15:34:04.3566667+00:00

Joe Pawlowski (DHL Supply Chain) It is indirectly supported via SQL Input binding via Stored Proc and check out https://learn.microsoft.com/en-us/azure/azure-functions/functions-bindings-azure-sql-input?tabs=isolated-process%2Cnodejs-v4&pivots=programming-language-javascript#http-trigger-delete-rows-3 sample.

We have an open item to add more samples to our doc and will let our doc team with the feedback.

Share via

Azure Function SQL Binding: UPDATE statement possible?

1 additional answer

Your answer