Professional Web Applications Themes

inet_aton return type? - MySQL

Greetings, I am writing a perl-script to deal with IP-Addresses and Subnets. I store IP-Addresses as unsigned ints using inet_aton and want to leave all calculations to the SQL-Query. my query is Select inet_ntoa(stored_ip),... from table where inet_aton("$IP") > ( stored_ip & inet_aton("$SUBNET_MASK")) and inet_aton("$IP") < ( stored_ip | ~inet_aton("$SUBNET_MASK")); (i.e is $IP in the same subnet as any previously stored IP-Address?) This does not work because inet_aton($SUBNET_MASK) returns a 64bit value and therefore the bit-negation (~) does not work as expected. (i.e. (~inet_aton("255.255.255.0")) comes to 18446744069414584575 insted of 255. Just to be clear: $IP and $SUBNET_MASK are two strings ...

  1. #1

    Default inet_aton return type?

    Greetings,


    I am writing a perl-script to deal with IP-Addresses and Subnets.


    I store IP-Addresses as unsigned ints using inet_aton and want to leave
    all calculations to the SQL-Query.

    my query is

    Select inet_ntoa(stored_ip),... from table
    where
    inet_aton("$IP") > ( stored_ip & inet_aton("$SUBNET_MASK"))
    and
    inet_aton("$IP") < ( stored_ip | ~inet_aton("$SUBNET_MASK"));

    (i.e is $IP in the same subnet as any previously stored IP-Address?)


    This does not work because inet_aton($SUBNET_MASK) returns a 64bit value
    and therefore the bit-negation (~) does not work as expected. (i.e.
    (~inet_aton("255.255.255.0")) comes to 18446744069414584575 insted of 255.

    Just to be clear: $IP and $SUBNET_MASK are two strings given by the
    script itself, they are not stored in the table anywhere.


    We use a MySQL5 Server and the only Mysql-book I have says inet_aton
    returns either a 32bit or 64bit value without further explanation.


    How do I change the return type from bigint to int in order to make this
    work? The only solution I came up with, is subtracting
    inet_aton("255.255.255.255.0.0.0.0") from
    ~inet_aton("$SUBNET_MASK"), but that is very ugly.



    Thanks in Advance

    Sue

    Susan Barnes Guest

  2. #2

    Default Re: inet_aton return type?

    "Susan Barnes" <susans-spamtrapuni-koeln.de> wrote in message
    news:48aiamFj66ouU1individual.net...
    > This does not work because inet_aton($SUBNET_MASK) returns a 64bit value
    [url]http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html[/url] says:
    " MySQL uses BIGINT (64-bit) arithmetic for bit operations, so these
    operators have a maximum range of 64 bits."

    I suspect inet_aton is returning a correct value, but the expression is
    promoted to a 64-bit expression once you use the ~ operator.

    Try one of these expressions to get the correct bitwise complement:

    select ~inet_aton('255.255.255.0') & inet_aton('255.255.255.255')

    select ~(inet_aton('255.255.255.0') |
    inet_aton('255.255.255.255.0.0.0.0'))

    I was thinking one also could use CAST to force it to be a 32-bit integer,
    but alas, CAST always returns a 64-bit BIGINT, ignoring the request for a
    32-bit INTEGER ([url]http://bugs.mysql.com/bug.php?id=4734[/url]). It seems that the
    only use for CAST is to make the expression signed or unsigned.

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. Return type for Xml Doent
    By lad4bear in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: May 13th, 04:55 PM
  2. WebService return type suggestion
    By Srinivas R. Loka in forum ASP.NET Web Services
    Replies: 1
    Last Post: May 5th, 03:01 PM
  3. web method return type
    By niraj in forum ASP.NET Web Services
    Replies: 0
    Last Post: April 6th, 07:56 PM
  4. WS return type, XML string or XmlDoent, which is better?
    By Bob in forum ASP.NET Web Services
    Replies: 0
    Last Post: March 5th, 06:47 PM
  5. Complex type as a return value
    By Fredy Villa in forum ASP.NET Web Services
    Replies: 0
    Last Post: November 5th, 09:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139