Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 2 of 2

Mappoint 2004 - SQL 2005 - CLR Integration

This is a discussion on Mappoint 2004 - SQL 2005 - CLR Integration within the MapPoint Desktop Discussion forums, part of the Map Forums category; Hello all, I have a problem that I cannot seem to find allot of information for, this site came across ...

  1. #1
    jodygreening is offline Junior Member White Belt
    Join Date
    Dec 2006
    Posts
    1

    Mappoint 2004 - SQL 2005 - CLR Integration

    Hello all,
    I have a problem that I cannot seem to find allot of information for, this site came across my searches many times so I figured I should start here.
    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 = String.Empty;

    MapClass map = new MapClass();
    FindResults startResults = null;
    FindResults finishResults = null;
    Location startLoc = null;
    Location finishLoc = null;
    object index = 1;
    string kilometerMatchString = "KILOMETERS";
    string tripTimeMatchString = "TRIPTIME";
    string kilometerTag = "KM";
    string mileTag = "MI";
    string unitAppend;

    try
    {
    if (calcUnits.ToUpper() == kilometerMatchString)
    {
    map.Application.Units = GeoUnits.geoKm;
    unitAppend = kilometerTag;
    }
    else
    {
    map.Application.Units = GeoUnits.geoMiles;
    unitAppend = mileTag;
    }
    startResults = map.FindAddressResults(fromStreet, fromCity, "", fromStateProv, fromZipPostal, fromCountry);
    finishResults = map.FindAddressResults(toStreet, toCity, "", toStateProv, toZipPostal, toCountry);
    if (startResults.ResultsQuality == GeoFindResultsQuality.geoAllResultsValid ||
    startResults.ResultsQuality == GeoFindResultsQuality.geoFirstResultGood)
    {
    if (finishResults.ResultsQuality == GeoFindResultsQuality.geoAllResultsValid ||
    finishResults.ResultsQuality == GeoFindResultsQuality.geoFirstResultGood)
    {
    startLoc = (Location)startResults.get_Item(ref index);
    finishLoc = (Location)finishResults.get_Item(ref index);
    map.ActiveRoute.Waypoints.Add(startLoc, "Start");
    map.ActiveRoute.Waypoints.Add(finishLoc, "Finish");
    map.ActiveRoute.Calculate();
    if (calcItem.ToUpper() == tripTimeMatchString)
    {
    returnValue = FormatTime(map.ActiveRoute.TripTime);
    }
    else
    {
    returnValue = FormatDouble(map.ActiveRoute.Distance, 2) + " " + unitAppend;
    }
    map.ActiveRoute.Clear();
    }
    else
    {
    returnValue = "End Address Not Found";
    }
    }
    else
    {
    returnValue = "Start Address Not Found";
    }
    }
    catch (Exception)
    {
    returnValue = "Error With Mappoint Module";
    }
    finally
    {
    // Clear ALL mappoint objects
    map = null;
    startResults = null;
    finishResults = null;
    startLoc = null;
    finishLoc = 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\CVSQLMappointAccess.dll'
    WITH PERMISSION_SET = 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 [CVSQLMappointAccess].[CVSQLMappointAccess.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.RuntimeLicenseContext .GetLocalPath(String fileName)
    at System.ComponentModel.Design.RuntimeLicenseContext .GetSavedLicenseKey(Type type, Assembly resourceAssembly)
    at System.ComponentModel.LicenseManager.LicenseIntero pHelper.GetCurrentContextInfo(Int32& fDesignTime, IntPtr& bstrKey, RuntimeTypeHandle rth)
    at CVSQLMappointAccess.CVMapPointUtility.CalcDrive(St ring 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?

    Thanks,
    Jody

  2. #2
    mkassa is offline Junior Member White Belt
    Join Date
    Nov 2007
    Posts
    1

    Re: Mappoint 2004 - SQL 2005 - CLR Integration

    Hi Jody,

    I am currently having the an exact problem, I have registered my dll with unsafe, and when I try to run the CLR, I get

    System.UriFormatException: Invalid URI: The URI is empty. ....

    I am making a reference to adodb.dll, and when I take out the line of code that instatiated a new instance of adodb.Connection, it works fine.

    I would really appreciate if you could tell me how you got around it.

    Thanks

    Michael



Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Mappoint and Map Integration
    By gisUser in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 08-28-2006, 12:01 PM
  2. Mappoint 2004 ActiveX Delphi 2005 How to add a Polyline
    By it-fraggel in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 04-04-2006, 01:23 PM
  3. MapPoint 2004 and Visual Studio 2005 Runtime error
    By tizer001 in forum MapPoint Desktop Discussion
    Replies: 10
    Last Post: 02-26-2006, 09:51 AM
  4. Street detail - Streets/Trips 2005 versus MapPoint 2004
    By tmtemple in forum MapPoint Desktop Discussion
    Replies: 1
    Last Post: 07-01-2005, 07:32 AM
  5. MapPoint 2004 updated from Autoroute 2005
    By Anonymous in forum MapPoint Desktop Discussion
    Replies: 0
    Last Post: 03-07-2005, 04:49 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91