[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:comment-tabpanel&focusedCommentId=245971#comment-245971 ] 

Kevin Harwell commented on ASTERISK-28242:
------------------------------------------

I noticed you have "asterisk-certified-13.8-cert4" as the version of Asterisk. Any reason you are using the certified version vs the latest release of Asterisk? That's not the latest version of Asterisk or even certified Asterisk. There have been many issues fixed between the version you are using and the current version of your branch. Please test with the latest version in your Asterisk branch and report whether the issue persists.

Please see the Asterisk Versions [1] wiki page for info on which versions of Asterisk are supported.

[1] https://wiki.asterisk.org/wiki/display/AST/Asterisk+Versions

> 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
>
> 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)