last updated: 2013-01-01

jDBexport versionbranch 3.0.x - user manual

This is the user manual for jDBexport. It is considered work-in-progress and is continously updated and enhanced. Please note that screenshots are taken from our DEMO website and may differ depending on layout templates and product versions. This documentation is written to our best knowledge, but if you spot mistakes please inform us (and we will correct them).

Please note that jDBexport 3.0.x is only compatible with Joomla 2.5.x - and will reach it's end of life on January 31, 2015

We therefore removed this versionbranch from our download area. If you wish to update to the latest available version 3.0.5 , please contact us directly.


Table of content


go to top


jDBexport is a Joomla component which allows you to export data from your databases (JOOMLA and others). The reports are generated on-demand into an Excel document, when the users request them.

Forked from the well known (Joomla 1.5 extension) SQL 2 Excel Pro data reports extension jDBexport allows you to deliver data from your databases directly to your users. Write any valid SQL query in the configuration of this module. The query is run when a user clicks the link and the current information in the database is delivered to the users browser as as an Microsoft Excel 2003 (or as CSV or HTML) document. Support for Microsoft Excel 2007 (*.xlsx) and other document formats is planned for future versions.

To make the most of this reporting component, you should have a basic knowledge of the SQL database language (see Structured Query Language on Wikipedia) and it's usage in your own RDBMS (Relational database management system, most probably one of the following: SQL:2008, PostgreSQL, DB2, MS SQL Server, MySQL, Oracle, Informix).

Although it is not the scope for this user manual to describe the usage of SQL, we still do include a short chapter with some very basic guidance. For further reading you can always refer to SQL Beginner's guides in bookstores...

For any questions about this manual or the jDBexport component, please contact us directly.


go to top

New and enhanced features

Starting with Version 3.0.1 we implement new features (above the original features set of the predecessor product) into jDBexport. We use this chapter of the documentation to present these features to you and give you some background information.


Contend datafield plugin

Version 3.0.3 comes with an additional datafield plugin to embed a single cell from any valid SQL query into any content. Check out the documentation below for details.

Multilanguage support

Version 3.0.1 comes with full multilanguage support for both frontend and backend. This means, that (hopefully) all text strings are converted to language-file strings and implementing another language only needs the translation of these files. In the download area you will find an additional file called with ALL available language files in it. Unzip this file to a local directory and use the the JOOMLA standard installation procedure to install individual language packages.

If you would like to help us with translating the product to more languages, you are very welcome to join our translation project on the Transifex translation plattform. You can use this to request new languages and to translate our language file resources into these languages. We look forward to see you there...

Color picker

Version 3.0.1 solves the issue with the wrong colorindex. We implemented a new color picker (resembling the color picker known from Excel 2003) instead of the previous selectlist. This colorpicker, developed by Brian Grinstead is written in javascript, so you must have javascript activated in your browser to use this (otherwise you will only see "rgb(150, 150, 150)" strings).

You now can easily select the font- or background colors for various elements from this color picker.

Background infos on Excel 2003 and it's handling of colors

Excel's color palette contains 56 colors. The blog describes the layout of the palette and enumerate the default palette colors. This is reflecting the Excel 2003 color palette, in earlier versions of Excel this palette is different, and in later versions of Excel (e.g. 2007 and following) workbooks can support millions of colors, but there is still an underlying workbook palette with 56 colors. In the jDBexport  color picker, we use the definitions published at ( please note that there are minor adjustments in the color picker with jDBexport 3.0.2 to fully comply with this definition).


go to top


Please note that jDBexport requires JOOMLA 2.5.6 or later, but note that the current version branch 3.0.x IS NOT compatible with JOOMLA 3.x - we are working on a J3.x compatible version, but have no estimate of a release date yet.

The installation utilises the JOOMLA standard installation procedure. All separate parts of this extension (the component, modules, plugins, language packs) are packed as JOOMLA extensions and can be installed using the JOOMLA extension manager.

Navigate to our Download aera, download the desired package and install it on your website. The package will decide whether an UPDATE or INSTALL is required depending on the the existence of a previous version (an UPDATE will not alter your data like categories, worksheets, workbooks, additional databases, schedules and settings).

AFTER INSTALLATION: do not forget to open the [Options] toolbar button and set the desired configuration options (see Configuration for details).

NOTE: To avoid possible problems we suggest you uninstall any previously installed BETA version of jDBexport before installing a stable version.

go to top


jDBexport uses a wide varity of configuration options. This options are accessible through the components toolbar button [Options].

We here describe all configuration options in the order they are found in the toolbar button [Options] window.


Toolbar button [Options]

General (Tab)

SQL security options

  • Check SQL
    Enable (default) or disable checking of the final (after substitution) SQL query for potentially bad words.
  • Disallowed words
    A list of potentially hazardous SQL keywords, that you wish to block. Please note that on top of this list (which can be maintained by you) we implemented an additional layer of SQL query elements, that are not allowed - this additional layer is not maintainable directly.

Preview options

  • Maximum number of rows
    The maximum number of rows that is to be created in preview mode (applied for each worksheet individually).
  • Show formulas
    The representation of EXCEL formulas in the preview mode (whether they should be calculated, labeled with a default label or completely hidden).
  • Show extra cells
    Whether or not extra cells should be visible in the preview mode
  • Whether or not the [Download] button should be visible in the preview mode

Scheduler options

  • The e-mail address the scheduler uses to send emails from
  • The name the scheduler uses to send emails from
  • The e-mail address the scheduler uses as REPLY-TO address
  • The name the scheduler uses as REPLY-TO name
  • Whether or not each receipient should receive an individual email
  • Whether or not to embed HTML workbooks in HTML emails

Caching options

  • Whether or not the caching should be used by the component
  • The time (in minutes) the caching holds documents


Writers (Tab)



Categories (Tab)



Worksheets (Tab)



Workbooks (Tab)





go to top


jDBexport utilises to the JOOMLA standard categories. This means that all functionality of the JOOMLA standard categories (access levels, nesting, ...) are likewise available for jDBexport categories. For more details on how to deal with JOOMLA categories, please refer to the JOOMLA documentation.


go to top




Howto debug the main SQL query

If your SQL query does not seem to work right, there is an easy way to debug it. In the header of the worksheet, add the following code


and make sure you activated the "include no results" parameter. Download the workbook and in the header of the worksheet you should see the query that was executed (including the substitutions). If it does look correct, take this SQL query to phpMyAdmin and run it there. You should hopefully be able to spot the problem there.


go to top







go to top

Workbook parameters

jDBexport allows you to define parameters for documents. These parameters can then be used in your SQL queries (similar to variable substitution), where the values will be substituted at runtime. You can set the default values and visibility for the parameters in the document configuration. If you set a parameter hidden then the default value will be used.

If visible parameters are defined for a document, then a lightbox window with the parameter input fields will appear before downloading the document (in preview mode, a separate fieldset will be visible with the parameter input fields). The content of these input fields will be used as substitutes for the parameters in your SQL query (or other places of your worksheet/workbook).



Available parameter types

Parameter type Description Example (see demo site) SQL  
String Character string DEMOSITE  

Integer value

Integer unsigned Interger value (only positive numbers possible) 4711  
Floatingpoint number      
Floatingpoint number unsigned        
Alphanumeric String with only alphanumeric characters (e.g. 'a-z', 'A-Z', '0-9') Demo4711    
Email email address      
Date formated date (details see below) 2010-11-18    
List of values - singleselect        
LoV - multselect - numeric        
LoV - multselect - string        
LoV - SQL - singleselect        
LoV - SQL - multselect - numeric        
LoV - SQL - multselect - string        


Date parameters

The date parameters are a powerful instrument to filter your downloaded data, but as such they need special considerations...

Date ranges

[to be written...]


Default values

In some cases you will want to prepopulate the date parameter with a default value depending on the current date (rather than a fixed default value). To achieve this, you can make use of the facts that

  • the initial values of parameters are evaluated sequentially (meaning that parameter 1 is evaluated BEFORE parameter 2, and so on), and
  • only those parameters are shown to the user, that are set to VISIBLE


To have a current-date dependent parameter (of the type "date"), you can do the following:

  • Create a workbook parameter 1 as following:
    • Name:      "invisible parameter 1" (or something else)
    • Variable:   "INVISIBLE1" (or something else)
    • Type:         "List of values, SQL single select"
    • Show:        FALSE
  • Create a workbook parameter 2 as following:
    • Name:      "My date" (or something else)
    • Variable:   "MYDATE" (or something else)
    • Type:         "Date"
    • Default:     "{INVISIBLE1}"
    • Show:        TRUE

This will prepopulate the MYDATE parameter with a default value one year earlier than today. For more details on the used (and other available) date functions in MySQL refer to the MySQL manual date functions section.




go to top

Variable substitution

With jDBexport, you can use substitution variables in the document's header / footer and also in the SQL query to create dynamic reports. These variables get substituted with the current value when the workbook is created (e.g. when the user clicks to download the workbook). Please consult the list of available variables below for details.

Note: to use variable substitution, please make sure that the corresponding options in the [OPTIONS] -> [Worksheets] dialog are set to YES.

List of available substitution variables

User variables

Variable name Description Example (see demo site) H / F SQL
{USER_ID} The user id, as in $user->id (0 when user not is logged in) 819
{USER_USERNAME} The username, as in $user->username SITS.Demouser
{USER_NAME} The name, as in $user->name Demo User
{USER_EMAIL} The email address, as in $user->email This email address is being protected from spambots. You need JavaScript enabled to view it.

Server variables

Variable name Description Example (see demo site) H / F SQL
{SERVER_URL} The Server root URL, JURI::root()
{SERVER_HOST} The Server hostname, $_SERVER['HTTP_HOST']  
{SERVER_IP} The IP address of the server, $_SERVER['SERVER_ADDR']  



go to top

Formulas in queries and extra cells

jDBexport allows to use Excel formulas in your document. You can use the following variables either in your SQL query or in definitions of extra cells.

You also need to tell jDBexport, where your formulas reside. To do so, list the columns (separated by comma if more than one) in the corresponding field of the [Layout] section of the [Main] tab of the edit worksheet view.

Formula substitution variables

Variable name Description Example (see demo site) SQL ExtraCell
{COLID} The current column ID Column F in your worksheet would be ID 6  
{ROWID} The current row ID Row 47 in your worksheet would be ID 47  
{MINROWID} The minimum row ID, e.g. first data row of the main query    
{MAXROWID} The maximum row ID, e.g. last data row of the main query    
{LMAXROWID} The last maximum row ID    
{LMINROWID} The last minimum row ID    
{OAMAXROWID} The overall maximum row ID (so far)    
{OAMINROWID} The overall minimum row ID (so far)    

To address cells, columns or rows relative to these IDs, you can use simple expressions like {COLID-5} or {ROWID+3}

You then can make use of Excel formulas in your worksheet with SQL queries like this one:


SQL query 








go to top

Frontend functionallity

For the frontend jDBexport has two menue items to be configured:

  • Show (nested) jDBexport categories
  • Show documents in jDBexport category

The first one shows a treeview of (accessible) jDBexport categories, starting from a top level category that is defined in the menue items required settings.

The second one shows a table of (accessible) documents within a jDBexport category that is defined in the menue items required settings.

This view allows the user to either preview or download the available documents. The preview creates the selected document and directly shows a limited number of records in a simplified layout.

Note: please remember to set the corresponding options (in the Category tab of the options dialog) to show the preview and download button!

We kept these frontend pages very simple. If you require additional styling for your website's frontend, you find all styling documents (images, stylesheet) in the /media/com_jdbexport folder of your JOOMLA root directory.






go to top

Database connections

With jDBexport, you can create reports not only from the JOOMLA database. You also can connect to external databases. Go to the [Databases]->[NEW] window and enter the required connection details (the details differ with different types of databases):

Once you saved the connection details for this database, you can check whether the connection can be established or not by clicking on the [Check connection] icon. Starting with jDBexport version 3.1.0 there will be additional connectors for other database management systems.


go to top

Using a customized database driver


go to top


jDBexport comes with a powerful scheduler application. The scheduler allows you to repeatedly run tasks on your website and databases, like sending emails, cache updating your workbooks or submit SQL queries directly. The scheduler consists of two parts:

  • The schedule with all its parameters (when, what, how, how often, ...). This is configured within the jDBexport component.
  • The schedule handler, that is actually executing the defined tasks. This is set up outside JOOMLA, as the CMS does not support running scheduled tasks.

Please note, that for security reasons, the scheduler functionality is NOT ACTIVATED within our demo environment ( ). If you need to try this (before being convinced that it works...) you can contact us directly to discuss this.

Configuring the schedule

A scheduled task (new or existing) consists of two screen tabs (Main and Advanced). Each of these tabs holds parts of the configuration of a single scheduled task. Let us walk through these configurations step by step now.

Main Tab

The Main tab is devided in two divisions (General and Action). Depending on your selection of an action to be performed, a third division may appear with further configuration details for this specific action.

General division

This is where the main scheduler configuration is set:

  • Title of the scheduled task (a string identifying the task)
  • Frequency (drop down list of frequencies, with which interval the task should be repeated)
  • Start date/time (select the date from the calendar popup. If needed, manually change the time in the created date/time string).
  • End date/time (if needed, select the date from the calendar popup. If needed, manually change the time in the created date/time string).
  • Counting (this is a readonly-field with tne number of runs for this task so far).
  • Maximum number of runs (if needed, set a maximum number of runs. The task will not be executed anymore, if this number is reached).
  • Failures (this is a readonly-field with tne number of failed runs for this task so far).
  • Published
  • Ordering
  • Scheduler security - This feature is not yet implemented, so you can enter anything here without any consequences.

Action division

This is where the type of the scheduled task is set. The following types are available:

  • Update cache (either for all workbooks or only for selected workbooks)
  • Send email (with or without links to workbooks / attached workbooks)
  • Run a defined SQL query

Workbooks division

If the (above) selected action requires a selection of workbooks, this division shows up with two controlpanes to easily select/deselect and order the workbooks, for which the task should be run.

Email division

If the (above) selected action involves sending an email, this division shows up with the required fields to setup the email:

  • Email format (Text or HTML)
  • TO email addresses (separated by comma)
  • CC email addresses (separated by comma)
  • BCC email addresses (separated by comma)
  • Subject for the email
  • Body for the email (formatable, if HTML is selected as email format)

SQL query division

If the (above) selected action is SQL query, this division shows up with the required fields to setup the SQL query:

  • Database on which the SQL query shall be executed on.
  • SQL query to be executed.


Advanced Tab






Setting up the schedule handler

There are two (manually) prerequests to be done before you can successfully use the scheduler

  • You will have to rename the scheduler script from
  • Create the scheduler caching folder at
    and make sure it is writeable for the component

As PHP or JOOMLA do not support a decent task scheduler, you need to set this up manually. There are multiple solutions for this task:

  • If your ISP allows you to define CRON jobs on your webserver, this would be the easiest solution to achieve this task. However, most ISP's deny this for security and performance reasons.
  • You can set up a task on a local computer or server, that will run the script on your webserver (the solution we prefer!)
  • You can set up a task on to run the script on your webserver (this service is not free, but very, very cheap)
  • You can add lines of code to your website template, that triggers the script to run (only if no other solution works for you!)
  • ...


Solution 1 consists of filling out the corresponding form on your ISP's webserver control panel, so this should be explained there.


Solution 3 is described in detail on website, although you better stick to their french original or english translation (the other translations have their own charmes)


Solution 4 has major disadvantages like:

  • Execution of the scheduler handler depends on the fact that your website has visitors.
  • It slows down the website for your visitor
  • The script will be executed "by the user", so all ACL issues are related to the users access level
  • it does not work, in case you have secured your /administrator directory by using an .htaccess file

Include this line into your website template:


Solution 2 is explained in detail here:

This is the solution we use for all kind of scheduled and repeated tasks on our and our customers websites (like backup, newsletter mailings, updating "featured elements", ...). We run this on a windows server 2008, so if you use a different operating system, the steps will be slightly different. However, the core elements will be compareable. You need:

  • a cron script utility like wget or cron as a means of accessing the function
  • a script that runs on your local computer (we use Windows BATCH scripting language for this)
  • a scheduled task to run this script

The script on your local computer would then look somewhat like this:

Line 1 
Line 2 
Line 3 
Line 4 
Line 5 
Line 6 
Line 7 
Line 8 
Line 9 

  1. turns off the screen echoing of batch commands (the @ turns off the echoing BEFORE the "ECHO OFF" command)
  2. sets a batch variable with the path to the WGET.EXE utility on your local computer
  3. sets a batch variable with the domain of your website
  4. sets a batch variable with the path to the cron script on your webserver
  5. sets a batch variable with the name of the cron script on your webserver
  6. sets a batch variable with the username of your FTP user (in case you have secured your /administrator directory by using an .htaccess file)
  7. sets a batch variable with the password for your FTP user (in case you have secured your /administrator directory by using an .htaccess file)
  8. sets a batch variable with the URL to the "schedule_cron.php" script on your webserver
  9. executes the WGET.EXE utility with the previously set parameters

In fact, if you put all the parameters (from line 2 to line 8) directly into the executeable command (line 9), you would only need this one single line in your batch script file, although we prefer to use the "set VARIABLE=value" functionality (and add comments of course) to better document the task.

The next step would be to execute this script on a defined interval. You should set up a scheduled task on your local computer (using the Windows Server 2008 Task Scheduler in this example):

  1. on your local server navigate to Start > All Programs > Administrative Tools > Server Manager. This brings you to the Server Manager interface
  2. Drill down to Configuration > Task Scheduler
  3. On the right hand side of the interface, click Create Basic Task. Give the task a name and description. Click Next
  4. Select the radio button for how often the task should run: Daily, Weekly, Monthly, one time only, when the computer starts, when I log in, or when a specific event is logged (once the task is created, you can edit it and fine-tune the repeating cycle). Click Next
  5. Select a start time, how often to perform the task, and an end time. Click Next
  6. Configure the task to Start a Program, Send an e-mail, or display a message by selecting the appropriate radio button. Click Next
  7. Browse to the program or script and enter in any arguments or paths to start in. Click Next
  8. Select the checkbox for Open the Properties dialog for this task when I click Finish if you wish to configure the task further. Click Finish
  9. The Advanced Properties window will appear. Configure the task further to your liking by adding comments to the task, telling the task when to run (only if logged in or otherwise), deleting the task after its run if it's scheduled to not run infinitely, to reschedule the task if it is missed, and so forth. Click Finish

After doing so, the task is setup and will run if configured properly (and if not, please read on...)

Debugging the schedule

Always make sure the schedule test runs ok from the backend [Schedules] page. If it doesn't then check

  • That you have specified the schedule FROM/REPLY email address and name in the [OPTIONS] dialog
  • That you can send emails from your server with Joomla. Try a password recovery etc

If the schedule test runs ok, but you don't think the schedule runs ok when executed by cron, please check:

  • That the Next Date/Time is not in the future. The schedule will not execute until that time has passed.
  • That the max number of runs hasn't been exceeded for the schedule
  • Does the schedule involve workbooks or categories which have non-public permissions (the cron job is executed as a Public user. Running the cron job as a specific Joomla user will be possible in one of the next versions).

Try executing the schedule_cron.php script from the command line (if you do have command line access to the server). There might be error messages that can help you figure out the problem.

Edit the ./administrator/components/com_jdbexport/helpers/schedule.php script and enable DEBUG Mode (at the top of the script).

define( 'DEBUG', 0 );
define( 'DEBUG', 1 );

Now you can excute the script from command line (if you have access to command line) or run the Scheduler with cron. The debug and eventual error messages will be sent to cron and you typically get an email with the output.



go to top

Control Panel module

The ControlPanel module (available in the downloadable installation package)  provides direct links to workbooks (edit, preview, download) from the website administration control panel.



go to top

Frontend module

The Frontend module (available in the downloadable installation package)  provides direct links to workbooks (preview, download) from any position of your website template.



go to top

Content Datagrid plugin

The Datagrid plugin (available in the downloadable installation package)  allows you to embed data from any valid SQL query as a simple HTML table directly into your content elements (such as articles).

To do this, activate the plugin after installation and include the following tags into your content item

Datagrid tags 

where WBID is the ID of the desired workbook and WSID is the ID of the desired worksheet.

The layout of the table can be adapted in the components frontend stylesheet (found at [JOOMLAROOT]/media/com_jdbexport/css/site.default.css)


go to top

Content Datafield plugin

The Datafield plugin (available as independent download) allows you to embed a single data field from any valid SQL query directly into your content elements (such as articles).

To do this, activate the plugin after installation and include the following tags into your content item

Datafield tags 

where WBID is the ID of the desired workbook, WSID is the ID of the desired worksheet, ROWID is the number of the row for the desired cell, and COLID is the number of the column for the desired cell. The default value for any of these four parameters is "1". Please note that the ROW_ID does not include the heading-row.


go to top

SQL: a (very) short introduction

From Wikipedia:
SQL (Structured Query Language) is a special-purpose programming language designed for managing data in relational database management systems (RDBMS). Originally based upon relational algebra and tuple relational calculus, its scope includes data insert, query, update and delete, schema creation and modification, and data access control. SQL was one of the first commercial languages for Edgar F. Codd's relational model, as described in his influential 1970 paper, ´A Relational Model of Data for Large Shared Data Banks´. Despite not adhering to the relational model as described by Codd, it became the most widely used database language. Although SQL is often described as, and to a great extent is, a declarative language, it also includes procedural elements. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standards (ISO) in 1987. Since then, the standard has been enhanced several times with added features. However, issues of SQL code portability between major RDBMS products still exist due to lack of full compliance with, or different interpretations of, the standard. Among the reasons mentioned are the large size and incomplete specification of the standard, as well as vendor lock-in.

SQL and jDBexport

jDBexport is designed as a data reporting instrument, and as such can only export data, not import - hence the name jDBexport. In relation to SQL this means that only the SELECT statement is what you need from SQL (in fact most other SQL statements - like ALTER, CREATE, DELETE, DROP, GRANT, INSERT, KILL, LOAD, LOCK, RENAME, REPLACE, REVOKE, SET, TRUNCATE, UPDATE - are activly disallowed in jDBexport for security reasons). Such SELECT statements can become very complex, but let us start with a simple example and get more complex step by step...

Select statements

    will select all columns for all rows from the table MYTABLE
    will select the columns FIRSTNAME and LASTNAME and CITY for all rows from the table CUSTOMERS and order the resulting data by ascending CUSTOMER_ID
    (the default sort order is ASC, if you want to order descending, simply add the keyword DESC after the column name to be sorted) 
    will select the articles ID and description for those 'active' articles that are more expensive than 35.48

[to be continued]


You can always go deeper into this by searching the web, for example at Wikipedia SQL Select .


go to top

End of user manual

Copyright Schultz IT Solutions

Copyright 2014 Schultz IT Solutions, Alle Rechte vorbehalten     Kontakt Impressum AGB Refund policy


Joomla 1.7 templates by hostgator coupon