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 95/98/NT, click on the ODBC icon in the Control Panel.

This first datasource uses the driver from Oracle Corporation.

Figure: 3.3.1.1. Oracle Driver DSN Configuration
Oracle Driver DSN Configuration

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

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

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

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
MS SQL Server DSN Configuration

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

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

DSNs can be created and configured within the Virtuoso System Manager. Expand the menu tree on the left of the screen to select Databases/ External Databases/ Configure Data Sources. This pictures shows the creation of a DSN to an Oracle database using the OpenLink Generic 32 Bit Driver V4.0:

Figure: 3.3.1.9. Creating a new DSN
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. Expand the menu tree on the left of the screen to select Query Tools/Relational Data Using SQL. Enter a SQL statement for example:

SELECT * FROM DEMO..products WHERE UnitPrice < 7
Figure: 3.3.3.1. Demo Database Query
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 the System Manager page. Expand the menu tree on the left of the screen to select Databases/ External Databases/ Connected Data Sources. Then hit the Edit but to add a new Table link:

Figure: 3.3.4.1. Adding to Connected Data Sources
Adding to Connected Data Sources

In section 1. specify the remote datasource that you wish to link to Virtuoso.

In section 2. pick the tables to be linked, and define the names to use.

Figure: 3.3.4.2. Define tables to link
Define tables to link

3.3.5. Listing or Unlinking Tables

To list the tables that have been linked into Virtuoso, expand the menu tree on the left of the screen to select Databases/ External Databases/ Connected Data Sources. A table may be unlinked by pressing Edit button.

Figure: 3.3.5.1. List of Connected Data Sources
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. Expand the menu tree on the left of the screen to select Query Tools/Relational Data Using SQL. Type a SQL that accesses a remote table such as: SELECT * FROM DB.ORACLETESTDSN.DEPT

Press the Execute button and review the results.

Figure: 3.3.6.1. Remote Table Query
Remote Table Query