Login
Templates, Tools and Utilities
for Clarion Developers
Templates, Tools and Utilities
|
||
|
|
|
Icetips Article
Back to search
Printer friendly
Add Comment
Direct link
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) endThat 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) endNow 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 April 25, 2024, 10:27 am This article has been viewed 35120 times. Google search has resulted in 366 hits on this article since January 25, 2004. Back to search Printer friendly Add Comment
|
|