Data sources and queries

1. Presentation
2. Data sources" tool
   1. Connection LDAP
   2. Connection SQL
   3. Request LDAP
   4. Request SQL

Presentation

The Data Sources tool enables users with appropriate rights (usually a webmaster or administrator) to manage connections to SQL databases or LDAP directories. These users can then define queries associated with one of these sources to obtain specific information that contributors can insert into the rich text fields of content.

Warning
Queries must be written using the syntax SQL or LDAP syntax (depending on the associated data source); knowledge of these areas is required to be able to create queries.

Examples of feasible queries:

• Get a list of teachers and their contacts
• Obtain a list of personnel in service
• Get the secretariat's telephone number

This page describes how to create and set up data sources. To find out how to use these queries in rich text content fields, go to the dedicated page

Data sources" tool

The plugin provides a Data Sources back-office tool to allow you to define connections and queries to LDAP or SQL data sources.

Click on the Data sources button in the Home tab tab to open the tool.

The tool takes the form of a tree with existing connections and queries.

From this tool, you can create, modify or delete connections (LDAP or SQL) and queries.

Connection LDAP

To add a data source LDAP, please enter the following parameters:

• Data source name
• An optional description
• The server address LDAP (which may contain the DN of the search base branch)
• Global search filter (e.g. objectClass=person)
• Search type (object, level or sublevel)
• The administrator's DN
• Administrator password
• Authentication method (Simple or Anonymous)
• Use of secure connections (SSL protocol)

Connection SQL

To add a data source SQL, please enter the following parameters:

• Data source name
• An optional description
• The JDBC driver used for the connection (e.g. com.mysql.jdbc.Driver for MySQL, org.apache.derby.jbc.EmbeddedDriver)
• Server address SQL
• User name to connect to the database SQL
• The password to connect to the database SQL

Request LDAP

To add a query LDAP, select a data source LDAP then click on the'New query' button.

In the dialog box, select :

• Query name (unique)
• A description detailed enough for contributors to use it easily.
• Visit type of result :
  
  • simple, if the query only returns a simple value to be inserted into a text (a person's name, for example)
  • multiple, if the query returns a list of results, which can be displayed in tabular form.
• Returned attributes: The list of returned LDAP attributes and their labels, separated by commas (for example: sn[lastname], givenName[firstname], mail)
• Visit constraintssearch criteria (which can be parameterized). Parameters are entered when the query is inserted into the content.
  
  • Non-configurable constraint: (mail=*.com)
  • Contrainte configurable, les paramètres commencent par le signe '$' suivi d'un jeu d'accolades : (uid = ${uid})

Request SQL

To add a query SQL, select a data source SQL then click on the'New query' button.

In the dialog box, select :

• Query name (unique)
• A description detailed enough for contributors to use it easily.
• Visit type of result :
  
  • simple, if the query only returns a simple value to be inserted into a text (a person's name, for example)
  • multiple, if the query returns a list of results, which can be displayed in tabular form.
• Visit request SQL (can be parameterized). Parameters are entered when the query is inserted into the content.
  
  • Non-configurable constraint: SELECT firstname AS 'Firstname', lastname AS 'LastName', mail AS 'E-Mail address', service FROM Staff
  • Contrainte configurable, les paramètres commencent par le signe '$' suivi d'un jeu d'accolades : SELECT firstname AS 'Firstname', lastname AS 'LastName', mail AS 'E-Mail address' FROM Staff WHERE service=${service}