Solved

Sorting IP's with Access SQL via ADO...

Posted on 2003-12-08
9
258 Views
Last Modified: 2010-04-05
I have this SQL, works great in MSAcess.

SELECT tblIPs.ID, tblIPs.IPAddress
FROM tblIPs
ORDER BY CLng(Left([IPAddress],InStr([IPAddress],".")-1)), CLng(Mid([IPAddress],InStr([IPAddress],".")+1,InStr(InStr([IPAddress],".")+1,[IPAddress],".")-InStr([IPAddress],".")-1)), CLng(Mid([IPAddress],InStr(InStr([IPAddress],".")+1,[IPAddress],".")+1,InStr(InStr(InStr([IPAddress],".")+1,[IPAddress],".")+1,[IPAddress],".")-InStr(InStr([IPAddress],".")+1,[IPAddress],".")-1)), CLng(Mid([IPAddress],InStr(InStr(InStr([IPAddress],".")+1,[IPAddress],".")+1,[IPAddress],".")+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.

0
Comment
Question by:EddieShipman
  • 4
  • 3
9 Comments
 
LVL 3

Expert Comment

by:julianpointer
ID: 9901603
If you are using Acces2000 + you could try this

SELECT Table1.ipaddress
FROM Table1
ORDER BY Val([ipaddress]), Val(StrReverse(Val(StrReverse([ipaddress]+"1"))))
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 9904248
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.
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 9904556
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([IPAddress],".")-1)) * 16777216 asc,
  Val(Mid([IPAddress],InStr([IPAddress],".")+1,InStr(InStr([IPAddress],".")+1,[IPAddress],".")-InStr([IPAddress],".")-1)) * 65536,
  Val(Mid([IPAddress],InStr(InStr([IPAddress],".")+1,[IPAddress],".")+1,InStr(InStr(InStr([IPAddress],".")+1,[IPAddress],".")+1,[IPAddress],".")-InStr(InStr([IPAddress],".")+1,[IPAddress],".")-1)) * 256,
  Val(Mid([IPAddress],InStr(InStr(InStr([IPAddress],".")+1,[IPAddress],".")+1,[IPAddress],".")+1));

This works exactly like I want.


0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 3

Expert Comment

by:julianpointer
ID: 9909857
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?
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 9912428
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.
0
 
LVL 3

Expert Comment

by:julianpointer
ID: 9915241
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.
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 9916016
Since moving to mySQL, the sorting as well as the importing is extremely fast. No need for it.
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 9935076
User resolved; points (125) refunded and question closed.

Netminder
EE Admin
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
FMX enumerated colours 2 117
Printing problem 2 102
DBGrid or StringGrid ? 6 105
Delphi XE7 Firemonkey - text turns black after tabbing between applications on android 5 44
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question