API query not working for some unknown reason

Graeme
Graeme used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
ste5anSenior Developer

Commented:
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:
Capture.PNG
When it works, then add some debugging output to your ASP code.
ste5anSenior Developer

Commented:
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

Graemewebber4technologies

Author

Commented:
Hi Ste5an, I have integrated the code, however it still shows at that I am in AU, not elsewhere
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Learn More!
ste5anSenior Developer

Commented:
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..
Graemewebber4technologies

Author

Commented:
I am currently in UAE (now), just in hotels so I wouldnt think so...
webber4technologies
Commented:
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

Graemewebber4technologies

Author

Commented:
self figured out

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial