Stored Procedures Parametres are incorrectly mapped

#1

Hi,

You have the parameters incorrectly mapped, they are in alphabetical order and not in the original order of the SP.

Example:
Create PROCEDURE [dbo].[usp_DTMSDroneAddEdit]
@ID_DTMS nvarchar(50) -- cast de id to nvarchar from origin
,@DroneModel nvarchar(120)
,@DroneManufacturer nvarchar(120)
,@DroneSerialNumber nvarchar(80)
,@TrackID nvarchar(50)
,@DroneType nvarchar(50)
,@ExtraHTML nvarchar(4000)
AS
BEGIN

Declare @returntable as table( ID_DTMS nvarchar(50) -- cast de id to nvarchar from origin
,DroneModel nvarchar(120)
,DroneManufacturer nvarchar(120)  
,DroneSerialNumber nvarchar(80)
,TrackID nvarchar(50)
,DroneType nvarchar(50)
,ExtraHTML nvarchar(4000))

	insert into @returntable values( @ID_DTMS ,@DroneModel,@DroneManufacturer,@DroneSerialNumber,@TrackID,@DroneType,@ExtraHTML)

	select  ID_DTMS	,DroneModel	,DroneManufacturer,DroneSerialNumber,TrackID,DroneType,ExtraHTML from  @returntable

END

URL Call: http://localhost:5000/odata/Hub/UspDtmsDroneAddEditsFunc(DroneManufacturer='1',DroneModel='XPTo',DroneSerialNumber='teste123',DroneType='Fixed-Wings',ExtraHTML='1111',ID_DTMS='9',TrackID='1234567890')

Response: {"@odata.context":"http://localhost:5000/odata/Hub/$metadata#UspDtmsDroneAddEdits","value":[{"ID_DTMS":"1","DroneModel":"XPTo","DroneManufacturer":"teste123","DroneSerialNumber":"Fixed-Wings","TrackID":"1111","DroneType":"9","ExtraHTML":"1234567890"}]}

Auto-Generated Class:
...
[HttpGet]
[EnableQuery(MaxExpansionDepth=10,MaxNodeCount=1000)]
[ODataRoute("UspDtmsDroneAddEditsFunc(DroneManufacturer={DroneManufacturer},DroneModel={DroneModel},DroneSerialNumber={DroneSerialNumber},DroneType={DroneType},ExtraHTML={ExtraHTML},ID_DTMS={ID_DTMS},TrackID={TrackID})")]
public IActionResult UspDtmsDroneAddEditsFunc([FromODataUri] string DroneManufacturer, [FromODataUri] string DroneModel, [FromODataUri] string DroneSerialNumber, [FromODataUri] string DroneType, [FromODataUri] string ExtraHTML, [FromODataUri] string ID_DTMS, [FromODataUri] string TrackID)
{
this.OnUspDtmsDroneAddEditsDefaultParams(ref DroneManufacturer, ref DroneModel, ref DroneSerialNumber, ref DroneType, ref ExtraHTML, ref ID_DTMS, ref TrackID);

    var items = this.context.UspDtmsDroneAddEdits.AsNoTracking().FromSql("EXEC [dbo].[usp_DTMSDroneAddEdit] {0}, {1}, {2}, {3}, {4}, {5}, {6}", DroneManufacturer, DroneModel, DroneSerialNumber, DroneType, ExtraHTML, ID_DTMS, TrackID);

    this.OnUspDtmsDroneAddEditsInvoke(ref items);

    return Ok(items);
}

...

Error: EXEC [dbo].[usp_DTMSDroneAddEdit] {0}, {1}, {2}, {3}, {4}, {5}, {6}", DroneManufacturer, DroneModel, DroneSerialNumber, DroneType, ExtraHTML, ID_DTMS, TrackID

0 Likes

#2

Thanks for the report! We will release fix later today.

0 Likes

#3

We just released Radzen 2.18.3.

0 Likes

#4

And it did not solve the problem. Or you out the parameters in the same order as DB or add to the 'Exec stprocedurename @var0name={0}, @var1name={1} ...", var0,var1,..

0 Likes

#5

We will check again to see what’s going on

0 Likes

#6

Hi,

I just tested your stored procedure again with Radzen 2.18.3 and here is the result:

The procedure parameters are:

Create PROCEDURE [dbo].[usp_TesteParametrsOrder] 
(1) @ID_DTMS nvarchar(50)
(2), @TrackID nvarchar(50)
(3), @DroneManufacturer nvarchar(120)  
(4), @ExtraHTML nvarchar(4000)
(5), @DroneModel nvarchar(120)
(6), @DroneSerialNumber nvarchar(80)
(7), @DroneType nvarchar(50)
(8), @ExtraHTMLInHex bit

Parameters are retrieved from Radzen in exactly the same order and saved in meta data-source definition:

"paths": {
    "/UspTesteParametrsOrdersFunc(ID_DTMS='{ID_DTMS}',TrackID='{TrackID}',DroneManufacturer='{DroneManufacturer}',ExtraHTML='{ExtraHTML}',DroneModel='{DroneModel}',DroneSerialNumber='{DroneSerialNumber}',DroneType='{DroneType}',ExtraHTMLInHex={ExtraHTMLInHex})": {
      "get": {
        "operationId": "getUspTesteParametrsOrders",
        "parameters": [
          {
            "in": "path",
(1)            "name": "ID_DTMS",
            "type": "string"
          },
          {
            "in": "path",
(3)            "name": "TrackID",
            "type": "string"
          },
          {
            "in": "path",
(3)            "name": "DroneManufacturer",
            "type": "string"
          },
          {
            "in": "path",
(4)            "name": "ExtraHTML",
            "type": "string"
          },
          {
            "in": "path",
(5)            "name": "DroneModel",
            "type": "string"
          },
          {
            "in": "path",
(6)            "name": "DroneSerialNumber",
            "type": "string"
          },
          {
            "in": "path",
(7)            "name": "DroneType",
            "type": "string"
          },
          {
            "in": "path",
(8)            "name": "ExtraHTMLInHex",
            "type": "boolean"
          },
          ...
        ]

The generated code is:

    [HttpGet]
    [EnableQuery(MaxExpansionDepth=10,MaxNodeCount=1000)]
    [ODataRoute("UspTesteParametrsOrdersFunc(ID_DTMS={ID_DTMS},TrackID={TrackID},DroneManufacturer={DroneManufacturer},ExtraHTML={ExtraHTML},DroneModel={DroneModel},DroneSerialNumber={DroneSerialNumber},DroneType={DroneType},ExtraHTMLInHex={ExtraHTMLInHex})")]
    public IActionResult UspTesteParametrsOrdersFunc([FromODataUri] string ID_DTMS, [FromODataUri] string TrackID, [FromODataUri] string DroneManufacturer, [FromODataUri] string ExtraHTML, [FromODataUri] string DroneModel, [FromODataUri] string DroneSerialNumber, [FromODataUri] string DroneType, [FromODataUri] bool ExtraHTMLInHex)
    {
        this.OnUspTesteParametrsOrdersDefaultParams(ref ID_DTMS, ref TrackID, ref DroneManufacturer, ref ExtraHTML, ref DroneModel, ref DroneSerialNumber, ref DroneType, ref ExtraHTMLInHex);

        var items = this.context.UspTesteParametrsOrders.AsNoTracking().FromSql("EXEC [dbo].[usp_TesteParametrsOrder] {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", ID_DTMS, TrackID, DroneManufacturer, ExtraHTML, DroneModel, DroneSerialNumber, DroneType, ExtraHTMLInHex);

        this.OnUspTesteParametrsOrdersInvoke(ref items);

        return Ok(items);
    }

Exactly in the same order as in the database.

Do you have different result?

Here is how I've executed the procedure:

0 Likes

#7

In my case its differente:

SP parameters:

ALTER PROCEDURE [dbo].[usp_DTMSDroneAddEdit]
@ID_DTMS nvarchar(50)
, @TrackID nvarchar(50)
, @DroneManufacturer nvarchar(120)
, @ExtraHTML nvarchar(4000)
, @DroneModel nvarchar(120)
, @DroneSerialNumber nvarchar(80)
, @DroneType nvarchar(50)
, @ExtraHTMLInHex bit

Parameters retrives from Radzen:
"paths": {
"/UspDtmsDroneAddEditsFunc(DroneManufacturer='{DroneManufacturer}',DroneModel='{DroneModel}',DroneSerialNumber='{DroneSerialNumber}',DroneType='{DroneType}',ExtraHTML='{ExtraHTML}',ExtraHTMLInHex={ExtraHTMLInHex},ID_DTMS='{ID_DTMS}',TrackID='{TrackID}')": {
"get": {
"operationId": "getUspDtmsDroneAddEdits",
"parameters": [
{
"in": "path",
"name": "DroneManufacturer",
"type": "string"
},
{
"in": "path",
"name": "DroneModel",
"type": "string"
},
{
"in": "path",
"name": "DroneSerialNumber",
"type": "string"
},
{
"in": "path",
"name": "DroneType",
"type": "string"
},
{
"in": "path",
"name": "ExtraHTML",
"type": "string"
},
{
"in": "path",
"name": "ExtraHTMLInHex",
"type": "boolean"
},
{
"in": "path",
"name": "ID_DTMS",
"type": "string"
},
{
"in": "path",
"name": "TrackID",
"type": "string"
},
{
"in": "query",
"name": "$filter",
"type": "string"
},
{
"in": "query",
"name": "$top",
"type": "integer"
},
{
"in": "query",
"name": "$skip",
"type": "integer"
},
{
"in": "query",
"name": "$orderby",
"type": "string"
},
{
"in": "query",
"name": "$count",
"type": "boolean"
},
{
"in": "query",
"name": "$expand",
"type": "string"
},
{
"in": "query",
"name": "$format",
"type": "string"
},
{
"in": "query",
"name": "$select",
"type": "string"
}
],

The generated code is:

[HttpGet]
[EnableQuery(MaxExpansionDepth=10,MaxNodeCount=1000)]
[ODataRoute("UspDtmsDroneAddEditsFunc(DroneManufacturer={DroneManufacturer},DroneModel={DroneModel},DroneSerialNumber={DroneSerialNumber},DroneType={DroneType},ExtraHTML={ExtraHTML},ExtraHTMLInHex={ExtraHTMLInHex},ID_DTMS={ID_DTMS},TrackID={TrackID})")]
public IActionResult UspDtmsDroneAddEditsFunc([FromODataUri] string DroneManufacturer, [FromODataUri] string DroneModel, [FromODataUri] string DroneSerialNumber, [FromODataUri] string DroneType, [FromODataUri] string ExtraHTML, [FromODataUri] bool ExtraHTMLInHex, [FromODataUri] string ID_DTMS, [FromODataUri] string TrackID)
{
this.OnUspDtmsDroneAddEditsDefaultParams(ref DroneManufacturer, ref DroneModel, ref DroneSerialNumber, ref DroneType, ref ExtraHTML, ref ExtraHTMLInHex, ref ID_DTMS, ref TrackID);

    var items = this.context.UspDtmsDroneAddEdits.AsNoTracking().FromSql("EXEC [dbo].[usp_DTMSDroneAddEdit] {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", DroneManufacturer, DroneModel, DroneSerialNumber, DroneType, ExtraHTML, ExtraHTMLInHex, ID_DTMS, TrackID);

    this.OnUspDtmsDroneAddEditsInvoke(ref items);

    return Ok(items);
}

0 Likes

#8

What version is your SQL server? Can you try to execute the following SQL script and post here the result?

SELECT SCHEMA_NAME(SCHEMA_ID) AS 'schema',
      OBJECT_SCHEMA_NAME(so.object_id) + '.' + so.name AS 'procedureName',
      p.parameter_id AS 'parameterId',
      p.name AS 'name',
      TYPE_NAME(p.user_type_id) AS 'type',
      p.max_length AS 'length',
      p.is_output as 'isOutput'
      FROM sys.objects AS so
      INNER JOIN sys.parameters AS p
      ON so.object_id = p.object_id
      WHERE so.object_id IN ( SELECT object_id
      FROM sys.objects
      WHERE TYPE IN ('P','FN'))
0 Likes

#9

Hi,

I was able to reproduce different order of SP parameters using the SQL script from my previous reply on different versions of MS SQL Server. I'm afraid that we cannot control this and the only option in my opinion is to infer/create Radzen MSSQL data-source using the same SQL server version as the app in production.

Best Regards,
Vladimir

0 Likes

#10

Sorry for the delay,

the result of the query was:

dbo dbo.usp_DTMSDroneAddEdit 1 @ID_DTMS nvarchar 100 0
dbo dbo.usp_DTMSDroneAddEdit 2 @TrackID nvarchar 100 0
dbo dbo.usp_DTMSDroneAddEdit 3 @DroneManufacturer nvarchar 240 0
dbo dbo.usp_DTMSDroneAddEdit 4 @ExtraHTML nvarchar 8000 0
dbo dbo.usp_DTMSDroneAddEdit 5 @DroneModel nvarchar 240 0
dbo dbo.usp_DTMSDroneAddEdit 6 @DroneSerialNumber nvarchar 160 0
dbo dbo.usp_DTMSDroneAddEdit 7 @DroneType nvarchar 100 0
dbo dbo.usp_DTMSDroneAddEdit 8 @ExtraHTMLInHex bit 1 0

The sersion of sql server is : 11.0.6020.0

Relative to the last post if you add to the string in the generated code the variable in the order that radzen receives for example :

var items = this.context.UspDtmsDroneAddEdits.AsNoTracking().FromSql("EXEC [dbo].[usp_DTMSDroneAddEdit] @DroneManufacturer={0}, @DroneModel={1}, @DroneSerialNumber={2}, @DroneType={3}, @ExtraHTML={4}, @ExtraHTMLInHex={5}, @ID_DTMS={6}, @TrackID={7}", DroneManufacturer, DroneModel, DroneSerialNumber, DroneType, ExtraHTML, ExtraHTMLInHex, ID_DTMS, TrackID);

this would solve the problem of sql server versions.

1 Like

#11

Yep, named parameters should solve this! We will release later today fix.

0 Likes

#12

New Version working ok.

0 Likes