Skip to content

[Bug]: MSSQL - Cannot obtain Schema for entity - Geography data type #1397

New issue
New issue
Open
Open
[Bug]: MSSQL - Cannot obtain Schema for entity - Geography data type#1397
Labels
bugSomething isn't workingcriCustomer Reported issue
Milestone
Backlog

Description

@Banchio
Simone Banchieri (Banchio)
opened on Mar 31, 2023

What happened?

I have tried to use DAB to expose Sales.Customer table from World Wide Importers database. DAB fails to start with the exception reported below. If I try to publish a non SystemVersioned Table (Sales.Orders, Sales.Invoices, etc) it works perfectly so I think this is due to the fact that Sales.Customers is a System-Versioned table in SQL.

I was able to repro the error in Visual Studio and it seems that exception is raised from the FillSchema method of the DbDataAdapter in the System.Data.Common but not sure about the reason.

Thanks

Version

0.5.34

What database are you using?

Azure SQL

What hosting model are you using?

Local (including CLI)

Which API approach are you accessing DAB through?

REST, GraphQL

Relevant log output

Information: Microsoft.DataApiBuilder 0.5.34
Information: Config not provided. Trying to get default config based on DAB_ENVIRONMENT...
Found config file: dab-config.json.
Information: Setting default minimum LogLevel: Debug for Development mode.
Starting the runtime engine...
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      Using file dab-config.json to configure the runtime.
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL type for Invoice is Invoice
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL type for InvoiceLine is InvoiceLine
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL type for Order is Order
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL type for OrderLine is OrderLine
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL type for Customer is Customer
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      Runtime configuration has been successfully loaded.
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      GraphQL path: /graphql
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      StaticWebApps
info: Azure.DataApiBuilder.Service.Configurations.RuntimeConfigProvider[0]
      Runtime config loaded from file.
info: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[63]
      User profile is available. Using 'C:\Users\sbanchie\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
info: Azure.DataApiBuilder.Service.Services.ISqlMetadataProvider[0]
      Invoice path: /api/Invoice
info: Azure.DataApiBuilder.Service.Services.ISqlMetadataProvider[0]
      InvoiceLine path: /api/InvoiceLine
info: Azure.DataApiBuilder.Service.Services.ISqlMetadataProvider[0]
      Order path: /api/Order
info: Azure.DataApiBuilder.Service.Services.ISqlMetadataProvider[0]
      OrderLine path: /api/OrderLine
info: Azure.DataApiBuilder.Service.Services.ISqlMetadataProvider[0]
      Customer path: /api/Customer
fail: Azure.DataApiBuilder.Service.Startup[0]
      Unable to complete runtime initialization operations due to:
Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot obtain Schema for entity Customer with underlying database object source: Sales.Customers due to: DataReader.GetFieldType(24) returned null.
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.GetTableWithSchemaFromDataSetAsync(String entityName, String schemaName, String tableName)
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.PopulateSourceDefinitionAsync(String entityName, String schemaName, String tableName, SourceDefinition sourceDefinition, String[] runtimeConfigKeyFields)
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.PopulateObjectDefinitionForEntities()
         at Azure.DataApiBuilder.Service.Services.SqlMetadataProvider`3.InitializeAsync()
         at Azure.DataApiBuilder.Service.Startup.PerformOnConfigChangeAsync(IApplicationBuilder app)
info: Azure.DataApiBuilder.Service.Startup[0]
      Loading config file: dab-config.json
fail: Azure.DataApiBuilder.Service.Startup[0]
      Exiting the runtime engine...
crit: Microsoft.AspNetCore.Hosting.Diagnostics[6]
      Application startup exception
      System.ApplicationException: Could not initialize the engine with the runtime config file: dab-config.json
         at Azure.DataApiBuilder.Service.Startup.Configure(IApplicationBuilder app, IWebHostEnvironment env, RuntimeConfigProvider runtimeConfigProvider)
         at System.RuntimeMethodHandle.InvokeMethod(Object target, Span`1& arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
         at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
         at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
         at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder)
         at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass15_0.<UseStartup>b__1(IApplicationBuilder app)
         at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
         at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)
         at Microsoft.AspNetCore.Hosting.GenericWebHostService.StartAsync(CancellationToken cancellationToken)
Unable to launch the runtime due to: System.ApplicationException: Could not initialize the engine with the runtime config file: dab-config.json
   at Azure.DataApiBuilder.Service.Startup.Configure(IApplicationBuilder app, IWebHostEnvironment env, RuntimeConfigProvider runtimeConfigProvider)
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Span`1& arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.Invoke(Object instance, IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.ConfigureBuilder.<>c__DisplayClass4_0.<Build>b__0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass15_0.<UseStartup>b__1(IApplicationBuilder app)   at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)
   at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)
   at Microsoft.AspNetCore.Hosting.GenericWebHostService.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host)
   at Azure.DataApiBuilder.Service.Program.StartEngine(String[] args)
Error: Failed to start the engine.

Code of Conduct

  • I agree to follow this project's Code of Conduct

Activity

  1. Simone Banchieri (Banchio)
    added
    bugSomething isn't working
    triageissues to be triaged
    on Mar 31, 2023

  2. seantleonard commented on Apr 1, 2023

    @seantleonard
    Sean Leonard (seantleonard)
    on Apr 1, 2023
    Contributor

    I'm still trying to identify a cross platform solution, but the underlying cause is from:
    Sales.Customers due to: DataReader.GetFieldType(24) returned null.
    and since GetFieldType is zero index, it seems to be pointing to the column: DeliveryLocation of sql type Geography
    On Windows there are a number of stackoverflow/github discussions which allude to using Microsoft.SqlServer.Types https://www.nuget.org/packages/Microsoft.SqlServer.Types/#readme-body-tab to allow proper handling of SQL spatial types like Geography.

  3. Simone Banchieri (Banchio)
    changed the title [-][Bug]: MSSQL - Cannot obtain Schema for entity - maybe because is a System-Versioned Table?[/-] [+][Bug]: MSSQL - Cannot obtain Schema for entity - Geography data type[/+] on Apr 3, 2023

  4. Banchio commented on Apr 3, 2023

    @Banchio
    Simone Banchieri (Banchio)
    on Apr 3, 2023
    Author

    Thanks Sean Leonard (@seantleonard) , removing that field from my sample database worked. I've renamed the issue to keep track, thanks

  5. Aniruddh Munde (Aniruddh25)
    added this to Data API builderon Apr 10, 2023
  6. github-project-automation
    moved this to Todo in Data API builderon Apr 10, 2023
  7. Aniruddh Munde (Aniruddh25)
    modified the milestones: Sept2023, Aug2023 on Apr 10, 2023

  8. seantleonard commented on Jul 7, 2023

    @seantleonard
    Sean Leonard (seantleonard)
    on Jul 7, 2023
    Contributor

    Need to test out preview version of sqlclient to see behavior difference, if any. https://github.com/dotnet/SqlClient/releases/tag/v5.2.0-preview1
    with release note:

    Added Microsoft.SqlServer.Types to verify support for SqlHierarchyId and Spatial for .NET Core. dotnet/SqlClient#1848

  9. seantleonard commented on Jul 7, 2023

    @seantleonard
    Sean Leonard (seantleonard)
    on Jul 7, 2023
    Contributor

    Will also need to see how/if SQL server returns the SqlDataType of the spatial/geography types , and add compatibility flag in https://github.com/Azure/data-api-builder/pull/1568/files/9e914ab71352cbd4b2d380f0bfba35fe4ed30615#diff-d76ea4e7b6d1e701ff517cfb7fc205c60de6c749348bb126f83e69dc6bbfcf4a

  10. Sean Leonard (seantleonard)
    modified the milestones: 0.11rc, 0.12 on Nov 16, 2023
  11. Sean Leonard (seantleonard)
    removed
    triageissues to be triaged
    on Nov 16, 2023
  12. Sean Leonard (seantleonard)
    modified the milestones: 0.12rc, 1.0 on Feb 7, 2024
  13. Sean Leonard (seantleonard)
    added
    criCustomer Reported issue
    on Apr 19, 2024
  14. Sean Leonard (seantleonard)
    modified the milestones: 1.1rc, 1.2rc on Apr 29, 2024
  15. Sean Leonard (seantleonard)
    modified the milestones: 1.2rc, Feature Backlog on May 17, 2024
  16. Erik Ejlskov Jensen (ErikEJ)
    marked [Bug]: Maybe by design? #2309 as a duplicate of this issue on Feb 26, 2025

  17. blacksmiths-aali commented on Jan 27, 2026

    @blacksmiths-aali
    blacksmiths-aali
    on Jan 27, 2026

    Sorry about the "me too", but I hit exactly the same issue, trying to do exactly the same thing, some years later. I think this issue is becoming more relevant with the SQL MCP functionality in 1.7 pre-release.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingcriCustomer Reported issue

    Type

    No type

    Projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions