[Help] Realtime Queues Partially Working using unixODBC

Hello,

I have a partially working Realtime Queue using unixODBC and Firebird. I followed the steps on asterisk-guru and the voip-info asterisk message boards but have been having no success.

I say that my queue is partially working because callers can join the queue. However the calls do not go to the agents even if the agent is not tied up and is available for calls. So the caller is stuck in the queue forever.

Here is some information about my setup:
Computer - Dell Poweredge 1400 (2 X P3 @ 866MHz with 1GB RAM)
Linux Distro - CentOS 4.2
Kernel - 2.6.9-22.0.1.ELsmp
Asterisk Version - 1.2.4
unixODBC version - 2.2.9-1
Firebird Super Server version - 1.5.3
Firebird ODBC driver version - 1.2.0.69

Here is my CLI output during the call:
– Executing Answer(“IAX2/117-14”, “”) in new stack
– Executing Queue(“IAX2/117-14”, “tsupport|hH”) in new stack
– Started music on hold, class ‘default’, on channel 'IAX2/117-14’
Feb 9 18:15:44 WARNING[32567]: channel.c:2535 ast_request: No channel type registered for ‘’
– Stopped music on hold on IAX2/117-14
– Playing ‘queue-youarenext’ (language ‘en’)
– Registered IAX2 ‘123’ (AUTHENTICATED) at 192.168.0.111:4569
– Told IAX2/117-14 in tsupport their queue position (which was 1)
– Playing ‘queue-thankyou’ (language ‘en’)
– Started music on hold, class ‘default’, on channel ‘IAX2/117-14’

-Nothing wrong here aside from getting a warning (which should not be there in the first place).

I check if realtime is loading properly so I do a realtime load:

realtime load queues name tsupport
Column Name Column Value
-------------------- --------------------
NAME tsupport
MUSICONHOLD default
ANNOUNCE technical-support
TIMEOUT 5
MONITOR_JOIN 1
MONITOR_FORMAT gsm
ANNOUNCE_FREQUENCY 60
ANNOUNCE_ROUND_SECONDS 0
ANNOUNCE_HOLDTIME once
RETRY 2
WRAPUPTIME 0
SERVICELEVEL 30
STRATEGY random
JOINEMPTY strict
LEAVEWHENEMPTY no
EVENTMEMBERSTATUS 1
EVENTWHENCALLED 1
REPORTHOLDTIME 1
MEMBERDELAY 2

I check my members:

realtime load queue_members queue_name tsupport
Column Name Column Value
-------------------- --------------------
QUEUE_NAME tsupport
INTERFACE Agent/1000

I check the queues:

show queues
tsupport has 0 calls (max unlimited) in ‘random’ strategy (0s holdtime), W:0, C:0, A:4, SL:0.0% within 30s
Members:
(Invalid) has taken no calls yet
No Callers

-this shows that I have invalid members despite having agent 1000 logged on (using AgentLogin app).

I think this has something to do with how I setup the datatypes for my queus and queue member tables as I have no problems having asterisk save my CDR through unixODBC with firebird nor does my iax peers/users table have any problems either.

Below is how I set up my queues_table and queue_member_table:
CREATE TABLE queue_table (
name VARCHAR(128) NOT NULL PRIMARY KEY,
musiconhold VARCHAR(128),
announce VARCHAR(128),
context VARCHAR(128),
timeout NUMERIC(11,0),
monitor_join SMALLINT,
monitor_format VARCHAR(128),
queue_youarenext VARCHAR(128),
queue_thereare VARCHAR(128),
queue_callswaiting VARCHAR(128),
queue_holdtime VARCHAR(128),
queue_minutes VARCHAR(128),
queue_seconds VARCHAR(128),
queue_lessthan VARCHAR(128),
queue_thankyou VARCHAR(128),
queue_reporthold VARCHAR(128),
announce_frequency NUMERIC(11,0),
announce_round_seconds NUMERIC(11,0),
announce_holdtime VARCHAR(128),
retry NUMERIC(11,0),
wrapuptime NUMERIC(11,0),
maxlen NUMERIC(11,0),
servicelevel NUMERIC(11,0),
strategy VARCHAR(128),
joinempty VARCHAR(128),
leavewhenempty VARCHAR(128),
eventmemberstatus SMALLINT,
eventwhencalled SMALLINT,
reportholdtime SMALLINT,
memberdelay NUMERIC(11,0),
weight NUMERIC(11,0),
timeoutrestart SMALLINT
);

CREATE TABLE queue_member_table (
queue_name varchar(100) NOT NULL,
interface varchar(100) NOT NULL,
penalty NUMERIC(11,0),
PRIMARY KEY (queue_name, interface)
);

I am having problems with creating the queue_member_table. Using varchar(128) as listed on both the asteriskguru and voip-info websites results with firebird telling me a ‘key size too big for index’ error. setting the size to 100 eliminates this problem and creates the table properly.

Am I missing a step here?

ok a little update on this problem.

I noticed that no members were being placed inside the queue even if I have specified them in my queue_member_table table.

Doing a:
CLI> realtime load queue_members queue_name tsuport yeilds the proper values:

      Column Name  Column Value
      --------------------  --------------------
                QUEUE_NAME  tsupport
                 INTERFACE  Agent/1000
                   PENALTY  0

So I know that data is being taken from my database.

I do a show queue tsupport and I get the results listed above:

tsupport has 0 calls (max 90) in ‘random’ strategy (0s holdtime), W:0, C:0, A:0, SL:0.0% within 60s
Members: nit02CLI>
(Invalid) has taken no calls yet
No Callersit02
CLI>
zerix-demo-unit02*CLI>
Mar 8 16:46:41 WARNING[4118]: app_queue.c:540 create_queue_member: No location at interface ‘’

so I get a create_queue_member error. It seems that blank data is being sent to * instead of the proper values (as show by my realtime load cli command).

I turn on logging for my odbc connection and here are the results:
[ODBC][4819][SQLAllocHandle.c][872]
Exit:[SQL_SUCCESS]
Output Handle = 0x91d5ae8
[ODBC][4819][SQLPrepare.c][189]
Entry:
Statement = 0x91d5ae8
SQL = [SELECT * FROM queue_member_table WHERE interface LIKE ? AND queue_name = ? ORDER BY interface][length = 93 (SQL_NTS)]
[ODBC][4819][SQLPrepare.c][364]
Exit:[SQL_SUCCESS]
[ODBC][4819][SQLBindParameter.c][193]
Entry:
Statement = 0x91d5ae8
Param Number = 1
Param Type = 1
C Type = 1 SQL_C_CHAR
SQL Type = 1 SQL_CHAR
Col Def = 1
Scale = 0
Rgb Value = 0xde43c5
Value Max = 0
StrLen Or Ind = (nil)
[ODBC][4819][SQLBindParameter.c][339]
Exit:[SQL_SUCCESS]
[ODBC][4819][SQLBindParameter.c][193]

[ODBC][4819][SQLBindParameter.c][339]
Exit:[SQL_SUCCESS]
[ODBC][4819][SQLExecute.c][183]
Entry:
Statement = 0x91d5ae8
[ODBC][4819][SQLExecute.c][344]
Exit:[SQL_SUCCESS]
[ODBC][4819][SQLNumResultCols.c][149]
Entry:
Statement = 0x91d5ae8
Column Count = 0x3095f3c
[ODBC][4819][SQLNumResultCols.c][234]
Exit:[SQL_SUCCESS]
Count = 0x3095f3c -> 3
[ODBC][4819][SQLFetch.c][158]
Entry:
Statement = 0x91d5ae8
[ODBC][4819][SQLFetch.c][340]
Exit:[SQL_SUCCESS]
[ODBC][4819][SQLDescribeCol.c][231]
Entry:
Statement = 0x91d5ae8
Column Number = 1
Column Name = 0x3096740
Buffer Length = 256
Name Length = 0x3095f3e
Data Type = 0x3095f3a
Column Size = 0x3095f20
Decimal Digits = 0x3095f38
Nullable = 0x3095f36
[ODBC][4819][SQLDescribeCol.c][474]
Exit:[SQL_SUCCESS]
Column Name = [QUEUE_NAME]
Data Type = 0x3095f3a -> 12
Column Size = 0x3095f20 -> 128
Decimal Digits = 0x3095f38 -> 0
Nullable = 0x3095f36 -> 1
[ODBC][4819][SQLGetData.c][224]
Entry:
Statement = 0x91d5ae8
Column Number = 1
Target Type = 1 SQL_CHAR
Buffer Length = 2048
Target Value = 0x3095f40
StrLen Or Ind = 0x3095f1c
[ODBC][4819][SQLGetData.c][470]
Exit:[SQL_SUCCESS]
Buffer = [tsupport]
Strlen Or Ind = 0x3095f1c -> 8
[ODBC][4819][SQLDescribeCol.c][231]
Entry:
Statement = 0x91d5ae8
Column Number = 2
Column Name = 0x3096740
Buffer Length = 256
Name Length = 0x3095f3e
Data Type = 0x3095f3a
Column Size = 0x3095f20
Decimal Digits = 0x3095f38
Nullable = 0x3095f36

[ODBC][4819][SQLDescribeCol.c][474]
Exit:[SQL_SUCCESS]
Column Name = [INTERFACE]
Data Type = 0x3095f3a -> 12
Column Size = 0x3095f20 -> 128
Decimal Digits = 0x3095f38 -> 0
Nullable = 0x3095f36 -> 1
[ODBC][4819][SQLGetData.c][224]
Entry:
Statement = 0x91d5ae8
Column Number = 2
Target Type = 1 SQL_CHAR
Buffer Length = 2048
Target Value = 0x3095f40
StrLen Or Ind = 0x3095f1c
[ODBC][4819][SQLGetData.c][470]
Exit:[SQL_SUCCESS]
Buffer = [Agent/1000]
Strlen Or Ind = 0x3095f1c -> 10
[ODBC][4819][SQLDescribeCol.c][231]
Entry:
Statement = 0x91d5ae8
Column Number = 3
Column Name = 0x3096740
Buffer Length = 256
Name Length = 0x3095f3e
Data Type = 0x3095f3a
Column Size = 0x3095f20
Decimal Digits = 0x3095f38
Nullable = 0x3095f36
[ODBC][4819][SQLDescribeCol.c][474]
Exit:[SQL_SUCCESS]
Column Name = [PENALTY]
Data Type = 0x3095f3a -> 4
Column Size = 0x3095f20 -> 10
Decimal Digits = 0x3095f38 -> 0
Nullable = 0x3095f36 -> 1
[ODBC][4819][SQLGetData.c][224]
Entry:
Statement = 0x91d5ae8
Column Number = 3
Target Type = 1 SQL_CHAR
Buffer Length = 2048
Target Value = 0x3095f40
StrLen Or Ind = 0x3095f1c
[ODBC][4819][SQLGetData.c][470]
Exit:[SQL_SUCCESS]
Buffer = [0]
Strlen Or Ind = 0x3095f1c -> 1
[ODBC][4819][SQLFetch.c][158]
Entry:
Statement = 0x91d5ae8
[ODBC][4819][SQLFetch.c][340]
Exit:[SQL_NO_DATA]
[ODBC][4819][SQLFreeHandle.c][365]
Entry:
Handle Type = 3
Input Handle = 0x91d5ae8
[ODBC][4819][SQLFreeHandle.c][462]
Exit:[SQL_SUCCESS]

So things are being taken from my database however the end result is SQL_NO_DATA.

Anybody know how to go about solving this?

Are you using the Firebird ODBC driver from www.easysoft.com? For me, I am using that driver and am having the same problem. It could be a driver related issue. My voicemail and sip works as expected though.

I tried a MySQL database with the MySQL odbc drivers and it works correctly. I am not sure but I did notice that the column names are all lowercase for MySQL and all uppercase for Firebird.

hello strider,

I am using the drivers from firebirdsql.org firebirdsql.org/index.php?op=files&id=odbc

I haven’t tried their newer drivers to see if it behaves correctly now. Likewise I have no problem with MySQL odbc drivers as well.

It really is the case-sensitive issue. All of Firebird columns are returned in all caps while MySQL is in all lowercase. To make it work, I wrote a small function to ignore case. Please note that I am very rusty in C and this is an inelegant solution. Anyway, the edit is to /asterisk_source/res/res_config_odbc.c:

/**
 * returns 1 if equal regardless of case, 0 otherwise
 */

int strcmp2(char *a, char *b)
{
  if ( strlen(a) != strlen(b) )
    return 0;

  while ( *a && *b ) {
    if ( tolower(*a) != tolower(*b) )
      return 0;
    a++;
    b++;
  }

  return 1;
}

Then, go to the function “realtime_multi_odbc” and change this one line:

if (initfield && !strcmp(initfield, coltitle))

to

if (initfield && strcmp2(initfield, coltitle)==1 )