Connecting to an SQL database

Last modified by Aurelie Bertrand on 2026/07/02 11:51

DigDash Enterprise allows you to retrieve information from databases (MySQL, SQL Server, PostgreSQL, Oracle, DB2, DB2(AS400), Sybase, H2...).

💡 In the case of a simple SQL query, you can use the database wizard, which does not require any particular knowledge of writing SQL queries. See the paragraph Connecting to an SQL database using the wizard.

Connecting to an SQL database

To connect to an SQL database :

  1. In the Create a new data model box, click on the DB (SQL) button in the Database section.
    DB SQL
    ➡ The Database dialog box appears.
    Database window
  2. First select the desired connection mode:

  • Manual connection mode
    The connection will be visible in the data source only

    1. In the Database URL field :
      • Select the JDBC driver from the drop-down list: Oracle, SQL Server, MySQL,
      • Enter the address of your database using the syntax required by the driver.
        Example of the syntax used for mysql :
        jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

        (the default port number is 3306).
    2. In the User field, enter the name of the user to be used when connecting to the database.
    3. In the Password field, enter the password to be used when connecting to the database.
    4. Click Connect to obtain the status of the connection (successful connection or failed connection). If the connection fails, change the connection parameters.
       
  • Named connection mode
    The connection will be visible in the Database Connection Manager and can be used by all data sources.
    1. Click Select: the Database Connection Manager is displayed.
    2. You can :
      • Select an existing connection from the list. Click OK.
      • Create a new connection as described on the Add a connection named.
         
  • Parameterized connection mode
    This mode allows you to use a database connection assigned to the user via a user parameter.
    See the page Parameterized connection to a database page for more details.
  1. In the SQL query field, enter the SQL query manually or using the SQL wizard. See the Writing the SQL query page for more details.
  2. Click Options if you wish to modify the advanced properties of the database. See the Options section below.
  3. Click Preview to display a preview of the result.
    See the page Preview page page for more details on column properties.
  4. You can apply data transformations.
    See the page Transform data page for more details.
  5. Once you are happy with the result, click Next to configure the data model.

You can save the query you have entered for future use. To do this

  1. Click the Favorites button below your SQL query.
  2. Select Add to favorites then enter the name you want to give to this query.

Options

Query timeout

Maximum query duration in seconds (default is 60 seconds).
If set to 0, there is no maximum limit.

Row fetch sizeNumber of rows to be fetched from the database.
If set to 0, the number takes the JDBC driver "fetchsize" value.
Number of retries on error

Number of times the query is repeated in the event of an error.
If other than 0, the time between retries must be specified, in seconds.

Persist error on schedule

Used to generate a cube containing the error. The whole process will fail later, but this avoids having to redo the job (and being in error again) for all the tasks.

Incremental data mode

Incremental data mode is used to feed the cube incrementally: each time the cube is refreshed, only the new data is brought back compared with the previous refresh.
It is based on a reference column and the use of a keyword in the query: ${LASTVAL}.
For example: select ... where ref_col> '${LASTVAL}

The reference column is entered in the Reference column field below.

Connecting to a SQL database using the wizard

To connect to an SQL database using the wizard :

  1. In the Create a new data model box, click on the DB (Wizard) button in the Database section.
    1769766976683-158.png
    ➡ The Database dialog box appears.
  2. Select a database from the Connection drop-down list or click the Select button to create a new one in the Database Connection Manager. See the Add a named connection page for more details.
  3. In the Query tab, create a query using the Wizard :
    • Select the table you want to use in the left-hand panel.
      ➡ The corresponding columns are displayed in the right-hand panel.
    • You can remove columns by unchecking the corresponding boxes.
    • You can define rules on columns: select the column then click on the 1740751369754-364.png button at the top right of the columns and define the rule. The rule is added to the table below the columns.
      Query
  4. Click on Options if you wish to modify the advanced properties of the database. See the options paragraph below.
  5. In the Results tab , preview the results of the defined query. See the Preview page page for more details on column properties.
  6. You can also add data transformations. See the Transform data page for more details.
  7. Once you are happy with the result, click Next to configure the data model.

Editing the query

You can edit the query manually by selecting Advanced.... 
The query generated by the wizard is displayed.

  1. Click Edit.
    1769767788332-438.png
  2. Modify the query as required.
  3. Click Apply.

⚠ If you switch back to the wizard, the query will be overwritten.

You can save the query you have entered for later use. To do this:

  1. Click the Favorites button .
  2. Select Add to favorites then enter the name you want to give to this query. 
    ➡ The query will be available from the same Favorites menu.​
    ​​​​​​Favorites

It is also possible to access the modification history via the History button.
History

Options

The options available are the same as for connecting to a SQL database without the wizard, but with an additional option: SQL Live Query. When this option is selected, the cube is generated when necessary, if it is no longer in memory. See the Live Data page for more details.