?
Solved

Asterisk PBX and MYSQL Syntax

Posted on 2009-02-18
7
Medium Priority
?
891 Views
Last Modified: 2013-12-21
I have a the following dial plan:
[Test]
exten => s,1,MYSQL(Connect CONNID 10.0.5.20 root password timecard)
exten => s,n,GotoIf($["${MYSQL_STATUS}" = "-1"]?lbl_Test_0:)
exten => s,n,MYSQL(Query ResultID ${CONNID} SELECT\ COUNT(*)\ FROM\ blockedcallerid\ WHERE\ blockedcallerid.callerid=\'${LogCallerID}\'\ AND\ blockedcallerid.blocked\ =\ 1;)
exten => s,n,GotoIf($["${MYSQL_STATUS}" = "-1"]?lbl_Test_0:)
exten => s,n(lbl_Test_1),MYSQL(Clear ${ResultID})
exten => s,n,GotoIf($["${MYSQL_STATUS}" = "-1"]?lbl_Test_2:)
exten => s,n,MYSQL(Disconnect ${CONNID})
exten => s,n,GotoIf($["${MYSQL_STATUS}" = "-1"]?lbl_Test_2:)
exten => s,n(lbl_Test_2),Return()
exten => s,n(lbl_Test_0),NoOp(****ERROR****SQL Command Failed)
exten => s,n,Goto(lbl_Test_1)

The line with SQL Query is where I am having problems:
exten => s,n,MYSQL(Query ResultID ${CONNID} SELECT\ COUNT(*)\ FROM\ blockedcallerid\ WHERE\ blockedcallerid.callerid=\'${LogCallerID}\'\ AND\ blockedcallerid.blocked\ =\ 1;)

The CLI Debug window has the following displayed when it hits the SQL Query Line:
     -- Executing [s@Test:3] MYSQL("SIP/238-acf019e0", "Query ResultID 9 SELECT COUNT(*") in new stack
[Feb 18 13:21:33] WARNING[20598]: app_addon_sql_mysql.c:270 aMYSQL_query: aMYSQL_query: mysql_query failed. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

The MYSQL query.log just shows: SELECT COUNT(*
The SQL query is being truncated after the *

My question is what is the syntax for the SQL string...
0
Comment
Question by:bullet117
  • 4
  • 3
7 Comments
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 23675734
Try it like this...
exten => s,n,MYSQL(Query ResultID ${CONNID} SELECT\ COUNT\ FROM\ blockedcallerid\ WHERE\ blockedcallerid.callerid='${LogCallerID}'\ AND\ blockedcallerid.blocked='1')

Open in new window

0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 23675804
sorry...like this...  
exten => s,n,MYSQL(Query ResultID ${CONNID} SELECT\ COUNT(*)\ FROM\ blockedcallerid\ WHERE\ blockedcallerid.callerid='${LogCallerID}'\ AND\ blockedcallerid.blocked='1')
0
 
LVL 25

Accepted Solution

by:
Ron Malmstead earned 1500 total points
ID: 23676012
lol....sorry....again...  I think you were referencing the table as well...  "blockedcallerid.callerid"...  It should just be the column name...  "callerid" and "blocked".

Like this,...
exten => s,n,MYSQL(Query ResultID ${CONNID} SELECT\ COUNT(*)\ FROM\ blockedcallerid\ WHERE\ callerid='${LogCallerID}'\ AND\ blocked='1')
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:bullet117
ID: 23677536
xuserx2000: You helped a lot the final syntax for this was:
exten => s,n,MYSQL(Query ResultID ${CONNID} SELECT\ COUNT(*)\ FROM\ blockedcallerid\ WHERE\ callerid=\'10.0.5.238\'\ AND\ blocked=1)

I needed to add the \ in front of the tick marks and deleted the tick marks from the numeric field (blocked)...Thanks for your help....
0
 

Author Comment

by:bullet117
ID: 23677600
xuserx2000: I'm getting as bad as you..Ha...Ha...Ha

Here is the final final syntax:
exten => s,n,MYSQL(Query ResultID ${CONNID} SELECT\ COUNT(*)\ FROM\ blockedcallerid\ WHERE\ callerid=\'${LogCallerID}\'\ AND\ blocked=1)
0
 

Author Closing Comment

by:bullet117
ID: 31548466
Thank you for your help, The only thing I had to add was a \ in front of the   ` (ticks)
Here is the final syntax:
exten => s,n,MYSQL(Query ResultID ${CONNID} SELECT\ COUNT(*)\ FROM\ blockedcallerid\ WHERE\ callerid=\'${LogCallerID}\'\ AND\ blocked=1)

)...Thanks again...Brian
0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 23677982
cool.

Thanks,
R
0

Featured Post

Eye-catchers on the conference table

Challenge: The i-unit group was not satisfied with the audio quality during remote meetings. They were looking for a portable solution with excellent audio quality for use in their conference room but also at their client’s offices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question