Eddie Shipman
asked on
Sorting IP's with Access SQL via ADO...
I have this SQL, works great in MSAcess.
SELECT tblIPs.ID, tblIPs.IPAddress
FROM tblIPs
ORDER BY CLng(Left([IPAddress],InSt r([IPAddre ss],".")-1 )), CLng(Mid([IPAddress],InStr ([IPAddres s],".")+1, InStr(InSt r([IPAddre ss],".")+1 ,[IPAddres s],".")-In Str([IPAdd ress],".") -1)), CLng(Mid([IPAddress],InStr (InStr([IP Address]," .")+1,[IPA ddress],". ")+1,InStr (InStr(InS tr([IPAddr ess],".")+ 1,[IPAddre ss],".")+1 ,[IPAddres s],".")-In Str(InStr( [IPAddress ],".")+1,[ IPAddress] ,".")-1)), CLng(Mid([IPAddress],InStr (InStr(InS tr([IPAddr ess],".")+ 1,[IPAddre ss],".")+1 ,[IPAddres s],".")+1) );
But when I try to use it in a TADOQuery, I get this error:
"[Microsoft][ODBC Microsoft Access Driver] '' is not a valid name. Make sure that it does not include
invalid characters or punctuation and that it is not too long."
What's up with that?
How could I write the above query to sort IPs using SQL. I DO NOT WANT TO BREAK UP THE IP INTO FOUR INTEGER COLUMNS.
SELECT tblIPs.ID, tblIPs.IPAddress
FROM tblIPs
ORDER BY CLng(Left([IPAddress],InSt
But when I try to use it in a TADOQuery, I get this error:
"[Microsoft][ODBC Microsoft Access Driver] '' is not a valid name. Make sure that it does not include
invalid characters or punctuation and that it is not too long."
What's up with that?
How could I write the above query to sort IPs using SQL. I DO NOT WANT TO BREAK UP THE IP INTO FOUR INTEGER COLUMNS.
ASKER
Very good but not quite right. Try sorting these:
10.0.25.125
13.14.154.5
15.14.69.42
23.45.18.12
62.31.221.234
128.196.206.203
142.35.92.187
204.5.2.52
212.202.54.83
212.227.126.156
212.227.228.107
214.126.15.222
214.24.1.45
217.233.150.230
217.235.62.116
217.24.48.119
Pay close attention to the 214 and 217 blocks.
214.126.15.222 is listed before 214.24.1.45
and
217.235.62.116 is listed before 217.24.48.119
which are both incorrect.
10.0.25.125
13.14.154.5
15.14.69.42
23.45.18.12
62.31.221.234
128.196.206.203
142.35.92.187
204.5.2.52
212.202.54.83
212.227.126.156
212.227.228.107
214.126.15.222
214.24.1.45
217.233.150.230
217.235.62.116
217.24.48.119
Pay close attention to the 214 and 217 blocks.
214.126.15.222 is listed before 214.24.1.45
and
217.235.62.116 is listed before 217.24.48.119
which are both incorrect.
ASKER
I found a way to do it based upon the formula on this page:http://www.maxmind.com/app/csv
I can sort descending by changing the asc in the first orderby to desc.
SELECT tblIPs.ID, tblIPs.IPAddress
FROM tblIPs
ORDER BY
Val(Left([IPAddress],InStr ([IPAddres s],".")-1) ) * 16777216 asc,
Val(Mid([IPAddress],InStr( [IPAddress ],".")+1,I nStr(InStr ([IPAddres s],".")+1, [IPAddress ],".")-InS tr([IPAddr ess],".")- 1)) * 65536,
Val(Mid([IPAddress],InStr( InStr([IPA ddress],". ")+1,[IPAd dress],"." )+1,InStr( InStr(InSt r([IPAddre ss],".")+1 ,[IPAddres s],".")+1, [IPAddress ],".")-InS tr(InStr([ IPAddress] ,".")+1,[I PAddress], ".")-1)) * 256,
Val(Mid([IPAddress],InStr( InStr(InSt r([IPAddre ss],".")+1 ,[IPAddres s],".")+1, [IPAddress ],".")+1)) ;
This works exactly like I want.
I can sort descending by changing the asc in the first orderby to desc.
SELECT tblIPs.ID, tblIPs.IPAddress
FROM tblIPs
ORDER BY
Val(Left([IPAddress],InStr
Val(Mid([IPAddress],InStr(
Val(Mid([IPAddress],InStr(
Val(Mid([IPAddress],InStr(
This works exactly like I want.
When you save the IP address into the DB you could also save a sort column, ie
IP_Address sort_column
217.235.62.116 217.235.062.116
217.24.48.119 217.024.048.119
this will speed up the sorting, how many addresses are you saving?
IP_Address sort_column
217.235.62.116 217.235.062.116
217.24.48.119 217.024.048.119
this will speed up the sorting, how many addresses are you saving?
ASKER
I am importing a Black Ice attack list and there could be upto 50,000 lines. I do not want to have
extra fields just for sorting. If I wsa going to do that, I might as well store the IP's as four integers.
There are 2 IP's per line that need to be stored, btw.
On another note. We have decided to dump Access due to performance and have gone to mySQL.
Access imported 48,000 lines in 7 min, mySQL imported the same file in 1 min. using the exact same
code.
But sorting and filtering is very slow using the ZEOS DB components.
extra fields just for sorting. If I wsa going to do that, I might as well store the IP's as four integers.
There are 2 IP's per line that need to be stored, btw.
On another note. We have decided to dump Access due to performance and have gone to mySQL.
Access imported 48,000 lines in 7 min, mySQL imported the same file in 1 min. using the exact same
code.
But sorting and filtering is very slow using the ZEOS DB components.
With 50,000 records it would be faster to add the extra column, than evaluate your sort expression. Disk space is cheap try it and see? I would expect a major difference in sorting speed.
ASKER
Since moving to mySQL, the sorting as well as the importing is extremely fast. No need for it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT Table1.ipaddress
FROM Table1
ORDER BY Val([ipaddress]), Val(StrReverse(Val(StrReve