Change request to daSQL.pas for ODBC

rbuser

Along with Grant Dunoon, I have written a DADE plug-in for ODBCExpress (so
far for RB versions up to v6.03 but this will be updated in the next month
hopefully for version 7).

I have recently come across a situation where dates with Microsoft SQL
Server were not working correctly and I noticed that the date and time
formats for it were wrong in this DADE plug-in. To this end, I have just
made changes to it so that the date/time formats correspond with the
documentation provided by Microsoft which specifies:

Dates as: {d 'yyyy-mm-dd'}
Times as: {t -hh:mm:ss'}
DateTimes as: {ts 'yyyy-mm-dd hh:mm:ss'}

However, there is code in the daSQL.pas 'ResolveCriteria' function that puts
single quotes around any dates except for Oracle:

if IsMSAccess then
Result := '#' + Result + '#'
else if not (IsOracle) then
Result := '''' + Result + '''';*)

Also, it is assumed that the format will always be a date followed by a
time, e.g. 30/05/2003 09:00:00, whereas from the ODBC this would read {ts
'2003-05-30 09:00:00'}. Note that there are no single quotes around the
outside of the string and that the date and time are together in one string
enclosed in braces.

I have made my own changes to daSQL.pas that prevent the single quotes on
SQL Server if the '{' character is present, and also shows the date/time
format correctly using the same criteria (the code is shown below). As the
changes I have made have been designed to have no impact on other data
access plug-ins, I was wondering whether these changes could be incorporated
as standard into the next version of RB (in daSQL.pas)? This way, the
ODBCExpress plug-in will work for everyone without having to make the
required source code changes as well.

I tried my hardest to minimise any changes to the RB source so that the
plug-in will work by itself, but because of the documented expected
date/time format for the SQL Server driver, there is no alternative. If I
can be of any further help in this matter, don't hesitate to contact me
(just remove the .nospam. from my email address).

Many thanks,
Jason.

From daSQL.pas:

if (aCriteria.Field.DataType in [dtDate, dtDateTime]) then
begin

try
ppStrToDateTime(aValue);
except on EConvertError do
begin
Result := '';

Exit;
end;
end;

lDateTime := ppStrToDateTime(aValue);

lcDateSeparator := DateSeparator;

lsDateFormat :=
Session.GetSearchCriteriaDateFormat(DatabaseType, DatabaseName);
lsTimeFormat :=
Session.GetSearchCriteriaTimeFormat(DatabaseType, DatabaseName);

if (IsOracle or IsSybaseASA or IsSybaseASE or IsMSSQLServer)
then
lbContainsTime := (Pos(#32, Result) <> 0)
else
lbContainsTime := (Pos(#32, Result) <> 0) or
(aCriteria.Field.DataType = dtDateTime);

try
DateSeparator := '/';

{OLD CODE}
{if lbContainsTime then
Result := FormatDateTime(lsDateFormat + ' ' + lsTimeFormat,
lDateTime)
else
Result := FormatDateTime(lsDateFormat, lDateTime);}

{JS new code: test on SQL Server for '{' char}
if lbContainsTime then
begin
if (IsMSSQLServer)
and (Pos('{', lsTimeFormat) > 0) then
begin
Result := FormatDateTime(lsTimeFormat, lDateTime)
end
else
Result := FormatDateTime(lsDateFormat + ' ' +
lsTimeFormat, lDateTime)
end
else
Result := FormatDateTime(lsDateFormat, lDateTime);

{add special function call to convert the Oracle date based on
the format of the criteria value}
if (IsOracle) then
begin
if (lbContainsTime) then
Result := 'TO_DATE(' + '''' + Result + '''' + ',' + ''''
+ lsDateFormat + ' HH24:MI:SS' + '''' + ')'
else
Result := 'TO_DATE(' + '''' + Result + '''' + ',' + ''''
+ lsDateFormat + '''' + ')';
end;

finally
DateSeparator := lcDateSeparator;
end; {try, finally}


(*OLD CODE
if IsMSAccess then
Result := '#' + Result + '#'
else if not (IsOracle) then
Result := '''' + Result + '''';*)

{JS new code: extra test for SQL Server containing '{' char}
if IsMSAccess then
Result := '#' + Result + '#'
else if IsMSSQLServer then
begin
If (Pos('{', Result) = 0) then
Result := '''' + Result + '''';
end
else if not (IsOracle) then
Result := '''' + Result + '''';
end

digitalmetaphors

Feel free to send the changes to support@digital-metaphors.com and we can
look into it. Have you tested your changes using the ADO and BDE DADE
plugins to make sure they also work with your changes?


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

I haven't tested these changes yet against other versions of the DADE, I
will before I submit them to you. However, I was careful in my code to
ensure that my changes only take place if the '{' character is present (and
then only for MS SQL Server databases), which for all the other data access
types won't be applicable.

Thanks,
Jason.

digitalmetaphors

Sounds good:)

--
Cheers,

Jim Bennett
Digital Metaphors