Should ATON_ATON be replaced with INET_ATON ?
And what about 'ipn'? Where is it used?

User: arjen_lentz Date: 2006-01-29 16:28 (UTC) Subject: (no subject)

INET_ATON() typo fixed, thanks for spotting that.

ipn is just used to show how the short form is represented (i.e., a single 32-bit unsigned integer value). Try and see what my story means, don't try to copy the statements exactly. They are just an example, you need to understand the concept and apply it to your situation.

In PostgreSQL there is a native network address type and MAC address data type:

http://www.postgresql.org/docs/8.1/interactive/datatype-net-types.html

Then there are functions and operators for them:

http://www.postgresql.org/docs/8.1/interactive/functions-net.html

--
Joseph Scott
http://joseph.randomnetworks.com/

User: arjen_lentz Date: 2006-03-29 16:38 (UTC) Subject: Re: PostgreSQL network address types

Thanks. But those types are mostly used for input validation, not efficient storage. Reading that documentation page, one sees that an IPv4 address is stored in 12. The above solution in MySQL uses 4 bytes. This is clearly more efficient in storage, less resource use for indexing, and increased performance for handling (lookups, etc).

They are stored in 12 because they can store IPv6 addresses as well. This is a big deal in many cases. In addition, the indexes support all sorts of fun queries, such as "inside netblock" and things like that in a pretty efficient manner.

IPv6 uses 128 bit addresses, equal to 16 octets.

I do like the idea, though, of the index supporting natural queries with regard to the data type. Currently an indexed date field would not utilize the index if one selects YEAR(datefield) and the same goes to LEFT(textfield,2).

It is possible to workaround using BETWEEN (or just > and < ) and LIKE in the two cases. But it might not seem intuitive or add to readability.

YEAR(datefield) = 2007
would be more self-explaining than
datefield BETWEEN '2007-01-01 00:00:00' AND '2007-12-31 23:59:59'

- Peter Brodersen

User: arjen_lentz Date: 2007-03-05 05:32 (UTC) Subject: Re: PostgreSQL network address types

The latter could be internally fixed up by the optimizer, without the user having to worry about it.
Of course it would need to detect all usable cases to deal with them appropriately.

Hardly as elegant as the PostgreSQL solution though.

User: arjen_lentz Date: 2007-08-06 13:12 (UTC) Subject: Re: PostgreSQL network address types

Elegant comes in many ways. I'll concur that PostgreSQL makes it look nice from the user perspective. But if you're only dealing with IPv4, you don't want to use more than 4 bytes for an address. The additional space costs you memory, disk, and I/O which you may not be able to afford - depending on the application.

I'm all for having specific data types for commonly used domains, and IPV4 and IPV6 (each individually) definitely fall into that category.

You just saved me untold hours of angst.
Thank you. Thank you. Thank you.

Thanks, found you via google.

Hi,

my C code goes like this...
sprintf(query, "INSERT INTO PeerInfo(IPAddress, Port, Active, Counter, Priority) VALUES('%d', '%d', '%c', '%d', '%c'", inet_aton(addr->sin_addr.s_addr), addr->sin_port, active, counter, priority);

where IPAddress is unsigned int and Port is unsigned short.I am getting this error "conversion from `in_addr_t' to non-scalar type `in_addr' requested".

can some body correct this plz.

User: arjen_lentz Date: 2007-05-14 03:28 (UTC) Subject: Re: Using MySQL C API

You'll want to use %u and cast to a uint32 or something.

The Problem here is i want to store the IP address as an integer in the table.when i am trying to convert it from addr->sin_addr to integer,it is giving error.
when i give the query like this..
insert into PeerInfo values(inet_aton("211.38.137.33")); the value in the table looks like this 3542518049.

but when i try to write the same in the query,its giving an error.

User: arjen_lentz Date: 2007-05-14 05:55 (UTC) Subject: Re: Using MySQL C API

Well, "it" would be your C compiler, this has nothing to do with MySQL or the MySQL client API. It's pure C coding stuff so that's where that problem needs to be solved, too.

And indeed, doing it in SQL is what this original posting was about. It works fine too.

if u can tell how to store ip addr range also.

User: arjen_lentz Date: 2008-02-20 23:21 (UTC) Subject: Re: Using MySQL C API

Store the low and high, then the server can find it using a range scan in its index.
If you work using a mask, it may not be able to use an index, although there might be some tricks available to work around that.

netmask operations are easy, they are based on bitwise operations.

if you have ip4 address a.b.c.d as an integer abcd
and netmask m.n.o.p as integer mnop

the first address is written in c as (abcd & mnop) and the last as (abcd | ^ mnop )

unlike the C version of inet_aton the mysql version transforms the result to host byte order, so betweeen can be used with sensible results

so in mysql, to see if addr in in the subnet described by
a.b.c.d and m.n.o.p you can say

addr between ( abcd & mnop ) and ( abcd | ~ mnop & 0xfffffff)

and get meaningful results.

User: arjen_lentz Date: 2008-07-11 00:16 (UTC) Subject: Re: Using MySQL C API

That's exactly the same as what the C version does. In fact MySQL calls the C function, as far as I know.

Doing the bitwise operations on the network ordered address is fine, however for searches you don't want to do that as it would make using an index lookup for the search impossible. In the best case it might use an index scan rather than a full table scan.

hi,

i perform the client- server connection with mysql using c. in the table contains 2 feilds.(i.e) ip, data. but the ip's & data's does't stored in the table. they stored as like this
----------------------|----------------------|
| IP(feild1) | DATA(field2) |
|----------------------|-------------------- |
| | |
|inet_ntoa(client | recv_data); |
| _addr.sin_addr)| |
| | |
-----------------------------------------------



i write the sql query as like this. mysql_query(&mysql,"INSERT INTO server(ip,data) VALUES('inet_ntoa(client_addr.sin_addr)','recv_data')");


Plzzzzzzz give me a solution

User: arjen_lentz Date: 2008-08-25 08:19 (UTC) Subject: Re: ip , data storing

For storage, you need to use the aton function not the ntoa function.
Also, quoting it feeds the server a string with "inet_ntoa(..." which is not what you want.
You need to put the parameters properly into the query string, the above is just incorrect application code.

User: dj_perl Date: 2008-10-28 11:55 (UTC) Subject: Thanks, Arjen!

You saved me a lot of time with your thoughtful post!
You Rock!

I'm trying to insert some data into a table named Site'
the commands i'm trying to use are the following:

load data infile "teste.txt"
INTO TABLE site(dia_da_semana, mes, dia_no_mes, Hora, Ano, Tipo_de_requisicao, protocolo, IP, Usuario, Endereco, tamanho_arquivo)
set IP = INET_ATON(IP)

After the commands i get this error message:
ERROR 1265 (01000): Data truncated for column 'IP' at row 1

I figured that the INET_ATON part is not working by trying to insert manually an IP into the table and it worked fine, what should i do to make it work?

Here is the result of the manual insert:
mysql> INSERT INTO site (ip) VALUES (INET_ATON("192.168.111.111"));
Query OK, 1 row affected (0.02 sec)

Really appreciate the help.
Thanks

User: arjen_lentz Date: 2008-11-21 01:06 (UTC) Subject: Re: can you give me a hand?

Use another column name as intermediate?

http://strictcoder.blogspot.com/2009/08/different-ways-to-query-for-ip-in-your.html

Good stuff! Used it as well.