Boa Tarde pessoal!!<br><br>Acabei resolvendo o problema.<br><br>Na verdade era eu que não estava sabendo fazer as configurações corretamente.<br><br>Obrigado a todos pela ajuda, foram vocês que me clarearam o caminho até chegar à solução.<br>
<br>Respondendo à última sugestão do Rodrigo Vian:<br><br> <i>-- <b>"Tente descomentar a linha do voicemail no extconfig.conf..."</b></i><br><br> R.: Para odbc voicemessage storage não há a necessidade de descomentar esta linha, pois (se nao me engano) ela se refere à configurações do voicemessage armazenadas diretamente no BD, nao existindo portanto, o arquivo "voicemessage.conf".<br>
<br><br>Segue os arquivos que tive de alterar (estas configurações foram testadas):<br><br><br><b>/etc/odbc.ini</b>:<br><br> [PostgreSQL]<br> Description = ODBC for PostgreSQL<br> Driver = /usr/lib/libodbcpsql.so<br>
Setup = /usr/lib/libodbcpsqlS.so<br> FileUsage = 1<br><br> [asterisk-psql]<br> Description = PostgreSQL connection to asterisk database<br> Driver = PostgreSQL<br>
Trace = No<br> TraceFile = sql.log<br> Database = asterisk<br> Servername = 127.0.0.1<br> UserName = asterisk<br>
Password = asterisk<br> Port = 5432<br> ReadOnly = No<br> RowVersioning = No<br> ShowSystemTables = No<br> ShowOidColumn = No<br>
FakeOidIndex = No<br> ConnSettings =<br><br><b>/etc/asterisk/cdr_odbc.conf</b>:<br><br> [global]<br> dsn = asterisk-psql<br> loguniqueid = yes<br>
dispositionstring = yes<br> table = cdr<br><br><b>/etc/asterisk/res_odbc.conf:<br></b><br> [psql]<br> enabled => yes<br> dsn => asterisk-psql<br> pre-connect => yes<br>
<br><b>/etc/asterisk/voicemail.conf</b><br><br> [general]<br> odbcstorage = psql<br> odbctable = voicemessages<br><br><br><b>No Banco de Dados do ASTERISK, tive criar a tabela voicemessages com algumas funções e triggers:</b><br>
<br> CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' <br> LANGUAGE internal IMMUTABLE STRICT;<br><br> CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' <br> LANGUAGE internal IMMUTABLE STRICT;<br>
<br> CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' <br> LANGUAGE internal IMMUTABLE STRICT;<br><br> CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' <br> LANGUAGE internal IMMUTABLE STRICT;<br>
<br> CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend, <br> INTERNALLENGTH = 4, PASSEDBYVALUE );<br><br> CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;<br> CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;<br>
<br> CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"<br> AS $$<br> declare<br> msgcount INTEGER;<br> begin<br> raise notice 'Starting lo_cleanup function for large object with oid %',old.recording;<br>
-- If it is an update action but the BLOB (lo) field was not changed, dont do anything<br> if (TG_OP = 'UPDATE') then<br> if ((old.recording = new.recording) or (old.recording is NULL)) then<br>
raise notice 'Not cleaning up the large object table, as recording has not changed';<br> return new;<br> end if;<br> end if;<br> if (old.recording IS NOT NULL) then<br>
SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemessages WHERE recording <br> = old.recording;<br> if (msgcount > 0) then<br> raise notice 'Not deleting record from the large object table, as object is still referenced';<br>
return new;<br> else<br> perform lo_unlink(old.recording);<br> if found then<br> raise notice 'Cleaning up the large object table';<br> return new;<br>
else<br> raise exception 'Failed to cleanup the large object table';<br> return old;<br> end if;<br> end if;<br> else<br> raise notice 'No need to cleanup the large object table, no recording on old row';<br>
return new;<br> end if;<br> end$$<br> LANGUAGE plpgsql;<br><br> CREATE TABLE voicemessages(<br> uniqueid BIGSERIAL PRIMARY KEY,<br> msgnum BIGINT,<br> dir varchar(80),<br> context varchar(80),<br>
macrocontext varchar(80),<br> callerid varchar(40),<br> origtime varchar(40),<br> duration varchar(20),<br> mailboxuser varchar(80),<br> mailboxcontext varchar(80),<br> recording lo,<br>
label varchar(30),<br> "read" bool DEFAULT false<br> );<br><br>-- -------------------------<br><br>É Isto aí pessoal!! funcionando redondinho da vida!<br>Um Abraço a todos vocês!!<br><br><br><div class="gmail_quote">
Em 18 de junho de 2010 08:42, Moisés Paes Sena <span dir="ltr"><<a href="mailto:moi.oriontec@gmail.com">moi.oriontec@gmail.com</a>></span> escreveu:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
Bom dia pessoal!<br><br>Estou querendo gravar as mensagens de voz no Banco de Dados Postgresql, usando a versão 8.44, nativa do CentOS 5.5, segui os passos de [1].<br><br>Tenho uma extensão:<br><br>exten => _1,1,Voicemail(250@default);<br>
<br>Quando ligo para '1', a aplicação Voicemail roda perfeitamente e me envia o email contendo a mensagem.<br><br>Entretanto, durante a execução de Voicemail recebo as seguintes mensagens de erro conforme log abaixo.<br>
OBS.: A aplicação CDR funciona perfeitamente com esta mesma conexão de banco de dados.<br><br>[1] <a href="http://astbook.asteriskdocs.org/en/2nd_Edition/asterisk-book-html-chunk/I_section12_tt1578.html" target="_blank">http://astbook.asteriskdocs.org/en/2nd_Edition/asterisk-book-html-chunk/I_section12_tt1578.html</a><br>
<br>OBS.: O meu ODBC está configurado apenas nos arquivos: cdr_odbc.conf e res_odbc.conf<br><br>cdr_odbc.conf: <br> [global]<br> dsn = asterisk-connector<br> username = asterisk<br> password = asterisk<br> loguniqueid = 1<br>
table = cdr<br><br>res_odbc.conf:<br> [asterisk]<br> enabled => yes<br> dsn => asterisk-connector<br> username => asterisk<br> password => asterisk<br> pooling => no<br> limit => -1<br>
pre-connect => yes<br><br>Log:<br><br>[Jun 18 08:36:50] DEBUG[2608] channel.c: Set channel SIP/250-00000002 to read format alaw<br>[Jun 18 08:36:50] DEBUG[2608] app.c: Locked path '/var/spool/asterisk/voicemail/default/250/INBOX'<br>
[Jun 18 08:36:50] DEBUG[2608] app.c: Unlocked path '/var/spool/asterisk/voicemail/default/250/INBOX'<br>[Jun 18 08:36:50] WARNING[2608] app_voicemail.c: SQL Prepare failed![DELETE FROM voicemessages WHERE dir=? AND msgnum=?]<br>
[Jun 18 08:36:50] WARNING[2608] res_odbc.c: SQL Prepare failed. Attempting a reconnect...<br>[Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Disconnected 0 from asterisk [asterisk-connector]<br>[Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Database handle deallocated<br>
[Jun 18 08:36:50] NOTICE[2608] res_odbc.c: Connecting asterisk<br>[Jun 18 08:36:50] NOTICE[2608] res_odbc.c: res_odbc: Connected to asterisk [asterisk-connector]<br>[Jun 18 08:36:50] WARNING[2608] app_voicemail.c: SQL Prepare failed![DELETE FROM voicemessages WHERE dir=? AND msgnum=?]<br>
[Jun 18 08:36:50] WARNING[2608] res_odbc.c: SQL Prepare failed. Attempting a reconnect...<br>[Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Disconnected 0 from asterisk [asterisk-connector]<br>[Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Database handle deallocated<br>
[Jun 18 08:36:50] NOTICE[2608] res_odbc.c: Connecting asterisk<br>[Jun 18 08:36:50] NOTICE[2608] res_odbc.c: res_odbc: Connected to asterisk [asterisk-connector]<br>[Jun 18 08:36:50] WARNING[2608] app_voicemail.c: SQL Execute error!<br>
[DELETE FROM voicemessages WHERE dir=? AND msgnum=?]<br><br>[Jun 18 08:36:50] VERBOSE[2608] logger.c: == Parsing '/var/spool/asterisk/voicemail/default/250/INBOX/msg0000.txt': [Jun 18 08:36:50] DEBUG[2608] config.c: Parsing /var/spool/asterisk/voicemail/default/250/INBOX/msg0000.txt<br>
[Jun 18 08:36:50] VERBOSE[2608] logger.c: Found<br>[Jun 18 08:36:50] WARNING[2608] app_voicemail.c: SQL Prepare failed![INSERT INTO voicemessages (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext) VALUES (?,?, ? , ?,?,?,?,?,?,?)]<br>
[Jun 18 08:36:50] WARNING[2608] res_odbc.c: SQL Prepare failed. Attempting a reconnect...<br>[Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Disconnected 0 from asterisk [asterisk-connector]<br>[Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Database handle deallocated<br>
[Jun 18 08:36:50] NOTICE[2608] res_odbc.c: Connecting asterisk<br>[Jun 18 08:36:51] NOTICE[2608] res_odbc.c: res_odbc: Connected to asterisk [asterisk-connector]<br>[Jun 18 08:36:51] WARNING[2608] app_voicemail.c: SQL Prepare failed![INSERT INTO voicemessages (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext) VALUES (?,?, ? , ?,?,?,?,?,?,?)]<br>
[Jun 18 08:36:51] WARNING[2608] res_odbc.c: SQL Prepare failed. Attempting a reconnect...<br>[Jun 18 08:36:51] DEBUG[2608] res_odbc.c: Disconnected 0 from asterisk [asterisk-connector]<br>[Jun 18 08:36:51] DEBUG[2608] res_odbc.c: Database handle deallocated<br>
[Jun 18 08:36:51] NOTICE[2608] res_odbc.c: Connecting asterisk<br>[Jun 18 08:36:51] NOTICE[2608] res_odbc.c: res_odbc: Connected to asterisk [asterisk-connector]<br>[Jun 18 08:36:51] DEBUG[2608] app_voicemail.c: Attaching file '/var/spool/asterisk/voicemail/default/250/INBOX/msg0000', format 'WAV', uservm is '2048', global is 2048<br>
[Jun 18 08:36:51] VERBOSE[2608] logger.c: == Parsing '/var/spool/asterisk/voicemail/default/250/INBOX/msg0000.txt': [Jun 18 08:36:51] DEBUG[2608] config.c: Parsing /var/spool/asterisk/voicemail/default/250/INBOX/msg0000.txt<br>
[Jun 18 08:36:51] VERBOSE[2608] logger.c: Found<br>[Jun 18 08:36:51] DEBUG[2608] devicestate.c: Notification of state change to be queued on device/channel Substitution/voicemail<br>[Jun 18 08:36:51] VERBOSE[2608] logger.c: == Parsing '/var/spool/asterisk/voicemail/default/250/INBOX/msg0000.txt': [Jun 18 08:36:51] DEBUG[2608] config.c: Parsing /var/spool/asterisk/voicemail/default/250/INBOX/msg0000.txt<br>
[Jun 18 08:36:51] VERBOSE[2608] logger.c: Found<br>[Jun 18 08:36:51] DEBUG[2608] devicestate.c: Notification of state change to be queued on device/channel Substitution/voicemail<br>[Jun 18 08:36:51] DEBUG[2608] app_voicemail.c: Sent mail to <a href="mailto:moises@oriontec.com.br" target="_blank">moises@oriontec.com.br</a> with command '/usr/sbin/sendmail -t'<br>
[Jun 18 08:36:51] DEBUG[2109] devicestate.c: No provider found, checking channel drivers for Substitution - voicemail<br>[Jun 18 08:36:51] DEBUG[2109] devicestate.c: Changing state for Substitution/voicemail - state 4 (Invalid)<br>
[Jun 18 08:36:51] DEBUG[2109] devicestate.c: No provider found, checking channel drivers for Substitution - voicemail<br>[Jun 18 08:36:51] DEBUG[2109] devicestate.c: Changing state for Substitution/voicemail - state 4 (Invalid)<br>
[Jun 18 08:36:51] DEBUG[2231] app_queue.c: Device 'Substitution/voicemail' changed to state '4' (Invalid) but we don't care because they're not a member of any queue.<br>[Jun 18 08:36:51] DEBUG[2231] app_queue.c: Device 'Substitution/voicemail' changed to state '4' (Invalid) but we don't care because they're not a member of any queue.<br>
[Jun 18 08:36:51] DEBUG[2608] pbx.c: Spawn extension (interno,1,1) exited non-zero on 'SIP/250-00000002'<br>[Jun 18 08:36:51] VERBOSE[2608] logger.c: == Spawn extension (interno, 1, 1) exited non-zero on 'SIP/250-00000002'<br>
[Jun 18 08:36:51] DEBUG[2608] channel.c: Soft-Hanging up channel 'SIP/250-00000002'<br>[Jun 18 08:36:51] DEBUG[2608] channel.c: Hanging up channel 'SIP/250-00000002'<br>[Jun 18 08:36:51] DEBUG[2608] chan_sip.c: Hangup call SIP/250-00000002, SIP callid NTg1MmFmZTQ3YjAxYmFkMzg1MjQxY2Y3ODk2YTc0OTc.)<br>
[Jun 18 08:36:51] DEBUG[2608] chan_sip.c: Updating call counter for incoming call<br>[Jun 18 08:36:51] DEBUG[2608] chan_sip.c: Call from user '250' removed from call limit 10<br>[Jun 18 08:36:51] VERBOSE[2608] logger.c: > cdr_odbc: Connected to asterisk-connector<br>
[Jun 18 08:36:51] VERBOSE[2608] logger.c: > cdr_odbc: Error in PREPARE -1<br>[Jun 18 08:36:51] VERBOSE[2608] logger.c: > cdr_odbc: Query FAILED Call not logged!<br clear="all"><font color="#888888"><br>
-- <br>Moisés P. Sena <<a href="mailto:moises@oriontec.com.br" target="_blank">moises@oriontec.com.br</a>><br>
(Desenvolvedor WEB)<br>Oriontec Automação Comercial<br><a href="http://www.oriontec.com.br" target="_blank">http://www.oriontec.com.br</a><br>
</font></blockquote></div><br><br clear="all"><br>-- <br>Moisés P. Sena <<a href="mailto:moises@oriontec.com.br">moises@oriontec.com.br</a>><br>(Desenvolvedor WEB)<br>Oriontec Automação Comercial<br><a href="http://www.oriontec.com.br">http://www.oriontec.com.br</a><br>