Asterisk PBX and MYSQL Syntax

[bullet117]

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...

 

[wardmundy]

Haven't tried this in a while. My recollection is that it was buggy as hell. You're much better off doing the MySQL processing in a PHP/AGI script and calling it from your dialplan. Most of the Nerd Vittles scripts will point you in the right direction.

 

[dghundt]

I think you are using \ when you should not.
Here are some examples of an old dialplan I used a few years back.
I wanted to learn mysql in dialplans with another project so I played with some of Ward's dial plans.
This was valid for asterisk 1.2

exten => s,2,MYSQL(Connect connid localhost root passw0rd databaseName)
exten => s,3,MYSQL(Query resultid ${connid} SELECT count(id) from callees) 
exten => s,4,MYSQL(Fetch fetchid ${resultid} COUNTER)
exten => s,5,MYSQL(Clear ${resultid})
exten => s,6,MYSQL(Disconnect ${connid})
exten => s,7,Playback(the-num-i-have-is)
exten => s,8,SayNumber(${COUNTER})

exten => s,11,MYSQL(Connect connid localhost root passw0rd database)
exten => s,12,MYSQL(Query resultid ${connid} SELECT callresult FROM callees)
exten => s,13,MYSQL(Fetch foundrow ${resultid} number)
exten => s,14,NoOp(Number: ${number}   foundrow: ${foundrow})
exten => s,15,GotoIf($[${foundrow} = 0]?21) ; if no more rows then exit
exten => s,16,NoOp(${number}) ; result of call like Answered or AnswMachine
exten => s,17,Set(temp=test) ; just a helper variable to test null condition which means call not connect so blank result
exten => s,18,Set(responsefound=${IF($[${number}${temp}=${temp}]?0:1)}) ; is 1 if a call was answered
exten => s,19,GotoIf($[${responsefound} = 1]?21:13) ; found answered call so exit
exten => s,20,Goto(13) ; loop if row was found to look for more responses if none so far
exten => s,21,MYSQL(Clear ${resultid})
exten => s,22,MYSQL(Disconnect ${connid})
exten => s,23,GotoIf($[${responsefound} = 0]?26:24} ; if found answered call then notify user

Notice below I have double slashes.
This is allowed me to enter the dial plan in config edit, which subsequently removed a single slash during when saved.
The working dial plan code should then have only one slash!

exten => h,2,Set(CALLRESULT='Earlyhangup'|g)
exten => h,3,MYSQL(Connect connid localhost root passw0rd database)
exten => h,4,Set(TIME=${TIMESTAMP})
exten => h,5,Set(FIRST=${TIME:0:8})
exten => h,6,Set(SECOND=${TIME:9:6})
exten => h,7,Set(TIME=${FIRST}${SECOND})
exten => h,8,MYSQL(Query resultid ${connid} UPDATE callees SET calledtime=\\'${TIME}\\'\\, callresult=\\'${CALLRESULT}\\' WHERE id=${ID})
exten => h,9,MYSQL(Clear ${resultid})
exten => h,10,MYSQL(Disconnect ${connid})
exten => h,11,Hangup

exten => h,1,MYSQL(Connect connid localhost root passw0rd database)
exten => h,2,Set(TIME=${TIMESTAMP})
exten => h,3,Set(FIRST=${TIME:0:8})
exten => h,4,Set(SECOND=${TIME:9:6})
exten => h,5,Set(TIME=${FIRST}${SECOND})
exten => h,6,MYSQL(Query resultid ${connid} UPDATE callees SET calledtime=\\'${TIME}\\'\\, callresult=\\'${CALLRESULT}\\' WHERE id=${ID}) 
exten => h,7,MYSQL(Clear ${resultid})
exten => h,8,MYSQL(Disconnect ${connid})
exten => h,9,Hangup

 

[bullet117]

Ward is there any good websites on AGI or books on the subject...

 

[dghundt]

If you want to use AGI and mysql, follow Wards advice and use his scripts to teach you how to do it. Then pick up a php book to craft your specific code.