Developers of Sympa

["Berry, Thomas M (173H-Affiliate)" <address@concealed>]

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?