Best way to call Oracle Stored Procedure with arguments that updates database

Support
#1

I am re-writing an old Oracle Forms application in Cuba as a proof of concept. The application has a button that calls an Oracle Stored Procedure that inserts a bunch of rows based on the arguments. I want to create a button that:

  1. Pops up a dialog for the arguments and contains an Execute button
  2. When Execute is pressed, the arguments are passed to the stored procedure and it runs
  3. The Cuba ORM is refreshed with the new data inserted by the stored procedure
  4. The web client shows the new rows loaded into the ORM

I’m new at writing JQPL/JPA code, so any/all advice or references are appreciated.

#2

Sorry to answer my own post, but I got it to work. I’m posting in case it will help someone else. Note that I gave up on the dialog box, as I didn’t need it (Step 1 above).

  1. Create a service to execute the stored procedure on the middle tier. Note that the service needs to create a transaction in order to access the EntityManager

/*
 * (c) 2016 Professional Advertising Systems Inc.
 */
package com.company.schweitzer.service;

import com.company.schweitzer.entity.SchMlgPkg;
import com.haulmont.cuba.core.EntityManager;
import com.haulmont.cuba.core.Persistence;
import com.haulmont.cuba.core.Query;
import com.haulmont.cuba.core.Transaction;
import org.eclipse.persistence.exceptions.JPQLException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.inject.Inject;

@Service(LoadKeysService.NAME)
public class LoadKeysServiceBean implements LoadKeysService {

    @Inject
    Persistence persistence;

    private Logger log = LoggerFactory.getLogger(LoadKeysService.class);

    public void loadKeys(SchMlgPkg schMlgPkg) {

        Transaction tx = persistence.createTransaction();
        EntityManager em = persistence.getEntityManager();

        Long pasnum = schMlgPkg.getPasnum();
        Long rekey = schMlgPkg.getRekeystep();
        Long outfile = schMlgPkg.getOutfilenum();

        Query storedProc = em.createNativeQuery("{call schresp.loadkeys(?,?,?)}")
                                .setParameter(1,pasnum)
                                .setParameter(2,rekey)
                                .setParameter(3,outfile);
        try {
            storedProc.executeUpdate();
            tx.commit();
        }
        catch (JPQLException e) {
            log.error("Can't execute schresp.loadkeys: " + e.getMessage());
            throw e;
        }
        finally {
            tx.end();
        }

      }
}
  1. On the web tier, create an action for my button. This code accesses my DataSource via injection to get the current row and passes a piece of data (the “pasnum” field) to the service. When the service returns, it calls refresh() to update the screen with the data that the stored procedure added to the database.

package com.company.schweitzer.web.schmlgpkg;

import com.company.schweitzer.entity.SchMlgPkg;
import com.company.schweitzer.service.LoadKeysService;
import com.haulmont.cuba.gui.components.AbstractLookup;
import com.haulmont.cuba.gui.components.Component;
import com.haulmont.cuba.gui.data.DataSupplier;
import com.haulmont.cuba.gui.data.Datasource;
import org.eclipse.persistence.exceptions.JPQLException;

import javax.inject.Inject;
import javax.inject.Named;

public class SchMlgPkgBrowse extends AbstractLookup {

    @Inject
    LoadKeysService loadKeysService;

    @Inject
    private Datasource<SchMlgPkg> schMlgPkgsDs;

    public void onLoadKeys(Component source) {

        SchMlgPkg schMlgPkg = (SchMlgPkg) schMlgPkgsDs.getItem();
        try {
            loadKeysService.loadKeys(schMlgPkg);
            schMlgPkgsDs.refresh();
        }
        catch (JPQLException e) {
            showNotification("Load Keys Failed: " + e.getMessage());
        }
    }
}

If I’ve done anything wrong or anybody has a better way, please let me know!

1 Like
#3

Thank you for sharing the solution!

I would just recommend opening the try-finally block right after the transaction start:


Transaction tx = persistence.createTransaction();
try {
    EntityManager em = persistence.getEntityManager();
...
} finally {
    tx.end();
}

For those who interested in using stored procedures, see also this topic: Use stored procedure MSSQL - CUBA.Platform

#4

Sorry for the bad formatting. Can’t find a link to edit this! :frowning:

#5

Thanks. I have made the change you suggested.