Solved

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

Posted on 2003-12-08
9
262 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month5 days, 1 hour left to enroll

636 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