Generic Database

The generic database plugin allows Noojee Answer to use an external database to map caller details into a url or fragment of javascript, paste data, caller name and display text.

JDBC

The generic database plugin uses JDBC (java's equivalent of ODBC) to access virtually any database.

The plugin needs the following configuration information:

• Database connection URL
• Database Class
• Query to return the data that takes 8 parameters and returns 4 fields.

The stored proc will be passed the following information.

Two version of the stored proc are supported. One that returns a set of URL that are to be poped and an alternate version that returns a fragment of javascript which will be executed within the browser.

The URL method is suitable for older page based web applications where each page is accessed by a unique URL. For new Web 2.0 web applications that operate multiple pages from a single URL the javascript method must be used.

To use the URL method use the following stored procedure name:

getData

<crm-plugin>
        <class>au.com.noojee.answerbar.server.plugin.JdbcPlugin</class>
        <params>
        <jdbcUrl>jdbc:sqlserver://192.168.0.3;DatabaseName...password=YYYYY</jdbcUrl>
            <driverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClass>
            <query>exec getData ?,?,?,?,?,?,?,?</query>
        </params>
    </crm-plugin>

The getData method can return one or more URL's each of which will be opened in a browser window or tab.

getJAVA

The java fragment will be executed within the page which hosts Noojee Answer. The fragment will normally call some existing javascript API within your CRM which will display the appropriate data (account, contact) with in your CRM.

To use the javascript method use the following stored procedure name:

For example:

 <crm-plugin>
        <class>au.com.noojee.answerbar.server.plugin.JdbcPlugin</class>
        <params>
        <jdbcUrl>jdbc:sqlserver://192.168.0.3;DatabaseName...password=YYYYY</jdbcUrl>
            <driverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClass>
            <query>exec getJAVA ?,?,?,?,?,?,?,?</query>
        </params>
    </crm-plugin>

Note: the parameter names used in the stored procedure are not actually important. It is the ORDER of the parameters that is CRITICAL!

The following table details each parameter and the order they must be defined in the stored procedures signature.

The query must return one or more rows containing the following fields, in this order

The data from the first row will be used by most tools. The url from each row will be used for screen pops, each url will be popped in it's own tab.

Sample Configuration

Note: Escaping special characters

You might have to escape certain parts of the jdbcURL value because of the inclusion of special characters such as spaces, semicolons, and quotation marks. The JDBC driver supports escaping these characters if they are enclosed in braces. For example, {;} escapes a semicolon.

Escaped values can contain special characters (especially '=', ';', '[]', and space) but cannot contain braces. Values that must be escaped and contain braces should be added to a properties collection.

Noojee recommend that your database name only contain alphnumeric characters.

<crm-plugin>
    <class>au.com.noojee.answerbar.server.plugin.JdbcPlugin</class>
        <params>
	    <jdbcUrl>jdbc:mysql://127.0.0.1/asterisk?user=asterisk&password=fred
	    </jdbcUrl>
	    <driverClass>com.mysql.jdbc.Driver</driverClass>
	    <query>call getData(?,?,?,?,?,?,?,?)</query>
	    <channelVariable>account</channelVariable>
	    <channelVariable>country</channelVariable>
	</params>
	<filter>
	    <didPattern>410</didPattern>
	    <queue>test</queue>
	</filter>
</crm-plugin>