Problem with Voicemail origtime Field

[wcolling]

We are experiencing a problem with the origtime field in the vmaildetail response. When voicemail data is returned, the fields look correct except for the origtime field which is returned as 1. I checked the database and the field contents appears correct there.

Here is an example:

16,vmaildetail=INBOX!eyJJTkJPWCI6IFt7Im1haWxib3h1c2VyIjogIjQxNTY4NDk4MTMiLCJtc2dudW0iOiAiMCIsImZsYWciOiAiIiwiZHVyYXRpb24iOiAiMTYiLCJtYWNyb2NvbnRleHQiOiAiZXh0LWxvY2FsIiwiZGlyIjogIi92YXIvc3Bvb2wvYXN0ZXJpc2svdm9pY2VtYWlsL2RlZmF1bHQvNDE1Njg0OTgxMy9JTkJPWCIsImNhbGxlcmlkIjogIlwiQ29sbGluZyxXaWxsaWFtXCIgPDk1MTc4MDc3MzY+IiwibWFpbGJveGNvbnRleHQiOiAiZGVmYXVsdCIsIm9yaWd0aW1lIjogIjEiLCJjb250ZXh0IjogIm1hY3JvLXZtIiwiaWQiOiAiNjM1MyJ9XX0= en slot 0

vmaildetail returns the following (I commented out the phone numbers so people do not call me):
{"INBOX": [{"mailboxuser": "415nnnnnnn","msgnum": "0","flag": "","duration": "16","macrocontext": "ext-local","dir": "/var/spool/asterisk/voicemail/default/4156849813/INBOX","callerid": ""Colling,William" <951nnnnnnn>","mailboxcontext": "default","origtime": "1","context": "macro-vm","id": "6353"}]}

But the database shows:

SELECT origtime from voicemail.voicemessages where id = 6353;
'1453737132'

Would you please explain how the fop2 server retrieves the origtime so we can check to see if we have an error on our side.

fop2_server version 2.29.02

[nicolas]

Hi,

I have seen in different setups that field casting in ODBC is "shady". I cannot say for sure, but I have seen many fields being kind of truncated with what appears to be wide characters or utf8 kind of encoding.. something really strange.. Just the other day working on a similar problem, I seend that the id field that is just an integer, was retrieved with this strange chars, ending up truncating the field... like the id was 234234 but the reeturned field was 234^00 (being the last part some kind of wide character or just noise), so it truncated it to 234. Solution in this case was to explicitely cast to char even if its a numeric value and convert the result on the server. Maybe, must maybe, origtime issues you are having is similar/related.

[wcolling]

The origtime field is defined as varchar(40) with a latin1 character set. Do you think this may be a problem and it should be redefined as datetime or timestamp?

[wcolling]

I did some more checking and I don't think this is related to ODBC. Assuming that FOP2 is written in perl, I wrote the following test program that retrieves the row contents with ODBC.

#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI-> connect('dbi:ODBC:voicemail');

my $sql = qq/select id, msgnum, dir, context, macrocontext, origtime, duration from voicemail where id='6353'/;
my $sth = $dbh->prepare($sql) or die "Unable to prepare $sql ".$dbh->errstr;

$sth->execute() or die "Unable to execute query $sql " . $sth->errstr;
my ($id, $msgnum, $dir, $context, $macrocontext, $origtime, $duration);
$sth->bind_col(1, $id);
$sth->bind_col(2, $msgnum);
$sth->bind_col(3, $dir);
$sth->bind_col(4, $context);
$sth->bind_col(5, $macrocontext);
$sth->bind_col(6, $origtime);
$sth->bind_col(7, $duration);

while ($sth->fetch()) {
print ("$id\t,$msgnum\t,$dir\t,$context\t,$macrocontext\t,$origtime\t,$duration\n");
}
$sth->finish();
$dbh->disconnect();

When I ran it, the results were:

6353 ,0 ,/var/spool/asterisk/voicemail/default/4156849813/INBOX ,macro-vm ,ext-local ,1453737132 ,16

which returned the correct values for each field, including the origtime field.

[nicolas]

Problem is due to casting to avoid issues with some systems.. in yours cast for that field is returning 1 char out of the whole bunch. I am changing the query to cast char(50) in the hope that it will cast all digits. There is a new binary server file on your test server you can test it out. I think it works fine now.