[asterisk-bugs] [JIRA] (ASTERISK-28242) Can't Retrieve Voicemail from PostgreSQL database when "msgnum" is INT type

[Kevin Harwell (JIRA)]

     [ https://issues.asterisk.org/jira/browse/ASTERISK-28242?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Kevin Harwell updated ASTERISK-28242:
-------------------------------------

    Assignee: Tahmim Ahmed Shibli
      Status: Waiting for Feedback  (was: Triage)

> Can't Retrieve Voicemail from PostgreSQL database when "msgnum" is INT type
> ---------------------------------------------------------------------------
>
>                 Key: ASTERISK-28242
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-28242
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Applications/app_voicemail/ODBC
>    Affects Versions: 13.8.2
>         Environment: CentOS 7, PostgreSQL 9.5, asterisk-certified-13.8-cert4
>            Reporter: Tahmim Ahmed Shibli
>            Assignee: Tahmim Ahmed Shibli
>
> When I connect to voicemail_message table which have msgnum columnt datatype INT, I get the below messages in the asterisk log, but the message is saved in database:
> {code:none}
> app_voicemail.c:3838 retrieve_file: SQL Execute error!
> [SELECT * FROM voicemail_messages WHERE dir=? AND msgnum=?]
> [Jan  9 12:26:43] WARNING[1386][C-00000005]: res_odbc.c:459 ast_odbc_print_errors: SQL Execute returned an error: HY000: ERROR:  operator does not exist: integer = character at character 102;
> Error while executing the query
> [Jan  9 12:26:43] WARNING[1386][C-00000005]: res_odbc.c:403 ast_odbc_prepare_and_execute: SQL Execute error -1!
> [Jan  9 12:26:43] WARNING[1386][C-00000005]: app_voicemail.c:3838 retrieve_file: SQL Execute error!
> [SELECT * FROM voicemail_messages WHERE dir=? AND msgnum=?]
> {code}
> And, when I dial to retrieve a voicemail I can hear to how many voicemail messages I have, and when I press 1 for a new message, it says the first message, and then print the below error and skips the message and goes to advance options.
> {code:none}
> WARNING[1076][C-00000007]: res_odbc.c:459 ast_odbc_print_errors: SQL Execute returned an error: HY000: ERROR:  operator does not exist: integer = character at character 102;
> Error while executing the query
> [Jan  9 13:49:55] WARNING[1076][C-00000007]: res_odbc.c:403 ast_odbc_prepare_and_execute: SQL Execute error -1!
> [Jan  9 13:49:55] WARNING[1076][C-00000007]: app_voicemail.c:3844 retrieve_file: SQL Execute error!
> [SELECT * FROM voicemail_messages WHERE dir=? AND msgnum=?]
>   == Parsing '/var/spool/asterisk/voicemail/shiblicus1/3206/INBOX/msg0000.txt': Found
>     -- <PJSIP/shiblicus1_cus1group2_1_3206-00000008> Playing 'vm-message.ulaw' (language 'en')
> [Jan  9 13:49:55] WARNING[1076][C-00000007]: app_voicemail.c:8692 play_message: No origtime?!
>     -- Executing [check_voicemail at maxcore:3] Hangup("PJSIP/shiblicus1_cus1group2_1_3206-00000008", "") in new stack
> {code}
> I further checked the asterisk source, and from *apps/app_voicemail.c* file I can see that, from int msgnum value is copied to char[] msgnums, and this msgnums char array is finally passed as the SQL parameter. *If msgnum column from the voicemail table is an Integer field, the msgnum int should be passed to the query, isn’t it!!*
> I saw this similar pattern on functions:
> static int retrieve_file(char *dir, int msgnum), static int message_exists(char *dir, int msgnum), static void delete_file(const char *sdir, int smsg), etc.
> I checked the recent versions to, there is no change in *apps/app_voicemail.c* in this regard.
> After seeing this, I changed the msgnum column type to varchar(80) on the PostgreSQL database, then I can retrieve and hear the voicemail from database. But, then there is no msgnum greater than '10'. After reaching to '10' everytime a new voicemail arrives, the last msgnum '10' is replaced by the new message.
> Am I doing something incorrectly?
> I tried asking the same question on [Asterisk community| https://community.asterisk.org/t/voicemail-odbc-ast-odbc-print-errors-sql-execute-returned-an-error-hy000-error-operator-does-not-exist-integer-character-at/77990/1], but, still now no reply there.



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