Hi,
Even if your fix is necessary in the case of SQL server only, I don't
see how it could be harmfull to other backends, so I applied it to the
stable branch. Here is the revision:
http://sourcesup.cru.fr/cgi/viewvc.cgi?view=rev&root=sympa&revision=6363
Thanks for submitting this fix!
Regards,
David
Le 08/02/2010 22:41, Thomas Berry a écrit :
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?
--
David Verdin
Comité réseau des universités
|
