dab error with stored procedure parameter and graphql

[camaro322hp]

Hello all. I hope this is the correct place to ask questions like this.

I am trying to set up a graphql endpoint that will execute a stored procedure with parameters. I'm stuck at the following error:

To me, that appears as if dab is trying to convert the word "number" to an int. Unsure of why that would be the case. Just for grins, I tried changing the word "number" in the config file to just "n" and the error message then showed "n" in the two places it shows "number" now. Here is the applicable portion of my dab-config.json:

As you can see, I've defined my parameter id as a number type, which I believe is correct as specified in this documentation.

The rest call to this procedure works fine and returns the expected result (http://localhost:5000/api/proc_books_get_by_id?id=1016). It's only the graphql that is not working.

Am I doing something wrong here?

EDIT: forgot to mention, I'm not even getting far enough to call the stored procedure in graphql, this error is simply the result of Postman trying to read the schema from http://localhost:5000/graphql.

[RubenCerna2079]

Hello @camaro322hp your error is not related to the way you defined the parameter id as a number type. Would you be able to provide the complete error message that you are getting so that we can help you further?

[camaro322hp]

Hi, @RubenCerna2079. Thanks for your assistance. Below is the full error message:

fail: Microsoft.AspNetCore.Server.Kestrel[13]
      Connection id "0HNARF4DR4N1H", Request id "0HNARF4DR4N1H:00000006": An unhandled exception was thrown by the application.
      Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: The parameter value number provided in configuration cannot be converted to the type Int
       ---> System.FormatException: The input string 'number' was not in a correct format.
         at System.Number.ThrowFormatException[TChar](ReadOnlySpan`1 value)
         at System.Int32.Parse(String s)
         at Azure.DataApiBuilder.Service.GraphQLBuilder.GraphQLStoredProcedureBuilder.ConvertValueToGraphQLType(String defaultValueFromConfig, ParameterDefinition parameterDefinition) in /_/src/Service.GraphQLBuilder/GraphQLStoredProcedureBuilder.cs:line 155
         --- End of inner exception stack trace ---
         at Azure.DataApiBuilder.Service.GraphQLBuilder.GraphQLStoredProcedureBuilder.ConvertValueToGraphQLType(String defaultValueFromConfig, ParameterDefinition parameterDefinition) in /_/src/Service.GraphQLBuilder/GraphQLStoredProcedureBuilder.cs:line 173
         at Azure.DataApiBuilder.Service.GraphQLBuilder.GraphQLStoredProcedureBuilder.GenerateStoredProcedureSchema(NameNode name, Entity entity, DatabaseObject dbObject, IEnumerable`1 rolesAllowed) in /_/src/Service.GraphQLBuilder/GraphQLStoredProcedureBuilder.cs:line 61
         at Azure.DataApiBuilder.Service.GraphQLBuilder.Queries.QueryBuilder.Build(DocumentNode root, Dictionary`2 databaseTypes, RuntimeEntities entities, Dictionary`2 inputTypes, Dictionary`2 entityPermissionsMap, Dictionary`2 dbObjects) in /_/src/Service.GraphQLBuilder/Queries/QueryBuilder.cs:line 69
         at Azure.DataApiBuilder.Core.Services.GraphQLSchemaCreator.GenerateQueryAndMutationNodes(DocumentNode root, Dictionary`2 inputTypes) in /_/src/Core/Services/GraphQLSchemaCreator.cs:line 155
         at Azure.DataApiBuilder.Core.Services.GraphQLSchemaCreator.Parse(ISchemaBuilder sb, DocumentNode root, Dictionary`2 inputTypes) in /_/src/Core/Services/GraphQLSchemaCreator.cs:line 103
         at Azure.DataApiBuilder.Core.Services.GraphQLSchemaCreator.InitializeSchemaAndResolvers(ISchemaBuilder schemaBuilder) in /_/src/Core/Services/GraphQLSchemaCreator.cs:line 173
         at Azure.DataApiBuilder.Service.Startup.<>c.<AddGraphQLService>b__17_0(IServiceProvider serviceProvider, ISchemaBuilder schemaBuilder) in /_/src/Service/Startup.cs:line 283
         at HotChocolate.Execution.RequestExecutorResolver.CreateSchemaAsync(NameString schemaName, RequestExecutorSetup options, RequestExecutorOptions executorOptions, IServiceProvider serviceProvider, TypeModuleChangeMonitor typeModuleChangeMonitor, CancellationToken cancellationToken)
         at HotChocolate.Execution.RequestExecutorResolver.CreateSchemaServicesAsync(NameString schemaName, RequestExecutorSetup options, CancellationToken cancellationToken)
         at HotChocolate.Execution.RequestExecutorResolver.GetRequestExecutorNoLockAsync(NameString schemaName, CancellationToken cancellationToken)
         at HotChocolate.Execution.RequestExecutorResolver.GetRequestExecutorAsync(NameString schemaName, CancellationToken cancellationToken)
         at HotChocolate.Execution.RequestExecutorProxy.GetRequestExecutorAsync(CancellationToken cancellationToken)
         at HotChocolate.AspNetCore.HttpPostMiddlewareBase.HandleRequestAsync(HttpContext context, AllowedContentType contentType)
         at HotChocolate.AspNetCore.HttpPostMiddlewareBase.InvokeAsync(HttpContext context)
         at Microsoft.AspNetCore.Builder.EndpointRouteBuilderExtensions.<>c__DisplayClass12_0.<<UseCancellation>b__1>d.MoveNext()
      --- End of stack trace from previous location ---
         at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|7_0(Endpoint endpoint, Task requestTask, ILogger logger)
         at Azure.DataApiBuilder.Core.Authorization.ClientRoleHeaderAuthorizationMiddleware.Invoke(HttpContext httpContext) in /_/src/Core/Authorization/ClientRoleHeaderAuthorizationMiddleware.cs:line 35
         at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
         at Azure.DataApiBuilder.Core.AuthenticationHelpers.ClientRoleHeaderAuthenticationMiddleware.InvokeAsync(HttpContext httpContext) in /_/src/Core/AuthenticationHelpers/ClientRoleHeaderAuthenticationMiddleware.cs:line 158
         at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
         at Azure.DataApiBuilder.Service.Startup.<>c__DisplayClass19_0.<<Configure>b__3>d.MoveNext() in /_/src/Service/Startup.cs:line 431
      --- End of stack trace from previous location ---
         at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
         at Azure.DataApiBuilder.Core.Services.PathRewriteMiddleware.InvokeAsync(HttpContext httpContext) in /_/src/Core/Services/PathRewriteMiddleware.cs:line 89
         at Azure.DataApiBuilder.Core.Services.CorrelationIdMiddleware.Invoke(HttpContext httpContext) in /_/src/Core/Services/CorrelationIdMiddleware.cs:line 53
         at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)

If it helps, here is my complete dab-config.json:

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.4.26/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "connection-string": "@env('dab_sql_connection_string')",
    "options": {
      "set-session-context": false
    }
  },
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/api",
      "request-body-strict": true
    },
    "graphql": {
      "enabled": true,
      "path": "/graphql",
      "allow-introspection": true
    },
    "host": {
      "cors": {
        "origins": [],
        "allow-credentials": false
      },
      "authentication": {
        "provider": "StaticWebApps"
      },
      "mode": "development"
    }
  },
  "entities": {
    "Author": {
      "source": {
        "object": "dbo.authors",
        "type": "table"
      },
      "graphql": {
        "enabled": true,
        "type": {
          "singular": "Author",
          "plural": "Authors"
        }
      },
      "rest": {
        "enabled": true
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "*"
            }
          ]
        }
      ]
    },
    "Book": {
      "source": {
        "object": "dbo.books",
        "type": "table"
      },
      "graphql": {
        "enabled": true,
        "type": {
          "singular": "Book",
          "plural": "Books"
        }
      },
      "rest": {
        "enabled": true
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "*"
            }
          ]
        }
      ]
    },
    "proc_books_get_top_1": {
      "source": {
        "object": "dbo.proc_books_get_top_1",
        "type": "stored-procedure"
      },
      "graphql": {
        "enabled": true,
        "operation": "query",
        "type": {
          "singular": "proc_books_get_top_1",
          "plural": "proc_books_get_top_1s"
        }
      },
      "rest": {
        "enabled": true,
        "methods": [
          "get"
        ]
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "execute"
            }
          ]
        }
      ]
    },
    "proc_books_get_by_id": {
      "source": {
        "object": "dbo.proc_books_get_by_id",
        "type": "stored-procedure",
        "parameters": {
          "id": "number"
        }
      },
      "graphql": {
        "enabled": true,
        "operation": "query",
        "type": {
          "singular": "proc_books_get_by_id",
          "plural": "proc_books_get_by_ids"
        }
      },
      "rest": {
        "enabled": true,
        "methods": [
          "get"
        ]
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "execute"
            }
          ]
        }
      ]
    }
  }
}

[RubenCerna2079]

Hi @camaro322hp, I was able to replicate the issue.
The problem is that you need to need to write a specific number inside of the parameter not "number".
An example of this (if it doesn't help, let me know):

"parameters": {
  "title": "randomX",      //This is "string"
  "publisher_id": 1234     //This is "number"
}

[camaro322hp]

Thank you @RubenCerna2079 that solves the issue.

[camaro322hp]

I updated the documentation to reflect this in the following pull request: MicrosoftDocs/data-api-builder-docs#23