Adding a relational database data source

  1. Click Admin > Work with InfoSets > Add .
  2. Select Relational Database.
  3. Specify this information:
    Access Type
    Select how to access the data. Select Query to create a new query. This is the default option.

    Select Stored Procedure to use an existing query. If you select this option, you must specify the name of the stored query and supply the attribute information.

    Query String
    If you selected Query, you must specify a valid SQL query this field.

    You can specify a [dynamic-where] clause to execute the query using the parameters that you choose in your notification. For example, SELECT * FROM MY_DATA_TABLE WHERE [dynamic-where]. This practice is useful to limit the amount of data the data source passes to the InfoSet.

    However, using a [dynamic-where] clause adds processing time because Smart Notification creates a new query each time it processes the notification.

    You can specify dates using [date]. If you use [date], the format is [date=<date-value>fmt=yyyy-MM-dd]. <date-value> is either a born on date, to use the date of the previous update, or a relative date.

    For example, to retrieve all data where the DATE_FIELD value is since the beginning of the year, use SELECT * FROM MY_DATA_TABLE WHERE DATE_FIELD > {d '[date=-YS0fmt+yyyy-MM-dd]'}.

    See Date Formats.

    Stored Procedure Name
    Specify the name of the stored procedure to be used. This name must match the name of the stored procedure in the relational database you are using as your data source.
    Attribute(s)

    If you add an attribute, these columns are activated:

    • Value
    • Type

    You can name a specific value, select a type, or remove the attribute.

    Use Predefined Data Source
    Select this check box to use a predefined data source. Selecting this check box must coincide with a data source name.

    You can build reusable relational data base (JDBC, JNDI) data sources with the Work With Data Source tool.

    Data Source Name
    Select the name of the predefined data source.
    JDBC Driver
    Specify the path and class name of the JDBC driver to connect to the database. The driver's class must be accessible from the server and reside in the server's classpath. The information provided for the remaining fields on this screen depends upon the path entered for this field.
    User Name
    Specify a user ID. The user you specify must have permission to connect to the database and execute the query or stored procedure. Also, the user ID must be configured on RDBMS before you can specify it in this field.
    Password
    Specify the password for the user ID.
    Database
    Optionally, specify the name of the database to extract data from. The database name is not necessarily the name of the application database (for example, Oracle). Also, this field is not required by all JDBC drivers.
    Database Location/URL
    Optionally, specify the location or URL of the database to connect to. The location format differs between driver vendors. For example, the IBM's DB2 driver format is jdbc:db2:sample.

    Use the JDBC driver documentation to help you determine the correct location format.

    Server
    Optionally, specify the name of the database's server or service name. The server name is often part of the database location or URL.
    Port
    Optionallly, specify the port ID. The port ID is often part of the database location or URL.
    Preview

    Click this link after you have completed the fields on the form. Smart Notification returns a data sample based on the query you provided.

    If you have specified any parameters for the data source, a form listing the parameters along with text boxes appears. Use this form to specify a value for each parameter.

    If Smart Notification does not return data, check your query statement for errors.

  4. Click Preview after you have completed the fields on the form.Smart Notification returns a data sample based on the query you provided.
    • If you have specified any parameters for the data source, a form listing the parameters along with text boxes appears. Use this form to specify a value for each parameter.
    • If Smart Notification does not return data, check your query statement for errors.