For storing IPV4 value we are using datatype int, IPV4 have 32 bit length. IPV6 have 128 bit length, and in this case if use in the same manner as IPV4 need use datatype numeric(39)or BINARY(16)!?. May be anyone have solved this issue. Thanks in advance |
I think the simplest way would to store them as a string in a varchar(39) column. oooo, a literal believer of the Canonical Text! :)
(22 Mar '12, 14:37)
Breck Carter
|
I think it depends on how you will be using the IP addresses. BINARY(16) is probably most efficient for storage and comparisons, but VARCHAR(39) would be best for display. FYI unique identifiers are also 128 bit values. For those, SQL Anywhere stores UNIQUEIDENTIFIER values internally as BINARY(16), and also provides functions for converting to and from string representations. You mean we can store the IPv6 as a UNIQUEIDENTIFIER ;-)
(23 Mar '12, 09:47)
Martin
Replies hidden
Additional question - in case using type BINARY(16), how load/insert data for this column. If load data from text file(using LOAD TABLE statement with HEXADECIMAL is ON) the value could be presentated in file as 0xnnnnnnnnnnnnnnn?
(23 Mar '12, 13:22)
HBrener
Replies hidden
I suppose, but I can't see a benefit over BINARY(16), since the extras for UNIQUEIDENTIFIER don't apply to IPv6 addresses.
(23 Mar '12, 14:04)
Graham Hurst
That appears to work... CREATE TABLE x ( h BINARY ( 16 ) );
(23 Mar '12, 14:19)
Breck Carter
The challenge will be parsing the wide variety of text representations down into the simple numeric representation: http://tools.ietf.org/html/rfc5952#section-1 That's why I like Graham's suggestion of VARCHAR ( 39 ).
(23 Mar '12, 14:29)
Breck Carter
I think you meant Graeme. :) Yes, VARCHAR( 39 ) would be easiest for loading the multitude of text formats and human readability. But if you want to do comparisons (eg. use it as a primary key), save storage space, or don't need to use SQL to parse all valid text formats, then BINARY(16) might be preferred.
(23 Mar '12, 18:56)
Graham Hurst
1
Good idea, if feature version of SA, especially SA web edition/fuji will have built-in functions supporting text to binary and binary to text transformation of ipv6 address according to http://tools.ietf.org/html/rfc5952#section-4 recommendations.
(24 Mar '12, 05:44)
AlexeyK77
Ahhh, yes but you BOTH mentioned VARCHAR(39) and I didn't scroll up far enough. ...sounds like fun writing the conversion functions :)
(24 Mar '12, 09:11)
Breck Carter
|