Solved

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

Posted on 2003-12-08
9
256 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now