Add support for routines

[nixos89]

Hi @jklingsporn , I'm having issue with performing select query on generated Routine i.e. user-defined PL/pgSQL function (which returns JSON type) inside of transaction() method in my project which is using vertx-jooq-classic-reactive 5.1.1 implementation.

All jOOQ code-generation has been successfully executed when building Maven project, but problem is when I try to invoke my generated Routine this way (by Official jOOQ User Manual):

Future<JsonObject> ordersFuture = queryExecutor.transaction(transactionQE -> transactionQE
                .query(dsl -> dsl
                    .select(Routines.getAllOrders())
            )); 

...I get these errors during compile-time in Eclipse IDE:

"Type mismatch: cannot convert from Future < Object > to Future < JsonObject > "

"Type mismatch: cannot convert from Future < QueryResult > to Future < Object > "

This is my Routines.java class code

/**
 * Convenience access to all stored procedures and functions in public
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Routines {

    /**
     * Call <code>public.get_all_orders</code>
     */
    public static JSON getAllOrders(Configuration configuration) {
        GetAllOrders f = new GetAllOrders();

        f.execute(configuration);
        return f.getReturnValue();
    }

    /**
     * Get <code>public.get_all_orders</code> as a field.
     */
    public static Field<JSON> getAllOrders() {
        GetAllOrders f = new GetAllOrders();

        return f.asField();
    }

// other methods left out for code brevity
}

...and this is my GetAllOrders.java (routine) class:

/**
 * This class is generated by jOOQ.
 */
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class GetAllOrders extends AbstractRoutine<JSON> {

    private static final long serialVersionUID = 917599810;

    /**
     * The parameter <code>public.get_all_orders.RETURN_VALUE</code>.
     */
    public static final Parameter<JSON> RETURN_VALUE = Internal.createParameter("RETURN_VALUE", org.jooq.impl.SQLDataType.JSON, false, false);

    /**
     * Create a new routine call instance
     */
    public GetAllOrders() {
        super("get_all_orders", Public.PUBLIC, org.jooq.impl.SQLDataType.JSON);

        setReturnParameter(RETURN_VALUE);
    }
}

Here is my plain PL/pgSQL function:

CREATE OR REPLACE FUNCTION get_all_orders() RETURNS JSON AS
$BODY$
DECLARE
    single_order RECORD;
    single_order_json json;
    orders_array json[];
BEGIN

    FOR single_order IN SELECT * FROM public.orders ORDER BY order_id
    LOOP
        SELECT get_order_by_order_id(single_order.order_id) INTO single_order_json;
        orders_array = array_append(orders_array, single_order_json);
    END LOOP;

    return (select json_build_object(
        'orders', orders_array
    ));
END;
$BODY$
LANGUAGE 'plpgsql';

I've already posted question on Stackoverflow and got answer from Lukas Eder that PROBABLY Future<QueryResult> needs to be explicitly mapped into to Future<JsonObject>. Is there any appropriate way to handle this conversion manually, i.e. are there any existing examples to follow in order to achieve this conversion? Thank you in advance.

P.S. I've researched for similar issues but closest one I've managed to find was this one, but it's using AsyncSQLClient while vertx-jooq-classic-reactive 5.1.1 is using newer (Reactive) one.

[nixos89]

I've managed to (ALMOST) fix this issue by creating <forcedType>s in pom.xml file for user-defined function RETURN_VALUE and INPUT parameter type as described here in Stackoverflow Q&A. Problem is that now I'm having is with properly extracting/mapping values from QueryResult variable and I presume (if I'm not wrong) that it has to do something with this statement mentioned in known issues of vertx-jooq-classic-reactive 5.1.1 API:

Although postgres and the reactive driver permit false or true as valid JSON, this is not supported. JSON- and JSONB-fields are automatically mapped to a io.vertx.core.json.JsonObject (or array) which cannot handle those types.

@jklingsporn does that ALSO include generated routine fields?

[jklingsporn]

Hello @nixos89, sorry for being unresponsive, but I was busy with my daily job and this project fell a bit behind. Unfortunately I have never used the routines-feature of jooq. You are writing:

Problem is that now I'm having is with properly extracting/mapping values from QueryResult variable

What exactly is the problem? If you can't use one of the get-methods you can call QueryResult.<Row>unwrap() to get the underlying vertx-Row and extract your object from there?

[nixos89]

Hi @jklingsporn , no problem and thanks for replying. :) The thing is that I can NOT extract result of selected routine from QueryResult (above in my 1st post I've made a mistake to put it into JsonObject). I've edited my pom.xml (added forcedTypes like in above mentioned link), regenerated my jOOQ code and modifed jOOQ-fied SELECT-clause and now it looks like this (method returns OrderService instance because I'm using service-proxies in my project now):

         @Override
    public OrderService getAllOrdersJooqSP(Handler<AsyncResult<JsonObject>> resultHandler) {
        Future<QueryResult> ordersFuture = queryExecutor.transaction(qe -> qe
            .query(dsl -> dsl.select(Routines.getAllOrders()) ));
        ordersFuture.onComplete(handler -> {
            if (handler.succeeded()) {                              
                QueryResult qRes = handler.result();                    
                JsonObject ordersJsonObject = OrderUtilHelper.convertGetAllOrdersQRToJsonObject(qRes);
                resultHandler.handle(Future.succeededFuture(ordersJsonObject));
            } else {
                LOGGER.error("Error, something failed in retrivening ALL orders! handler.cause() = " + handler.cause());
                queryExecutor.rollback();               
                resultHandler.handle(Future.failedFuture(handler.cause()));
            }
        });         
        return this;
    }

I've tried to use as you've suggested QueryResult.<Row>unwrap() method, but no success - it looks like this:

public static JsonObject convertGetAllOrdersQRToJsonObject(QueryResult qr) {
        JsonObject finalRes2 = new JsonObject();
        LOGGER.info("qr.hasResults() = " + qr.hasResults());        
        LOGGER.info("qr.get(\"orders\", JSON.class) = " + qr.get("orders", JSON.class)); // returns "null"
        Row ordersRow = qr.<Row>unwrap(); // I'm using it HERE!!!       
        String strAllOrders = ordersRow.get(String.class, 0); // returns "null"
        LOGGER.info("strAllOrders = " + strAllOrders); // returns "null"
        JSON jooqJSON = ordersRow.get(JSON.class, 0); // after this LINE it HANGS and get "Error: Time out after waiting 30000ms"
        LOGGER.info("jooqJSON = " + jooqJSON);
        LOGGER.info("jooqJSON.toString() = " + jooqJSON.toString());
        for (QueryResult qRes: qr.asList()) {           
            LOGGER.info("qRes.toString() = " + qRes.toString()); // returns "io.github.jklingsporn.vertx.jooq.shared.reactive.ReactiveQueryResult@6eaed780"
            Row ordersRowIn = qRes.<Row>unwrap();
            Long orderId = ordersRowIn.getLong("order_id");
            LOGGER.info("(in da for-loop) orderId = " + orderId); // returns "null"
            JsonArray val1 = qRes.get("orders", JsonArray.class); // also HANGS here and returns Timed out ERROR
            finalRes2.put("orders", val1); // does not reach it!
        }               
//      JsonObject newConverterJO = new PostgresJSONVertxJsonObjectBinding().from(qr.get("orders", JSON.class)); 
        LOGGER.info("newConverterJA.encodePrettily() = \n" + newConverterJA.encodePrettily());
        LOGGER.info("finalRes2.encodePrettily() = \n" + finalRes2.encodePrettily());
        return new JsonObject().put("orders", "bla, bla"); // does 
    }

Following classes are other jOOQ GENERATED classes used in jOOQ-SELECT-statement:
1) This is com.ns.vertx.pg.jooq.routines.GetAllOrders.java class:

// This class is generated by jOOQ.
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class GetAllOrders extends AbstractRoutine<String> {
    private static final long serialVersionUID = 1813538537;

     // The parameter <code>public.get_all_orders.RETURN_VALUE</code>.     
    public static final Parameter<String> RETURN_VALUE = Internal.createParameter("RETURN_VALUE", org.jooq.impl.SQLDataType.JSON, false, false, 
org.jooq.Converter.ofNullable(org.jooq.JSON.class, String.class, Object::toString, org.jooq.JSON::valueOf));

     // Create a new routine call instance
    public GetAllOrders() {
        super("get_all_orders", Public.PUBLIC, org.jooq.impl.SQLDataType.JSON, 
org.jooq.Converter.ofNullable(org.jooq.JSON.class, String.class, Object::toString, org.jooq.JSON::valueOf));
        setReturnParameter(RETURN_VALUE);
    }
}

2) com.ns.vertx.pg.jooq.Routines.java class:

// Convenience access to all stored procedures and functions in public
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class Routines {

    // Call <code>public.get_all_orders</code>
    public static String getAllOrders(Configuration configuration) {
        GetAllOrders f = new GetAllOrders();
        f.execute(configuration);
        return f.getReturnValue();
    }

    // Get <code>public.get_all_orders</code> as a field.
    public static Field<String> getAllOrders() {
        GetAllOrders f = new GetAllOrders();
        return f.asField();
    }

This is link to my updated project repo. Is it possible I'm not doing something right...or I'm missing something? BTW, does this known issue:

Although postgres and the reactive driver permit false or true as valid JSON, this is not supported. JSON- and JSONB-fields are automatically mapped to a io.vertx.core.json.JsonObject (or array) which cannot handle those types.

... have to something with properly reading boolean types in my JSON result (of my PL/pgSQL user-defined function ~ generated routine)? Thank you in advance.

[jklingsporn]

Now that I am looking into the generated Routines-class, I can see the issue. The generated routine tries to fetch all the data using jdbc:

public static String getAllOrders(Configuration configuration) {
        GetAllOrders f = new GetAllOrders();
        **f.execute(configuration);**
        return f.getReturnValue();
    }

That is, because the generation of routines is not intercepted by vertx-jooq. As a workaround, you should be able to create a query to call the function by using queryExecutor.query(DSL.resultQuery("SELECT get_all_orders()"))

[nixos89]

I see.. anyway, I've tried out what you suggested - replaced parameter of queryExecutor.query() method like this:

Future<QueryResult> ordersFuture = queryExecutor.transaction(qe -> 
   qe.query( dsl -> dsl.resultQuery("SELECT get_all_orders()")
));

...and tried out to extract values from QueryResult instance in convertGetAllOrdersQRToJsonObject(QueryResult) method -> no luck! Here is how that method looks like:

public static JsonObject convertGetAllOrdersQRToJsonObject(QueryResult qr) {
        LOGGER.info("qr.hasResults() = " + qr.hasResults());        
        JsonArray ordersJA = qr.get("orders", JsonArray.class);
        JsonObject ordersJO = qr.get("orders", JsonObject.class);
        LOGGER.info("ordersJA = " + ordersJA);
        LOGGER.info("ordersJO = " + ordersJO);
        Row ordersRow = qr.<Row>unwrap();
        String jooqJson = ordersRow.getString("orders");
        LOGGER.info("jooqJson = " + jooqJson);

        String strAllOrders = ordersRow.get(String.class, 0);
        LOGGER.info("strAllOrders = " + strAllOrders); // NOW it returns "null" -> does NOT hang and throw Error!
        for (QueryResult qRes: qr.asList()) {           
            LOGGER.info("qRes.toString() =\n" + qRes.toString());
            JsonArray ordersJA2 = qRes.get("orders", JsonArray.class);
            JsonObject ordersJO2 = qRes.get("orders", JsonObject.class);
            LOGGER.info("ordersJA2 = " + ordersJA2);
            LOGGER.info("ordersJO2 = " + ordersJO2);
            Row singleRow = qRes.<Row>unwrap();
            LOGGER.info("singleRow.getLong(\"order_id\") = " + singleRow.getLong("order_id"));
            LOGGER.info("singleRow.getColumnIndex(\"order_id\") = " + singleRow.getColumnIndex("order_id"));
            LOGGER.info("qRes.get(\"orders\", JSON.class) = " + qRes.get("orders", JSON.class));
            LOGGER.info("qRes.get(\"orders\", JSONArray.class) = " + qRes.get("orders", JSONArray.class));
            LOGGER.info("qRes.get(\"orders\", JsonArray.class) = " + qRes.get("orders", JsonArray.class));
            LOGGER.info("qRes.get(\"orders\", String.class) = " + qRes.get("orders", String.class));
            LOGGER.info("qRes.get(\"orders\", JsonObject.class) = " + qRes.get("orders", JsonObject.class));
            Row ordersRowIn = qRes.<Row>unwrap();
            Long orderId = ordersRowIn.getLong("order_id");
            LOGGER.info("(in da for-loop) orderId = " + orderId);
        }               
        return new JsonObject().put("orders", newConverterJA);
    }

...and this is output I get on my terminal (when getAllOrdersJooqSP() method is executed):

15:17:28.687 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderServiceImpl - Passed ordersFuture...
15:17:28.983 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - qr.hasResults() = true
15:17:28.988 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - ordersJA = null
15:17:28.988 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - ordersJO = null
15:17:28.988 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - jooqJson = null
15:17:28.988 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - strAllOrders = null
15:17:28.989 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - qRes.toString() = 
io.github.jklingsporn.vertx.jooq.shared.reactive.ReactiveQueryResult@5fee6fd
15:17:28.990 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - ordersJA2 = null
15:17:28.990 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - ordersJO2 = null
15:17:28.990 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - singleRow.getLong("order_id") = null
15:17:28.990 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - singleRow.getColumnIndex("order_id") = -1
15:17:28.990 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - qRes.get("orders", JSON.class) = null
15:17:28.990 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - qRes.get("orders", JSONArray.class) = null
15:17:28.990 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - qRes.get("orders", JsonArray.class) = null
15:17:28.990 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - qRes.get("orders", String.class) = null
15:17:28.990 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - qRes.get("orders", JsonObject.class) = null
15:17:28.990 [vert.x-eventloop-thread-1] INFO com.ns.vertx.pg.service.OrderUtilHelper - (in da for-loop) orderId = null

Any idea how to fix/workaround this?

[nixos89]

BTW, please have a look at <forcedType>s from this line in project's pom.xml file for generated routines and it's types (maybe it might help).

[nixos89]

I've tired out to print out in convertGetAllOrdersQRToJsonObject() method outside and inside of its FOR-loop Row.getColumnName(0); and got the same result which is "get_all_orders" and for each next column with index > 0 it returns null. Is there ANY way that org.jooq.Configuration instance can be passed into public static String getAllOrders(Configuration configuration){...} method so it can return proper value type i.e. String instead of Field<String> ?

[jklingsporn]

• Are you sure the routine returns results? E.g. have you tried selecting the function on the database (using DBeaver for example)
• If yes, can you try it with plain jooq?

Am Sa., 4. Juli 2020 um 10:38 Uhr schrieb Nikola Stevanović < notifications@github.com>:

I've tired out to print out in convertGetAllOrdersQRToJsonObject() method outside and inside of its FOR-loop Row.getColumnName(0); and got the same result which is "get_all_orders" and for each next column with index > 0 it returns null. Is there ANY way that org.jooq.Configuration instance can be passed into public static String getAllOrders(Configuration configuration){...} method so it can return proper value type i.e. String instead of Field ?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/jklingsporn/vertx-jooq/issues/157#issuecomment-653739472, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABQLZXS27SFG4PO5YHILCQ3RZ3TAVANCNFSM4OGW3W2A .

-- Jens Klingsporn Beselerstraße 33 22607 Hamburg Tel: 0151/70102082

[nixos89]

1) Yes, I'm sure it returns result - I've tested it in pgAdmin4 and DataGrip by running SELECT get_all_orders(); query. 2) Tried it with plain jOOQ and it works. Code for running it in plain main() method:

Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/vertx-jooq-cr",
                "postgres", "postgres");
        DSLContext create = DSL.using(connection, SQLDialect.POSTGRES);
        Result<Record1<String>> resultR1S = create.select(Routines.getAllOrders()).fetch();
        System.out.println("resultR1S =\n" + resultR1S);

Result I get on Console (in Eclipse IDE):

resultR1S = 
+--------------------------------------------------+
|get_all_orders                                    |
+--------------------------------------------------+
|{"orders" : [{"order_id" : 1, "total_price" : 2...|
+--------------------------------------------------+

[nixos89]

As a temporary workaround I've managed to solve my use-case by using plain-jOOQ code in combination with executeBlocking(..). I did it by using try-catch blocks to perform jOOQ query:

Connection connection = null;
        try {
            connection = DriverManager.getConnection(
                    "jdbc:postgresql://localhost:5432/vertx-jooq-cr",
                    "postgres", "postgres");
            DSLContext create = DSL.using(connection, SQLDialect.POSTGRES);
            Result<Record1<String>> resultR1S = create.select(Routines.getAllOrders()).fetch();
            String strResultFinal = resultR1S.formatJSON(
                    new JSONFormat()
                    .header(false)
                    .recordFormat(RecordFormat.ARRAY)
            );
            final String fixedJSONString = strResultFinal
                    .substring(3, strResultFinal.length() - 3)
                    .replaceAll("\\\\n", "")
                    .replaceAll("\\\\", "");
            JsonObject ordersJA = new JsonObject(fixedJSONString);
            connection.close();
            resultHandler.handle(Future.succeededFuture(ordersJA));

        } catch (SQLException e) {
            e.printStackTrace();
        }   

...and invoke it in inside of executeBlocking(..) method which is inside of getAllOrdersHandler method in my HttpServerVerticle. More info for additional JSON formatting in try-block can be found in this Stackoverflow Q&A.

@jklingsporn this is only way I could get it to run and if you have any advice/suggestion to improve my (current) solution which I've describe in this comment is more than welcome. :)

[jklingsporn]

Hey, I tried to run you project but it fails when executing restore.sql, e.g. COPY public.author (author_id, first_name, last_name) FROM '$$PATH$$/3984.dat'; because these files do not exist.

[jklingsporn]

And one more thing: I've created a function in vertx-jooq that just performs a simple select like this:

CREATE OR REPLACE FUNCTION get_something_by_id(id INT) 
   RETURNS something as $$
   declare res something;
BEGIN
   select * into res from something where "someId" = id;
  return res;
END;
$$ language 'plpgsql' STRICT;

In a simple test I can query the function like this: dao.queryExecutor().query(dsl -> dsl.resultQuery("select get_something_by_id("+id+")")) This will actually return a result, however adding the following code dao.queryExecutor().query(dsl -> dsl.resultQuery("select get_something_by_id("+id+")")).map(res -> res.get(0,Something.class)) fails with the following message org.jooq.exception.DataTypeException: Cannot convert from (1,my_string,1963297861899981181,26262,2077017515,0.86091272758393556,BAZ,"{""key"":""value""}",,"[1,2,3]",,"2020-07-06 16:38:16.054") (class java.lang.String) to class generated.classic.reactive.guice.vertx.tables.pojos.Something. Apparently the vertx-pg-client returns function results as a String.

[nixos89]

Hey, I tried to run you project but it fails when executing restore.sql, e.g. COPY public.author (author_id, first_name, last_name) FROM '$$PATH$$/3984.dat'; because these files do not exist.

I've updated README file how to perform full restore of DB using vertx-jooq-cr-backup.tar file (sorry - my mistake, shouldn't have only put restore.sql from .tar file). Just make sure to manually restore DB before you try to rebuild project (using maven).

In a simple test I can query the function like this: dao.queryExecutor().query(dsl -> dsl.resultQuery("select get_something_by_id("+id+")"))

Why are you using + before and after id as parameter in your code and how do you know that vertx-pg-client is the one that returns function results as String since you get org.jooq.exception.DataTypeException execption? Couldn't it be that String is because of RETURN_VALUE parameter:

public static final Parameter<String> RETURN_VALUE = Internal.createParameter("RETURN_VALUE", org.jooq.impl.SQLDataType.JSON, false, false, org.jooq.Converter.ofNullable(org.jooq.JSON.class, String.class, Object::toString, org.jooq.JSON::valueOf));

...from GetAllOrders.java class?

[jklingsporn]

No, my test was outside of yours. I just wanted to point out that - as I understand - the vertx-pg-client returns strings for routines - regardless what type you configure for them.