Connecting to Databases

Importing data directly from database(s)

This section contains articles covering various aspects of importing data directly into Visokio applications from SQL-compliant relational database tables/reporting views using the Database Connection dialog and standard ODBC or JDBC protocols. Omniscope can import the results of any SQL statement that returns a single, flat table using either ODBC (one database, multiple tables) or JDBC (multiple databases, multiple tables) protocols. If you have large datasets, the JDBC option will perform better with most relational databases.

Topics:

Connecting to Relational Databases - contains various examples of making connections to transactional tables or (preferably) reporting views generated by relational databases from various different vendors.

MS Access ODBC - examples for 2007,
MS SQL Server JDBC - examples for 2008, 2005 and 2000 versions
Oracle 10g Thin JDBC - example for recent versions of Oracle
Oracle 8.1 JDBC - example for older versions of Oracle
MySQL 5.1 JDBC - example for MySQL
Generic JDBC  - general example that should work with most datbase vendors that support JDBC connections

Connection-caching - database connection caching is supported by Omniscope. This is mainly of use in Enterprise server installations, and should be ignored for typical desktop use as it will provide little benefit.

DSN-less ODBC - describes a method of connecting/refreshing multiple machines on a network without the administrative overhead of configuring the data source on each machine.

ODBC & JDBC - two standard protocols for connecting to relational databases, these options are sufficient for connecting to SQL-based transactional tables or reporting views. Some data sources which do not support these standards, for example MDX-based multi-dimensional 'cubes'.

Note: Importing and refreshing from delimited data files (including data files exported from relational databases) is covered in the section on importing/exporting to/from data files.