vertx verticle - Vert.x - run stored procedure call only when all are successful

Question

I have an issue in my Vertx application and I would appreciate if you can help me get solution.

I have a stored procedure which run in batch.

The batch printout looks like so

batch [[20.0,11,41], [20.0,11,42], [10.0,11,45], [60.0,11,46], [50.0,11,49], [50.0,11,47], [20.0,11,48], [10.0,11,43]]

The result of the stored procedure output looks like this:

Rowssss ar.result() : [0, 0, 0, 1, 1, 1, 1, 0]

The 0 are the ones which the SP ran successfully and the 1 are the one's which failed to run(qty in the table column was less or was not available.).

I want all the batch result to be 0 for the process to be successful otherwise it should not reduce the qty for anyone(in a way it should rollback table qty to previous state). Is this achievable ??

Can someone please help me to find a solution to my problem. If you need more clarification, please let me know.

public Future<Void> update_Add_Contractmfg_RawInventory_4(final SendToCompanyFromContractmfg data, JsonArray rawmaterial)
{
    Promise<Void> promise = Promise.promise();
    LOG.debug("Inside update_Add_Contractmfg_RawInventory_4 " + data.toJson().encodePrettily());

    LOG.debug("rawmaterialid " + rawmaterial.encodePrettily());
    // Timestamp jobCreationDate = Timestamp.valueOf(LocalDateTime.now());

    List<JsonArray> batch = new ArrayList<JsonArray>();
    int size = rawmaterial.size();
    LOG.debug("size " + size);

    for (int i = 0; i < size; i++)
    {
        Integer rawmaterialid = rawmaterial.getJsonObject(i).getInteger("rawmaterialid");
        Double qty = rawmaterial.getJsonObject(i).getDouble("quantity");
        LOG.debug("rawmaterial qty: " + qty);
        Double total = (data.getQty() * qty);
        LOG.debug("rawmaterial total qty: " + total);
        batch.add(new JsonArray().add(total)
                .add(data.getContractmfg())
                .add(rawmaterialid));
        //LOG.debug("BATCH " + batch);
    }

    LOG.debug("BATCH " + batch);

    String sql = "{call updQty_mfginventory(?, ?, ?,@p_result)}";
    sqlClient.getConnection(conn ->
    {
        if (conn.failed())
        {
            LOG.error("Can't get Vertx connection", conn.cause());
        } 
        else
        {
            LOG.debug(" Vertx connected for BATCH INSERT!!!!!!!!!");

            final SQLConnection connection = conn.result();

            connection.batchWithParams(sql, batch, ar ->
            {
                LOG.debug(" sql " + sql);
                LOG.debug(" batch " + batch);
                if (ar.failed())
                {
                    // Forward error
                    LOG.debug("ar.failed()--- " + ar.cause());
                    promise.fail(ar.cause());
                    return;
                }
                // Return failure if updated count is not 1
                if (ar.result().size() < 1)
                {
                    LOG.debug("Wrong update count on insert");
                    promise.fail(new IllegalStateException("Wrong update count on insert " + ar.result()));
                    return;
                }
                LOG.debug("Rowssss ar.result() : " + ar.result());
                LOG.debug("Rows Inserted : " + ar.result().size());
                // Return success
                promise.complete();
            });
            connection.close();
        }
    });

    return promise.future();
}

This the table which I am trying to manipulate. [20.0,11,41] = [quantity,contractmfgid, rawmaterialid]

Answer 1

等待大神答复