Using the Built-In CTI32 Database Access Class

Author’s note:  This article supports our legacy products.  At Inventive Labs, after two decades of providing telephony tools, we never stop supporting those who rely on our products to run their businesses.  We no longer recommend Dialogic. This article is for our customers who are using legacy equipment. For more information about these legacy products and where we are today, read  Ditch Dialogic & Convert to Voice Elements.

Accessing a SQL Database is simple using CTI32.

You have the option of connecting to:

• SQL Server Database
• ODBC database

CTI32 will make a single connection to your database. All channels will share this single connection. CTI32 handles most of the details on coordinating access.

Here are the steps to setting it up:

• In the CTI32Config application set the following properties under the “Default” tree
  • DatabaseConnectString
    • Example for SQL server: Data Source=localhost;initial catalog=Cti32;packet size=4096;integrated security=SSPI;persist security info=True
    • Alternate Example for SQL Server: server=64.1.3.205;database=yourdb;uid=sa;pwd=password
    • Example for ODBC: DSN=Excal;UID=xxx;pwd=yyy (You would have set up a dataset on your machine)
  • UseSqlServer
    • true if using SQL Server
    • false if using ODBC

In your code you would create a Db class as follows:

• Db db = new Db(cfg.log, cfg.dbConnection);

Here is an example of implementing a DnisMethod using a database to read the module that you wish to load:

        
public int LookupDNIS(ConfigData configData, ChannelData channelData, String did, StringBuilder returnModule, StringBuilder returnMethod, StringBuilder returnType)
{

    //Set the log class globally for easy access. (passed in from the engine)
    log = channelData.log;

    log.Write("In LookupDNIS...");

    string sLookup = did;

    if (channelData.globalCallProtocol == "SIP")
    {
        // call is SIP, split the DID out 
        // the request should be in the format 3035551212@thisipaddress.com

        string[] sParse = did.Split('@');

        if (sParse.Length > 1)
        {
            sLookup = sParse[0];
        }
    }

    DbConnection dbc = configData.dbConnection;
    Db db = new Db(log, dbc);

    string sqlStmt = String.Format("SELECT * from Dnis Where Dnis = '{0}'", sLookup);

    DataRow dr = db.GetFirst(sqlStmt);

    if (dr != null)
    {
        returnModule.Append(dr["Dll"]);
        returnMethod.Append(dr["Method"]);
        returnType.Append(dr["Type"]);

        return 0;
    }
       
    // not found - use default
    returnModule.Append("Rejected");
    returnMethod.Append("Rejected");
    returnType.Append("Rejected");
            
    // not found, reject call!
    return 1;
    }
}

If you want to read multiple rows out of a selection, you could do something like this:

                
Db db=new Db(log,dbc);
bool firstTime=true;
sqlStmt=String.Format("SELECT * FROM AATree WHERE AAId = '{0}' ORDER BY PromptNumber",aaId);
log.WritePort(lineData.port,"Loading Tree for {0}", dnis);
dr=db.GetFirst(sqlStmt);
int cnt = 0;
for(;;)
{
    if(firstTime) firstTime=false;
    else dr=db.GetNext();
    if(dr == null) break;
    int prompt = Convert.ToInt32(dr["PromptNumber"]);
    cnt++;
}
log.WritePort(lineData.port,"Loaded {0} steps for {1}", cnt, dnis);

If you want to Insert, Update, or Delete you would do it like this:

            
sqlStmt = String.Format("INSERT INTO email_queue ( email_to, email_sub, email_msg, email_attachment, submitted, send_in_test ) VALUES ( '{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", email, subject, msg, att, DateTime.Now.ToString(), sendInTest);
db.Update(sqlStmt);