[AsteriskBrasil] Erro ao executar query em PostgreSQL odbc voicemessage
Moisés Paes Sena
moi.oriontec em gmail.com
Segunda Junho 21 16:44:41 BRT 2010
Em 21 de junho de 2010 16:36, Rodrigo Vian
<listas em porttaltecnologia.com.br>escreveu:
> 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
>
>
Sim, me lembro, no ODBC também conectava perfeitamente, havia feito o
seguinte teste:
# echo "select 1" | isql -v asterisk-psql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> +------------+
| ?column? |
+------------+
| 1 |
+------------+
SQLRowCount returns 1
1 rows fetched
Abraços
>
> 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>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 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>
>> (Desenvolvedor WEB)
>> Oriontec Automação Comercial
>> http://www.oriontec.com.br
>>
>
>
>
> --
> Moisés P. Sena <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.orgAsteriskBrasil em listas.asteriskbrasil.orghttp://listas.asteriskbrasil.org/mailman/listinfo/asteriskbrasil
>
>
>
> _______________________________________________
> 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
>
--
Moisés P. Sena <moises em oriontec.com.br>
(Desenvolvedor WEB)
Oriontec Automação Comercial
http://www.oriontec.com.br
-------------- Próxima Parte ----------
Um anexo em HTML foi limpo...
URL: http://listas.asteriskbrasil.org/pipermail/asteriskbrasil/attachments/20100621/5bb18030/attachment-0001.htm
Mais detalhes sobre a lista de discussão AsteriskBrasil