WP Data Access
  • Download
  • Support
  • Features
  • Pricing
  • Documentation
Select Page
Popular Search formimportqueryshortcodesql

WP Data Access

  • Getting started
  • Installation
  • What’s new?
  • Patches
  • Known limitations
  • FAQ

Data Tables

  • Getting Started
  • Creating and publishing
  • Responsive data tables
  • SEO friendly data tables
  • Adding hyperlinks
  • Extension Manager
  • Style Manager
  • Language settings
  • Styling
    • Style Manager
    • Premium styling
    • Global styling
    • Code Manager Styling
    • Manual styling
    • Styling issues
  • Filters
    • Static filters
    • Interactive filters
      • URL parameters
      • Search Panes
      • Search Builder
      • Search form
      • Advanced search
      • Geolocation search
  • Export & other buttons
    • Export to PDF, CSV, Excel, SQL
    • Print & copy buttons
    • Column selection buttons
    • Custom buttons
  • Advanced features
    • Advanced settings
    • Large tables & performance
    • Progressively SHOW MORE
    • Custom queries
    • Custom Post Types
    • Row grouping
    • Responsive master-detail
    • Master-detail tables
    • Non admin user access
  • Demos & tutorials
    • Demos
    • Tutorials

Data Apps

  • Data Projects
  • Projects and templates
  • Supported Data Types
  • One-To-Many relationships
  • Many-To-Many relationships
  • Registration page
  • Manage table options
  • Shortcode usage
  • Project Templates
    • Project Templates
    • Creating templates
    • Table Settings
    • Relationships
    • List Table
    • Data Entry
    • Reconcile
  • Data Forms
    • Data Forms
    • Overview
    • Project Demo
    • Page Demo
  • Advanced features
    • Adding filters
    • Custom validation
    • Add less/more button
    • User roles
    • Managing roles and users
    • Advanced table options
    • Advanced lookups
    • Front-end toolbar
    • Transfer to other WP site
  • Demos & tutorials
    • Demos
    • Tutorials

Data Explorer

  • Getting started
  • Navigation
  • Explore tables & views
  • Manage tables & views
  • Naming conventions
  • Manage Actions
    • Actions
  • Manage Settings
    • Settings
    • Table Settings
    • Column Settings
    • Search Settings
    • Geolocation Settings
    • Dynamic Hyperlinks
    • Dashboard Menus
  • Data Management
    • Import CSV & SQL files
    • Run SQL script files
    • Data Backup
  • Problems & solutions
    • InnoDB #rows estimation
    • Import file too large
    • Transfer to other WP DB

Remote Databases

  • MySQL | MariaDB
  • Other DBMS
    • Heterogeneous Connections
    • Using ODBC | JDBC
    • Connect to SQL Server
  • Premium Data Services
    • Remote wizard
    • START HERE
    • Errors and messages
    • Limitations
    • Tutorials
    • Remote Databases
      • Remote databases
      • SQL Server
      • PostgreSQL
      • Oracle
      • MariaDB | MySQL
    • Remote Data Files
      • Remote data files
      • CSV files
      • JSON files
      • MS Access
      • XML files
      • Public URL
      • Synchronization
    • Advanced features
      • Performance
      • Indexes

SQL

  • Getting Started
  • Tutorials
  • Visual Query Builder
  • Writing queries
  • Data exchange
  • Stored procedures
  • Database triggers
  • Scheduled queries

Business Intelligence

  • Getting started
  • Dashboards
  • Sharing dashboards
  • Dashboards Widgets
    • Dashboard Widgets
    • Publication Widget
    • Project Widget
    • Chart Widget
    • Custom Code Widget
    • Database Widget
    • Sharing Widgets

Table Design

  • Getting started
  • Introduction
  • Basic mode

Plugin Settings

  • Getting started
  • Plugin
  • Back-end
  • Front-end
  • Dashboard
  • Data Forms
  • Data Tables
  • Data Backup
  • Uninstall
  • Manage Repository
  • Manage Roles
  • System Info

Code

  • Shortcodes
    • Overview
    • wpdataaccess
    • wpdadataproject
    • wpdadataforms
    • wpdadiehard
    • wpdageomap
    • wpdawidget
  • Plugin Variables
    • WordPress user ID in SQL
    • Environment variables
  • Hooks & filters
    • Overview
    • Hooks
      • Overview
      • wpda_add_search_actions
      • wpda_add_search_filter
      • wpda_before_list_table
      • wpda_after_list_table
      • wpda_wpdataaccess_prepare
    • Filters
      • Overview
      • wpda_column_default
      • wpda_before_simple_form
      • wpda_after_simple_form
      • wpda_construct_where_clause
  • API
    • WP Data Access API
    • Remote database access
    • CRUD Example
    • Extensions
      • Alternative search algorithm
      • Alternative buttons extension
  • Code Manager
    • Overview
    • Write code
    • Share(d) code

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 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.
Share This Article :
  • Facebook
  • Twitter
  • LinkedIn
  • Pinterest
Still stuck? How can we help?

How can we help?

Updated on 2022-12-19
Using ODBC | JDBC

Submit a Comment Cancel reply

Your email address will not be published. Required fields are marked *

Table of Contents
  • Using a heterogeneous database connection
    • Plugin tools
    • Custom PHP code
  • Limitations

Links

Blogs
Tutorials
Demos

Socials

LinkedIn
Twitter

Get in touch

Premium support
Free support forum
Contact us

Resources

WordPress plugin directory
YouTube tutorials

Passionate Programmers B.V. © 2017 - 2023 All rights reserved