| Jody Greening 2006-12-12, 7:12 pm |
| Hello all,
I require the ability to be able to get route info (specifically
Distance) from inside a SQL 2005 Function (so that it may be displayed
in a view). I know that it may be rough performance wise but I have no
other option right now (I don't think anyway...)
I created a small class library in .NET2.0 to load into the SQL
Database to handle the calls to the COM+ Mappoint component. I have
made much progress but now I cannot seem to get the Item to work.
Am I missing something here with the Assembly security, or can Mappoint
even be used in this context?
C# Code:
using System;
using System.Globalization;
using Microsoft.SqlServer.Server;
using MapPoint;
namespace CVSQLMappointAccess
{
public static class CVMapPointUtility
{
[Microsoft.SqlServer.Server.SqlFunction]
public static string CalcDrive(
string calcItem, // Item to calc. TRIPTIME will give you
time, Default/UnMatched will give TRIPTIME
string calcUnits, // Units to calc in, kilometers will give
you KMS, Default/UnMatched will give MILES
string fromStreet,
string fromCity,
string fromStateProv,
string fromZipPostal,
string fromCountry,
string toStreet,
string toCity,
string toStateProv,
string toZipPostal,
string toCountry)
{
string returnValue =3D String.Empty;
MapClass map =3D new MapClass();
FindResults startResults =3D null;
FindResults finishResults =3D null;
Location startLoc =3D null;
Location finishLoc =3D null;
object index =3D 1;
string kilometerMatchString
=3D "KILOMETERS";
string tripTimeMatchString =3D "TRIPTIME";
string kilometerTag =3D "KM";
string mileTag =3D "MI";
string unitAppend;
try
{
if (calcUnits.ToUpper() =3D=3D kilometerMatchString
)
{
map.Application.Units =3D GeoUnits.geoKm;
unitAppend =3D kilometerTag;
}
else
{
map.Application.Units =3D GeoUnits.geoMiles;
unitAppend =3D mileTag;
}
startResults =3D map. FindAddressResults(f
romStreet,
fromCity, "", fromStateProv, fromZipPostal, fromCountry);
finishResults =3D map. FindAddressResults(t
oStreet,
toCity, "", toStateProv, toZipPostal, toCountry);
if (startResults.ResultsQuality =3D=3D
GeoFindResultsQualit
y.geoAllResultsValid ||
startResults.ResultsQuality =3D=3D
GeoFindResultsQualit
y.geoFirstResultGood)
{
if (finishResults.ResultsQuality =3D=3D
GeoFindResultsQualit
y.geoAllResultsValid ||
finishResults.ResultsQuality =3D=3D
GeoFindResultsQualit
y.geoFirstResultGood)
{
startLoc =3D (Location)startResul
ts.get_Item(ref
index);
finishLoc =3D
(Location)finishResu
lts.get_Item(ref index);
map.ActiveRoute.Waypoints.Add(startLoc,
"Start");
map.ActiveRoute.Waypoints.Add(finishLoc,
"Finish");
map.ActiveRoute.Calculate();
if (calcItem.ToUpper() =3D=3D tripTimeMatchString)
{
returnValue =3D
FormatTime(map.ActiveRoute.TripTime);
}
else
{
returnValue =3D
FormatDouble(map.ActiveRoute.Distance, 2) + " " + unitAppend;
}
map.ActiveRoute.Clear();
}
else
{
returnValue =3D "End Address Not Found";
}
}
else
{
returnValue =3D "Start Address Not Found";
}
}
catch (Exception)
{
returnValue =3D "Error With Mappoint Module";
}
finally
{
// Clear ALL mappoint objects
map =3D null;
startResults =3D null;
finishResults =3D null;
startLoc =3D null;
finishLoc =3D null;
}
return returnValue;
}
public static string FormatDouble(double val, int
decimalPlaces)
{
....
}
public static string FormatTime(double val)
{
....
}
}
}
Registering the DLL with SQL 2005:
ALTER DATABASE MyDB SET trustworthy ON
CREATE ASSEMBLY CVSQLMappointAccess
FROM 'C:\SQLAssemblies\CV
SQLMappointAccess.dll'
WITH PERMISSION_SET =3D UNSAFE;
GO
NOTE: This also automatically adds the Interop.Mappoint.dll into SQL
2005 as unsafe as well.
SQL 2005 Function:
CREATE FUNCTION fn_calc_drive(
@calcItem NVarChar(20),
@calcUnits NVarChar(20),
@fromStreet NVarChar(100),
@fromCity NVarChar(100),
@fromStateProv NVarChar(100),
@fromZipPostal NVarChar(100),
@fromCountry NVarChar(100),
@toStreet NVarChar(100),
@toCity NVarChar(100),
@toStateProv NVarChar(100),
@toZipPostal NVarChar(100),
@toCountry NVarChar(100))
RETURNS NVarChar(100)
AS
EXTERNAL NAME
& #91;CVSQLMappointAcc
ess].& #91;CVSQLMappointAcc
ess.CVMapPointUtility].[CalcDrive]
Go
Calling the Function:
Select dbo.fn_calc_drive(
'distance',
'kilometers',
'1027 Wayne Pl',
'Burlington',
'ON',
'CA',
'',
'8 Riverview Ave',
'Mount Pearl',
'NL',
'CA',
'')
Error Received:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined
routine or aggregate 'CalcDrive':
System.UriFormatException: Invalid URI: The URI is empty.
System.UriFormatException:
at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind
uriKind)
at
System.ComponentModel.Design. RuntimeLicenseContex
t.GetLocalPath(String
fileName)
at
System.ComponentModel.Design. RuntimeLicenseContex
t. GetSavedLicenseKey(T
ype
type, Assembly resourceAssembly)
at
System.ComponentModel.LicenseManager. LicenseInteropHelper
.GetCurrentContext=
=ADInfo(Int32&
fDesignTime, IntPtr& bstrKey, RuntimeTypeHandle rth)
at CVSQLMappointAccess.CVMapPointUtility.CalcDrive(String calcItem,
String calcUnits, String fromStreet, String fromCity, String
fromStateProv, String fromZipPostal, String fromCountry, String
toStreet, String toCity, String toStateProv, String toZipPostal, String
toCountry)
I did a bit of searching regarding Error 6522 and I found mention of
permissions issues. I tried to register the Assemblies with
EXTERNAL_ACCESS but I get the following error:
Msg 6215, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'get_Application' on type
'MapPoint._Application' in external_access assembly 'Interop.MapPoint'
has invalid attribute 0x1003.
Can anybody provide any input on this?=20
Thanks,=20
Jody
|