Academic Company Events NI Developer Zone Support Solutions Products & Services Contact NI MyNI
What is Developer Zone?
United States

Document Type: Tutorial
NI Supported: Yes
Publish Date: Sep 06, 2006


Feedback


Yes No

Related Categories

Products

Development Topic

Related Links - Developer Zone

Related Links -Products and Services

Citadel 5 Remote Database Access Using COM+ and ADO

11 Ratings | 3.91 out of 5
 Print |  PDF

Overview

With the update to Citadel 5 included with LabVIEW DSC 7.0 and later, many improvements have been made. Along with these improvements come some changes that may be problematic for certain use cases. In particular, it is now more difficult to obtain data from a Citadel database from remote client computers that do not have at least the LabVIEW Datalogging and Supervisory Control (DSC) Module Run-Time System installed.

Citadel 4 and Citadel 5 both include ODBC drivers which let you make standard SQL queries to your Citadel data. In both cases, when a query is made to retrieve data from a Citadel database on a remote server, the local ODBC driver passes the query to the local Citadel database. The local Citadel database is then responsible for communicating with the remote server database and retrieving the requested data. Once retrieved, the local Citadel database returns the requested data to the requestor via the ODBC driver. The database ODBC driver cannot be installed separately to retrieve data from a remote server because the ODBC driver does not perform inter-PC communication. The complete Citadel database is required for this operation.

The Citadel 4 database came with the Logos communication drivers so any computer that had Logos installed (which can be downloaded at no charge) could easily communicate with a Citadel 4 database on a remote server. Due to licensing issues associated with the tools used to build Citadel 5, Logos can no longer be distributed at no charge. The most cost-effective solution for easy remote data access is to purchase the LabVIEW DSC Module Run-Time System for all client computers. This will install the Citadel 5 database on the client computers and allow easy ODBC queries to remote Citadel 5 databases.

If purchasing the LabVIEW DSC Module Run-Time System for the client computers is not an option for your system, the following technique provides a mechanism for remotely accessing Citadel 5 data from a client that does not have the Citadel 5 database installed. The only requirement for the client is that the program trying to remotely access a Citadel 5 database must support ActiveX communication.

Note: The remainder of this document pertains to Citadel 5 only, so following mentions of Citadel refer to Citadel 5.

Table of Contents

  1. COM+ and ADO Approach
  2. Creating the Server Interface
  3. Configuring the Server Interface
  4. Creating Client / Server Distribution Packages
  5. Installing Client / Server Distribution Packages
  6. Writing Client Code
  7. Possible Issues

COM+ and ADO Approach

Microsoft's ADO provides a common ActiveX-based interface that will communicate with a database through its ODBC driver. COM+ then provides the technology to expose a custom ActiveX-based interface to other computers on the network. This document discusses how to create a custom ActiveX interface to ADO and then expose the interface to other client computers on the network through COM+. These technologies are supported only in Microsoft Windows XP/2000.

This technique is useful only when writing custom ADO/ODBC client code. It will not work with existing database clients such as MSQuery. MSQuery is a component of Microsoft Office, and is the most common way to import database data into Microsoft Excel. It is still possible to import data from your Citadel database into Microsoft Excel, but not using MSQuery. Since Microsoft Excel has the capability of running Visual Basic code in the form of macros, you can use macros to import data from a Citadel database into Microsoft Excel. Refer to the Writing Client Code section for an example of using macros to import data from a Citadel database.

The approach taken by this example is to configure our application using a three-tier architecture. The application is divided into three components: a client responsible for displaying data to the user, a database that stores the application data (in this case, Citadel), and a middle-tier component who's construction is covered in this document. The purpose of the middle-tier component is to keep the client and database isolated from one another, so that changes to one do not require changes to the other. The middle tier can be installed on the same machine as the Citadel database server, and since the client has no need to talk to the Citadel database directly, Citadel does not need to be installed on the client machine.
See Also:
Microsoft ActiveX Data Objects (ADO)
Microsoft Data Access Components (MDAC)
Microsoft Component Services Overview (COM+)
Using a Three-Tier Architecture Model

Creating the Server Interface


If you just need a simple SQL interface to your remote Citadel database, you may not need to create your own interface. If the simple SQL interface described in this section is sufficient for your application, you may want to skip ahead to the Installing Client / Server Distribution Packages section.

To grant access to its Citadel databases, the server will publish a custom ActiveX interface as a Windows COM+ application. This section shows how to create such an interface using Microsoft Visual Basic 6.0. Note that due to significant changes in the programming environment, the code below will not work if copied directly into Microsoft Visual Basic 7.0 or later (.NET). The important point is that the same techniques can be employed with any language capable of creating an ActiveX DLL, the syntax would just be somewhat different.

  1. Launch the Visual Basic editor.
  2. Create a new ActiveX DLL project. This will create a project containing a single, empty Visual Basic class.
  3. Using the Project Explorer and Properties Window, rename the project to something appropriate. For this example, CitadelRemoteODBC is used.
  4. Using the Project Explorer and Properties Window, rename the class to something appropriate. For this example, RemoteQuery is used.
  5. In the property list for the RemoteQuery class, change the MTSTransactionMode property to have a value of 1 - NoTransactions.
  6. Open the Project»References dialog, and add Microsoft ActiveX Data Objects 2.7 Library to your project and click OK.
  7. Implement the methods for the class. The RemoteQuery class in this example provides a single method, and its code is shown below. You can make your application more sophisticated by adding individual methods for predefined queries, more robust error handling, etc.
    '*****************************************************************
'* Method Query()
'*   Param: sqlString [in]
'*     The sqlString used to query data from Citadel. This value
'*     is taken ByVal to minimize network traffic when this method
'*     is invoked by a remote client
'*   Param: dsnName [in]
'*     The dsnName is used to specify which Citadel database is
'*     being queried
'*   Returns:
'*     An ADO recordset containing the query results. The returned
'*     recordset will be disconnected from the database for best
'*     optimal performance over the network
'*****************************************************************
Public Function Query(ByVal sqlString As String, _
                      ByVal dsnName As String) As ADODB.Recordset
    On Error GoTo ErrorHandler:

    Set Query = New ADODB.Recordset
    With Query
        ' In order to support a disconnected recordset, we must
        ' use a client-side cursor.
        .CursorLocation = adUseClient

        ' Set the connection and query strings. This is no different
        ' than any other ADO connection to the Citadel 5 ODBC driver
        .ActiveConnection = "Provider=MSDASQL.1; " & _
            "DRIVER={National Instruments Citadel 5 Database};" & _
            "DATABASE=Citadel5;" & _
            "Persist Security Info=False; " & _
            "Data Source=" & dsnName
        .Source = sqlString

        'Open the connection and retrieve data
        .Open

        ' Close the connection. The data will still be accessible
        ' through our client-side cursor, and COM+ will be able
        ' to efficiently move the results across the network to
        ' the client.
        Set .ActiveConnection = Nothing
    End With
    Exit Function

ErrorHandler:
    Set Query = Nothing
End Function
  8. Save your project to an appropriate location and filename. Note that the location you save to is where your COM+ DLL will be created.
  9. Select File»Make CitadelRemoteODBC.dll. This will compile the project and generate the appropriate DLL.
  10. Select Project»CitadelRemoteODBC Properties. Select the Component tab and in the Version Compatibility section, select Binary Compatibility and click OK. This setting is required for compatibility with COM+.
  11. Save your project again, and make the DLL again, overwriting the existing one. Note that this rebuild is required because you must have compiled the DLL once already before setting the Binary Compatibility option.
To download the Visual Basic project for the server interface above, click the ServerInterface download link on the examples page below.
See Also:
Citadel 5 Remote Database Access Using COM+ and ADO - Examples

Configuring the Server Interface


If you just need a simple SQL interface to your remote Citadel database, you may not need to configure your own interface. If the simple SQL interface described in the Creating the Server Interface section is sufficient for your application, you may want to skip ahead to the Installing Client / Server Distribution Packages section.

  1. Launch the Windows control panel. Open the Administrative Tools folder, and then open the Component Services applet.
  2. Expand the tree view: Component Services»Computers»My Computer.
  3. Right-click the My Computer node, and select Properties.
  4. On the Default Properties tab, verify that the Enable Distributed COM on this computer box is checked, then click the OK button to close the dialog.
  5. Expand the My Computer node.
  6. Right-click the COM+ Applications folder and select New»Application.
  7. Click Next, then Create an empty application. Choose a name for this application. For this example, CitadelRemoteODBC was used. Set the Activation type to Server application. Click the Next button.
  8. Choose an account under which the application will run. For testing purposes, Interactive user is sufficient, but a dedicated user account should be created and assigned for deployed systems. Note that for interactive user configurations to work properly, there must be a user with sufficient user privileges currently logged into the server machine for a client to be able to access it. To remove this limitation, you must specify a user. Once the server is configured, or if you install the server rather than creating it, this important option can be accessed by right-clicking the CitadelRemoteODBC application and selecting Properties. On the Identity tab, change the option to This user, and enter that user name and password.
  9. The default security configuration is sufficient to allow client machines logged into the same domain to access a COM+ application. If the client and server computers are not on the same domain, you may be able to connect properly by ensuring that the client computer and server computer have users with the same name and password configured, and that this user is either logged in or configured as the user for the COM+ application. If you have trouble connecting to your application, consult the Component Services documentation for information on configuring COM+ security. Some additional information is discussed below in the Possible Issues section.
  10. Click the Next button, then the Finish button.
  11. Expand the node for your component. Right-click the Components folder and select New»Component.
  12. Click the Next button, then Install new component(s). Browse to the DLL you built in the Creating the Server Interface section, then click the Next button and the Finish button.
The above steps are the bare minimum required to create a running application. COM+ provides configuration options, which may be of use with your application. You can find additional information about COM+ configuration either in the Component Services help, or by following the link below.

By right-clicking your application in the Component Services tree, you can start and stop running instances of your application. This is helpful during development, as you will need Windows to release your DLL whenever you need to build a new version.
See Also:
Microsoft COM+ (Component Services)

Creating Client / Server Distribution Packages


If you just need a simple SQL interface to your remote Citadel database, you may not need to create your own distribution packages. If the simple SQL interface described in the Creating the Server Interface section is sufficient for your application, you may want to skip ahead to the Installing Client / Server Distribution Packages section.

Complete the following steps to create an installer for distributing your ActiveX interface to multiple clients.
  1. Launch the Component Services applet and navigate to your application, as described in the Configuring the Server Interface section.
  2. Right-click your application node and select Export.
  3. Click the Next button.
  4. Under Export as, select Application proxy. Enter a path, such as C:\ClientInstaller. If you are running Windows XP and you need to support clients running Windows 2000, place a checkmark in the Save application in COM+ 1.0 format - some properties may be lost checkbox.
  5. Click the Next button then the Finish button.
  6. This will generate C:\ClientInstaller.msi and C:\ClientInstaller.cab. Run ClientInstaller.msi on the client machine to register your ActiveX class with the client.
To download the Client Installer for the server interface above, click the ClientInstaller download link on the examples page below.

Complete the following steps to create an installer for distributing your ActiveX interface to multiple servers.
  1. Launch the Component Services applet and navigate to your application, as described in the Configuring the Server Interface section.
  2. Right-click your application node and select Export.
  3. Click the Next button.
  4. Under Export as, select Server application. Enter a path, such as C:\ServerInstaller. If you are running Windows XP and you need to support servers running Windows 2000, place a checkmark in the Save application in COM+ 1.0 format - some properties may be lost checkbox.
  5. Click the Next button then the Finish button.
  6. This will generate C:\ServerInstaller.msi and C:\ServerInstaller.cab. Run ServerInstaller.msi on the server machine to register your ActiveX class with the server.
To download the Server Installer for the server interface above, click the ServerInstaller download link on the examples page below.
See Also:
Citadel 5 Remote Database Access Using COM+ and ADO - Examples

Installing Client / Server Distribution Packages


To distribute the packages, run the appropriate installer on the clients and servers. The server and client interfaces in this example are named ServerInstaller and ClientInstaller respectively. ServerInstaller.msi should be run on the machines with Citadel installed to allow clients access to the Citadel databases. ClientInstaller.msi should be run on any machine without Citadel installed from which you want to be able to access the Citadel databases on your servers. These files are included, and can be installed as-is, or they can be created as described in the previous sections.

Note that the included files are configured with the default security options and set to run as the Interactive user. Changes to these settings may be required for your network configuration, and can be made in the Component Services applet accessed as described in the Configuring the Server Interface section.

To download the Client Installer for the server interface above, click the ClientInstaller download link on the examples page below.
To download the Server Installer for the server interface above, click the ServerInstaller download link on the examples page below.
See Also:
Citadel 5 Remote Database Access Using COM+ and ADO - Examples

Writing Client Code


In your client code, add the Microsoft ActiveX Data Objects 2.7 Library and CitadelRemoteODBC controls to the project references. In the Excel Visual Basic Editor, this is done via Tools»References. In Visual Basic, it is done through Project»References.

Once this is done, the following code snippet is all you need to do to perform a remote query, modifying the server, DSN, and SQL strings for your application.
Const svrStr As String = "dataserver"
Const dsnStr As String = "C__Program_Files_National_Instru"
Const sqlStr As String = "SELECT * FROM IntData " & _
                         "WHERE IntInterval = '1:00'"
Dim result As ADODB.Recordset
Dim citadel As CitadelRemoteODBC.RemoteQuery
Set citadel = CreateObject("CitadelRemoteODBC.RemoteQuery", svrStr)
Set result = citadel.Query(sqlStr, dsnStr)
Once you have the result in the code above, it is not necessarily intuitive what to do with the ADO Recordset that is returned by the query. Included in the example page below are three client examples that show some ways to handle this data.

For all client examples, keep the following caveats in mind. DSN names should be System DSNs on the server computer. View DSN names by selecting Start»Settings»Control Panel»Administrative Tools»Data Sources (ODBC) and then the System DSN tab on the server computer. The default SQL query string will return all traces from the selected database, and decimate the data on one-minute intervals. Keep in mind that if your database contains data that spans a week or more, returning this data with a point every minute can quickly become a huge set of data, and the query could take a very long time. Refer to Appendix A, Using SQL to Access Historical Data in a Citadel Database, of the LabVIEW Datalogging and Supervisory Control Module Developer Manual, linked below, for more information on how to format the SQL query string.

LabVIEW Client
The LabVIEW client included is fairly self-explanatory. Set the Server, DSN, and SQL query string, and run the VI. Refer to the block diagram and VI description for more information. Refer to the LabVIEWClient download from the example page linked below.

Excel Client
The Excel Client is a simple VBA macro in the worksheet. Follow the instructions on Sheet1 to run the macro. You may need to modify your Excel security settings under Tools»Options to allow macros to run. There is little-to-no explicit error handling in this example; for more robust error handling, refer to the LabVIEW or Visual Basic clients. Once viewing the Excel Worksheet, select Tools»Macro»Visual Basic Editor to view the macro code. When creating such a module, do not forget to add Microsoft ActiveX Data Objects 2.7 Library and CitadelRemoteODBC under Tools»References. Refer to the ExcelClient download from the example page linked below.

Visual Basic Client
The Visual Basic client is similar to the code in the Excel Client, but it handles the data differently, using a Microsoft DataGrid ActiveX control to display the Recordset data. Run the program, enter your Server, DSN, and SQL query string, then click the Query button. When creating such a client, do not forget to add Microsoft ActiveX Data Objects 2.7 Library and CitadelRemoteODBC under Project»References. Refer to the VisualBasicClient download from the example page linked below.
See Also:
LabVIEW Datalogging and Supervisory Control Module Developer Manual
Citadel 5 Remote Database Access Using COM+ and ADO - Examples

Possible Issues


When dealing with such a variety of technologies - networks, databases, COM+, ADO, etc. it is sometimes difficult to make things work as desired on the first try. Often some errors are seen. These errors, while frustrating at first, can often help you discover what the problem is, and sometimes even what you can do to fix it. The following are some of the errors encountered while testing and preparing this document and the associated examples, along with a more detailed explanation of possible causes and what you might be able to do to resolve the issue. There are other errors that you could run into, but these are the most common, and hopefully this information will be useful to you if you encounter them.

Error Code: -2147023174
Error Message: The RPC server is unavailable.
Possible Reason: The server name specified is incorrect or cannot be reached.
Possible Resolution: Verify that you are entering the server name correctly and that the server machine is on. Try connecting to the server via some other means, such as pinging, to ensure the client can see the server. Try using the machine numeric IP address instead of the host name.

Error Code: -2147221164
Error Message: Class not registered.
Possible Reason: The server specified is responding, but does not have the server COM+ component installed.
Possible Resolution: Install the server COM+ component via the included installer or your own custom installer on the server specified.

Error Code: -2147467238
Error Message: The server process could not be started because the configured identity is incorrect. Check the username and password.
Possible Reason: The included server, as configured, requires a user to be logged in to the server computer for queries to function properly.
Possible Reason: The user logged in to the client computer is not a user on the same domain as the user logged into the server computer.
Possible Resolutions: Log into the server computer as the appropriate user. Alternatively, modify the settings for the COM+ application to run as a specific user, as described in the Configuring the Server Interface section. To resolve the second issue you may need to contact your IT department to ensure your computers are on the same domain and that you are logged into both computers as a user on that domain. For additional options, consult the COM+ documentation on security settings.

Error Code: -2147024891
Error Message: Access is denied.
Possible Reason: The client computer is not on the same network domain as the server.
Possible Reason: The user logged in to the client computer is not a user on the same domain as the user logged into the server computer.
Possible Resolutions: You may need to contact your IT department to ensure your computers are on the same domain and that you are logged into both computers as a user on that domain. For additional options, consult the COM+ documentation on security settings.

Error Code: 97
Error Message: Unknown System Error
Possible Reason: The DSN Name specified does not exist or is a User DSN that was created by a different user than the one currently logged in or configured for the COM+ application.
Possible Reason: The SQL query string was formatted incorrectly.
Possible Resolutions: Verify the SQL query format in Appendix A, Using SQL to Access Historical Data in a Citadel Database of the LabVIEW Datalogging and Supervisory Control Module Developer Manual. When dealing with DSNs, National Instruments recommends using System DSNs to avoid issues associated with User DSNs only being visible to specific users. If you are using a System DSN already, verify that it exists on the server as you have typed it. Refer to the Writing Client Code section for information about verifying a system DSN.
See Also:
Microsoft COM+ Security
LabVIEW Datalogging and Supervisory Control Module Developer Manual

11 Ratings | 3.91 out of 5
 Print |  PDF

Reader Comments | Submit a comment »

Very effective

- Chandan Chakraborty,CJ Softwares. chandan_agt@yahoo.com.au - Nov 18, 2009

 

Legal
This tutorial (this "tutorial") was developed by National Instruments ("NI"). Although technical support of this tutorial may be made available by National Instruments, the content in this tutorial may not be completely tested and verified, and NI does not guarantee its quality in any way or that NI will continue to support this content with each new revision of related products and drivers. THIS TUTORIAL IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND AND SUBJECT TO CERTAIN RESTRICTIONS AS MORE SPECIFICALLY SET FORTH IN NI.COM'S TERMS OF USE (http://ni.com/legal/termsofuse/unitedstates/us/).