IP to Country using Oracle and CFMX

Firstly download the IP-To-Country Database and unzip it

Then create a table in oracle

CREATE TABLE IPTOCOUNTRY(
IP_FROM NUMBER,
IP_TO NUMBER,
COUNTRY_CODE2 CHAR(2 BYTE),
COUNTRY_CODE3 CHAR(3 BYTE),
COUNTRY_NAME VARCHAR2(50 BYTE),
REGISTRY VARCHAR2(30 BYTE)
) TABLESPACE USERS;


ALTER TABLE IPTOCOUNTRY ADD (
CONSTRAINT PK_IPTOCOUNTRY PRIMARY KEY (IP_FROM, IP_TO)
USING INDEX
TABLESPACE USERS);

Load the data using the following sqlldr control file
(hint edit the paths)

OPTIONS (SKIP=132, DIRECT=TRUE)
LOAD DATA
INFILE 'C:\downloads\geoip\IpToCountry.csv'
BADFILE 'C:\downloads\geoip\IpToCountry.bad'
DISCARDFILE 'C:\downloads\geoip\IpToCountry.dsc'

INTO TABLE "IPTOCOUNTRY"
WHEN (1) <> '#'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' AND '"'
( IP_FROM,
IP_TO,
REGISTRY,
assigned filler,
COUNTRY_CODE2,
COUNTRY_CODE3,
COUNTRY_NAME)

then once that's loaded, you can then query the database, but you need to convert the ip address ( IE CGI.REMOTE_ADDR ) to a long ip format (ip2long in php, inet_aton in mysql), here's a CFMX library IPConvert for CFMX that will convert dotted quads to long numeric ip addresses and back.

then you just need to do a simple database query

<cfquery name="q_ip" datasource="oracle">

SELECT *

FROM iptocountry

WHERE ip_from <= <cfqueryparam value="#ip_long#" cfsqltype="CF_SQL_NUMERIC">

     and ip_to >= <cfqueryparam value="#ip_long#" cfsqltype="CF_SQL_NUMERIC">

</cfquery>

#q_ip.COUNTRY_CODE3# is the country name and #q_ip.COUNTRY_CODE2# is the two letter equivalent which can be used with thse small country flags or large country flags simply with the following code ("ZZ" are unassigned countries)

<fif q_ip.recordcount and q_ip.COUNTRY_CODE2 neq "ZZ">

<cfoutput>
  #q_ip.COUNTRY_CODE3#
  <img src="#q_ip.COUNTRY_CODE2#.png"
   width="20" height="13" alt="">
</cfoutput>

<CFELSE>

unknown

</cfif>