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

[Tahmim Ahmed Shibli (JIRA)]

Tahmim Ahmed Shibli created ASTERISK-28242:
----------------------------------------------

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