Posted by: Betul Ozmen
Hi,
I'm trying to use ODBC and OleDb connections in my project, determining
which one to use by observing a parameter in an "ini" file. Sql statements
have been reviewed to eliminate odbc-only implementations such as
sub-queries and so on. Meanwhile, it is MS Sql Server 2000 and TD 2.0 where
I'm running my tests.
Here is an occasion where I read some information from the database :
Set sSql = "Select StudentID, StudentName
into :xID, :xName
From Students
where Registration_Date BETWEEN :xDate1 AND :xDate2"
which prepares ands executes succesfully (both odbc and oledb supports this
type of parameter using)
1 )Set sSql = "SELECT X.DepartmentID, X.StudentNumber, D.DepartmentName
INTO :xID, :xCount, :xName
FROM
( Select DepartmentID, count(StudentID) As
StudentNumber
From Students
where Registration_Date BETWEEN :xDate1 AND
:xDate2
Group By DepartmentID
) X, Departments D
WHERE X.DepartmentID = D.DepartmentID
ORDER BY X.DeparmentID"
is a succesfull statement that executes on odbc, though it should be
implemented as below to be executed succesfully on oledb :
2) Set sSql = "SELECT X.DepartmentID, X.StudentNumber, D.DepartmentName
INTO :xID, :xCount, :xName
FROM
( Select DepartmentID, count(StudentID) As StudentNumber
From Students
where Registration_Date BETWEEN \'" || xDate1 || "\'
AND \'" || xDate2 || "\'
Group By DepartmentID
) X, Departments D
WHERE X.DepartmentID = D.DepartmentID
ORDER BY X.DeparmentID"
In other words, all parameters used in temporary tables like the "X" table
above, should be converted to string first, and shouldn't be used directly.
It is all right for number and string parameters, but datetime parameters
rise a serious problem which is in fact directly related to datetime format.
Sometimes I see that if I format xDate1 as :
Set dateformat = "dd/MM/yyyy"
Call SalFmtFormatDateTime( xDate1, dateformat )
and run the sql statement(2) it works, and on some clients if it datetime
format is "MM/dd/yyyy", it works.
It is possible to put a parameter into an "ini" file and choose the date
format according to this parameter, but the question is this : How will I
decide which format to use? What influences Sql Server about which format to
handle? A simple sql statement can easily handle direct parameter using (
such as ':xDate1' ). So where does it read the dateformat from ? From Sql
Server registration, or anywhere else?
I hope it is clear what I mean.
And sorry for such a long long explanation.
Any help will be so valuable.
Regards
Betul