Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Output binding is unable to update column that contains a slash in their name. #1026

Closed
rc-ig opened this issue Jan 26, 2024 · 0 comments · Fixed by #1042
Closed

Output binding is unable to update column that contains a slash in their name. #1026

rc-ig opened this issue Jan 26, 2024 · 0 comments · Fixed by #1042
Assignees
Labels
Milestone

Comments

@rc-ig
Copy link

rc-ig commented Jan 26, 2024

  • Azure Functions SQL Extension or Extension Bundle Version:
  "extensionBundle": {
      "id": "Microsoft.Azure.Functions.ExtensionBundle",
    "version": "[4.*, 5.0.0)"
  }
  • Is this a deployed or local function:

Issue is present on both deployed and local

  • What type of Database are you using? (Run SELECT @@VERSION as Version, SERVERPROPERTY('EngineEdition') as EngineEdition on your database):
Version EngineEdition
Microsoft SQL Azure (RTM) - 12.0.2000.8 Jan 3 2024 16:32:18 Copyright (C) 2022 Microsoft Corporation 5
  • List any custom settings for your function app. This could be a custom time-out defined specifically for your database server or optional configuration that can be customized for the app defined here.

The contents of function.json:

{
  "scriptFile": "__init__.py",
  "bindings": [
    {
      "name": "myblob",
      "type": "blobTrigger",
      "direction": "in",
      "path": "my_blob_path",
      "connection": "my_connection_string"
    },
    {
      "name": "tableCols",
      "type": "sql",
      "direction": "in",
      "commandText": "SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_table';",
      "commandType": "Text",
      "connectionStringSetting" : "SqlConnectionString"
    },
    {
      "name": "outputTable",
      "type": "sql",
      "direction": "out",
      "commandText": "dbo.my_table",
      "connectionStringSetting": "SqlConnectionString"
    }
  ]
}

Contents of __init__.py:

def main(
    myblob: func.InputStream,
    tableCols: func.SqlRowList,
    outputTable: func.Out[func.SqlRow],
  ):
  """Main function"""
  blob_contents = myblob.read()
  blob_data = create_dataframe(blob_contents)

  database_columns = {sql_row['COLUMN_NAME'] for sql_row in tableCols}
  
  cols_in_dataset = set(blob_data.columns)
  columns_to_insert = list(cols_in_dataset & cols_in_db)

  records = (
      blob_data
      [columns_to_insert]
      .dropna(how='all', axis=1)
      .to_dict('records')
  )

  output_rowlist = func.SqlRowList(func.SqlRow.from_dict(row) for row in records)

  outputTable.set(output_rowlist)

SQL create statement for the target table:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[my_table](
	[ID1] [varchar](4) NOT NULL,
	[Foo1] [varchar](255) NULL,
	[Foo2] [tinyint] NULL,
	[Foo/Bar3] [tinyint] NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[my_table] ADD  CONSTRAINT [PK_my_table] PRIMARY KEY CLUSTERED 
(
	[ID1] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is my database table' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'my_table'
GO

In the above Azure Function, the output_rowlist which is used to set the output binding consists of SqlRows which look like this:
{'ID1': '9999', 'Foo1': None, 'Foo2': '1', 'Foo/Bar3': '1'}

When the function runs, all of the columns are updated properly except for 'Foo/Bar3', which is always set to NULL regardless of the value present in output_rowlist.
When I update my_table using an SQL statement in Azure Data Studio, the column 'Foo/Bar3' updates without issue.
I have since changed the name of the column in the database to 'FooBar3' and the output binding updates the column correctly. I have not seen similar issues when updating columns that contain non-alphanumeric characters ie %, #.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants