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.
- Using the CONNECT engine (requires MariaDB and full OS access)
- Setup your own proxy server (requires a proxy server and full OS access)
- Connect through our Premium Data Service (zero configuration)
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 a 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 subclass 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 columns are truncated after 1024 characters.
