Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

Geocode Addresses in T-SQL Expand / Collapse
Author
Message
Posted Friday, October 26, 2012 1:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 2:44 PM
Points: 55, Visits: 430
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.867595 Santa Ana CA 92701 1234 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.



Post #1377422
Posted Sunday, October 28, 2012 10:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 28, 2012 10:43 PM
Points: 3, Visits: 21
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. :)
Post #1378095
Posted Tuesday, August 27, 2013 1:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 3:04 AM
Points: 10, Visits: 129
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

Post #1488578
Posted Tuesday, August 27, 2013 3:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 3:04 AM
Points: 10, Visits: 129
Do you have an example how to do this with SQL CLR?

Post #1488640
Posted Tuesday, August 27, 2013 4:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 2:44 PM
Points: 55, Visits: 430
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

Post #1488656
Posted Tuesday, August 27, 2013 4:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 3:04 AM
Points: 10, Visits: 129
Hi David,

Thank you so much for taking all this trouble to help me.

Works really good!!

Regards!
Post #1488666
Posted Tuesday, August 27, 2013 4:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 2:44 PM
Points: 55, Visits: 430
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" 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------

Post #1488667
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse