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

asked 22 Mar '12, 10:24

HBrener's gravatar image

HBrener
426212333
accept rate: 0%


I think the simplest way would to store them as a string in a varchar(39) column.

permanent link

answered 22 Mar '12, 13:02

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109
accept rate: 52%

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.

permanent link

answered 23 Mar '12, 08:47

Graham%20Hurst's gravatar image

Graham Hurst
2.7k11843
accept rate: 30%

edited 23 Mar '12, 14:02

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 ) );

BEGIN DECLARE s VARCHAR ( 100 ); SET s = '0x123456789ABCDEF1'; LOAD TABLE x USING VALUE s; SELECT * FROM x; END;

h 0x123456789abcdef1

(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
showing 3 of 8 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×409

question asked: 22 Mar '12, 10:24

question was seen: 1,878 times

last updated: 24 Mar '12, 09:11