For the record, I've created a feature request to think about this particular data type conversion case a bit more thoroughly: https://github.com/jOOQ/jOOQ/issues/8842
It's great that the existing Binding can already accomodate 80% of your use-case, but it would be much better if you could automatically transform your columns using INET6_NTOA as well, if you're projecting them. On Mon, Jun 24, 2019 at 8:58 AM Lukas Eder <[email protected]> wrote: > Hi John, > > You can write your own custom data type Binding and re-implement the sql() > method to produce the correct SQL string ("INET6_ATON(?)") for your bind > variables. That would make it work for writing to the database using this > function (both with insert/updates, and with queries): > > https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings/ > > Using a binding, you can also make sure that your client facing data type > is not byte[], but a more reasonable format, e.g. String, or whatever IP > Address data type you have. > > In order to read the type, however, you'd have to convert the byte[] to > your type in the client, or call a MySQL function explicitly. > > I hope this helps, > Lukas > > On Mon, Jun 24, 2019 at 1:15 AM John Bai <[email protected]> wrote: > >> I have an ip_address column with a VARBINARY(16) data type in a mysql >> table. I want to store IP addresses in that column using the INET6_ATON >> <https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton> >> built-in function provided by mysql. I also want to lookup records by IP >> address using that built-in function as well (SQL example: WHERE ip_address >> = INET6_ATON('4.2.2.1')"). Here's the docs for that function: >> >> Given an IPv6 or IPv4 network address as a string, returns a binary >>> string that represents the numeric value of the address in network byte >>> order (big endian). Because numeric-format IPv6 addresses require more >>> bytes than the largest integer type, the representation returned by this >>> function has the VARBINARY >>> <https://dev.mysql.com/doc/refman/5.6/en/binary-varbinary.html> data >>> type: VARBINARY(16) >>> <https://dev.mysql.com/doc/refman/5.6/en/binary-varbinary.html> for >>> IPv6 addresses and VARBINARY(4) >>> <https://dev.mysql.com/doc/refman/5.6/en/binary-varbinary.html> for >>> IPv4 addresses. If the argument is not a valid address, INET6_ATON() >>> <https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton> >>> returns NULL. >>> >> >> Here's a snippet of my generated jOOQ for the table and ip_address column: >> >> public class Events extends TableImpl<EventsRecord> { >> >> public static final Events EVENTS = new Events(); >> >> // other stuff... >> >> public final TableField<EventsRecord, byte[]> IP_ADDRESS = createFiel >> d("ip_address", org.jooq.impl.SQLDataType.VARBINARY(16), this, ""); >> } >> >> *Q1. How can I insert/update records using INET6_ATON('4.2.2.1') as a >> value for the IP_ADDRESS field?* >> >> I have a new EventsRecord instance via EventsRecord eventsRecord = >> dslContext.newRecord(EVENTS). There's existing code using this object to >> set values for all the fields. How can I set a value "INET6_ATON('4.2.2.1')" >> on my ip_address field? eventsRecord.setIpAddress() method accepts byte[] >> only, so I can't pass "INET6_ATON('4.2.2.1')" as a value. >> >> *Q2. How do I lookup records using INET6_ATON('4.2.2.1') as an equals >> value for the IP_ADDRESS field?* >> >> I tried using EVENTS.IP_ADDRESS.eq("INET6_ATON('4.2.2.1')") but that >> doesn't work because the field's eq method expects a byte[] value. I >> couldn't figure out from reading the docs if it was possible to get around >> this byte[] data type constraint, so I ended up doing this instead and it >> worked. Is there a way I can do this better? >> >> DSL.condition("{0} = INET6_ATON({1})", EVENTS.IP_ADDRESS, "4.2.2.1") >> >> >> -- >> 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 [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/jooq-user/c3c8b7a9-bdc7-41f9-a6d3-7312b7f529cc%40googlegroups.com >> <https://groups.google.com/d/msgid/jooq-user/c3c8b7a9-bdc7-41f9-a6d3-7312b7f529cc%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> For more options, visit https://groups.google.com/d/optout. >> > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO49gdrxExfo2npHVDe0HBjOGBFYdhz%3DtSnubxnL%3DRJcog%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
