-
Notifications
You must be signed in to change notification settings - Fork 53
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Add support for routines #157
Comments
I've managed to (ALMOST) fix this issue by creating
@jklingsporn does that ALSO include generated routine fields? |
Hello @nixos89,
What exactly is the problem? If you can't use one of the |
Hi @jklingsporn , @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 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:
// 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);
}
}
// 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.
... have to something with properly reading |
Now that I am looking into the generated
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 |
I see.. anyway, I've tried out what you suggested - replaced parameter of Future<QueryResult> ordersFuture = queryExecutor.transaction(qe ->
qe.query( dsl -> dsl.resultQuery("SELECT get_all_orders()")
)); ...and tried out to extract values from 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):
Any idea how to fix/workaround this? |
BTW, please have a look at |
I've tired out to print out in |
- 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ć <
[email protected]>:
… 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> ?
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
<#157 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/ABQLZXS27SFG4PO5YHILCQ3RZ3TAVANCNFSM4OGW3W2A>
.
--
Jens Klingsporn
Beselerstraße 33
22607 Hamburg
Tel: 0151/70102082
|
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):
|
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 @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. :) |
Hey, I tried to run you project but it fails when executing |
And one more thing: I've created a function in vertx-jooq that just performs a simple select like this:
In a simple test I can query the function like this: |
I've updated README file how to perform full restore of DB using
Why are you using + before and after 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? |
No, my test was outside of yours. I just wanted to point out that - as I understand - the |
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):
...I get these errors during compile-time in Eclipse IDE:
This is my Routines.java class code
...and this is my GetAllOrders.java (routine) class:
Here is my plain PL/pgSQL function:
I've already posted question on Stackoverflow and got answer from Lukas Eder that PROBABLY
Future<QueryResult>
needs to be explicitly mapped into toFuture<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.The text was updated successfully, but these errors were encountered: