www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

Quick Start & Tours

Where to Start
Client Connections
Virtual Database Server
Configuring Your ODBC Data Sources Datasource Check Demo Datasource Query Linking Remote Tables Into Virtuoso Listing or Unlinking Tables Querying Linked Tables
Web Server
WebDAV
Web Services
Exposing Persistent Stored Modules as Web Services
VSMX - Virtuoso Service Module for XML
SQL to XML
NNTP
Dynamic Web Pages
VSP Examples
Third-Party Runtime Typing, Hosting & User Defined Types
Troubleshooting Tips

3.3. Virtual Database Server

Virtuoso's Virtual Database Engine enables you to produce Dynamic Web Content from any major database management system. This enables dynamic, real-time HTML and XML generation from any number of different database engines concurrently. The Visual Server Administration Interface of Virtuoso allows you to effortlessly remotely choose and configure remote data sources to be linked into Virtuoso. Once a table is linked into Virtuoso then it is usable like any native table leaving you free to perform join queries without ever worrying about the underlying data source.

The Virtual Database (VDB) Engine section of the Conceptual Overview chapter explains the concepts in more details.

Visit the Visual Server Administration Interface section to see how to link tables into Virtuoso, or follow the small example below to get you started:

3.3.1. Configuring Your ODBC Data Sources

Before you can link a table into Virtuoso, you need to configure an ODBC datasource. It is quite likely that you already have ODBC data sources defined. If so, you can skip this part of the tour.

If you do not have any data sources defined, you should configure your datasource using an appropriate ODBC driver. The ODBC driver may come from the database vendor or a third-party middleware vendor, such as OpenLink Software. You will need to configure your ODBC datasource in accordance with the directions from your driver vendor.

In this part of the tour, we show sample DSN configuration information for some database vendor drivers. These examples of working data sources are not meant to replace the instructions from the driver vendor. In your specific installation other parameters may be necessary.

To define ODBC data sources on Windows XP/95/98/NT, in the Control Panel go to Administrative Tools and then click the Data Sources (ODBC) icon.

This first datasource uses OpenLink Generic ODBC Driver V6.0 to create DSN to a MySQL 5.x database.

OpenLink Mutli Tier DSN Configuration with database MySQL 5.x
Figure: 3.3.1.1. OpenLink Mutli Tier DSN Configuration with database MySQL 5.x

The next example is an Informix 7 datasource using the driver from Informix Software, Inc.

Informix Driver DSN Configuration
Figure: 3.3.1.2. Informix Driver DSN Configuration

The next few images show a Microsoft SQL Server 6.5 datasource using the Microsoft Corporation SQL Server driver.

MS SQL Server DSN Configuration
Figure: 3.3.1.3. MS SQL Server DSN Configuration
MS SQL Server DSN Configuration
Figure: 3.3.1.4. MS SQL Server DSN Configuration
MS SQL Server DSN Configuration
Figure: 3.3.1.5. MS SQL Server DSN Configuration
MS SQL Server DSN Configuration
Figure: 3.3.1.6. MS SQL Server DSN Configuration
MS SQL Server DSN Configuration
Figure: 3.3.1.7. MS SQL Server DSN Configuration

Finally, review the configuration information for your default local Virtuoso datasource, named "Local Virtuoso".

OpenLink Virtuoso DSN Configuration
Figure: 3.3.1.8. OpenLink Virtuoso DSN Configuration

DSNs can be created and configured within the Virtuoso Conductor. Go to "Database", then to "External Data Sources" and then go to "Configure Data Sources" tab. Click the "Add System DSN", or "Add User DSN", or "Add File DSN" button.

Creating a new DSN
Figure: 3.3.1.9. Creating a new DSN

3.3.2. Datasource Check

Before trying to link a table into Virtuoso, you should verify that the datasource is working correctly. Use a tool such as Microsoft Access, or ODBCtest to test your datasource. Your sanity check will verify that the datasource is correctly configured, that all necessary software is installed, that the database is running, and that there are no permission related problems. In addition, it will verify that the driver that you are using works correctly with the underlying datasource.

If there is a problem using a specific tool such as Microsoft Access with a specific driver, then that same problem will be manifested when the datasource is exposed via Virtuoso.


3.3.3. Demo Datasource Query

To query the sample data in the Demo Database make sure the web browser is pointing to the Demo database HTTP port which is typically 8890. From Conductor go to tab "Database" and then select the tab "Interactive SQL". Enter a SQL statement and click the button "Execute". For example:

SELECT * FROM DEMO..products WHERE UnitPrice < 7
Demo Database Query
Figure: 3.3.3.1. Demo Database Query

3.3.4. Linking Remote Tables Into Virtuoso

A table on a remote datasource may be linked into the Virtuoso database so that it appears as a local table. Start a browser and point to Conductor, for ex. http://localhost:8890/conductor/. Go to tab "Database", then select "External Data Sources" and then "Sata Sources". Specify the remote datasource that you wish to link to Virtuoso. For example, click the link "connect" for DSN "VirtuosoDemoTest".

Enter login name and password.
Figure: 3.3.4.1. Enter login name and password.

As result should be shown for DSN "VirtuosoDemoTest" the links "Link objects", "Change Credentials", "Disconnect". Click the "Link objects" link.

Connected to datasource.
Figure: 3.3.4.2. Connected to datasource.

Pick the tables to be linked, and define the names to use.

Define tables to link
Figure: 3.3.4.3. Define tables to link

3.3.5. Listing or Unlinking Tables

To list the tables that have been linked into Virtuoso, from Conductor go to Databases/ External Data Sources/ External Linked Objects. A table may be unlinked by pressing its "Unlink" icon.

List of Connected Data Sources
Figure: 3.3.5.1. List of Connected Data Sources

3.3.6. Querying Linked Tables

Once the tables have been linked into Virtuoso, they can be queried using the Interactive SQL query. From the Conductor UI go to Database/Interactive SQL. Type a SQL that accesses a remote table such as: SELECT * FROM Demo.VirtuosoDemoTest.CustomersLinked. Click the "Execute" button.

Press the Execute button and review the results.

Remote Table Query
Figure: 3.3.6.1. Remote Table Query