ARTICLES

MapPoint Automation with C/C++ - Part 2

This is the second of three articles by Sergey Kostrov on automating MapPoint with C/C++. In this installment, Sergey demostrates how to automate MapPoint from a MS SQL Server Extended Stored procedure.

What is an Extended Stored procedure?

Microsoft’s Extended Stored procedures extend functionality of the MS SQL Server. An Extended Stored Procedure can be implemented in Dynamic-Link Library ( DLL ) using C/C++ languages and called exactly in the same way as regular Stored procedures are called. Since C/C++ languages are used to create Extended Stored procedures you can do almost everything on the Server side! It means, that actually all COM-objects, installed on your Server computer, and Win32 API are available for you.

MapPoint Automation in Client-Server architecture

Microsoft Visual C++ has a template for the project that can contain Extended Stored procedures:

As soon as you created “Extended Stored Procedure Dll” project open “ReadMe.txt” file because it has very useful information about how to register/un-register an Extended Stored procedure.

In order to automate MapPoint from an Extended Stored procedure you can use C/C++ code from the Part 1 of my article. Also, don’t forget to install MapPoint on the computer where MS SQL Server is already installed!

In Client-Server architecture OLE Automation from an Extended Stored procedure is a very powerful method to do an advanced processing. Amount of data that will be sent over network is controllable. You do all processing on the Server side and it will be done significantly faster since usually server computers are faster and have more memory.

A few comments and recommendations

• First advice is one of the most important: Don’t try to do Research and Development ( R&D ) with Extended Stored procedures on your company’s primary MS SQL Server! Use local MS SQL Server installed on your computer to learn all necessary things and only when you’re very confident in your code use it on the primary MS SQL Server;
• Install MapPoint on the computer where MS SQL Server is already installed, that is, where you will do R&D;
• If you like the idea of OLE Automation with the MS SQL Server on the Server’s side I strongly recommend to study Microsoft’s documentation related with how to implement Extended Stored procedures. Unfortunately you won’t be able to find lots of examples with Extended Stored procedures and be ready to spend uncountable number of hours learning how to do it. But you will be rewarded because performance of such subsystem can be significantly higher;
• You also must study Microsoft’s documentation to learn how to debug an Extended Stored procedure. SQL Server Books Online has one small article and it describes everything in details. Believe me it will be sufficient just to follow all steps described in the article to understand basic steps in debugging of an Extended Stored procedure. To find that article you have to open SQL Server Books Online, on the Index page enter expression “extended stored procedures” and then double click on the item debugging;
• Remember, that Microsoft’s developers have created a “hidden problem” for all of us! It is related with using Extended Stored procedures in the Multi-User environment. It means, that if you’re going to execute the same Extended Stored procedure from several client computers at the same time you have to synchronize your calls;
• Here is the picture that shows how everything works:
  

  It doesn’t matter how you invoke an Extended Stored procedure because it can be called in the same way as a regular Stored Procedure is called. As you can see on the scheme MS SQL Server and MS MapPoint are installed on the same computer. Take into account that standalone version of MapPoint can’t be used in the Internet environment because it has some limitations described in the End-User License Agreement. If you will use MapPoint only for internal purposes you won’t violate End-User License Agreement but I recommend you to contact Microsoft for more information.

• Your Extended Stored procedure that automates MapPoint can look like:

  …
  MapPoint::_ApplicationPtr g_pMapPoint = NULL;
  …
  RETCODE __declspec(dllexport) xp_proc( SRV_PROC *srvproc )
  {
  …								// anything you want to do
  if (g_pMapPoint == NULL )
  {
  g_pMapPoint.CreateInstance( "MapPoint.Application" );
  g_pMapPoint->Visible = true;
  g_pMapPoint->PutWindowState( geoWindowStateNormal );
  g_pMapPoint->PutCaption( "MapPoint 2004 is in Automation Mode!" );
  g_pMapPoint->PutUnits( geoKm );
  }
  
  _MapPtr myMapPtr = g_pMapPoint->ActiveMap;
  
  FindResultsPtr resultsPtr = myMapPtr->FindAddressResults
  (“400 Coronado Dr”, “Denton”, "", “TX”, “76209”, geoCountryDefault );
  
  VARIANT idx;
  VariantInit( &idx );
  V_VT(&idx) = VT_I4;
  V_I4(&idx) = 1;
  LocationPtr locAddressPtr = resultsPtr->GetItem( &idx );
  PushpinPtr pinAddressPtr = myMapPtr->AddPushpin( locAddressPtr, "Your property is here" );
  
  pinAddressPtr->PutSymbol( 57 );					// Red triangle ( big )
  pinAddressPtr->BalloonState = geoDisplayBalloon;
  pinAddressPtr->Highlight = true;
  locAddressPtr->GoTo();
  myMapPtr->ZoomIn();
  …								// anything you want to do
  }
  

• If you want to pass parameters into your Extended Stored procedure you should use pointer *srvproc to SRV_PROC structure. Microsoft’s documentation describes how to do it;
• To execute xp_proc Extended Stored procedure, for example from MS Query Analyzer, use the command:
   EXECUTE master..xp_proc
• Don’t forget about compiler-generated files mpna82.tlh and mpna82.tli because to some degree they have everything you need;
• MapPoint is a very resource consuming application and usually it uses at least 20MB of memory:
  

• I would compare Microsoft’s Extended Stored procedures with Novell’s Netware Loadable Modules ( NLMs ). Both work on the Server side and both have to be as reliable as possible because even a small error in your code can “destroy” or affect lots of processes on the Server. For both design, implementation, debugging and testing is a very time consuming process;
• Here is an example how everything looks like under the control of Visual C++ Debugger:
  
  (Click on the image to view the full screenshot.)

  On the picture you can see 4 overlapped windows: Visual C++, SQL Server (window with black background), SQL Query Analyzer ( right window ) and automated MapPoint.

	MP2Kmag Internet