BellaDati can be connected to numerous Databases.

    Icon

    Please consult with support particular solutions.

    Connecting to Database

    From Data Source Connectors page select SQL Database Connector.

    Supported Databases

    Depending on application usage (cloud or On-Premise) BellaDati is able to connect to these SQL databases.

    Icon

    Support for database not listed below can be added on-demand.

    Name

    Supported versions

    Driver versions

    Note

    Oracle

    10.1.0.5, 10.2.0.1-10.2.0.5, 11.1.0.7, 11.2.0.1 -11.2.0.4, 12.1.0.1, 12.1.0.2, 12.2.0.1

    v6-11.1.0.7, v6-11.2.0.1, v6-11.2.0.2, v14-10.1.0.5, v14-10.2.0.1, v14-10.2.0.2, v14-10.2.0.3 ,v14-10.2.0.4, v14-10.2.0.5

     

    MySQL

    3.1.3 and higher

    5.1.13

     

    PostgreSQL

    8 and higher

    9.0-801-jdbc4

     

    Microsoft SQL Server

    MSSQL 2008, MSSQL 2008 R2, MSSQL 2008 Express, MS SQL Server 2012, MS SQL Server 2014 and higher

    sqljdbc4.1

     

    SAP Max DB

    7.3 and higher

    7.4.4 Build 003-000-002-502

     
    SAP HANA
    all

    SAP In-Memory Database JDBC Driver, 1.00.48 Build 0372847-1510

     
    Hadoop Hive
    Hadoop 2 and higher2.3.3 
    Cloudera Impala
    5 and higher2.5.31.1051 
    Microsoft Access
    allJava SE JDBC/ODBCScheduled import is not supported. Before each import, the file has to be selected again.
    Sybase
    alljconn4 
    Teradata
    11 and higherterajdbc4 
    Firebird Jaybird 2.2.7 
    Custom 

     

    Supported in BellaDati 2.9.6.2 and newer.

    Usable when a custom connection string is needed or user provides own JDBC driver to the application server.

     two mandatory fields:

    • driverClassName
    • connectionString

     Example:

    • driverClassName=org.postgresql.Driver
    • connectionString=jdbc:postgresql://localhost:5432/belladati
    Icon

    Database support varies by BellaDati Cloud or On-Premise integration environment.

    Connection Parameters

    Connection parameters may vary depending on the selected database vendor. Most common parameters are the following

    • host: IP address or domain name
    • database: database name
    • password
    • user

    Additional parameters can be specified by clicking on Add link in bottom left part of Connection parameters window. They can include:

    • port
    • driver: If different drivers are required for various database versions, you can select the right version here (eg. Oracle).

    For connection via SSH Tunnel, following parameters have to be added:

    • port: this value must be specified
    • sshHost: overrides the "host" parameter (optional)
    • sshPort: specifies the SSH port, default value is 22 (optional)
    • sshUser
    • sshPassword



    Icon

    Connection parameters above may vary according database vendor. Please, refer to your database vendor`s documentation for details or see the Connection parameters examples for examples.

    Icon

    Connection to the database will be checked immediately - if a problem arises, you will be informed via error message. Please also check your firewall settings - if BellaDati can connect to the data source.*

    Icon

    Direct connection using localhost keyword or localhost IP address is disabled due to security reasons. Please define an alias in hosts file (eg. C:\WINDOWS\system32\drivers\etc in Windows). Than use this alias in BellaDati.

    Troubleshooting

    If you cannot connect to your database, please verify:

    1. Host, port, driver and database name (where applicable) are correct. Host should be an IP address or a domain name.
    2. The database server is reachable from the server running BellaDati. For BellaDati cloud, this means your database must be reachable from the internet.
    3. The database server's firewall allows incoming requests from the BellaDati server on the database port. 
    4. Database username and password are correct.


    Icon

    Still having problems with connection to your SQL database? Please navigate to separate page focused on troubleshooting of this issue.


    Querying Database

    There are two options how to query database:

    • Database Discovery
    • SQL Query Window

    Database Discovery

    Database Discovery is a visual editor for specifying database queries. To obtain data from the your database:

    1. Click table you want to query. BellaDati includes all columns by default.
    2. All available columns are selected to be imported. Click on values that you don't want to import. Click on button Update SQL to update sql query according to changes. Use Select all/Unselect all buttons to quickly manipulate with columns.

     

    Icon

    BellaDati will construct corresponding SQL query in the right SQL window

    SQL Query Window

    For advanced users or queries, BellaDati offers SQL Query Window. Use Query Window to construct desired SQL commands (SQL query with join, stored procedures, etc.).

    Icon

    Click validate button to make sure that your command is correct before proceeding with data mapping.

     

    Using Stored Procedures

    You can also use stored procedures for data import. Stored procedures should have syntax like following:

    execute [db].[sp] 
    @BegDate = '2015-07-01' 
    ,@EndDate = '2015-07-31' 

    Where [db] is the name of the database, [sp] is the name of the stored procedure, @BegDate and @EndDate are parameters used by stored procedure. 

    Connection Modifications

    Following data source parameters can be modified within the existing data source in Data Set page:

    • Connection parameters
    • SQL statement
    Icon

    When changing SQL query to extract more columns from the database, use Get new columns function - this function adds new columns and preserves previous settings. Otherwise the additional columns will not be imported.

     Second option is use of Reset function. This function resets previous settings and adds new columns. You need to setup new import settings in this case. Otherwise the additional columns will not be imported.

    Tutorial video

    Next Steps