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

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?

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.