MYSQL application not found error

[ARehman]

Hi

Can someone please share the context to use CIDNUM variable from CID Lookup using MySQL.

I want to select customer email based on the caller id from the database and then use that email address in context to send an email

I was able to test DB connection, added Cid loopup (MySQL), added in route pattern.

I am not sure, how to use CIDNUM in context, or if I can run SQL from context and store the output in variable.

[ARehman]

I am getting MYSQL application not found error, any idea ?

Error :

[2021-08-14 20:53:10] WARNING[1687][C-0000006e]: pbx.c:2927 pbx_extension_helper: No application ‘MYSQL’ for extension (service-noti-email-60010, s, 4)
== Spawn extension (service-noti-email-60010, s, 4) exited non-zero on ‘PJSIP/1001_1-00000075’

Custome Context

[customerlookup]
exten => s,1,Answer()
exten => s,n,Playback(please-enter-your&pin_number)
exten => s,n,Read(pinnumber,then-press-pound,4,1,10)
exten => s,n,MYSQL(Connect connid localhost root SQLPASSWORD customerinfo)
exten => s,n,MYSQL(Query resultid ${connid} SELECT name from account where id=${pinnumber})
exten => s,n,MYSQL(Fetch fetchid ${resultid} accountname)
exten => s,n,MYSQL(Disconnect ${connid})
exten => s,n,NoOp(&& the value found is ${accountname} )

;if the account number is not found, the system hangsup.
exten => s,n,ExecIf($["${accountname}"=""]?playback(hangup-try-again))
exten => s,n,ExecIf($["${accountname}"=""]?HANGUP())

;if the account number IS FOUND the system says “THANK YOU”
exten => s,n,PlayBack(auth-thankyou)
exten => s,n,Hangup()

[ARehman]

here is my working example, except email part, when I use ${email} variable which is saving the SQL result, the script is breaking the line and not sending email, I have tested with hardcoded sender email , it works, even with system variable ${CALLERID(num)} it is working

any idea ?
what is wrong ?

[service-noti-email-60010]
exten=> s,1,Noop( checking the callerid ${CALLERID(num)})
same=>n,Set(email=${SHELL(mysql -sN customerinfo -u admin -pPASSWORD -h IPADDRESS -e ‘select email from account where phone=’"${CALLERID(num)}"’ ')})
#same=>n,Set(email=$["$email}"])
same=> n,TrySystem(echo “Incoming Call - From: ${CALLERID(name)} ${CALLERID(number)} received at ${STRFTIME(${EPOCH},%l:%M:%S %p %Z on %A %B %e)}” | mail -s “Phone - Incoming Call to IT 6108” -r “${email}” hello@example.com)
~
~

– Executing [s@service-noti-email-60010:1] NoOp(“PJSIP/1001_1-000000f1”, " checking the callerid 123456789) in new stack
– Executing [s@service-noti-email-60010:2] Set(“PJSIP/1001_1-000000f1”, "email=test@example.net
– ") in new stack
– Executing [s@service-noti-email-60010:3] TrySystem(“PJSIP/1001_1-000000f1”, "echo “Incoming Call - From: Ali 4232104455 received at 2:30:21 AM PKT on Sunday August 15” | mail -s “Phone - Incoming Call to IT MailRoom 6108” -r “customer@example.
– " hello@example.net”) in new stack
– Auto fallthrough, channel ‘PJSIP/1001_1-000000f1’ status is ‘UNKNOWN’
phone*CLI>

[mo10]

See my example here again:

Email send when extension or custom application dialed

Try this: same => n,System(echo -e 'Body Callback from:\\n ${CALLERID(num)} (${CALLERID(name)}) \\n on ${STRFTIME(${EPOCH},,%d.%m.%Y at %H:%M:%S)}' | mail -s "Subject Callback ${CALLERID(num)} - PBX" -r from@mail.com to@mail.com) Most of the time the from@mail.com is important so you e-mail-server will not reject that email on the way out. Use as you did in the Vitalpbx email Settings. And do what PitzKey said to keep on going with the destination you put in at the Custom Context module. Th…

I am playing with ’ and " on purpose. Some " " might cause you trouble!

[ARehman]

I have tired the variable with ’ or " quotes but still breaking in to new stack line, mail -s command test from bash using variable and works fine…may be SQL result is breaking the line ?

Executing [s@service-noti-email-60010:2] Set(“PJSIP/1001_1-0000010c”, "email=test@example.com
– ") in new stack
– Executing [s@service-noti-email-60010:3] TrySystem(“PJSIP/1001_1-0000010c”, "echo “Incoming Call - From: Test 1001 received at 10:41:36 AM PKT on Sunday August 15” | mail -s “Phone - Incoming Call to Canadatel MailRoom 6108” "test@example.com
– " hello@example.com") in new stack

• -*- Auto fallthrough, channel ‘PJSIP/1001_1-0000010c’ status is ‘UNKNOWN’

Code

[service-noti-email-60010]
exten=> s,1,Noop( checking the callerid ${CALLERID(num)})
same=>n,Set(email=${SHELL(mysql -sN customerinfo -u admin -pPASSWORD -h IPADDRESS -e ‘select email from account where phone=’"${CALLERID(num)}"’ ')})
same=> n,TrySystem(echo “Incoming Call - From: ${CALLERID(name)} ${CALLERID(number)} received at ${STRFTIME(${EPOCH},%l:%M:%S %p %Z on %A %B %e)}” | mail -s “Phone - Incoming Call to MailRoom 6108” “${email}” hello@example.com)

[mo10]

I have no idea why you are using “${email}” only use ${email}.

Try this. I first used ’ and then " there.

same=> n,TrySystem(echo 'Incoming Call - From: ${CALLERID(name)} ${CALLERID(number)} received at ${STRFTIME(${EPOCH},%l:%M:%S %p %Z on %A %B %e)}' | mail -s “Phone - Incoming Call to IT 6108” -r ${email} hello@example.com)

Also good dialplan command to check all channel variables:
DumpChan()

[ARehman]

I tried email variable without quotes bit vital is complaining cannot run echo

I will check it further

Thanks

[ARehman]

same=> n,TrySystem(echo ‘Incoming Call - From: ${CALLERID(name)} ${CALLERID(number)} received at ${STRFTIME(${EPOCH},%l:%M:%S %p %Z on %A %B %e)}’ | mail -s “Phone - Incoming Call to IT 6108” -r ${email} hello@example.com)

Error :
Executing [s@service-noti-email-60010:2] Set(“PJSIP/1001_1-0000010f”, "email=aamir@example.com
– ") in new stack
– Executing [s@service-noti-email-60010:3] TrySystem(“PJSIP/1001_1-0000010f”, “echo ‘Incoming Call - From: Test 12345 received at Sun Aug 15 14:02:14 2021’ | mail -s “Phone - Incoming Call to IT 6108” -r aamir@example.com
– hello@canadatel.net”) in new stack
[2021-08-15 19:02:14] WARNING[10833][C-00000108]: app_system.c:146 system_exec_helper: Unable to execute ‘echo ‘Incoming Call - From: Ali 4232104455 received at Sun Aug 15 14:02:14 2021’ | mail -s “Phone - Incoming Call to IT 6108” -r aamir@example.com
hello@example.com’

[mo10]

Might be a “DOS vs. Unix Line Endings” problem.
Or problem with some special characters while copy/paste.

[ARehman]

Thanks , I will look into SQL return format

[ARehman]

I think SQL select is breaking the line for some reasons, I am not able to find , whay and how to use REPLACE or any other function in vital dialplan to remove the line ?

Executing [s@service-noti-email-60010:1] NoOp(“PJSIP/1001_1-0000014e”, " checking the callerid 4232104455") in new stack
– Executing [s@service-noti-email-60010:2] Set(“PJSIP/1001_1-0000014e”, "email=aamir
*** – ") in new stack***
– Executing [s@service-noti-email-60010:3] TrySystem(“PJSIP/1001_1-0000014e”, "echo ‘Incoming Call - From: Ali 4232104455 received at Sun Aug 15 19:05:50 2021’ | mail -s “Phone - Incoming Call to IT 6108” -r “aamir
– " hello@exampl.net”) in new stack
– Auto fallthrough, channel ‘PJSIP/1001_1-0000014e’ status is ‘UNKNOWN’
phone*CLI>

[ARehman]

@mo10

found the issue, line break or extra spaces

after converting the email variable using this same=>n,Set(mailg=${getemail:0:-1})

everything is working as expected

thanks for all your help…

here is my working code, it will query the DB based on the caller ID and send email to support team with real caller’s email address

[service-noti-email-60010]
exten=> s,1,Noop( checking the callerid ${CALLERID(num)})
same=>n,Set(getemail=${SHELL(mysql -s customerinfo -u admin -pPASSWORD -h IPADDRESS --skip-column-names -e ‘select email from account where phone="${CALLERID(num)}"’)})
same=>n,Set(mailg=${getemail:0:-1})
same=>n,TrySystem(echo “Incoming Call - From: ${CALLERID(name)} ${CALLERID(number)} received at ${STRFTIME(${EPOCH},%l:%M:%S %p %Z on %A %B %e)}” | mail -s “Phone - Incoming Call to IT” -r ${mailg} support@example.com, hello@example.com)

[PitzKey]

When posting code or dialplan, please use preformatted text.

Also, you can probably do the same with less lines

[service-noti-email-60010]
exten=> s,1,Noop( checking the callerid ${CALLERID(num)})
same=>n,TrySystem(echo “Incoming Call - From: ${CALLERID(name)} ${CALLERID(number)} received at ${STRFTIME(${EPOCH},%l:%M:%S %p %Z on %A %B %e)}” | mail -s “Phone - Incoming Call to IT” -r ${mailg} support@example.com, ${SHELL(mysql -s customerinfo -u admin -pPASSWORD -h IPADDRESS --skip-column-names -e ‘select email from account where phone="${CALLERID(num)}"’):0:-1})

Also, you may want to look into using ODBC to query MySQL.

[ARehman]

Thank you,

Do you have any examples to share with ODBC function?

[PitzKey]

http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/getting_funky.html

[ARehman]

I am sorry, I am asking so many question but I am very new to aestrix based PBX.

is there a way to read the variable from ARRAY(), mysql returns three columns and stored in three ARRAY(name,getemail,phone) nad I want to read the variable separately.

same=>n,Set(ARRAY(name,getemail,phone)=${SHELL(mysql -s customerinfo -u admin -pPQASSWORD -h IPADDRESS --skip-column-names -e ‘select name,email,phone from account where phone="${CALLERID(num)}"’)})

same=>n,Set(sendmail=${getemail:0:-1})

[PitzKey]

Again, please use preformatted text for ALL your dialplan and code blocks.

Regarding the array, it must be comma separated. I don’t think your SQL query returns the values in a comma separated format.

See resources:
https://wiki.asterisk.org/wiki/display/AST/Asterisk+18+Function_ARRAY

Asterisk func array - VoIP-Info

http://the-asterisk-book.com/1.6/funktionen-array.html

[ARehman]

after adding CONCAT(), it is working now, I can read ARRAY() variables.

thanks

same=>n,Set(ARRAY(name,getemail,phone)=${SHELL(mysql -s customerinfo -u admin -pPASSWORD -h IPADDRESS --skip-column-names -e 'select CONCAT(name, ",", email, ",", phone)  from account where phone="${CALLERID(num)}"')})

[ARehman]

is there a list of variable of Queue or RingGroup ? like ${CALLERID(num)}

I need to read the value of Queue number & name or Ring Group number & name in the context ?