Bookmark and Share Share

Declaring Data Sources

If your mashup script invokes SQL queries or statements, you must declare a <datasource> with connection information for the database. You can have several datasources in a mashup script as well as a single, default datasource.

You must also add and configure drivers for datasources. See Configuring Datasource Drivers for instructions. You can also configure the connection pools used mashup datasources. See EMML Reference Runtime Engine Configuration for instructions.

Defining the Connection

Each <datasource> declaration must have one of these two sets of properties:

  • A URL that defines the JDBC connection and credential information to log into the database. For example: 

    <datasource url="jdbc:hsqldb:hsql://localhost:9001" 
  username="system" password="sa"/>

    The form of the url depends on the type of database. Common URL patterns include:

    • jdbc:hsqldb:driver-type://hostname:port

    • jdbc:mysql://hostname/databasename

      Important: for MySQL databases, it is recommended that you include the database name in data source URLs. If this information is omitted, you may experience access errors when running or debugging the mashup script.

    • jdbc:oracle:driver-type@hostname:port

    • jdbc:postgresql://hostname:port/database-name

    • jdbc:sqlserver://hostname:port;databaseName=database-name

    • jdbc:sybase:Tds:hostname:port

    Both the username and password attributes are also required, but password can be empty if no password is required.

    In most cases, you also must include a driver class to define the JDBC driver to use with the connection. This is optional, but if you omit the driver class, the EMML Reference Runtime Engine uses the HSQL driver for the EMML Engine. For example: 

    <datasource url="jdbc:oracledb:osql://localhost:9001" 
  driverclassname="oracle.jdbc.driver.OracleDriver" 
  username="system" password=""/>

  • A JNDI name for connections to the database. For example:

    <datasource jndiname="java:/comp/env/jdbc/myDatasource" />

    The JNDI name must be in the form java:/comp/env/jdbc/jndi-name. No other properties for <datasource> are needed with JNDI connections.

Dynamic Connections

Like most EMML attributes, connection information can be set dynamically using input input parameters or variables. For example: 

<datasource url="$system.datasourceURL" 
   driverclassname="$system.datasourceDriver" 
   username="$system.dbUserName" password="$system.dbPassword"/>

Default versus Named Datasources

Datasources can have a name that you can use in SQL statements to have those statements affect a specific datasource. Datasource names for each connection must be unique across all mashups in one EMML Engine.

If the name is omitted, the datasource is considered a default datasource. SQL commands without a datasource name are invoked against the default datasource.

Note: It is a good practice to name all datasources.

In this example, the datasource on the localhost is treated as the default datasource. Two additional datasources, named HR and Finance are declared:

<datasource url="jdbc:oracledb:osql://localhost:1521" 
  driverclassname="oracle.jdbc.driver.OracleDriver" 
  username="system" password=""/> 
<datasource url="jdbc:oracledb:osql://234.10.25.2:1521" 
  driverclassname="oracle.jdbc.driver.OracleDriver" 
  name="Finance" username="system" password=""/> 
<datasource url="jdbc:oracledb:osql://234.10.35.2:1521" 
  driverclassname="oracle.jdbc.driver.OracleDriver" 
  name="HR" username="system" password=""/>