ASP
ASP

API query not working for some unknown reason

Hi all, I have a particular API Query that isnt working...

	Set SessionLookup=oConn.Execute("SELECT * FROM site_sessions WHERE session_ID="&Session.SessionID)
	If Not SessionLookup.EOF Then
		Session_ID=SessionLookup("session_ID")
	Else
		Set oXMLHTTPLocIP=CreateObject("MSXML2.ServerXMLHTTP")
		LocIP="http://api.ipify.org/"
		oXMLHTTPLocIP.Open "GET",LocIP,False
		oXMLHTTPLocIP.Send
		If oXMLHTTPLocIP.Status="200" Then
			IP=oXMLHTTPLocIP.responseText
			Set oXMLHTTPLoc=CreateObject("MSXML2.ServerXMLHTTP")
			IP=oXMLHTTPLocIP.responseText
			LocAPI="http://ip-api.com/json/"&IP&"?fields=countryCode,city"
			oXMLHTTPLoc.Open "GET",LocAPI,False
			oXMLHTTPLoc.Send
			If oXMLHTTPLoc.Status="200" Then
				GetTextFromUrlLoc=oXMLHTTPLoc.responseText
				CityLoc=Replace(Split(Split(GetTextFromUrlLoc,",")(0),":")(1),"""","")
				CountryLoc=Replace(Replace(Split(Split(GetTextFromUrlLoc,",")(1),":")(1),"""",""),"}","")
			End If
		End If
		oConn.Execute("INSERT INTO site_sessions(session_ID,brand_ID,session_IP,session_geoCity,session_geoCountry,session_timezone,session_date,session_time) VALUES("&Session.SessionID&",'"&SubDomain("brand_ID")&"','"&IP&"','"&CityLoc&"','"&CountryLoc&"','"&Timezone()&"','"&Date()&"','"&Time()&"')")
		Set NewSession=oConn.Execute("SELECT @@IDENTITY AS New_Session_ID")
		Session_ID=Cstr(NewSession("New_Session_ID"))
	End If

Open in new window

I am currently in Malaysia and this API thinks im in Australia.. I am so confused!

Graemewebber4technologiesAsked: 2016-10-01

Who is Participating?

Graemewebber4technologiesAuthor Commented: 2016-10-07

After some playing around, I have got this code to work:

Set oXMLHTTPLoc=CreateObject("MSXML2.ServerXMLHTTP")
LocAPI="http://ip-api.com/json/"&IP&"?fields=countryCode,city"
oXMLHTTPLoc.Open "GET",LocAPI,False
oXMLHTTPLoc.Send
If oXMLHTTPLoc.Status="200" Then
	GetTextFromUrlLoc=oXMLHTTPLoc.responseText
	CityLoc=Replace(Split(Split(GetTextFromUrlLoc,",")(0),":")(1),"""","")
	CountryLoc=Replace(Replace(Split(Split(GetTextFromUrlLoc,",")(1),":")(1),"""",""),"}","")
End If

Open in new window

LVL 39

ste5anSenior DeveloperCommented: 2016-10-01

Well, it works from here:

Set httpIP=CreateObject("MSXML2.ServerXMLHTTP")
LocIP="http://api.ipify.org/"
httpIP.Open "GET",LocIP,False
httpIP.Send
WScript.Echo "httpIP status:", httpIP.Status
If httpIP.Status="200" Then
	IP=httpIP.responseText
	WScript.Echo "httpIP response:", httpIP.responseText
	
	Set httpLocation=CreateObject("MSXML2.ServerXMLHTTP")
	IP=httpIP.responseText
	LocAPI="http://ip-api.com/json/"&IP&"?fields=countryCode,city"
	httpLocation.Open "GET",LocAPI,False
	httpLocation.Send
	WScript.Echo "httpLocation status:", httpLocation.Status
	If httpLocation.Status="200" Then
		WScript.Echo "httpLocation response:", httpLocation.responseText
		GetTextFromUrlLoc=httpLocation.responseText		
		City = Replace(Split(Split(GetTextFromUrlLoc,",")(0),":")(1),"""","")
		Country = Replace(Replace(Split(Split(GetTextFromUrlLoc,",")(1),":")(1),"""",""),"}","")
		WScript.Echo "City: ", City
		WScript.Echo "Country: ", Country
	End If
End If

Open in new window

Run it on the command line using cscript.exe:

When it works, then add some debugging output to your ASP code.

LVL 39

ste5anSenior DeveloperCommented: 2016-10-01

Ah, well, then it seems to use an outdated IP to location database. I don't think there can be done anything about it.

Try a different one: ip-api

Set httpIP=CreateObject("MSXML2.ServerXMLHTTP")
LocIP="http://ip-api.com/json"
httpIP.Open "GET",LocIP,False
httpIP.Send
WScript.Echo "httpIP status:", httpIP.Status
If httpIP.Status="200" Then
	IP=httpIP.responseText
	WScript.Echo "httpIP response:", httpIP.responseText	
End If

Open in new window

Graemewebber4technologiesAuthor Commented: 2016-10-05

Hi Ste5an, I have integrated the code, however it still shows at that I am in AU, not elsewhere

LVL 39

ste5anSenior DeveloperCommented: 2016-10-05

hmm, another interesting question is: What IP do you use? Maybe your provider is a global company who's reusing IP addresses on a global scale. In such a scenario a simple IP lookup cannot work..