I am using ODBC to store voicemail messages.
My asterisk version is asterisk-certified-13.8-cert4.
I’m using PostgreSQL 9.5 as my ODBC store.
In the contrib/realtime/postgresql/postgresql_voicemail.sql there is an SQL for creating voicemail message table, where msgnum is INTEGER. But, when I use the msgnum as Integer I can see there are some errors in asterisk log:
While a call goes to a voicemail, I can see the below error, but the messages are saved with msgnum 0, 1, 2…9, 10, 11, 12, and so on.
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=?]
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 error and skips the message and goes to advance options.
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@maxcore:3] Hangup("PJSIP/shiblicus1_cus1group2_1_3206-00000008", "") in new stack
Then, I changed the msgnum to VARCHAR it works fine.
I can save and retrieve messages without any error in the log. But, problems occur after 11th message. And, I think asterisk increase the msgnum with a query from the database, sorted by msgnum. And since I changed the msgnum to VARCHAR, ‘9’ is greater than ‘10’ as String.
So, the max msgnum is always 9, and msgnum ‘10’ is always gets replaced by a new message, and there is no ‘11’ as msgnum.
So, I get baffled here. Searching for solutions. What is wrong here! How to solve this issue?
Some additional findings:
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.
Am I the only one who faced this issue with PostgreSQL, or am I doing something incorrectly? Or, is there any configuration on Postgres I missed configuring?
Any help or clue will be very much appreciated.
Thanks.