Hi, How do we execute a stored procedure in lb4?

[sureshkodur]

No description provided.

[sureshkodur]

Hi , I'm trying to execute stored procedure from MSSQL.

constructor(
    @repository(MyRepository)
    public myRepo: MyRepository,
  ) {
    this.user_Profile = userProfile;
  }
 async testData(
  ): Promise<any> {
    return await this.myRepo.dataSource.execute(`CALL Get_Customer(?)`, ['CUSTID01']);
  }

I'm getting

500 RequestError: Could not find stored procedure 'CALL'.

Any Help?

#ref link

[raymondfeng]

Try to use DEBUG=loopback:connector:mssql to print out the SQL statement being executed and see if it's good.

[raymondfeng]

See https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017. You probably need to use EXEC instead of CALL.

[sureshkodur]

@raymondfeng , I used EXEC instead of CALL. I'm getting

[dhmlau]

Cross posting my comment in #3798 (comment)

@1057437122, inspired by the example from #3459 (comment). I've added one more endpoint / function in the controller so that it's easier to test.

 @get('/test', {
   responses: {
     '200': {
       description: 'test for executing query',
     },
   },
 })
 async test(): Promise<any> {
   return this.customerRepository.dataSource.execute('SELECT * FROM CUSTOMER');
 }

Please note that the command you put as the parameter are specific to the database you're calling. I'm using Postgresql, but the above example is simple enough that is probably working for other relational database.

It seems like there's issue with the command you're trying to execute. I'm not familiar with how MSSQL syntax work, so not sure what could go wrong. I'd suggest you run this outside your LoopBack application first to better isolate the problem. Hope it helps.

With that, I'd like to close this issue. If you encounter other issues, feel free to open a new ticket. Thanks!

[kanusoni]

@ dhmalu The above code is working fine.
but how we can create an API to get the list of stored procedure and to execute the stored procedure of MySQL database in loopback4 GUI explorer.