Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    Unanswered: Search Range of IP Addresses in table

    Hi :
    I have a question.
    I have IPAddresses table in which Admin can enter single ip or range of ips
    like
    192.168.0.1
    192.168.0-255.0-255

    If the admin tries to add 192.168.2.2
    then the system should let the user know that this already exists in database.
    How can I achieve that in sql statement?
    Thank you in advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    INSERT IPAddress
    INTO tblIPAddress
    VALUES ('192.168.2.2')
    WHERE '192.168.2.2' NOT IN
     (
     SELECT IPAddress
     FROM tblIPAddress
     )
    Give that a quick run (I assume this is what you mean by "in sql statement")
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by VBAPROGRAMMER
    Hi :
    I have a question.
    I have IPAddresses table in which Admin can enter single ip or range of ips
    like
    192.168.0.1
    192.168.0-255.0-255

    If the admin tries to add 192.168.2.2
    then the system should let the user know that this already exists in database.
    How can I achieve that in sql statement?
    Thank you in advance
    Hi

    To do so would be very awkward indeed. Arguably this design violates first normal form too.

    I think I would be tempted to allow the admin to enter the range but "intercept" the data entry and have the db calculate each individual IP address and record those. This would of course mean a lot more rows.

    Another alternative might be to have two fields for each byte in the address (eight in total) each representing an upper and lower value.

    I'm not certain on the best course but I think you are better redesigning the table rather than writing complicated code. I can think of one or two other queries that you might one day need that would each require a new, complicated statement with this design.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Another way is to proclaim a field "IP" (IPAddresses table),
    as an INDEX with properties UNIQUE = Yes.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    George, MGStef - reread OPs post. The problem is that (s)he is storing a range in a field and wants to be able to check if a particular IP falls within that range.

    There are no simple SQL or table constraint solutions.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah just noticed that.

    I mean... You're violating 1NF etc
    Yeah, you can't really do ranges in IP, you can't do a 'compare' as such.

    I wrote that before I left the house, pootled to work and decided I was wrong. By then you'd already told me
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    SQL Statement

    Quote Originally Posted by VBAPROGRAMMER
    Hi :
    I have a question.
    I have IPAddresses table in which Admin can enter single ip or range of ips
    like
    192.168.0.1
    192.168.0-255.0-255

    If the admin tries to add 192.168.2.2
    then the system should let the user know that this already exists in database.
    How can I achieve that in sql statement?
    Thank you in advance
    Hi Guys:
    To solve thisproblem. I split the ip field into two columns ip and EndingIp. I woul dlike to search the entered ip between ip and Ending ip. My SQl Statement is not giving any error message but not returing the result as well.

    lets say that user enteredip = 10.174.55.65
    ip.ip=10.174.0.0 (this is the field in the table)
    ip.EndingIp=10.174.255.255 (field in the table)

    my SQL Statement:

    String strSQL = "select ip.ip, ip.EndingIp from IPAddressesTest ip inner join members mem on ip.memberID = mem.memberID where mem.memberTypeID = 3 and ip.memberID="+inMID+" and '"+ sAddIP +"' between ip.ip and ip.EndingIp order by ip.ip";


    this sqlstatement works but never gives any results back.

    Thank you in advance
    Regards,

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by VBAPROGRAMMER
    Hi Guys:
    To solve thisproblem. I split the ip field into two columns ip and EndingIp.
    I believe you have created a problem rather than solved one. Your problems with the SQL kind of support this view.

    This got mentioned on SQLTeam recently. Things carried on after I lost track of the thread and it becomes less relevent the more you read but I still think the ideas are the same.
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82533
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    Wouldent it be easier in the form to input the IP addresses to use a FOR NEXT command to enter the entire range of IP addresses from start to finish all in a table. Then you would not have this problem at all but your table would be just be larger.

    -Tony

  10. #10
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Quote Originally Posted by gorf8083
    Wouldent it be easier in the form to input the IP addresses to use a FOR NEXT command to enter the entire range of IP addresses from start to finish all in a table. Then you would not have this problem at all but your table would be just be larger.

    -Tony
    but then it has to check if that single ip exists in the table either without or within the range. The whole puprose is to check whether this already exists in the table or not

  11. #11
    Join Date
    Apr 2007
    Location
    Auburn, Kansas, USA
    Posts
    37
    Quote Originally Posted by VBAPROGRAMMER
    but then it has to check if that single ip exists in the table either without or within the range. The whole puprose is to check whether this already exists in the table or not
    Well true, With the FOR NEXT Statement just write an If statement that would quickly check through the current list to see if its there before it adds it.

    Code:
    FOR countervariable = StartIP TO EndIP
        IF (statement to check if the ip address exists, Not quite sure how it would be written in SQL)
           NEXT
        ELSE
           (SqlStatement to write the IPaddress to the table)
        END IF
    NEXT
    Sorry I am new at VBA and SQL Coding but I know a lot of Basic and qBasic and that statement should work.

    Then you just have a flat listing of all IP Addresses that have been entered stored in a table. Ya it takes longer to run and you have a larger table but its more complete and searching for IP's later should be quicker.

    -Tony

  12. #12
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    erm

    why not convert/store the IP address as a long integer?

    And, when comparing ranges, convert the lower and upper ranges to long integers also.

    Then it's a pretty simple matter to write a query where IP >= intLower and IP <= intUpper
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by loquin
    why not convert/store the IP address as a long integer?
    Well - an Access Long just isn't long enough for an IP address. And aren't you just converting something from one thing to another?

    An IP address is four one byte numbers. Why not store it as four one byte numbers? Why convert it to a string? Why convert it to a single integer? Seriously - I'm not being rhetorical.

    BTW - VBAPROGRAMMER - you do know why your last plan didn't work yes? You are storing the IP address as a string but expecting to be able to treat it as a number which obviously you can't.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I was just coming to suggest that you store it in it's octets, but it looks like I was beaten to it again! If you save it in four pieces you can compare each seperate octet (is that the right word?) and even use ranges. Alternatively you ave to build a function to split the IP into it's respective parts and convert to numbers, before then performing your comparisons...

    Which do you think sounds easier/better on performance?
    George
    Home | Blog

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could do that.....
    you could also store it as a string but force the formatting to make sure string comparisons work
    eg 192.168.1.1
    would be stored as 192.168.001.001
    ...that way round you could do string comparisons

    Id certainly not be in favour of storing a range eg 192.168.1.1-230

    it might be 'smart' to consider the impact IPv6 foramtting if the app is to survive a bit longer.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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