[AsteriskBrasil] Erro ao executar query em PostgreSQL odbc voicemessage

Moisés Paes Sena moi.oriontec em gmail.com
Segunda Junho 21 15:15:44 BRT 2010


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
-------------- Próxima Parte ----------
Um anexo em HTML foi limpo...
URL: http://listas.asteriskbrasil.org/pipermail/asteriskbrasil/attachments/20100621/ebd7bfe8/attachment-0001.htm 


Mais detalhes sobre a lista de discussão AsteriskBrasil