| One thing to consider when writing a CW program
accessing an MS-SQL database is the use of the TODAY() function to get
the current date. This will get the date from the local PC, rather than
from the Server where the SQL Data is maintained. This can be
problematic where a user had messed with the PC's date. What I do is to
use a global variable ( ServerToday - a CW LONG with the date in
Clarion Standard Date format ) which I update at certain critical
points within my application. Then, in lieu of the TODAY() call, I use
my ServerToday variable.
|
| So, how do I get the value from the server?
Simple. I coded a small procedure that I call from certain critical
points. The procedure uses a "dummy table" to update the ServerToday
variable. I do not expect anyone to be running a process around
Midnight Server time, but just in case someone does not close their
program one day and starts using it again the next, we need to get the
ServerToday updated before any critical processing takes place. |
| I named my proc GetServerDate and it is
a small source proc. The table it uses is named TempF1 with a field F1
defined a a CSTRING(50) This proc simply sets my variable using the
code shown below... |
| |
GetServerDate
PROCEDURE
iOpenedit BYTE
CODE
! sets ServerToday with the SQL Server date in Clarion format
if not STATUS(TempF1)
open(TempF1)
iOpenedit = TRUE
end
clear(TempF1)
tempF1{prop:sql} = 'select datediff( dd,
<39>18001228<39> ,GetDate() ) '
next(tempF1)
if not errorcode()
ServerToday = tempF1.F1
end
if iOpenedit
close(TempF1)
end
RETURN |
Some explanation: The line with the
{prop:sql} calls the MS-SQL function GetDate( ) Then we have
the SQL engine massage that result using the MS-SQL function DateDiff
to get the date as the number of days since 1800-12-28 The
result is an integer = CW LONG and will equivalent to the CW TODAY()
result that would be returned by a program running on the server.
|
Perhaps you have an app that connects to your database across different time
zones. In such cases, you will probably not want the time at the server, but the
UTC/GMT date/time so you can make allowances for the program's running location,
and the server's location. Luckily, since SQL Server2000 we have an
inbuilt SQL function that helps us here. This is the GetUTCdate() function. So
instead of just the simple GetServerdate shown above, we can create a separate
func to get the Clarion standard LONG date for the UTC time. See source code
below...
|
GetUTCDate
PROCEDURE
iOpenedit BYTE
RetVal LONG
CODE
! Returns the UTC date as a Clarion LONG
if not STATUS(TempF1)
open(TempF1)
iOpenedit = TRUE
end
clear(TempF1)
RetVal = 0
tempF1{prop:sql} = 'select datediff( dd,
<39>18001228<39> ,GetUTCDate() ) '
next(tempF1)
if not errorcode()
RetVal = tempF1.F1
end
if iOpenedit
close(TempF1)
end
RETURN RetVal |
John Griffiths. |
