Programmable Voice

  1. Home
  2. Docs
  3. Programmable Voice
  4. HMP Elements
  5. Using the build-in CTI32 Database Access Class

Using the build-in CTI32 Database Access Class

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);
Was this article helpful to you? Yes 7 No

How can we help?