[asterisk-bugs] [JIRA] (ASTERISK-24441) Unable to store voicemail greetings using PostgreSQL with ODBC

[Anthony Messina (JIRA)]

    [ https://issues.asterisk.org/jira/browse/ASTERISK-24441?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=223075#comment-223075 ] 

Anthony Messina commented on ASTERISK-24441:
--------------------------------------------

Tables created with the Alembic scripts: http://svnview.digium.com/svn/asterisk/trunk/contrib/ast-db-manage/voicemail/versions/a2e9769475e_create_tables.py?view=markup

{code}
-- Table: voicemail_messages

-- DROP TABLE voicemail_messages;

CREATE TABLE voicemail_messages
(
  dir character varying(255) NOT NULL,
  msgnum integer NOT NULL,
  context character varying(80),
  macrocontext character varying(80),
  callerid character varying(80),
  origtime integer,
  duration integer,
  recording lo,
  flag character varying(30),
  mailboxuser character varying(30),
  mailboxcontext character varying(30),
  msg_id character varying(40),
  CONSTRAINT voicemail_messages_dir_msgnum PRIMARY KEY (dir, msgnum)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE voicemail_messages
  OWNER TO asterisk;
GRANT ALL ON TABLE voicemail_messages TO asterisk;

-- Index: voicemail_messages_dir

-- DROP INDEX voicemail_messages_dir;

CREATE INDEX voicemail_messages_dir
  ON voicemail_messages
  USING btree
  (dir COLLATE pg_catalog."default");


-- Trigger: vm_cleanup on voicemail_messages

-- DROP TRIGGER vm_cleanup ON voicemail_messages;

CREATE TRIGGER vm_cleanup
  AFTER UPDATE OR DELETE
  ON voicemail_messages
  FOR EACH ROW
  EXECUTE PROCEDURE vm_lo_cleanup();
{code}

> Unable to store voicemail greetings using PostgreSQL with ODBC
> --------------------------------------------------------------
>
>                 Key: ASTERISK-24441
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-24441
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Applications/app_voicemail/ODBC
>    Affects Versions: 13.0.0-beta2
>         Environment: Fedora 20 x86_64, PostgreSQL 9.3.5
>            Reporter: Anthony Messina
>
> Using ODBC voicemail with PostgreSQL backend, I am unable to store voicemail greetings as Asterisk (app_voicemail) attempts to insert invalid integers into the recommended PostgreSQL table, yielding the PostgreSQL error
> {code}
> invalid input syntax for integer: ""
> {code}
> The issue appears to be that Asterisk is passing NULL values as "" and is quoting integers, like "-1" via the store_file function in app_voicemail.c
> I get errors similar to what the user described at http://stackoverflow.com/questions/22863602/unable-to-store-voicemail-through-odbc-in-asterisk
> {code}
> [Oct 19 13:32:04] DEBUG[28796][C-00000000]: res_odbc.c:1062 odbc_release_obj2: odbc_release_obj2(0x252f738) called (obj->txf = (nil))
> [Oct 19 13:32:04] WARNING[28796][C-00000000]: app_voicemail.c:4186 insert_data_cb: SQL Direct Execute failed!
> [Oct 19 13:32:04] WARNING[28796][C-00000000]: res_odbc.c:617 ast_odbc_direct_execute: SQL Execute error! Verifying connection to asterisk [asterisk]...
> [Oct 19 13:32:04] WARNING[28796][C-00000000]: app_voicemail.c:4186 insert_data_cb: SQL Direct Execute failed!
> [Oct 19 13:32:04] WARNING[28796][C-00000000]: app_voicemail.c:4302 store_file: SQL Execute error!
> [INSERT INTO voicemail_messages (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext,flag,msg_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)]
> {code}



--
This message was sent by Atlassian JIRA
(v6.2#6252)