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

[Asterisk Team (JIRA)]

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

Asterisk Team commented on ASTERISK-28242:
------------------------------------------

Thanks for creating a report! The issue has entered the triage process. That means the issue will wait in this status until a Bug Marshal has an opportunity to review the issue. Once the issue has been reviewed you will receive comments regarding the next steps towards resolution.

A good first step is for you to review the [Asterisk Issue Guidelines|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines] if you haven't already. The guidelines detail what is expected from an Asterisk issue report.

Then, if you are submitting a patch, please review the [Patch Contribution Process|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process].

Please note that once your issue enters an open state it has been accepted. As Asterisk is an open source project there is no guarantee or timeframe on when your issue will be looked into. If you need expedient resolution you will need to find and pay a suitable developer. Asking for an update on your issue will not yield any progress on it and will not result in a response. All updates are posted to the issue when they occur.

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