SQLConnection -> updateWithParams doesn't return generated keys

[Michael Remme]

Hey,

inside a Maria database i have a simple table in the following structure:

+-------+--------------+------+-----+---------+----------------+

| Field | Type         | Null | Key | Default | Extra          |

+-------+--------------+------+-----+---------+----------------+

| name  | varchar(255) | YES  |     | NULL    |                |

| id    | int(10)      | NO   | PRI | NULL    | auto_increment |

+-------+--------------+------+-----+---------+----------------+

In vertx i am executing:

        JsonArray array = new JsonArray().add("new name");

        String insertExpression = "insert into MiniMapper set name=?";

        conn.updateWithParams(insertExpression, array, ur -> { 

        ...

When i am executing that and log the result, i am getting an output like

{"updated":1,"keys":[]} 

My problem is, that the generated keys of the record are not returned, like it is described in the documentation.

Any idea?

tia, Mike

[Nicolaas Frederick Huysamen]

I stumbled upon the same issue yesterday.  It would be great if prepared statements return keys the same as normal update statements.  For now, for single INSERT operations I do a separate query with "SELECT LAST_INSERT_ID()" to get the key. That operation is connection specific so you will always get the last key inserted by your current connection.

But agreed, fist prize would be the prepared statement returning the generated keys.

[Michael Remme]

Thank you very much. Me too i found that in the meantime.

Additionally i read, that by actions, which are inserting several records, the SELECT_LAST_INSERT_ID() will return the first generated ID of the series.

I will test that either

[Paulo Lopes]

I'll have a look into this issue but be careful with SELECT LAST_INSERT_ID() because laste_insert_id is managed per connection so if your pool if allowing other threads/handlers to use the same connection after the insert and before the select it can be that the id gets messed up...

[Paulo Lopes]

I've created a small test using 3 notations for inserting into the database:

@Test
public void testInsert() {
  String sql = "INSERT INTO animals (name) VALUES ('monkey');";
  connection().update(sql, onSuccess(result -> {
    System.out.println(result.getKeys());
    testComplete();
  }));

  await();
}

@Test
public void testInsert2() {
  String sql = "INSERT INTO animals (name) VALUES (?);";
  connection().updateWithParams(sql, new JsonArray().add("macaco"), onSuccess(result -> {
    System.out.println(result.getKeys());
    testComplete();
  }));

  await();
}

@Test
public void testInsert3() {
  String sql = "INSERT INTO animals set name = ?;";
  connection().updateWithParams(sql, new JsonArray().add("ape"), onSuccess(result -> {
    System.out.println(result.getKeys());
    testComplete();
  }));

  await();
}

They all inserted data and returned the correct primary key.

This is my json config:

return new JsonObject()
    .put("url", "jdbc:mariadb://localhost:3306/test?user=root&password=my-secret-pw")
    .put("driver_class", "org.mariadb.jdbc.Driver");

And i am using the following driver:

<dependency>
  <groupId>org.mariadb.jdbc</groupId>
  <artifactId>mariadb-java-client</artifactId>
  <version>1.2.2</version>
  <scope>test</scope>
</dependency>

All fine here :-/

What's your ddl, driver and connection string?

[Nicolaas Frederick Huysamen]

Using exactly the same connection details and driver.  I am using the 3.1.0-SNAPSHOT build of vertx-mysql-postgresql-client.

[Paulo Lopes]

That is some new piece of information ;) I was testing using the jdbc-client I'll port the tests to the async mysql/psql client then...

[Subir Chhibber]

What is the await() call at the end of each test? Does it have anything to do with vertx-sync awaitResult?

[Julien Viet]

the await() is provided from the vert.x core test class. it allows to write asynchronous tests.

we usually rather encourage people to use Vert.x Unit rather.

-- 
Julien Viet
www.julienviet.com

--
You received this message because you are subscribed to the Google Groups "vert.x" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.
Visit this group at https://groups.google.com/group/vertx.
To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/6da285c9-0ba7-470d-8284-4424d5543765%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Wenqiang Kong]

hi dear:  This problem has taken my a few minutes. But，Fortunately, I finally solved the problem。Here is my solution:

after get the SQLConnection, you should set the SQLOptions by invoke the  SQLConnection.setOptions。 

The SQLOptions class has a attribute called autoGeneratedKeys。Set the attribute is true，Every things will be fine.
 
在 2015年9月25日星期五 UTC+8下午9:37:45，Michael Remme写道：

[Paulo Lopes]

This is a regression, I've just fixed it on master.

https://github.com/vert-x3/vertx-jdbc-client/commit/14aa549213afdd5bc15a6b984064176529fb8e7f