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 = createField( "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. For more options, visit https://groups.google.com/d/optout.
