[Home]

Summary:ASTERISK-24441: Unable to store voicemail greetings using PostgreSQL with ODBC
Reporter:Anthony Messina (amessina)Labels:
Date Opened:2014-10-19 15:21:32Date Closed:
Priority:MajorRegression?
Status:Open/NewComponents:Applications/app_voicemail/ODBC
Versions:13.0.0-beta2 Frequency of
Occurrence		Constant
Related
Issues:
Environment:Fedora 20 x86_64, PostgreSQL 9.3.5Attachments:
Description: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}
Comments:By: Matt Jordan (mjordan) 2014-10-19 16:33:37.435-0500

What is your table schema?

PostgreSQL is a bit of a pain to set up for Asterisk ODBC VoiceMail, but things haven't really changed at all since this was written:

http://asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/I_section12_tt1578.html
By: Anthony Messina (amessina) 2014-10-19 17:26:32.470-0500

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}
By: Matt Jordan (mjordan) 2014-10-28 11:19:21.621-0500

Based on the definition of the {{insert_data}} struct, it does appear as if all of the integer columns should in fact be strings.

If you convert your table to have those columns as {{character varying}}, does the insertion work correctly?
By: Anthony Messina (amessina) 2014-10-28 18:06:53.689-0500

Yes, it does work properly if I change the columns to character varying.  I guess there are two ways things could go:
1. Use integer columns (as the data truly are integers) and correct app_voicemail to insert the data as integers rather than strings (probably more "correct")
2. Fix the table creation alembic scripts and documentation indicating that the tables be created with integer columns (probably "easier")

The only issue I see with 2 is that there isn't a way to upgrade the table to convert from integer to varchar.  The data would need to be manually dumped, corrected for reinsertion (properly quoted) as string values, and reinserted into the table with varchar columns.