Dialplan MySQL Inserted ID

Home » Asterisk Users » Dialplan MySQL Inserted ID
Asterisk Users 7 Comments

Hello,

how can I obtain the “inserted ID” after having inserted a row with MySQL in the dialplan ?

exten => s,n,MYSQL(Query resultid ${connid} INSERT IGNORE INTO myTable SET
C1=”${ARG1}”, C2=”${ARG2}”, timestamp=”${STRFTIME(${EPOCH},,%Y-%m-%d_%H:%M:%S)}”)

I need to know the ID of the newly inserted row.

Kind regards, Jonas.

7 thoughts on - Dialplan MySQL Inserted ID

  • You could write an AGI script in something like php or perl and get it to write to the mysql database instead. It can then set a variable which the dialplan can pick up.

  • I’m not sure it’s possible to do that using the simple MySQL interface provided within the dialplan.

    Why not write an AGI script in your favourite language (Perl, Python, PHP, Java all have AGI and MySQL bindings) to perform the INSERT query for you?
    You can supply values for C1 and C2 easily enough; and have your AGI script return the insert ID in a channel variable. (You could also return another channel variable indicating success or failure, if this is important.)

  • Hello,

    can I echo this variable ?

    Like : exten => s,n,NoOp(${LAST_INSERT_ID()})

    Kind regards,

    Jonas.

  • +1. It would also give you somewhere to perform sanity checks on your
    ${ARGS} to avoid SQL injection attacks…

    Kind regards,

    Chris

  • Tuesday, August 20, 2013, 6:08:19 PM, Jonas wrote:

    Like : exten =>> s,n,NoOp(${LAST_INSERT_ID()})

    No, this is a mysql query, so:

    exten => s,n,MYSQL(Query resultid ${connid} INSERT IGNORE INTO myTable SET C1=”${ARG1}”, C2=”${ARG2}”, timestamp=”${STRFTIME(${EPOCH},,%Y-%m-%d_%H:%M:%S)}”)
    exten => s,n,MYSQL(Query resultid ${connid} SELECT LAST_INSERT_ID())
    exten =>> s,n,NoOp(${resultid})

    first is your original insert query, next you must read the last_insert_id() mysql function with an other query, then you can echo the resultid variable which contains the last inserted id.

  • I would be a bit concerned about doing this on a busy system. What would happen if one call inserted a value, a second call inserted a value and then the first call read the LAST_INSERT_ID? Would it get the wrong value back?

    If you do it in AGI then each query can have its own database connection and so avoid this issue. If thats a problem use FastAGI and have a daemon running and use transactions or another method to avoid the issue.