1. Home
  2. Docs
  3. Remote Databases
  4. Heterogeneous Connections

Heterogeneous Connections

WordPress uses a MariaDB or MySQL database to store its data. A connection to another remote MariaDB or MySQL database can be defined as a homogeneous database connection, which is a connection to a similar database management system.

A heterogeneous database connection is a remote connection to a different database management system (e.g. SQL Server, Oracle, PostgreSQL or MS Access). A heterogeneous database connection is more complex and requires additional resources and technologies.

WP Data Access users can connect to heterogeneous databases in three ways:

  1. Using the CONNECT engine (requires MariaDB and full OS access)
  2. Setup your own proxy server (requires a proxy server and full OS access)
  3. Connect through our Premium Data Service (zero configuration)

Article “Using ODBC | JDBC” explains the general preparation to setup a proxy server or use the MariaDB connect engine directly to connect to heterogeneous databases. Article “Connect to SQL Server (tutorial)” describes how to connect to SQL Server using a proxy or the MariaDB connect engine. Our premium data service makes it very easy to connect to homogeneous and heterogeneous databases and is explained in a separate article. Read more…

Using a heterogeneous database connection

Plugin tools

Heterogeneous connections are available from all WP Data Access tools. Just select a table or view from your heterogeneous remote database connection to create a publication, project or chart. Please be aware that a table must have a primary key or unique index to support data management, which is a general requirement in WP Data Access. Check your data types before you start updating (see limitations).

Custom PHP code

Heterogeneous database connections can be used in custom PHP code as well. Here is a template:

<?php

function heterogeneous_connection_example() {
	$wpdadb = WPDataAccess\Connection\WPDADB::get_db_connection( 'rdb:wpdafree.youniquedata.com' );
	$rows   = $wpdadb->get_results( 'select * from my_table' );
	foreach ( $rows as $row ) {
		// Process table row...
	}
}

add_action('plugins_loaded', 'heterogeneous_connection_example');

?>

The code example above connects to ‘rdb:wpdafree.youniquedata.com’. This is the free premium data service for premium users. Table ‘my_table’ can be a SQL Server, MS Access, Oracle or PostgreSQL table, but can also be a public CSV, JSON or XML file converted to a table automatically.

Class WPDADB is a sub class of WordPress class wpdb. You can use all available wpdb methods (query, get_results, insert, update, delete, and so on) to access your remote heterogeneous database.

Limitations

Heterogeneous connections have limitations:

  • A connection through a proxy adds a delay. The duration of the delay depends on the location of the servers (web server, proxy server, database server) involved.
  • Remote table structure modifications need to be synchronized locally.
  • Remote data types that are not available in MariaDB | MySQL are converted. Check your columns before you start updating.
  • Text column are truncated after 1024 characters.
Was this article helpful to you? Yes 1 No

Leave a Reply

Your email address will not be published.