Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2003-12-08
9
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

721 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