insertInto() with returning() creating new row but not returning value

[Ian Clarke]

I'm trying to use the following code to create a row in my "campaigns" table: https://gist.github.com/f3f7580dcca4f9d28ae8 (using Jooq with Mysql).

The campaign is created in the DB, but campaignRecord is null after this statement, whereas I would hope it would contain the generated value for the "id" field (which is set to autoincrement in my schema).

What am I doing wrong here?

Ian.

[Lukas Eder]

Hello Ian,

That code seems correct to me. Here are a couple of things you could check:

- Did you set <relations>true</relations> in your code-generator?
Verify that the Campaign table overrides the Identity method to
provide jOOQ with the identity column (Campaign.ID). This flag is
needed if you wish to use PK / UK / FK / ID features in jOOQ. Here is
the sample table from the integration test:
https://github.com/lukaseder/jOOQ/blob/master/jOOQ-test/src/org/jooq/test/mysql/generatedclasses/tables/TIdentityPk.java#L51

Apart from that:

- What version of MySQL are you using? jOOQ officially supports 5.1 and above
- What version of the MySQL JDBC driver are you using? I run
integration tests with mysql-connector-5.1.15

Cheers
Lukas

2012/3/24 Ian Clarke <ian.c...@gmail.com>:

[Ian Clarke]

Thanks as always for the quick response!

On Sat, Mar 24, 2012 at 1:18 PM, Lukas Eder <lukas...@gmail.com> wrote:

- Did you set <relations>true</relations> in your code-generator?

Yes, this is the configuration in my pom.xml:

  https://gist.github.com/998fd6eb40fbed119ac6

Also, I'm using almost exactly the same type of structure elsewhere in my code with a different table and it works perfectly, so I think it might be something specific to this table.

 

Verify that the Campaign table overrides the Identity method to
provide jOOQ with the identity column (Campaign.ID).

Here is my Campaigns table: https://gist.github.com/17bd372788848ee80d78

I don't see any mention of "identity", but it does have this:

@Override

public org.jooq.UniqueKey<amplify.jooq.tables.records.CampaignsRecord> getMainKey() {

 return amplify.jooq.Keys.KEY_CAMPAIGNS_PRIMARY;

}

- What version of MySQL are you using? jOOQ officially supports 5.1 and above

I'm using 5.5.20

 

- What version of the MySQL JDBC driver are you using? I run
integration tests with mysql-connector-5.1.15

5.1.18 - should I try downgrading?

In case it's useful, here is the mysql dump of the campaigns table:

  https://gist.github.com/46e5fa5f5a768725eb7c

Thanks for your help!

Ian.

--
Ian Clarke

Blog: http://blog.locut.us/

[Lukas Eder]

Hello Ian,

> In case it's useful, here is the mysql dump of the campaigns table:
>
> https://gist.github.com/46e5fa5f5a768725eb7c

I tried running the code generator on your table with my setup and it
correctly generated an identity for campaigns.id. So the problem is
not related to your JDBC driver. There's probably a bug in
jooq-codegen's MySQL integration.

>> - What version of MySQL are you using? jOOQ officially supports 5.1 and
>> above
>
> I'm using 5.5.20

Maybe there is some feature in the MySQL dictionary views, that I am
not aware of. What does the following query return on your system?

SELECT extra FROM information_schema.columns
WHERE lower(table_name) = 'campaigns'
AND lower(column_name) = 'id'

Cheers
Lukas

[Ian Clarke]

Here we go:

mysql> SELECT extra FROM information_schema.columns

    -> WHERE lower(table_name) = 'campaigns'

    -> AND lower(column_name) = 'id';

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

| extra          |

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

| auto_increment |

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

1 row in set (0.05 sec)

Does this help?

Ian.

[Lukas Eder]

Hello Ian,

That is precisely what jooq-meta expects: "auto_increment". I'm afraid
that everything looks ok to me. To further analyse this, you could try
these two things:

- Debug jooq-meta and set a breakpoint around here to see what's happening:
https://github.com/lukaseder/jOOQ/blob/master/jOOQ-meta/src/main/java/org/jooq/util/mysql/MySQLTableDefinition.java#L114

- Run jooq-codegen with log4j and log-level trace. You could post that
log output somewhere, I could have a look. Here's a sample log4j
configuration (change "debug" to "trace"):
https://github.com/lukaseder/jOOQ/blob/master/jOOQ-test/src/log4j.xml

Cheers
Lukas

2012/3/25 Ian Clarke <ian.c...@gmail.com>:

[Ian Clarke]

On Mon, Mar 26, 2012 at 1:37 AM, Lukas Eder <lukas...@gmail.com> wrote:

Hello Ian,

That is precisely what jooq-meta expects: "auto_increment". I'm afraid
that everything looks ok to me. To further analyse this, you could try
these two things:

- Debug jooq-meta and set a breakpoint around here to see what's happening:
https://github.com/lukaseder/jOOQ/blob/master/jOOQ-meta/src/main/java/org/jooq/util/mysql/MySQLTableDefinition.java#L114

I tried that, and it's possible I was using Eclipse's debugger incorrectly, but it apparently didn't hit that breakpoint :-/  Is that possible?

I'll try trace debugging, not that familiar with log4j but I'll figure it out.

Ian.

 

[Lukas Eder]

>> https://github.com/lukaseder/jOOQ/blob/master/jOOQ-meta/src/main/java/org/jooq/util/mysql/MySQLTableDefinition.java#L114
>
> I tried that, and it's possible I was using Eclipse's debugger incorrectly,
> but it apparently didn't hit that breakpoint :-/  Is that possible?

Line numbers may have shifted. From your code snippets, I guess you're
using jOOQ 2.0.5. The link I posted is from 2.2.0-SNAPSHOT.

> I'll try trace debugging, not that familiar with log4j but I'll figure it
> out.

It will be sufficient to put log4j.jar and the log4j.xml file on the
classpath. jOOQ will discover it automatically, then.

Cheers
Lukas

[Ian Clarke]

On Mon, Mar 26, 2012 at 11:21 AM, Lukas Eder <lukas...@gmail.com> wrote:

>> https://github.com/lukaseder/jOOQ/blob/master/jOOQ-meta/src/main/java/org/jooq/util/mysql/MySQLTableDefinition.java#L114
>
> I tried that, and it's possible I was using Eclipse's debugger incorrectly,
> but it apparently didn't hit that breakpoint :-/  Is that possible?

Line numbers may have shifted. From your code snippets, I guess you're
using jOOQ 2.0.5. The link I posted is from 2.2.0-SNAPSHOT.

Yeah, I didn't go by line number, I found the equivalent code in my version.

 

> I'll try trace debugging, not that familiar with log4j but I'll figure it
> out.

It will be sufficient to put log4j.jar and the log4j.xml file on the
classpath. jOOQ will discover it automatically, then.

Ok, I'll give it a shot.

Ian. 

[Lukas Eder]

Hello Ian,

Did you have any luck further investigating this issue where
identities weren't generated by jooq-codegen?

Cheers
Lukas

2012/3/26 Ian Clarke <ian.c...@gmail.com>:

[Ian Clarke]

I'm sorry Lukas, I didn't - I ended up having to rewrite that code anyway and so it wasn't necessary to solve the problem.

Ian.

[Lukas Eder]

Alright. Thanks for the feedback. Let's keep an eye on this...

Cheers
Lukas

2012/4/11 Ian Clarke <ian.c...@gmail.com>:

[Christopher Riley]

Hi,

I am having this same issue using Jooq, I see it's been almost a year
since this issue was last encountered, did anyone ever get this issue
resolved?

Thanks,

~Christopher Riley

On Apr 11, 5:16 pm, Lukas Eder <lukas.e...@gmail.com> wrote:
> Alright. Thanks for the feedback. Let's keep an eye on this...
>
> Cheers
> Lukas
>

> 2012/4/11 Ian Clarke <ian.cla...@gmail.com>:

>
>
>
>
>
>
>
> > I'm sorry Lukas, I didn't - I ended up having to rewrite that code anyway
> > and so it wasn't necessary to solve the problem.
>
> > Ian.
>

> > On Wed, Apr 11, 2012 at 12:23 PM, Lukas Eder <lukas.e...@gmail.com> wrote:
>
> >> Hello Ian,
>
> >> Did you have any luck further investigating this issue where
> >> identities weren't generated by jooq-codegen?
>
> >> Cheers
> >> Lukas
>

> >> 2012/3/26 Ian Clarke <ian.cla...@gmail.com>:
>
> >> > On Mon, Mar 26, 2012 at 11:21 AM, Lukas Eder <lukas.e...@gmail.com>
> >> > wrote:
>
> >> >> >>https://github.com/lukaseder/jOOQ/blob/master/jOOQ-meta/src/main/java...

[Lukas Eder]

Hello Christopher,

Thanks for your feedback. I've been made aware of this about a month
ago. Unfortunately, I couldn't reproduce it so far, using Ian's DDL.
Can you help me reproduce this? What setup is required for a MySQL
AUTO_INCREMENT column not to generate an IDENTITY in jOOQ-generated
artefacts?

Cheers
Lukas

2012/4/20 Christopher Riley <rigu...@gmail.com>:

[Garret Wilson]

I'm having this same problem with PostgreSQL. I asked the question elsewhere but no one has responded yet. I tried to post the question here on this list, but for some reason it didn't show up.

Garret

[Lukas Eder]

Hi Garret,

For some reason, your other message to the jOOQ user group was treated as spam... I'll respond to the other message and to the Stack Overflow question separately,

Cheers

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.