October 23, 2012 at 1:27 am
Hi.. I did this for reverse geocoding. I changed the url to:
DECLARE @URL varchar(MAX)
SET @URL = 'http://maps.google.com/maps/api/geocode/xml?sensor=false&latlng=' +
CAST(@GPSLatitude AS VARCHAR(20)) +
',' + CAST(@GPSLongitude AS VARCHAR(20))
But it shows null. What should I do to show the address for specific Lattitude and longitude using this techinique?
October 23, 2012 at 8:01 am
Hello,
I would suggest to use Google API ver. 3 (the current one) rather than ver. 2 referred in this post. Therefore, the URL should be something like:
http://maps.googleapis.com/maps/api/geocode/.....etc' - ver. 3
instead of:
http://maps.google.com/maps/api/geocode/ - ver. 2
October 23, 2012 at 8:30 am
It works on my system, I get:
33.755319-117.867595Santa AnaCA927011234 N Main St
but I modified the code to use Google API ver. 3 instead of ver. 2 referred in the article, please see the post just above this one.
Not sure though this is the reason, did you try to execute spGeocode with other parameter variations as described in the article?
Regards,
M.R.
October 24, 2012 at 6:25 am
thanx for the reply. I solved it with a little modification before seeing the reply... anywz, thnx again...:-)
October 25, 2012 at 10:32 pm
hi ,
i have changed api url from v2 to v3.
still getting null value after trying this : EXEC spGeocode '1234 N. Main Street, Santa Ana, CA'
Thanks
October 26, 2012 at 1:32 am
Sorry you are having problems. I just re-tested by downloading the unmodified Geocode.sql from the resources link at the end of the article and executed:
EXEC spGeocode '1234 N. Main Street, Santa Ana, CA'
I got valid results back:
33.755319-117.867595Santa AnaCA927011234 N Main St ...
(FYI, I just re-tested on SQL 2005 and 2008 R2)
I suspect your database is not set to allow calls to OLE Automation procedures. (I tested this--if this is disabled, I do get a one-row resultset with nulls...along with an error message.)
To enable, execute the following:
EXEC sp_configure 'show advanced options';
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;
If that is not the problem, you are likely dealing with some network issue--involving a proxy or filtering.
October 28, 2012 at 10:44 pm
Thanks,
It works now.
I just execute below things:
EXEC sp_configure 'show advanced options';
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;
Thanks a lot. 🙂
August 27, 2013 at 1:10 am
I really loved the article!
So I tried the same with the NS api (www.ns.nl/api), but when I put the url in firefox, I get results, but within my stored procedure I get @XML = null.
I do have to send parameters with my login details, but I don't think that would be the problem.
Here's my code (without username and password):
CREATE PROCEDURE [dbo].[spNS]
AS
BEGIN
SET NOCOUNT ON
DECLARE @URL varchar(MAX)
-- SET @URL = 'http://webservices.ns.nl/ns-api-treinplanner?fromStation=Utrecht+Centraal&toStation=Wierden&departure=true'
SET @URL = 'https://username:password@webservices.ns.nl/ns-api-treinplanner?fromStation=Arnhem&toStation=Wierden&departure=true'
DECLARE @Response varchar(8000)
DECLARE @XML xml
DECLARE @Obj int
DECLARE @Result int
DECLARE @HTTPStatus int
DECLARE @ErrorMsg varchar(MAX)
EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT
BEGIN TRY
EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml', @Response OUT
END TRY
BEGIN CATCH
SET @ErrorMsg = ERROR_MESSAGE()
END CATCH
EXEC @Result = sp_OADestroy @Obj
IF (@ErrorMsg IS NOT NULL) OR (@HTTPStatus <> 200) BEGIN
SET @ErrorMsg = 'Error in spNS: ' + ISNULL(@ErrorMsg, 'HTTP result is: ' + CAST(@HTTPStatus AS varchar(10)))
RAISERROR(@ErrorMsg, 16, 1, @HTTPStatus)
RETURN
END
SET @XML = CAST(@Response AS XML)
-- SET @GPSLatitude = @XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'numeric(9,6)')
-- SET @GPSLongitude = @XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'numeric(9,6)')
SELECT @Response AS XMLResults
END
August 27, 2013 at 3:13 am
Do you have an example how to do this with SQL CLR?
August 27, 2013 at 4:05 am
We're off-topic a bit (not talking about Geocoding any more, but rather calling an arbitrary web service). That's OK...
When I run your code (with either URL) I am getting:
Error in spNS: HTTP result is: 401
So the code is working, but the server is returning an HTTP 401 code (unauthorized)
The problem is that the MSXML2.ServerXMLHttp supports providing username / password credentials via properties, but not embedded in the URL.
You can use an updated GetHTTP routine(see below) that accepts username and password. You can use it like this:
DECLARE @MyResults varchar(MAX)
DECLARE @HTTPStatus int
EXEC dbo.sputilGetHTTP
@URL = 'http://webservices.ns.nl/ns-api-treinplanner?fromStation=Utrecht+Centraal&toStation=Wierden&departure=true',
@User = 'username',
@Password = 'password',
@ResponseText = @MyResults OUTPUT,
@HTTPStatus = @HTTPStatus OUTPUT
SELECT
@HTTPStatus AS HTTPStatus,
@MyResults AS Response
Here is the code for the updated spGetHTTP procedure:
CREATE PROCEDURE [dbo].[sputilGetHTTP]
@URL varchar(MAX),
--URL to retrieve data from
@HTTPMethod varchar(40) = 'GET',
--can be either GET or POST
@ContentType varchar(80)= 'text/http',
--set to 'application/x-www-form-urlencoded' for POST, etc.
@DataToSend nvarchar(4000) = NULL,
--data to post, if @HTTPMethod = 'POST'
@HTTPStatus int = NULL OUTPUT,
--HTTP Status Code (200=OK, 404=Not Found, etc.)
@HTTPStatusText nvarchar(4000) = NULL OUTPUT,
@ResponseText nvarchar(MAX) = NULL OUTPUT,
--Full text returned by remote HTTP server (if @SuppressResponseText = 0)
@ErrorMsg varchar(MAX) = NULL OUTPUT,
--NULL unless an error message was encountered
@LastResultCode int = NULL OUTPUT,
--0 unless an error code was returned by MSXML2.ServerXMLHttp
@User varchar(512) = NULL,
--If provided, use this value for the HTTP authentication user name
@Password varchar(512) = NULL,
--If provided, use this value for the HTTP authentication password
@SuppressResponseText bit = 0,
--If 0, actual content is not returned from remote server (just status code)
@SuppressResultset bit = 1,
--If 0, result set is is not returned (just parameters)
@SilenceErrors bit = 0
--If 1, errors are not raised with RAISEERROR(), but caller can check @ErrorMsg.
--@ErrorMsg will be null if no error was raised.
--Written by David Rueter (drueter@assyst.com)
AS
BEGIN
--Retrieves data via HTTP
--http://msdn.microsoft.com/en-us/library/aa238861(v=sql.80).aspx
SET NOCOUNT ON
DECLARE @Debug bit
SET @Debug = 0
DECLARE @CRLF varchar(5)
SET @CRLF = CHAR(13) + CHAR(10)
DECLARE @Obj int
DECLARE @PerformedInit bit
SET @PerformedInit = 0
DECLARE @ErrSource varchar(512)
DECLARE @ErrMsg varchar(512)
DECLARE @tvResponse TABLE (Response nvarchar(MAX))
SET @ErrorMsg = NULL
IF @Debug = 1 PRINT 'About to call sp_OACreate for MSXML2.ServerXMLHttp'
BEGIN TRY
EXEC @LastResultCode = sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END
ELSE BEGIN
SET @PerformedInit = 1
END
END TRY
BEGIN CATCH
SET @ErrorMsg = ERROR_MESSAGE()
END CATCH
BEGIN TRY
IF @LastResultCode = 0 BEGIN
IF @HTTPMethod = 'GET' BEGIN
IF @Debug = 1 PRINT 'About to call sp_OAMethod for open (GET)'
EXEC @LastResultCode = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false, @User, @Password
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END
END
ELSE BEGIN
IF @Debug = 1 PRINT 'About to call sp_OAMethod for open (POST)'
EXEC @LastResultCode = sp_OAMethod @Obj, 'open', NULL, 'POST', @URL, false, @User, @Password
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END
IF @Debug = 1 PRINT 'About to call sp_OAMethod for setRequestHeader'
IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', @ContentType
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END
END
END
IF @Debug = 1 PRINT 'About to call sp_OAMethod for send'
IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAMethod @Obj, 'send', NULL, @DataToSend
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END
IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END
IF @LastResultCode = 0 EXEC @LastResultCode = sp_OAGetProperty @Obj, 'statusText', @HTTPStatusText OUT
IF @LastResultCode <> 0 BEGIN
EXEC sp_OAGetErrorInfo @obj, @ErrSource OUTPUT, @ErrMsg OUTPUT
END
IF (@LastResultCode = 0) AND (ISNULL(@SuppressResponseText, 0) = 0) BEGIN
INSERT INTO @tvResponse (Response)
EXEC @LastResultCode = sp_OAGetProperty @Obj, 'responseText' --, @Response OUT
--Note: sp_OAGetProperty (or any extended stored procedure parameter) does not support
--varchar(MAX), however returning as a resultset will return long results.
END
END TRY
BEGIN CATCH
SET @ErrorMsg = ERROR_MESSAGE()
END CATCH
DECLARE @DestroyResultCode int
EXEC @DestroyResultCode = sp_OADestroy @Obj
SELECT @ResponseText = Response FROM @tvResponse
SET @ErrorMsg =
NULLIF(RTRIM(
ISNULL(@ErrorMsg, '') +
ISNULL(' (' + @ErrMsg + ')', '') +
ISNULL(' [' + @ErrSource + ']', '')
), '')
IF @ErrorMsg IS NOT NULL BEGIN
SET @ErrorMsg = 'Error in sputilGetHTTP: ' + @ErrorMsg
IF @PerformedInit = 0 BEGIN
SET @ErrorMsg = @ErrorMsg + @CRLF +
'Remember that this stored procedure uses OLE. To work properly you may need to configure ' +
'your database to allow OLE, as follows: ' + @CRLF +
' EXEC sp_configure ''show advanced options'', 1;' + @CRLF +
' RECONFIGURE;' + @CRLF +
' EXEC sp_configure ''Ole Automation Procedures'', 1;' + @CRLF +
' RECONFIGURE;' + @CRLF +
'Also, your SQL user must have execute rights to the following stored procedures in master:' + @CRLF +
' sp_OACreate' + @CRLF +
' sp_OAGetProperty' + @CRLF +
' sp_OASetProperty' + @CRLF +
' sp_OAMethod' + @CRLF +
' sp_OAGetErrorInfo' + @CRLF +
' sp_OADestroy' + @CRLF +
'You can grant rights for each of these as follows:' + @CRLF +
' USE master' + @CRLF +
' GRANT EXEC ON sp_OACreate TO myuser' + @CRLF +
' GRANT EXEC etc. ...'
IF ISNULL(@SilenceErrors, 0) = 0 BEGIN
RAISERROR(@ErrorMsg, 16, 1)
END
END
END
IF ISNULL(@SuppressResultset, 0) = 0 BEGIN
SELECT
@URL AS URL,
@ResponseText AS ResponseText,
@HTTPStatus AS HTTPStatus,
@LastResultCode AS LastResultCode,
@ErrorMsg AS ErrorMsg
END
END
August 27, 2013 at 4:39 am
Hi David,
Thank you so much for taking all this trouble to help me.
Works really good!!
Regards!
August 27, 2013 at 4:43 am
Yes, as a matter of fact I do. Here is the CLR source (see below).
See another article I wrote "Deploying CLR Assemblies with T-SQL[/url]" for one way to deploy CLR assemblies like this one.
//------start of CLR Source------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;
public partial class OpsStreamProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HTTPGet(
SqlString URL,
SqlString HTTPMethod,
SqlString ContentType,
SqlString DataToSend,
SqlString User,
SqlString Password,
out SqlInt32 HTTPStatus,
out SqlString HTTPStatusText,
out SqlBinary ResponseBinary,
out SqlString ErrorMsg
)
{
string thisURL = Convert.ToString(URL);
string thisHTTPMethod = Convert.ToString(HTTPMethod);
string thisContentType = Convert.ToString(ContentType);
string thisDataToSend = Convert.ToString(DataToSend);
string thisUser = Convert.ToString(User);
string thisPassword = Convert.ToString(Password);
string thisErrorMsg = new string();
byte[] binData = new byte[1];
byte[] buffer = new byte[4096];
Int32 responseStatusCode = 0;
string responseStatusDescription = null;
try
{
HttpWebRequest request = null;
HttpWebResponse response = null;
Stream responseStream = null;
request = (HttpWebRequest)WebRequest.Create(thisURL);
request.UserAgent = "SQL CLR Client";
if (thisHTTPMethod.Length == 0) {
request.Method = "GET";
}
else {
request.Method = thisHTTPMethod; //PUT/POST/GET/DELETE
}
request.ContentType = thisContentType;
if (thisDataToSend.Length > 0) {
thisErrorMsg = "thisDataToSend.Length > 0";
//convert string thisDataToSend to byte array
byte[] binSendData = System.Text.Encoding.Default.GetBytes(thisDataToSend);
//set ContentLength
request.ContentLength = binSendData.Length;
//get stream object for the request
Stream dataStream = request.GetRequestStream();
//write byte array to the stream
dataStream.Write (binSendData, 0, binSendData.Length);
//close the stream
dataStream.Close();
}
else {
request.ContentLength = 0;
}
response = (HttpWebResponse)request.GetResponse();
responseStream = response.GetResponseStream();
using(MemoryStream memoryStream = new MemoryStream())
{
int count = 0;
do
{
count = responseStream.Read(buffer, 0, buffer.Length);
memoryStream.Write(buffer, 0, count);
} while(count != 0);
binData = memoryStream.ToArray();
}
responseStatusCode = Convert.ToInt32(response.StatusCode);
responseStatusDescription = response.StatusDescription;
response.Close();
responseStream.Dispose();
}
catch (Exception ex)
{
SqlContext.Pipe.Send(ex.Message.ToString());
}
ResponseBinary = binData;
thisErrorMsg = "Hello World";
HTTPStatus = new SqlInt32(responseStatusCode);
HTTPStatusText = new SqlString(responseStatusDescription);
ErrorMsg = new SqlString(thisErrorMsg);
}
};
//------end of CLR Source------
March 21, 2016 at 11:19 am
Thank you, Great Article.
Do you have thoughts on if this would work with Bing Maps too ?
August 24, 2017 at 9:13 pm
hi all,
i have created the SP, mention in this topic.
when i execute the sp : EXEC GECODE_U
@Address = '1234 N. Main Street',
@City = 'Santa Ana',
@State = 'CA'
I got the out put like below
lat | long | add | city |state
33.755324 |-117.867594 |1234 North Main Street |Santa Ana | CA
Here I given input address : 1234 N. Main Street after execution sp i got address :1234 North Main Street
I want retrive exactely given input address is : 1234 N. Main Street only using this sp and without using cursor conecpt.
can you please tell me how to achieve this case.
February 9, 2018 at 5:01 pm
Thanks. This script still working
Viewing 15 posts - 46 through 60 (of 70 total)
You must be logged in to reply to this topic. Login to reply