<br><br><div class="gmail_quote">Em 21 de junho de 2010 16:36, Rodrigo Vian <span dir="ltr"><<a href="mailto:listas@porttaltecnologia.com.br">listas@porttaltecnologia.com.br</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;">
<div bgcolor="#ffffff" text="#000000">
Que bom que resolveu Moisés....<br>
<br>
Mas você lembra que eu te perguntei se você conseguia conectar o banco
através de linha de comando?<br>
Isso incluia o ODBC... rs<div class="im"><br></div></div></blockquote><div><br>Sim, me lembro, no ODBC também conectava perfeitamente, havia feito o seguinte teste:<br><br> # echo "select 1" | isql -v asterisk-psql<br>
+---------------------------------------+<br> | Connected! |<br> | |<br> | sql-statement |<br> | help [tablename] |<br> | quit |<br> | |<br> +---------------------------------------+<br> SQL> +------------+<br>
| ?column? |<br> +------------+<br> | 1 |<br> +------------+<br> SQLRowCount returns 1<br> 1 rows fetched<br><br>Abraços<br> </div><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div bgcolor="#ffffff" text="#000000"><div class="im">
<br>
Abs<br>
<br>
Moisés Paes Sena escreveu:
</div><blockquote type="cite">Boa Tarde pessoal!!<div><div></div><div class="h5"><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" target="_blank">moi.oriontec@gmail.com</a>></span>
escreveu:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; 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" 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>
</div></div><pre><hr size="4" width="90%"><div class="im">
_______________________________________________
KHOMP: qualidade em placas de E1, GSM, FXS e FXO para Asterisk.
- Hardware com alta disponibilidade de recursos e qualidade KHOMP
- Suporte técnico local qualificado e gratuito
Conheça a linha completa de produtos KHOMP em <a href="http://www.khomp.com.br" target="_blank">www.khomp.com.br</a>
_______________________________________________
Temos tudo para seu projeto VoIP com Asterisk!
Descontos especiais para assinantes da AsteriskBrasil.org.
Registre-se e receba um cupom exclusivo de desconto!
Acesse agora <a href="http://www.voipmania.com.br" target="_blank">www.voipmania.com.br</a>
______________________________________________
Lista de discussões AsteriskBrasil.org
<a href="mailto:AsteriskBrasil@listas.asteriskbrasil.org" target="_blank">AsteriskBrasil@listas.asteriskbrasil.org</a>
<a href="http://listas.asteriskbrasil.org/mailman/listinfo/asteriskbrasil" target="_blank">http://listas.asteriskbrasil.org/mailman/listinfo/asteriskbrasil</a></div></pre>
</blockquote>
<br>
</div>
<br>_______________________________________________<br>
KHOMP: qualidade em placas de E1, GSM, FXS e FXO para Asterisk.<br>
- Hardware com alta disponibilidade de recursos e qualidade KHOMP<br>
- Suporte técnico local qualificado e gratuito<br>
Conheça a linha completa de produtos KHOMP em <a href="http://www.khomp.com.br" target="_blank">www.khomp.com.br</a><br>
_______________________________________________<br>
Temos tudo para seu projeto VoIP com Asterisk!<br>
Descontos especiais para assinantes da AsteriskBrasil.org.<br>
Registre-se e receba um cupom exclusivo de desconto!<br>
Acesse agora <a href="http://www.voipmania.com.br" target="_blank">www.voipmania.com.br</a><br>
______________________________________________<br>
Lista de discussões AsteriskBrasil.org<br>
<a href="mailto:AsteriskBrasil@listas.asteriskbrasil.org">AsteriskBrasil@listas.asteriskbrasil.org</a><br>
<a href="http://listas.asteriskbrasil.org/mailman/listinfo/asteriskbrasil" target="_blank">http://listas.asteriskbrasil.org/mailman/listinfo/asteriskbrasil</a><br></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>