Important!

Connecting to remote databases is not easy and there are many things that are required in order for it to work.

• SQL 2 Excel Pro assumes you have a working connection using the PHP extension listed below for each database type.
• The database needs to be configured to accept remote connections
• You need a user account to login to the database
• You need to know the connect information / instance name etc of the database
• There are firewall issues, both on the web server as well as on the database server.

Joomla-R-Us will NOT support you in getting the database connection working! It's beyond the scope of SQL 2 Excel Pro. Tips and suggestions are posted below for your convenience.

You can select which database connection to use in each Worksheet. You could setup one Excel Workbook with multiple Worksheets which all came from different databases if you wanted.

MySQL

Joomla comes with a driver for MySQL so this works very well right out of the box. You don't need to install any extra extension in PHP. You do need to make sure you can connect to the remote database through the webserver and database server firewalls.

MySQL Database Connection Test Script

Oracle

This requires the Oracle OCI8 PHP extension to be installed and working on your server. You also have to setup the TNS Names for the Oracle database if the database is on a remote server. Currently the TNS_ADMIN environental variable needs to be set for PHP in order for it to find the tnsname.ora configuration file. A configuration setting for this is planned to be added inside SQL 2 Excel Pro shortly so that you can set it up easier on a shared server.

Please try this test script and make sure you can connect successfully:

Oracle Database Connection Test Script

MSSQL

Requires the mssql PHP extension to be installed and working on your server. I had a lot of problems getting it to work on my Windows XP machine. Turned out that a DLL that the newer version of PHP ships with is NOT compatible with MS SQL Server. Had to download and install an older version of the DLL in order to get it to work.

Microsoft SQL Server Connection Test Script

postgreSQL

Requires the PostgreSQL PHP extension to be installed and working on your server.

Custom Driver

If you need to connect to a non-listed database type, no worries! You can connect to any database, as long as you have some PHP extension/library that enables you to connect.

You can define your own custom database driver for SQL 2 Excel Pro. A sample driver for a MySQL database ships with SQL 2 Excel Pro. You can find it  in the SQL 2 Excel Pro installation :  ./administrator/components/com_sql2excel/helpers.driver_sample.php.

You can also download a sample driver here

The only limitation in SQL 2 Excel Pro with using a custom database driver is that the dynamic syntax check and explain is not supported.

For more information on how to define your own custom database driver for SQL 2 Excel, please see Using a Custom Database Driver.