[AsteriskBrasil] Erro ao executar query em PostgreSQL odbc voicemessage
Rodrigo Vian
listas em porttaltecnologia.com.br
Segunda Junho 21 16:36:09 BRT 2010
Que bom que resolveu Moisés....
Mas você lembra que eu te perguntei se você conseguia conectar o banco
através de linha de comando?
Isso incluia o ODBC... rs
Abs
Moisés Paes Sena escreveu:
> Boa Tarde pessoal!!
>
> Acabei resolvendo o problema.
>
> Na verdade era eu que não estava sabendo fazer as configurações
> corretamente.
>
> Obrigado a todos pela ajuda, foram vocês que me clarearam o caminho
> até chegar à solução.
>
> Respondendo à última sugestão do Rodrigo Vian:
>
> /-- *"Tente descomentar a linha do voicemail no
> extconfig.conf..."*/
>
> 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".
>
>
> Segue os arquivos que tive de alterar (estas configurações foram
> testadas):
>
>
> */etc/odbc.ini*:
>
> [PostgreSQL]
> Description = ODBC for PostgreSQL
> Driver = /usr/lib/libodbcpsql.so
> Setup = /usr/lib/libodbcpsqlS.so
> FileUsage = 1
>
> [asterisk-psql]
> Description = PostgreSQL connection to asterisk
> database
> Driver = PostgreSQL
> Trace = No
> TraceFile = sql.log
> Database = asterisk
> Servername = 127.0.0.1
> UserName = asterisk
> Password = asterisk
> Port = 5432
> ReadOnly = No
> RowVersioning = No
> ShowSystemTables = No
> ShowOidColumn = No
> FakeOidIndex = No
> ConnSettings =
>
> */etc/asterisk/cdr_odbc.conf*:
>
> [global]
> dsn = asterisk-psql
> loguniqueid = yes
> dispositionstring = yes
> table = cdr
>
> */etc/asterisk/res_odbc.conf:
> *
> [psql]
> enabled => yes
> dsn => asterisk-psql
> pre-connect => yes
>
> */etc/asterisk/voicemail.conf*
>
> [general]
> odbcstorage = psql
> odbctable = voicemessages
>
>
> *No Banco de Dados do ASTERISK, tive criar a tabela voicemessages com
> algumas funções e triggers:*
>
> CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin'
> LANGUAGE internal IMMUTABLE STRICT;
>
> CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout'
> LANGUAGE internal IMMUTABLE STRICT;
>
> CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv'
> LANGUAGE internal IMMUTABLE STRICT;
>
> CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv'
> LANGUAGE internal IMMUTABLE STRICT;
>
> CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv,
> SEND = losend,
> INTERNALLENGTH = 4, PASSEDBYVALUE );
>
> CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
> CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;
>
> CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
> AS $$
> declare
> msgcount INTEGER;
> begin
> raise notice 'Starting lo_cleanup function for large object
> with oid %',old.recording;
> -- If it is an update action but the BLOB (lo) field was not
> changed, dont do anything
> if (TG_OP = 'UPDATE') then
> if ((old.recording = new.recording) or (old.recording is
> NULL)) then
> raise notice 'Not cleaning up the large object table, as
> recording has not changed';
> return new;
> end if;
> end if;
> if (old.recording IS NOT NULL) then
> SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemessages
> WHERE recording
> = old.recording;
> if (msgcount > 0) then
> raise notice 'Not deleting record from the large object
> table, as object is still referenced';
> return new;
> else
> perform lo_unlink(old.recording);
> if found then
> raise notice 'Cleaning up the large object table';
> return new;
> else
> raise exception 'Failed to cleanup the large object
> table';
> return old;
> end if;
> end if;
> else
> raise notice 'No need to cleanup the large object table,
> no recording on old row';
> return new;
> end if;
> end$$
> LANGUAGE plpgsql;
>
> CREATE TABLE voicemessages(
> uniqueid BIGSERIAL PRIMARY KEY,
> msgnum BIGINT,
> dir varchar(80),
> context varchar(80),
> macrocontext varchar(80),
> callerid varchar(40),
> origtime varchar(40),
> duration varchar(20),
> mailboxuser varchar(80),
> mailboxcontext varchar(80),
> recording lo,
> label varchar(30),
> "read" bool DEFAULT false
> );
>
> -- -------------------------
>
> É Isto aí pessoal!! funcionando redondinho da vida!
> Um Abraço a todos vocês!!
>
>
> Em 18 de junho de 2010 08:42, Moisés Paes Sena <moi.oriontec em gmail.com
> <mailto:moi.oriontec em gmail.com>> escreveu:
>
> Bom dia pessoal!
>
> 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].
>
> Tenho uma extensão:
>
> exten => _1,1,Voicemail(250 em default);
>
> Quando ligo para '1', a aplicação Voicemail roda perfeitamente e
> me envia o email contendo a mensagem.
>
> Entretanto, durante a execução de Voicemail recebo as seguintes
> mensagens de erro conforme log abaixo.
> OBS.: A aplicação CDR funciona perfeitamente com esta
> mesma conexão de banco de dados.
>
> [1]
> http://astbook.asteriskdocs.org/en/2nd_Edition/asterisk-book-html-chunk/I_section12_tt1578.html
>
> OBS.: O meu ODBC está configurado apenas nos arquivos:
> cdr_odbc.conf e res_odbc.conf
>
> cdr_odbc.conf:
> [global]
> dsn = asterisk-connector
> username = asterisk
> password = asterisk
> loguniqueid = 1
> table = cdr
>
> res_odbc.conf:
> [asterisk]
> enabled => yes
> dsn => asterisk-connector
> username => asterisk
> password => asterisk
> pooling => no
> limit => -1
> pre-connect => yes
>
> Log:
>
> [Jun 18 08:36:50] DEBUG[2608] channel.c: Set channel
> SIP/250-00000002 to read format alaw
> [Jun 18 08:36:50] DEBUG[2608] app.c: Locked path
> '/var/spool/asterisk/voicemail/default/250/INBOX'
> [Jun 18 08:36:50] DEBUG[2608] app.c: Unlocked path
> '/var/spool/asterisk/voicemail/default/250/INBOX'
> [Jun 18 08:36:50] WARNING[2608] app_voicemail.c: SQL Prepare
> failed![DELETE FROM voicemessages WHERE dir=? AND msgnum=?]
> [Jun 18 08:36:50] WARNING[2608] res_odbc.c: SQL Prepare failed.
> Attempting a reconnect...
> [Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Disconnected 0 from
> asterisk [asterisk-connector]
> [Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Database handle deallocated
> [Jun 18 08:36:50] NOTICE[2608] res_odbc.c: Connecting asterisk
> [Jun 18 08:36:50] NOTICE[2608] res_odbc.c: res_odbc: Connected to
> asterisk [asterisk-connector]
> [Jun 18 08:36:50] WARNING[2608] app_voicemail.c: SQL Prepare
> failed![DELETE FROM voicemessages WHERE dir=? AND msgnum=?]
> [Jun 18 08:36:50] WARNING[2608] res_odbc.c: SQL Prepare failed.
> Attempting a reconnect...
> [Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Disconnected 0 from
> asterisk [asterisk-connector]
> [Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Database handle deallocated
> [Jun 18 08:36:50] NOTICE[2608] res_odbc.c: Connecting asterisk
> [Jun 18 08:36:50] NOTICE[2608] res_odbc.c: res_odbc: Connected to
> asterisk [asterisk-connector]
> [Jun 18 08:36:50] WARNING[2608] app_voicemail.c: SQL Execute error!
> [DELETE FROM voicemessages WHERE dir=? AND msgnum=?]
>
> [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
> [Jun 18 08:36:50] VERBOSE[2608] logger.c: Found
> [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 (?,?, ? , ?,?,?,?,?,?,?)]
> [Jun 18 08:36:50] WARNING[2608] res_odbc.c: SQL Prepare failed.
> Attempting a reconnect...
> [Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Disconnected 0 from
> asterisk [asterisk-connector]
> [Jun 18 08:36:50] DEBUG[2608] res_odbc.c: Database handle deallocated
> [Jun 18 08:36:50] NOTICE[2608] res_odbc.c: Connecting asterisk
> [Jun 18 08:36:51] NOTICE[2608] res_odbc.c: res_odbc: Connected to
> asterisk [asterisk-connector]
> [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 (?,?, ? , ?,?,?,?,?,?,?)]
> [Jun 18 08:36:51] WARNING[2608] res_odbc.c: SQL Prepare failed.
> Attempting a reconnect...
> [Jun 18 08:36:51] DEBUG[2608] res_odbc.c: Disconnected 0 from
> asterisk [asterisk-connector]
> [Jun 18 08:36:51] DEBUG[2608] res_odbc.c: Database handle deallocated
> [Jun 18 08:36:51] NOTICE[2608] res_odbc.c: Connecting asterisk
> [Jun 18 08:36:51] NOTICE[2608] res_odbc.c: res_odbc: Connected to
> asterisk [asterisk-connector]
> [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
> [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
> [Jun 18 08:36:51] VERBOSE[2608] logger.c: Found
> [Jun 18 08:36:51] DEBUG[2608] devicestate.c: Notification of state
> change to be queued on device/channel Substitution/voicemail
> [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
> [Jun 18 08:36:51] VERBOSE[2608] logger.c: Found
> [Jun 18 08:36:51] DEBUG[2608] devicestate.c: Notification of state
> change to be queued on device/channel Substitution/voicemail
> [Jun 18 08:36:51] DEBUG[2608] app_voicemail.c: Sent mail to
> moises em oriontec.com.br <mailto:moises em oriontec.com.br> with
> command '/usr/sbin/sendmail -t'
> [Jun 18 08:36:51] DEBUG[2109] devicestate.c: No provider found,
> checking channel drivers for Substitution - voicemail
> [Jun 18 08:36:51] DEBUG[2109] devicestate.c: Changing state for
> Substitution/voicemail - state 4 (Invalid)
> [Jun 18 08:36:51] DEBUG[2109] devicestate.c: No provider found,
> checking channel drivers for Substitution - voicemail
> [Jun 18 08:36:51] DEBUG[2109] devicestate.c: Changing state for
> Substitution/voicemail - state 4 (Invalid)
> [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.
> [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.
> [Jun 18 08:36:51] DEBUG[2608] pbx.c: Spawn extension (interno,1,1)
> exited non-zero on 'SIP/250-00000002'
> [Jun 18 08:36:51] VERBOSE[2608] logger.c: == Spawn extension
> (interno, 1, 1) exited non-zero on 'SIP/250-00000002'
> [Jun 18 08:36:51] DEBUG[2608] channel.c: Soft-Hanging up channel
> 'SIP/250-00000002'
> [Jun 18 08:36:51] DEBUG[2608] channel.c: Hanging up channel
> 'SIP/250-00000002'
> [Jun 18 08:36:51] DEBUG[2608] chan_sip.c: Hangup call
> SIP/250-00000002, SIP callid
> NTg1MmFmZTQ3YjAxYmFkMzg1MjQxY2Y3ODk2YTc0OTc.)
> [Jun 18 08:36:51] DEBUG[2608] chan_sip.c: Updating call counter
> for incoming call
> [Jun 18 08:36:51] DEBUG[2608] chan_sip.c: Call from user '250'
> removed from call limit 10
> [Jun 18 08:36:51] VERBOSE[2608] logger.c: > cdr_odbc:
> Connected to asterisk-connector
> [Jun 18 08:36:51] VERBOSE[2608] logger.c: > cdr_odbc: Error
> in PREPARE -1
> [Jun 18 08:36:51] VERBOSE[2608] logger.c: > cdr_odbc: Query
> FAILED Call not logged!
>
> --
> Moisés P. Sena <moises em oriontec.com.br
> <mailto:moises em oriontec.com.br>>
> (Desenvolvedor WEB)
> Oriontec Automação Comercial
> http://www.oriontec.com.br
>
>
>
>
> --
> Moisés P. Sena <moises em oriontec.com.br <mailto:moises em oriontec.com.br>>
> (Desenvolvedor WEB)
> Oriontec Automação Comercial
> http://www.oriontec.com.br
> ------------------------------------------------------------------------
>
> _______________________________________________
> 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 www.khomp.com.br
> _______________________________________________
> 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 www.voipmania.com.br
> ______________________________________________
> Lista de discussões AsteriskBrasil.org
> AsteriskBrasil em listas.asteriskbrasil.org
> http://listas.asteriskbrasil.org/mailman/listinfo/asteriskbrasil
-------------- Próxima Parte ----------
Um anexo em HTML foi limpo...
URL: http://listas.asteriskbrasil.org/pipermail/asteriskbrasil/attachments/20100621/44c454a1/attachment-0001.htm
Mais detalhes sobre a lista de discussão AsteriskBrasil