02. Microsoft SQL Server example

The required plugin configuration is as follows:

You need to replace the args:

dbname - the name of your database

someusername - an SQL Auth username with permission to exec a stored proc on the db.

somepassword - the password for the above username.

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

Stored Proc example

The following provides an example of a suitable stored proc for using with the Noojee Answer Generic Database plugin.

USE [YourDatabaseName]
GO
 
/****** Object:  StoredProcedure [dbo].[getData]  ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[getData]
    @callerIdNum varchar(50),
    @exten varchar(50),
    @didNumber varchar(50),
    @queueName varchar(50),
    @v1 varchar(50),
    @v2 varchar(50),
    @v3 varchar(50),
    @v4 varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from 
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
    SELECT   (account.name + ' outstanding balance ' + account.balance) as [DisplayData]
    , account.accountId [PasteData]
    , 'http://www.yourcrm.com.au/searchAccountID=' + account.accountId as[URL]
    , contact.firstName + ' ' + contact.surname as [CallerName]
    from [YourDatabaseName].[dbo].[tblContact] as contact
    join [YourDatabaseName].[dbo].[tblAccount] as account
        on contact.accountId = account.accountId
    where contact.phoneNumber = @callerIdNum
END

Sample Query

You can test your query by running the following commands from the mysql cli

The following example assumes that you have a contact in your database with an phone number of '5551234'.

Note for this to work the phone number in your database must contain no spaces or other characters.

If you phone numbers do then you will need to have the above select statement strip them out.

use YourDatabaseName;
exec getData '5551234','100','61383208100','support','','','',''