Inserting string values into numeric columns

[smud...@snaplogic.com]

Hi Guys,

  I am using JOOQ library in my Java project to perform JDBC-based integrations with various databases.  For MySQL, I have a table defined as follows:

     CREATE TABLE T1 (C1 smallint, C2 int, C3 biging);

  And I am trying to insert the values ("someString", 2, 3) into T1.  Instead of getting error, I am surprised that the query executed successfully and JOOQ converted "someString" into a NULL value.

  Is there a way to configure JOOQ not to convert invalid values into NULL and rather throw an error?

[Lukas Eder]

Hello,

Thanks for your message. Would you mind showing your jOOQ code?

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/d0f974e2-b922-49b3-866c-db6c915e8fb5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[smud...@snaplogic.com]

Yes sir, the code is as follows:

public class BatchExecuteListener extends DefaultExecuteListener {
    private static final Logger log = LoggerFactory.getLogger(BatchExecuteListener.class);

    @Override
    public void executeEnd(ExecuteContext ctx) {
        try {
            ctx.statement().clearBatch();
        } catch (SQLException e) {
            log.warn(CLEAR_BATCH_FAILED);
        }
    }
}

public static void main(String args[]) {
    Connection conn = getJdbcConnection();
    Settings settings = new Settings()
            .withRenderNameStyle(RenderNameStyle.QUOTED);
    DSLContext context = DSL.using(conn, SQLDialect.MYSQL, settings);
    context.configuration()
            .set(new DefaultExecuteListenerProvider(new BatchExecuteListener()));
    Map<Field<?>, Object> fieldMap = populateFieldMap();
    // If we print fieldMap, its as follows: {"BIGINTcol"=hello, "INTEGERcol"=22, "SMALLINTcol"=33}
    Query query = context.insertInto(DSL.table(quoteIdentifier(tableName)),
            fieldMap.keySet()).values(fieldMap.values());
    Batch batch = context.batch(query);
    ((BatchBindStep) batch).bind(fieldMap.values().toArray());
    batch.execute();
}

On Tuesday, July 2, 2019 at 7:01:48 PM UTC+5:30, Lukas Eder wrote:

Hello,

Thanks for your message. Would you mind showing your jOOQ code?

Cheers,

Lukas

On Tue, Jul 2, 2019 at 3:25 PM <smud...@snaplogic.com> wrote:

Hi Guys,

  I am using JOOQ library in my Java project to perform JDBC-based integrations with various databases.  For MySQL, I have a table defined as follows:

     CREATE TABLE T1 (C1 smallint, C2 int, C3 biging);

  And I am trying to insert the values ("someString", 2, 3) into T1.  Instead of getting error, I am surprised that the query executed successfully and JOOQ converted "someString" into a NULL value.

  Is there a way to configure JOOQ not to convert invalid values into NULL and rather throw an error?

--
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...@googlegroups.com.

[smud...@snaplogic.com]

Any update on this pls?  Anyone having any clue/idea?

[Lukas Eder]

Hi there,

Thank you very much for your patience.

I have a few questions / remarks:

1. What's the purpose of your BatchExecuteListener? Anything that you think we should improve in jOOQ directly?

2. Your code example is incomplete. What's in populateFieldMap?

3. You don't need to roll your own "quoteIdentifier()" method. Just use jOOQ's DSL.name()

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/7bb2260d-97c2-4bb8-a3d1-e1e47da319bd%40googlegroups.com.

[smud...@snaplogic.com]

Can we find the cause of the issue I am facing pls?  I have not shown populateFieldMap().  In this method, I am creating an instance of  Map<Field<?>, Object>.  In this instance, I am populating the table values I want to add.  I already said in the comment, if I print this instance, the output would be: {"BIGINTcol"=hello, "INTEGERcol"=22, "SMALLINTcol"=33}

I wouldn't worry about BatchExecuteListener or DSL.name().

My concern is why non-integer values are converted into null by JOOQ?  How to tame JOOQ not to do that conversion.

[Lukas Eder]

I will be extremely happy to help you, but can you please show the *exact* code that helps reproduce this issue? It would really help to give you an appropriate answer if I can actually reproduce this.

Thanks for your understanding :-)

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/22ec072e-b71b-4594-b76e-1a43e2b10c1a%40googlegroups.com.

[Lukas Eder]

Anyway, I'll just "guess" what could be going on for the time being. jOOQ has a legacy feature where data type conversion produces nulls whenever they fail. This feature is implemented in org.jooq.tools.Convert. We'll change this feature in jOOQ 4.0, backwards incompatibly. Until then, that's the status quo. Surely, you can override the data type conversion in your implementation of populateFieldMap()...

For the future, providing a complete example that helps reproduce an issue will help speed up support cases. We even have a template for this:  https://github.com/jOOQ/jOOQ-mcve

[Syed Mudassir]

Let me give the implementation of populateFieldMap().  Lets say I have my table record in a Map as follows: {BIGINTcol=hello, INTEGERcol=22, SMALLINTcol=33}

Map<Field<?>, Object> populateFieldMap(final Map<String, Object> record) {

  Map<Field<?>, Object> fieldMap = Maps.newLinkedHashMap();

  for (Map.Entry<String, Object> entry : record.entrySet()) {

    String key = entry.getKey();
    Object val = entry.getValue();

    DataType dataType;

    if (key.equals("BIGINTcol")) {

       dataType = MySQLDataType.BIGINT;

    } else if (key.equals("INTEGERcol")) {

       dataType = MySQLDataType.INT;

    } else {

        dataType = MySQLDataType.SMALLINT;

    } 
    Field<?> field = DSL.field(DSL.name(key), dataType);
    fieldMap.put(field, val);
}

  return fieldMap;

}

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/hv__uowVkpA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7ySSxCkAg3W4k5j5Y3tbZXZLUK84g34aw8h%3DTUBg0UXg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

Thanks,

Syed.

[Syed Mudassir]

Thanks Luker, appreciate your response.  When we expect the next JOOQ release with the fix?

--

Thanks,

Syed.

[Lukas Eder]

Thank you very much Syed. I see, the problem is really the one I guessed in the end.

We won't "fix" this lightheartedly as a lot of people rely on the status quo. Again, it would be just a matter of a few lines of code in your populate method to validate your input directly yourself. Please let me know if you need any help with that.

Thanks,

Lukas 

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAG2tRYDeiWUc3CUmrQYJTcD8ZUTbw6_bM43Ld23Bu9_%2B1P4gvg%40mail.gmail.com.