IP Range Query Structure (using ASP and Access)

[jay.barnes]

Hello!

I've got a question here that has me scratching my head - I've got a table of IP addresses. Each octet has its own field (OCT1,OCT2,OCT3,OCT4).

The first two octets are fixed, and will never change (so, the IP addresses on my table are between X.Y.0.0 and X.Y.255.255). As such, I've got a range form where the user can type in the third and fourth octets of the low and high IP search range.

The problem is, is I can't figure out the proper format of the SELECT statement. I've tried a bunch of ideas and looked up several resources, but haven't been able to put something together that works.

Can anyone come up with any suggestions?

Thanks,

-Jay

[adam_k]

Let me see if I got this right:
You want to query with min-max for both oct3 & oct4 and get all the possible combinations?

I'm guessing that you could do it like this:

select oct3, oct4 from 
(select oct3 from ip_table
where oct3 between @oct3_min and @oct3_max) oct3
cross join 
(select oct4 from ip_table
where oct4 between @oct4_min and @oct4_max) oct4

You can expand this to include oct1 & oct2 if you like.

PS: I haven't tried this code, so it might contain errors or typos.

[adam_k]

Just saw that it's Access, so you cross won't fly.
try:

select oct3, oct4 from 
(select oct3 from ip_table
where oct3 between oct3_min and oct3_max) oct3, 
(select oct4 from ip_table
where oct4 between oct4_min and oct4_max) oct4

[jay.barnes]

Adam,

Thanks for the suggestion! I'll give it a try at my next opportunity (on Monday), and let you know what I find.

-Jay

[jay.barnes]

So, I gave it a shot, after customizing the query a little to match my DB and form structure:

"SELECT IP3, IP4 FROM"&_
"(SELECT IP3 FROM IPAddresses WHERE IP3 BETWEEN "&request.form("Oct3LO")&" AND "&request.form("Oct3HI")&") IP3, "&_
"(SELECT IP4 FROM IPAddresses WHERE IP4 BETWEEN "&request.form("Oct4LO")&" AND "&request.form("Oct4HI")&") IP4 "&_
"ORDER BY IP3, IP4 ASC"

And the response I got after doing a search for X.Y.1.250 to X.Y.2.10 from ASP was:

*Response object error 'ASP 0251 : 80004005'
Response Buffer Limit Exceeded
path/maintain_IPs.asp, line 0
Execution of the ASP page caused the Response Buffer to exceed its configured limit. *

Sounded like ASP was choking on the query. So I narrowed my search down to a range of just one IP address (X.Y.1.1 to X.Y.1.1), and got a list of 5376 instances of X.Y.1.1 (which explained why ASP choked on a 15 IP-range query) Incidentally, 5,736 is the total number of records on this table.

Any thoughts? It sounds like you are on to something with the 'query within a query' idea, though - thank you very much for that suggestion!

-Jay

[adam_k]

Try this one out:

select oct3, oct4 from 
(select oct3 from ip_table
where oct3 between oct3_min and oct3_max) oct3, 
(select oct3,oct4 from ip_table
where oct4 between oct4_min and oct4_max) oct4
where oct3.oct3 = oct4.oct3

I guess I should have thought about it in the first place, but it's either this or selecting distinct oct3 and distinct oct4 and then cross joining them.

[jay.barnes]

Okay...something a little different, this time...

The query:

SELECT IP3, IP4 FROM (SELECT IP3 FROM IPAddresses WHERE IP3 BETWEEN "&request.form("Oct3LO")&" AND "&request.form("Oct3HI")&") IP3, (SELECT IP3, IP4 FROM IPAddresses WHERE IP4 BETWEEN "&request.form("Oct4LO")&" AND "&request.form("Oct4HI")&") IP4 WHERE IP3.IP3 = IP4.IP3

Returned the following:

*Microsoft JET Database Engine error '80004005'
The specified field 'IP3' could refer to more than one table listed in the FROM clause of your SQL statement.
path/maintain_IPs.asp, line 20 *

Not sure which instance of 'IP3' it's referring to, but it seems that something in the query string would need to be clarified further for the query to work.

-Jay

[adam_k]

This should clarify it for us and the JET Engine:

SELECT IP3, IP4 FROM (SELECT IP3 FROM IPAddresses WHERE IP3 BETWEEN "&request.form("Oct3LO")&" AND "&request.form("Oct3HI")&") T3, (SELECT IP3, IP4 FROM IPAddresses WHERE IP4 BETWEEN "&request.form("Oct4LO")&" AND "&request.form("Oct4HI")&") T4 WHERE T3.IP3 = T4.IP3

[jay.barnes]

Nope, got the same error.

I did manage to figure out that the instance of "IP3" that the error refers to are the field references at the very beginning of the string "SELECT IP3, IP4 FROM (...", if that's any help at all.

[adam_k]

I'm running out of ideas and I'm starting to think about creating an Access db to test this out.

SELECT T3.IP3, IP4 FROM (SELECT IP3 FROM IPAddresses WHERE IP3 BETWEEN "&request.form("Oct3LO")&" AND "&request.form("Oct3HI")&") T3, (SELECT IP3, IP4 FROM IPAddresses WHERE IP4 BETWEEN "&request.form("Oct4LO")&" AND "&request.form("Oct4HI")&") T4 WHERE T3.IP3 = T4.IP3

[jay.barnes]

It's getting better....if I narrow my range to one IP address (from X.Y.1.1 to X.Y.1.1), it only gives me 256 repeats of the single, resulting IP address (rather than 5,736 repeats, like it did before).

So, just for the heck of it, I changed the first "SELECT" to "SELECT DISTINCT" to eliminate all the duplicates. Now, while it gives me the single instance of X.Y.1.1 when I do a 1 IP address range, if I search for a range of X.Y.1.250 to X.Y.2.10, it returns the wrong results:

X.Y.1.10 - X.Y.1.250, after which, it picks up again at X.Y.2.10, and lists all the addresses up to X.Y.2.250

The query seems to look fine when I stick to the same third octet (for example, searching X.Y.1.20 to X.Y.1.30 gives me the correct results), but, if I go beyond that, the results are off.

[adam_k]

I guess that approach won't work, so I've finally created a test db and inserted some test data. What I've come up with is:

SELECT ip.oct1, ip.oct2, ip.oct3, ip.oct4
FROM ip
where [oct3]*1000+[oct4] between " & request.form("Oct3LO")&" * 1000 + "&request.form("Oct4LO")&" and "&request.form("Oct4LO") & " * 1000 + "&request.form("Oct4HI") 

I've tried it and seems to work.

[jay.barnes]

Eureka! I figured it out! :-D

I looked around online, and found an ASP function to convert IP addresses to decimal:

function IP2Dec(ipadr)
    result=0
    faktorer=split(ipadr,".")
    for ix=0 to 3
        expn=3-ix
    result=result + faktorer(ix) * 256 ^ expn
    next
    IP2Dec=result
end function

I created a new column on the table called "DecimalAddress", and populated it with all the addresses' decimal counterparts.

I then updated my form handling to use the function convert the form's IP addresses to decimal before executing the query, and then did a range search based on the decimal values, which are all totally sequential:

SELECT * FROM IPAddresses WHERE DecimalAddress BETWEEN "&IPSearchLO&" AND "&IPSearchHI

Worked like a charm!! Thank you, adam_k for giving me the insight on how to tackle this particular issue!