While updating record by id using repository throwing ROWCOUNT error

[dineshkuncham7]

Steps to reproduce

my model looks like these

@model({
  settings: {
    'mssql': {schema: `boim`, table: `test`},
    strict: true,
  },
})
export class TestModel extends Entity {
  @property({
    type: 'number',
    id: true,
    generated:true,
  })
  id?: number;

  @property({
    type: 'date',
    required: true,
  })
  date_created: string;

  @property({
    type: 'date',
  })
  start_date?: string;

  @property({
    type: 'date',
  })
  end_date?: string;

  @property({
    type: 'string',
  })
  charge?: string;

  @property({
    type: 'string',
  })
  param?: string;

  @property({
    type: 'string',
  })
  output?: string;

  @property({
    type: 'string',
  })
 response?: string;

  @property({
    type: 'string',
  })
  status?: string | null;
@property({
    type: 'boolean',
  })
  working?: boolean;
  
  constructor(data?: Partial<TestModel>) {
    super(data);
  }
}

export interface TestModelRelations {
  // describe navigational properties here
}

export type TestModelWithRelations = TestModel & TestModelRelations;

My repository

export class TestModelRepository extends DefaultCrudRepository<
  TestModel,
  typeof TestModel.prototype.id,
  TestModelRelations
> {
  constructor(
    @inject('datasources.msSqlDs') dataSource: MsSqlDsDataSource,
  ) {
    super(TestModel, dataSource);
  }
}

in my controller

constructor(
    @inject(RestBindings.Http.RESPONSE) private response: Response,
    @repository(TestModelRepository)
    private testModelRepository: TestModelRepository,
  ) {}

I have generated my application using lb4 cli

Current Behavior

When I use updateById() (e.g. this.testModelRepository.updateById(id,{working:true})), I am getting the Must declare the scalar variable "@@ROWCOUNT" error. I tried to print the SQL query that is generated by changing debug settings and I got this in the log:

loopback:connector:mssql SQL: UPDATE [boim].[test] SET [working]=@param1 WHERE [id]=@param2;SELECT @@ROWCOUNT as count Parameters: [true,987] +4s
  loopback:connector:mssql Result: {"code":"EREQUEST","originalError":{"info":{"name":"ERROR","event":"errorMessage","number":137,"state":1,"class":16,"message":"Must declare the scalar variable \"@@ROWCOUNT\".","serverName":"mydatabase","procName":"","lineNumber":1}},"name":"RequestError","number":137,"lineNumber":1,"state":1,"class":16,"serverName":"mydatabase","procName":"","precedingErrors":[]} undefined +2s

Expected Behavior

Link to reproduction sandbox

Additional information

darwin x64 10.16.3

npm ls --prod --depth 0 | grep loopback ├── @loopback/[email protected] ├── @loopback/[email protected] ├── @loopback/[email protected] ├── @loopback/[email protected] ├── @loopback/[email protected] ├── @loopback/[email protected] ├── [email protected] ├── [email protected] -->

Related Issues

See Reporting Issues for more tips on writing good issues

[achrinza]

Hi @dineshkuncham7,

1. As per-the issue template, could you please also provide the output of these commands and put it under "Additonal information"?

   node -e 'console.log(process.platform, process.arch, process.versions.node)' npm ls --prod --depth 0 | grep loopback

2. Could you also please provide what's the value of the variable id in this.testModelRepository.updateById(id, {working:true})?

[achrinza]

npm ls --prod --depth 0 | grep loopback --> no output for this command -->

This command should be executed at the LB4 project root (same level as package.json) and should provide an output of the installed LB4 dependencies.

[dineshkuncham7]

Hi, I have updated the additional informations. As for the value of 'id' it is a number i am getting from POST request. In this case it is 987 as we can see in the log

[achrinza]

Unfortunately I've not been able to replicate the issue from my end using the model you've provided (tested with MSSQL 2019). Are you able to provide a minimum-reproducable GitHub repo, and/or provide a sequence of steps to reproduce the issue?

[dineshkuncham7]

The column 'id' is an identity column in MSSQL, have you tried it that way?. Unfortunately i can't provide Github repo, in general what might me the issue?

[achrinza]

I've generated the table using npm run migrate -- --rebuild. Was the table generated outside of LoopBack 4? If so, would it be possible to provide the table schema?

[dineshkuncham7]

CREATE TABLE boim.test ( id int NOT NULL, date_created datetime NOT NULL, start_date datetime NULL, end_date datetime NULL, charge nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, param nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, output nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, response nvarchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL, status varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, working bit NULL, )

here column 'id' is identity column

[achrinza]

Thanks for providing the SQL command.

Unfortunately I'm still unable to replicate the issue from my end.

This is the SQL command used for setting up the database:

-- Drop any previous instance
DROP TABLE test;

CREATE TABLE test (
id int IDENTITY(1,1),
date_created datetime NOT NULL,
start_date datetime NULL,
end_date datetime NULL,
charge nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
param nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
output nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
response nvarchar COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
status varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
working bit NULL,
);

-- Allow custom `id` value
SET IDENTITY_INSERT test on;

-- Insert test value
INSERT INTO test (id, date_created) VALUES (987, '2021-01-01');

-- Show the inserted data
SELECT * FROM test;

Here's my SQL Server version:

@@VERSION
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) 	Sep 23 2020 16:03:08 	Copyright (C) 2019 Microsoft Corporation	Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>

[dineshkuncham7]

I don't understand, can you try with the create script I have posted as it is ( I am sure it doesn't make difference but still) . You no need to insert the same value as I used, let the sql decide the id value. After creating the table insert the record with test data then try to update any field with loopback. Actually I am using MSSQL in azure sql datawarehouse. Here is my version: Microsoft Azure SQL Data Warehouse - 10.0.15543.0 Nov 13 2020 00:37:56 Copyright (c) Microsoft Corporation.

While I am trying to update using loopback4 why it is executing 'SELECT @@ROWCOUNT as count' this query? Is it becasue of data warehouse?

[dineshkuncham7]

Any update on this? I created table with the SQL script you provided, I am getting the same error can you tell me what am I missing?

[achrinza]

Sorry for the delayed reply; After some internet sleuthing, it seems like the @@ROWCOUNT function is not supported in Azure Synapse Analytics (formerly Azure Data Warehouse):

https://feedback.azure.com/forums/307516-azure-synapse-analytics/suggestions/14889516--rowcount-support

I was able to find an article that shows a workaround T-SQL query, which referenced to the (now deleted) Microsoft Docs article.

Unfortunately none of this is a immediate solution to solve the problem as we'll need to implement this into the connector. One of the problems arising from this is that, AFAIK, there's no way to test against Azure Synapse Analytics in the LB4 CI pipeline, hence maintaining support for may prove to be difficult.

[achrinza]

Re-opening as Azure Credits for Open Source Projects was announced, hence providing a possibility for us to test against Azure Synapse Analytics.