Extension:External Data/Databases/de-formal

The parser function #get_db_data allows retrieval of data from external databases. This function executes a simple SELECT statement and assigns the results to local variables that can then be used with the #external_value or #for_external_table functions.

A note about security: - If you are going to use #get_db_data you should think about the security implications. Configuring a database in LocalSettings.php will allow anyone with edit access to your wiki to run arbitrary SQL statements against that database, unless prepared statements are configured for the database connection. You should use a database user that has the minimum permissions for what you are trying to achieve. It is possible that complex SQL constructions could be passed to this function to cause it to do things vastly different from what it was designed for.

Konfiguration
Each database being accessed needs to be configured separately in LocalSettings.php. For normal databases (i.e., everything except for SQLite), add the following stanza for each database:

Where:


 * ID - a label for this database which is used when calling #get_db_data
 * server URL - the hostname on which the database lives
 * DB type - the type of database, i.e. mysql, postgres, mssql, oracle, sqlite, db2 or mongodb
 * DB name, username, password - details for accessing the database.

An example of a set of values would be:

The following optional settings can also be added:

Example values for these variables are:

Support for database systems
MySQL, Postgres (i.e. PostgreSQL), DB2 and MongoDB should work fully by default (though there are syntax limitations, and differences, for MongoDB - see below). For MS SQL/SQLServer, SQLite and Oracle, you may need to perform some special handling.

Postgres
If you cannot connect to a PostgreSQL database, it may be because your PHP installation is lacking the PostgreSQL database module, php-pgsql. On many Linux systems, you can install it by calling the following, then restarting the web server: yum install php55-php-pgsql

Amend the above configuration in LocalSettings.php to change the server type to "postgres":

SQLite
To connect to SQLite, you need something like the following in LocalSettings.php:

Oracle
Connecting to Oracle may work by default. If it doesn't work, the following may help:
 * Make sure that the Oracle client, and the PHP version being used, are using the same architecture: they have to either both be 32-bit, or both be 64-bit.
 * Make sure that the value of $edgDBServer for the installation matches something in the corresponding Oracle client .ora files. The value may need to look like "serverName/dbName", as opposed to "serverName".
 * If none of the above are the issue, you could try using the extension, which should work as well.

MongoDB
For MongoDB, there are no special connection parameters, although the username and password may be optional. There are two optional query parameters:  and. Under PHP 7.*, the extension  and library    is required. Unfortunately, due to the way that MediaWiki continuous integration is built, this library cannot be simply added to  for this extension (see T259743).

MongoDB is a non-SQL (or "NoSQL", if you prefer) database system, with its own querying language. When accessing MongoDB, you can either pass in a standard MongoDB query, or use the standard SQL-like syntax of #get_db_data. To use standard MongoDB querying, pass the query to the parameter  or.

You can also use the standard querying functionality. There are some restrictions and differences, however, for the "where" clause:


 * only "AND"s can be used, not "OR"s
 * for the "LIKE" comparison, no text should be placed around the comparator - it should look like "Username LIKE Jo", not "Username LIKE '%Jo%'".

Because MongoDB returns values in JSON that may be complex, and contain compound values, you can get data that is stored in such a way by separating field names with dots. For instance, if the return data contains a value for a field called "Measurements" that is an array, holding values for fields called "Height" and "Width", then the "data=" parameter to #get_web_data could have a value like "height=Measurements.Height,width=Measurements.Width".

You can do Memcached- or APCU-based caching of values retrieved from MongoDB; to do that, you need the following two lines in LocalSettings.php:

To enable ModgoDB under PHP 7.4,  extension should be enabled (and also   library should be installed with Composer:   (this will be necessary until bug T259743 is resolved).

Microsoft SQL Server
MediaWiki formerly natively supported MS SQL Server; this support went away in version 1.34. However, starting with version 3.1, External Data provides its won support for accessing MS SQL Server via ODBC.

The typical settings for accessing MS SQL Server will be:

Note the  setting and the comma between domain name and port in the   parameter. The odbc PHP extension has to be installed, as well as the Microsoft ODBC driver for SQL Server. The file  should contain

[ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1

Usage
To get data from an external database, call the following:

An explanation of the fields:

Unless the database is MongoDB,  can be omitted, in which case, the select statement will begin with   and all columns will be mapped to internal variables, with names in lowercase.
 * - the identifying label configured in LocalSettings.php
 * - an SQL "FROM" clause, i.e. one or more tables - can be as simple as  or as complex as   etc.
 * - corresponds to an SQL "JOIN ... ON" clause; used if there is more than one table being queried. An example value would be  etc.
 * - an SQL "WHERE" clause (optional)
 * - an SQL "LIMIT" clause, i.e. a number, limiting the number of results (optional)
 * - an SQL "ORDER BY" clause (optional)
 * - mapping of database column names to local variables (syntax: localVariable=databaseColumn - i.e. "employeeName" is the name of the database column in the example below).
 * - prevents any error message from getting displayed if there is a problem retrieving the data (optional)

An example call, using the "employee database" example from above:

Prepared statements
A safer approach is to define one or more prepared statements for the database connections defined in, in   item of the data source configuration array, which can be a string, containing a SQL query with parameters, for the only statement, or an associative array , for several.

Parameters to the prepared statement are passed as a comma-separated list in parser function argument. If there are several prepared statements defined for the same connection, the needed statement ID is passed as  parameter. If prepared statements are defined, arbitrary queries will not be created for the same connection.

PostgreSQL
Download and restore 'dvdrental' example database. Create user 'ED' with password 'ED' and grant to it read rights on 'public' scheme in 'dvdrental' database.

Create the function :

Add 'postgresql' database connection to LocalSettings.php:

Now, you can obtain a list of films shorter than 55 minutes with:

Microsoft SQL Server
It is assumed that Microsoft SQL server and the ODBC driver for it are installed, as well as the odbc extension for PHP; and the Northwind database is imported.

should have the entry: [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1

Then necessary configuration setting for External Data will be: and the parser function call retrieving and displayng the Washington employees data will be: