[asterisk-users] Help Please - Asterisk MYSQL interface seems to be eating data

[Steve Totaro]

On Mon, May 5, 2008 at 4:21 AM, Al Baker <bwentdg at pipeline.com> wrote:
>
>  I would appreciate any and all advice on what appears to be a BUG (or a
> brainfart on my part) with the MySQL add-on for Asterisk this is of FEDORA 8
> fully patched with Asterisk Addons 1-4-6 with the Asterisk 1.4.18.1
>
>  It appears that the interface "eats" the first field requested from a
> table. If only One Field is Requested from the Table , that field is eaten
> ENTIRELY by Asterisk. If several fields are requested, the First Field Is
> Eaten and the remaining filed are returned, but place in the WRONG Variable
> since the 1tst fileld data was eaten. In the DIALPLAN below I have tried 3
> Different ways to approach this.
>
>  Extension 9999 – Get only ONE (1) field from Table
>
>  Extension 8888 – Get THREE(3) fields from the Table and Quote Them.
>
>  Extension 7777 - Get THREE(3) fields from the Table
>
>  I have show the Output from the Asterisk CL for each, which clearly show
> that SOMETHING is not
>  right. Maybe the Software, maybe the person using the software :)
>
>  Here is the Table in the Database.
>
>  mysql> select * from agent;
>
>  +----------+---------+--------+------------+---------------------+
>
>  | id          | cust_id  | status |    phone     |    tlce |
>
>  +----------+---------+--------+------------+---------------------+
>  | 00000001 | NAMB | free | 1234567890 | 2008-04-17 02:32:02 |
>
>  | 00000002 | NAMB | free | 2234567890 | 2008-04-17 02:32:02 |
>
>  | 00000003 | NAMB | free | 3234567890 | 2008-04-17 02:32:02 |
>
>  | 00000004 | NAMB | free | 4234567890 | 2008-04-17 02:32:02 |
>  +----------+---------+--------+------------+---------------------+
>
>  4 rows in set (0.00 sec)
>
>
>  Here is the DIALPLAN
>
>  exten => 7777,1,MYSQL(Connect connid localhost ivr ivrxxx dtc)
>
>  exten => 7777,n,MYSQL(Query resultid ${connid} SELECT\ cust_id\, \
> status\,\ tlce\ from\ agent\ where\ phone=\'1234567890\')
>
>  exten => 7777,n,MYSQL(Fetch fetchid ${resultid} custid mystatus mytlce)
>
>  exten => 7777,n,NoOp(CUSTID is ${custid} MYSTATUS is ${mystatus} MYTLCE is
> ${mytlce})
>
>  exten => 7777,n,NoOp(FETCHID is ${fetchid} RESULUT ID is .. ${resultid}
> CONNID is ${connid})
>
>  exten => 7777,n,MYSQL(Clear ${resultid})
>
>  exten => 7777,n,MYSQL(Disconnect ${connid})
>
>  exten => 7777,n,HANGUP
>
>
>
>  exten => 8888,1,MYSQL(Query resultid ${connid} SELECT\ 'cust_id'\, \
> 'status'\,\ 'tlce'\ from\ agent\ where\ phone=\'1234567890\')
>
>  exten => 8888,n,MYSQL(Fetch fetchid ${resultid} custid mystatus mytlce)
>
>  exten => 8888,n,NoOp(CUSTID is ${custid} MYSTATUS is ${mystatus} MYTLCE is
> ${mytlce})
>
>  exten => 8888,n,NoOp(FETCHID is ${fetchid} RESULUT ID is .. ${resultid}
> CONNID is ${connid})
>
>  exten => 8888,n,MYSQL(Clear ${resultid})
>
>  exten => 8888,n,MYSQL(Disconnect ${connid})
>
>  exten => 8888,n,HANGUP
>
>
>  exten => 9999,1,MYSQL(Connect connid localhost ivr ivrxxx dtc)
>
>  exten => 9999,n,MYSQL(Query resultid ${connid} SELECT\ 'cust_id'\ from\
> agent\ where\ phone=\'1234567890\')
>
>  exten => 9999,n,MYSQL(Fetch fetchid ${resultid} custid)
>
>  exten => 9999,n,NoOp(CUSTID is ${custid})
>
>  exten => 9999,n,NoOp(FETCHID is ${fetchid} RESULUT ID is .. ${resultid}
> CONNID is ${connid})
>
>  exten => 9999,n,MYSQL(Clear ${resultid})
>
>  exten => 9999,n,MYSQL(Disconnect ${connid})
>
>  exten => 9999,n,HANGUP
>
>
>
>
>  Here is the Asterisk CLI Output
>
>  dial 9999
>
>  == Console is full duplex
>
>  *CLI> -- Executing [9999 at default:1] MYSQL("OSS/dsp", "Connect connid
> localhost ivr ivrxxx dtc") in new stack
>
>  -- Executing [9999 at default:2] MYSQL("OSS/dsp", "Query resultid 5 SELECT
> cust_id from agent where phone='1234567890'") in new stack
>
>  -- Executing [9999 at default:3] MYSQL("OSS/dsp", "Fetch fetchid 6 custid") in
> new stack
>
>  -- Executing [9999 at default:4] NoOp("OSS/dsp", "CUSTID is ") in new stack
>
>  -- Executing [9999 at default:5] NoOp("OSS/dsp", "FETCHID is 1 RESULUT ID is
> .. 6 CONNID is 5") in new stack
>
>  -- Executing [9999 at default:6] MYSQL("OSS/dsp", "Clear 6") in new stack
>
>  -- Executing [9999 at default:7] MYSQL("OSS/dsp", "Disconnect 5") in new stack
>
>  -- Executing [9999 at default:8] Hangup("OSS/dsp", "") in new stack
>
>  == Spawn extension (default, 9999, 8) exited non-zero on 'OSS/dsp'
>
>  << Hangup on console
>
>  *CLI> dial 8888
>
>  == Console is full duplex
>
>  *CLI> -- Executing [8888 at default:1] MYSQL("OSS/dsp", "Connect connid
> localhost ivr ivrxxx dtc") in new stack
>
>  -- Executing [8888 at default:2] MYSQL("OSS/dsp", "Query resultid 5 SELECT
> cust_id, status, tlce from agent where phone='1234567890'") in new stack
>
>  -- Executing [8888 at default:3] MYSQL("OSS/dsp", "Fetch fetchid 6 custid
> mystatus mytlce") in new stack
>
>  -- Executing [8888 at default:4] NoOp("OSS/dsp", "CUSTID is free MYSTATUS is
> 2008-04-17 02:32:02 MYTLCE is ") in new stack
>
>  -- Executing [8888 at default:5] NoOp("OSS/dsp", "FETCHID is 1 RESULUT ID is
> .. 6 CONNID is 5") in new stack
>
>  -- Executing [8888 at default:6] MYSQL("OSS/dsp", "Clear 6") in new stack
>
>  -- Executing [8888 at default:7] MYSQL("OSS/dsp", "Disconnect 5") in new stack
>
>  -- Executing [8888 at default:8] Hangup("OSS/dsp", "") in new stack
>
>  == Spawn extension (default, 8888, 8) exited non-zero on 'OSS/dsp'
>
>  << Hangup on console >
>
>  OK – Have I done something really dumb or is this a BUG ?
>  Thank VERY MUCH for any and ALL Help

While it may be a bug, I am under the impression that UnixODBC is the
preferred way of connecting to to MySQL.  Maybe you should look into
that and see if you come the the same conclusion about being
"preferred" and see if the "bug" still exists.  That should help in a
couple different ways.

Thanks,
Steve Totaro