Back to article list
Search Articles
Add Comment
Printer friendly
SQL Related Articles: Better SQL Part 4
2002-07-23 -- Dan Pressnell
Somebody made a really stupid decision when designing SQL when he invented
the Date-Time type. Two values in one field? Hey, let's have a
firstname-lastname type, and a city-state-country-postalcode type! Why stop
with one stupid idea?
But it's done, and we have to live with it.
In the demo app at
http://www.icetips.com/downloadfile.php?FileID=59 you
can see one simple way of handling a date-time column for a browse queue.
In the UpdatePlayers procedure, there is a browse for "At bats". The first
column in the browse is a date filed. Because the queue will be filled by
the QueryFromQueue procedure, some special thought has to be given to the
queue structure.
To handle a date, declare your queue like this:
atbatqueue queue
Date string(10), name('atbats.date')
OppTeam like(tea:teamname), name('teams.teamname')
Result string(10), name('atbats.result')
AtBatID like(atb:atbatid), name('atbats.atbatid')
end
The date field in the queue is declared as string(10) simply because that's
what you are going to get for the value, in the format "yyyy-mm-dd". There
can also be a time portion to that, but I'll get to that below.
After you fill the queue from the query, do something like this:
loop i=1 to records(atbatqueue)
get(atbatqueue, i)
atbatqueue.Date = deformat(atbatqueue, @d010-)
atbatqueue.Result = AtBatResultToString(atbatqueue.Result)
put(atbatqueue)
end
That converts the SQL date format to a standard date format that Clarion
handles, and you can use any picture in the browse for that column that you
want.
Don't worry too much about handling date-time fields for updates. The
standard Clarion update procedure works pretty well for doing all the
conversions.
If you need the time portion as well, it's another one line of code.
Suppose this queue:
MyQueue queue
MyDate string(20), name('mytable.date')
MyTime long, name('''''')
end
Note the name attribute for the MyTime field. Your query, when generated by
the QueryFromQueue procedure, will include '' in the SELECT portion. How
much bandwidth does a blank string take? I don't know, but I suspect it's
not much. When you have a field in the queue which you will set later
yourself, you can set the name atrribute like that. Or you could set it to
a default value. I don't think there is much difference one way or the
other, since you'll be processing it later anyway.
Also notice that the MyDate field is now string(20). That's so it can hold
the full date-time combination from the result set.
Now to get the date and time fields converted in the queue. Be sure to
convert the time field first, because when you convert the date, you'll wipe
out the time portion. Do it like this:
loop i=1 to records(MyQueue)
get(MyQueue, i)
MyQueue.MyTime = deformat(MyQueue.MyDate[12 : 19], @t4)
MyQueue.MyDate = deformat(MyQueue.MyDate[1 : 10], @d010-)
put(MyQueue)
end
Now look back at the atbatsqueue declaration. It has this field:
Result string(10), name('atbats.result')
In the atbats table, the result column is a numeric. In practice, it should
contain only the values 0,1,2,3, or 4. So why not declare the Result field
as a byte in the queue? Because we want to hold something descriptive,
something like "Out", "Single", "Double", "Triple", or "Home Run."
Immediately after the query, the Result field holds the number that is in
the table row. While converting the date column for the queue, the code in
the SendAtBatsQuery routine also converts that number to the string
representation so it can display in the browse.
Now back to date-time fields. Don't rely on something like this:
SELECT CUSTOMER WHERE LASTBILLED >= '2000-01-01' and
LASTBILLED <= '2000-01-31'
The first part of the condition is okay. But what if there is a time
portion to the LASTBILLED column? You will miss the records that have a
value of '2000-01-31 14:00:00'.
The correct way to do that is to look for values that are less than the
target date plus one date. For example, if you want the dates up through
2000-01-31, then it would be
LASTBILLED < '2000-02-01'
Comments...
23-July-2002 -- Michael Gould
That depends on the SQL backend. MS-SQL has it's roots in Sybase ASE 4.2
and
they only have a single timestamp type of column. We use Sybase's ASA 8.0.1
and
it has column types of timestamp, date and time. We use all three formats
depending
on what the need for that is. Works well within Clarion also. If your
using just the
date or time, there is nothing special that needs to be done, except for
formatting the
date when doing a prop:sql but other than that, it's a breeze.
Comments...
23-July-2002 -- Gus M. Creces
Another way to handle a compound date/time field when querying only the date
is as follows:
ORD.ORDERDATE BETWEEN {ts '1994-09-30 00:00:00'} AND {|
ts '1994-09-30 23:59:59'} )
The new SQL extensions to The Clarion Handy Tools handle date stamps in this
way.
The classes detect when the back end is using a date stamp data type.
If you're doing a query involving only the date part of this, it intercepts
your query statement (date = 9/30/1994) and expands it to include the
highest and lowest possible times in a range.
You don't really have to think about this since the work is done for you in
the background.
We've added some easy to use functions to our browse class extensions that
let you keep your querying statements in pure Clarion if you want to:
OBJ.AppendSQLFilter('ORD:ORDERDATE = ' & YourDateValue)
OBJ.ReplaceSQLFilter('ORD:ORDERSATE = ' & YourDateValue)
The back end receives
ORD.ORDERDATE BETWEEN {ts '1994-09-30 00:00:00'} AND |
{ts '1994-09-30 23:59:59'} )
The classes are aware that it is possible to change the date formats used by
various back end data bases and allow you to indicate by template
configuration or at run time how the back end is formatting dates. So the
SQL output string is adjusted accordingly.
Today is September 8, 2010, 4:44 am
This article has been viewed 9170 times.
Google search
has resulted in 356 hits on this article since January 25, 2004.
