Use stored procedure MSSQL

Support
#1

Hello,
how can I exec stored procedure (from mssql) under button in screen? I can do it in cuba platform ?

1 Like
#2

Hi Marek,

Sure, you can execute a stored procedure, but you will be able to call it from the server side, so:

-Scaffold a service from the Studio

-Go to IDE and create a method definition in the service interface


public interface MssqlStoredProcedureService {
    String NAME = "mssqlexecstoredprocedure_MssqlStoredProcedureService";

    List execSpDataTypeInfo(String param);
}

-Implement the method in the corresponding service


package com.company.mssqlexecstoredprocedure.service;

import com.haulmont.cuba.core.EntityManager;
import com.haulmont.cuba.core.Persistence;
import com.haulmont.cuba.core.Transaction;
import org.springframework.stereotype.Service;

import javax.inject.Inject;
import java.util.List;

@Service(MssqlStoredProcedureService.NAME)
public class MssqlStoredProcedureServiceBean implements MssqlStoredProcedureService {

    @Inject
    private Persistence persistence;

    @Override
    public List execSpDataTypeInfo(String param) {
        List result = null;  
        try (Transaction tx = persistence.getTransaction()) {
            EntityManager em = persistence.getEntityManager();
            result = em.createNativeQuery("exec sp_datatype_info " + param).getResultList();
            tx.commit();
        }
        return result;
    }
}

-Inject the service in your screen and call it from the invocation method for a button


public class CustomerBrowse extends AbstractLookup {
    ...
    @Inject
    private MssqlStoredProcedureService mssqlStoredProcedureService;
    ...

    public void onExecStoredProcedureClick() {
        List result = mssqlStoredProcedureService.execSpDataTypeInfo("-9");
        showNotification(Integer.toString(result.size()), NotificationType.HUMANIZED);
    }    
}

Note, that if your stored procedure doesn’t return any values use the

executeUpdate

method instead of

getResultList

, otherwise you will get an exception. Also you can parse the result in your service to return a list of, for example, non-persistent entities.

Another way you could call stored procedures is:


EntityManager em = persistence.getEntityManager();
em.getDelegate().createStoredProcedureQuery(...)

Full java docs of how to use this method is available here .

Regards,

Aleksey

2 Likes
#3

Ok super. Thank you for your answer

#4

Hi. I used your code to execute a procedure and everything is fine but after exec i don’t have any change in tables (truncate, insert). Sql do it fine and i used the same SQL user in CUBA.

1 Like
#5

Slawek,

Make sure you commit the transaction:


...
try (Transaction tx = persistence.getTransaction()) {
     EntityManager em = persistence.getEntityManager();
     result = em.createNativeQuery(...).executeUpdate();
     tx.commit();
}
...

Regards,

Aleksey

2 Likes
#6

I will try it , many thanks.

#7

Hi,
I replied as reported in this article. I have an error in:

…
@Inject
private MssqlStoredProcedureService mssqlStoredProcedureService;
…

It does not solve inject, can you help me ?
Thanks
Giovanni

#8

Hi Aleksey, i’m trying to use your code. But when i run all and i click on the button i get the follow error:
“IllegalArgumentException: Service app-core/untitled5_MssqlStoredProcedureService is not registered in LocalServiceDirectory”
What it means? How can risolve it?
Thanks in advance.

#9

Could you please provide a sample project which reproduces the problem?

#10

Sorry, but I can’t do it. I need to run a stored procedure sited in my database. And I follow your instructions:

  • interface
  • bean
  • onClick

I have to do something to “register” my service? I run the app in Cuba Studio when i get the error. I need to deploy something? I have to copy files from/to particular folder?

If you have a complete example to execute stored procedure in MSSQL Server , i can try to run it.
Thanks.

#11

I solved the previous error. The error is the NAME in the interface. Sorry.

1 Like