Developers of Sympa

[Thomas Berry <address@concealed>]

FYI.  These changes were necessary to support Microsoft SQL Server 2005 sp3; they may not necessarily apply to all Sybase SQL servers.


Berry, Thomas M (173H-Affiliate) wrote:

I have a fix for this issue; the double quotes (") in the datediff and dateadd functions used in SQLSource.pm need to be changed to escaped single quotes (\') .

- 45:         'Sybase' => 'datediff(second, "01/01/1970",%s)',
+ 45:         'Sybase' => 'datediff(second, \'01/01/1970\',%s)',

- 52:         'Sybase' => 'dateadd(second,%s,"01/01/1970")',
+ 52:         'Sybase' => 'dateadd(second,%s,\'01/01/1970\')',

Thomas


On Feb 8, 2010, at 10:10 AM, Thomas Berry wrote:

Looks like there might be a problem with the use of datediff when the 
database is set to Sybase

I just did a fresh install of Sympa 6.0.1; I created a test mailing list 
and tried to add a



Feb  8 09:41:50 listhost wwsympa[30854]: List::get_admin_user() Unable 
to execute SQL statement "SELECT user_admin AS email, comment_admin AS 
gecos, reception_admin AS reception, visibility_admin AS visibility, 
datediff(second, "01/01/1970",date_admin) AS "date", datediff(second, 
"01/01/1970",update_admin) AS update_date, info_admin AS info, 
profile_admin AS profile, subscribed_admin AS subscribed, included_admin 
AS included, include_sources_admin AS id FROM admin_table WHERE 
(user_admin = 'address@concealed' AND list_admin = 
'testlist' AND robot_admin = 'ums-list-dev.jpl.nasa.gov' AND role_admin 
= 'owner')" : Server message number=207 severity=16 state=1 line=1 
server=ALTVIRSQLCLUP11\REMEDYARS text=Invalid column name '01/01/1970'. 
Server message number=207 severity=16 state=1 line=1 
server=ALTVIRSQLCLUP11\REMEDYARS text=Invalid column name '01/01/1970'.



I found the following information for the Sybase SQL function:


datediff(second, datetime1,datetime2)


Sympa's datediff definition is (as found in SQLSource.pm)

datediff(second, "01/01/1970",%s)



This is used by List.pm on the following lines:

4760: my $date_field = sprintf 
$date_format{'read'}{$Conf::Conf{'db_type'}}, 'date_subscriber', 
'date_subscriber';
4761: my $update_field = sprintf 
$date_format{'read'}{$Conf::Conf{'db_type'}}, 'update_subscriber', 
'update_subscriber';


# here's the code [from sub get_admin_user] responsible for the log 
entry above:
4891: my $date_field = sprintf 
$date_format{'read'}{$Conf::Conf{'db_type'}}, 'date_admin', 'date_admin';
4892: my $update_field = sprintf 
$date_format{'read'}{$Conf::Conf{'db_type'}}, 'update_admin', 
'update_admin';


4974: my $date_field = sprintf 
$date_format{'read'}{$Conf::Conf{'db_type'}}, 'date_subscriber', 
'date_subscriber';
4975: my $update_field = sprintf 
$date_format{'read'}{$Conf::Conf{'db_type'}}, 'update_subscriber', 
'update_subscriber';

5321: my $date_field = sprintf 
$date_format{'read'}{$Conf::Conf{'db_type'}}, 'date_admin', 'date_admin';
5322: my $update_field = sprintf 
$date_format{'read'}{$Conf::Conf{'db_type'}}, 'update_admin', 
'update_admin';

5687: my $date_field = sprintf 
$date_format{'read'}{$Conf::Conf{'db_type'}}, 'date_subscriber', 
'date_subscriber';
5688: my $update_field = sprintf 
$date_format{'read'}{$Conf::Conf{'db_type'}}, 'update_subscriber', 
'update_subscriber';



The following seem OK since they use date values:

6017: $value = sprintf $date_format{'write'}{$Conf::Conf{'db_type'}}, 
$value, $value;
6019: $value = sprintf $date_format{'write'}{$Conf::Conf{'db_type'}}, 
$value, $value;
6155: $value = sprintf $date_format{'write'}{$Conf::Conf{'db_type'}}, 
$value, $value;
6157: $value = sprintf $date_format{'write'}{$Conf::Conf{'db_type'}}, 
$value, $value;

6375: my $date_field = sprintf 
$date_format{'write'}{$Conf::Conf{'db_type'}}, $new_user->{'date'}, 
$new_user->{'date'};
6376: my $update_field = sprintf 
$date_format{'write'}{$Conf::Conf{'db_type'}}, 
$new_user->{'update_date'}, $new_user->{'update_date'};

6460: my $date_field = sprintf 
$date_format{'write'}{$Conf::Conf{'db_type'}}, 
$new_admin_user->{'date'}, $new_admin_user->{'date'};
6461: my $update_field = sprintf 
$date_format{'write'}{$Conf::Conf{'db_type'}}, 
$new_admin_user->{'update_date'}, $new_admin_user->{'update_date'};


etc.


Am I misunderstanding how the datediff function is intended to be used?