SQLite3 COM SQLite3 COM SQL functions

In addition to the standard SQLite SQL functions that you can use in any expression SQLite3 COM implements a number of functions such as OLE date/time and Session parameters.


Session parameter functions 
OLE date/time functions

Session parameter functions

The session parameter functions work together with the Parameters collection maintained by the SQLite3 COM object. No matter if the functions are called explicitly or implicitly they return the value corresponding to the parameter in the same collection. By explicit use we mean usage in a query passed to an Execute method, by implicit usage we mean a call to these functions caused by invoking a view or a trigger in result of execution of a query that refers to the view or triggers the trigger.

The functions:

Returns the value of a parameter in the Parameters collection. <param_name> is the name of the parameter in the Parameters collection as set when the Add method or object.Parameters(<param_name>) = soemthing has been used. If an object is set under that name in the Parameters collection its default property is returned. If the object has no such property database error will occur. If the parameter does not exist "Parameter not found" database error will occur.

RefDate() and RefDateSys()
Return the current date/time or the reference date/time as specified in the Parameters object (see the ReferenceDate and UseReferenceDate properties). The difference between the two function is as follows: RefDate function returns the current local date/time when Parameters.UseReferenceDate = false, while RefDateSys returns the UTC date/time. Both functions return the reference date "as is" if Parameters.UseReferenceDate = true.

CallObject('param_name' [,'method_or_prop_name' [, arg1 [, arg2 [ ...] ]]]
If the parameter in the Parameters collection specified by the name param_name is an object this function calls a method on it or gets the value of objects property. All the arguments except the param_name are optional. If all are omitted the default property is returned (if exists). 
If a method_or_prop_name is specified the specified property or method is called on the object.
All the remaining arguments are passed to the called method or property. Their number and type must match the arguments of the called method or property.
If an error occurs in the called method/property "Object error" database error will occur.
If the param_name points to a parameter that is not an object "The specified parameter is NULL or not an object" database error is issued.
The called method or property must return result which can be converted to one of the database types INTEGER, REAL, TEXT, BLOB or NULL. I.e. if the result is an object (without default property returning scalar value) an error will occur: "Cannot convert/obtain the returned result in useful form."


OLE date/time functions

SQLite3 COM defines a set of Date/Time SQL functions which can be used in SQL statements to deal with date and time values in a manner compatible and convenient for COM programming. They allow you work with date/time values even without support from the outside - for instance making conversion every time you read/write values from to the database. You can move most of the date/time related work to the SQL instead of doing it in the application code (VBScript, JScript, VB etc.). This is often more productive and especially useful if embedding date/time calculations in the SQL will offer more simplicity and better performance. For instance if you need to filter some records based on some date/time criteria that involves calculation of intervals for example, instead of feeding the SQL with pre-calculated values it is better to do this in the SQL statement in-place and thus benefit of the ability to calculate them dynamically in the SQL over the current data.

What is the OLE DATE type? In short it is double precision floating point value that counts the time from 30 December 1899 00:00:00. The positive values mean date after this date, negative values mean date before that date. Thus 0.0 will equal to 30 December 1899 00:00:00. Therefore when OLE DATE is used to specify time only (without a date) it will convert to 30 December 1899 plus some hours, minutes seconds if it is converted to full date in a mistake. The OLE DATE values act correctly in any expression because they are just real numbers, they can be summed, subtracted and otherwise processed. The fact that the time and the date are kept in a single value allows complex calculations that involve date and time parts (and not only one of them) to be performed easily. In contrast the Julian date supported by the most databases (SQLite contains other set of functions for this) keeps the date and the time in separate values and makes the expressions more difficult to write. The additional benefit of using OLE DATE is that the values that are result of expressions/statements can be directly passed to any script or a COM routine that requires date/time value without any conversion.

The functions:

ParseOleDate - Parses a date/time string in standard format and returns the double precision value that represents it. The format is:
YYYY-MM-DD hh:mm:ss. Example:

SELECT * FROM T WHERE Created > ParseOleDate("2001-01-05");
will return the records with field "Created" containing date bigger than or equal to January, 05, 2001.
SELECT * FROM T WHERE Created > ParseOleDate("2001-01-05 13:30");
will return the records with field "Created" containing date/time bigger than or equal to January, 05, 2001 01:30 pm

You can pass date only or time only to ParseOleDate function. For instance all these:
ParseOleDate("2004-06-17 05:15:00")
will be ok. The seconds part of the time specification are optional.

Note that we are using this function in the samples below to make them more readable. In the real world you will pass to them arguments that are results from the query or an expression. 

OleDateTime - Composes a date/time string in the standard format from a date value. For instance PleDateTime(0.0) will return "1899-12-30 00:00:00". This function is needed when the date values are to be converted to human readable format after some calculations.

OleDate and OleTime - Act as above but return only the date part of the string representation (OleDate) or only the time part (OleTime) of the date/time value passed as argument. For example:
SELECT OleDate(ParseOleDate("2001-12-22 14:30:10"));
 will return "2001-12-22"
SELECT OleTime(ParseOleDate("2001-12-22 14:30:10"));
 will return "14:30:10"

OleDay, OleMonth, OleYear, OleHour, OleMinute, OleSecond and OleWeekDay - all return numeric value that represents the corresponding part of the date value passed to them as argument. For example:
SELECT OleDay(ParseOleDate("2001-12-22 14:30:10"));
 will return 22 (22 - day of the month)
SELECT OleMonth(ParseOleDate("2001-12-22 14:30:10"));
 will return 12 (12 month - December)
SELECT OleYear(ParseOleDate("2001-12-22 14:30:10"));
 will return 2001 (the year specified in the date value)
SELECT OleHour(ParseOleDate("2001-12-22 14:30:10"));
 will return 14 (2 p.m.)
SELECT OleMinute(ParseOleDate("2001-12-22 14:30:10"));
 will return 30 (the minutes of the time contained in the value)
SELECT OleSeconds(ParseOleDate("2001-12-22 14:30:10"));
 will return 10 (the seconds of the time contained in the value)
SELECT OleWeekDay(ParseOleDate("2001-12-22 14:30:10"));
 will return 7 (Saturday)

For example if you want to query for records created on Mondays, assuming the Created field contains their creation time you can use query like this:
SELECT * FROM T WHERE OleWeekDay(Created) = 7;

The week days are numbered as follows: 1 - Sunday, 2 - Monday ... 7 - Saturday

OleDateAdd - This function provides way to calculate new date over existing one adding an interval to it. The full specification of the function is:
  interval - is a character which can be: "Y" - years, "M" - months, "D" - days, "h" - hours, "m" - minutes, "s" - seconds
  count - is a number specifying how many interval-s to add. Can be negative if you want to subtract from the date.
  date - is the date value to which the interval will be added.
For example this can be useful to fetch the records created in past month:
SELECT * FROM T WHERE Created > OleDateAdd("M",-1,ParseOleDate("2004-12-14")) AND Created < ParseOleDate("2004-12-14");
Assuming that the string in the ParseOleDate is passed from outside.

OleDateDiff - This function calculates the difference between two date/time values in the interval-s specified. The full specification is:
  interval - One character specifying the interval in which the difference will be calculated. Can be: "Y" - years, "M" - months, "D" - days, "h" - hours, "m" - minutes, "s" - seconds
  date1 - The first date
  date2 - The second date
If the date2 is bigger than date1 the result is positive (or 0) and negative (or 0) otherwise.
For example if you want to fetch the records created this month you can use query like this one:
SELECT * FROM T WHERE OleDateDiff("M",Created,ParseOleDate("2004-12-14")) = 0;

OleLocalTime(), OleSysTime() - return the current local time (OleLocalTime) or the current UTC time (OleSysTime).


A small sample ASP code. These few lines of code Execute a query that retrieves the records created during the previous year from a table "T", the field "Created" is assumed to contain the record creation date.

Set db = Server.CreateObject("newObjects.sqlite.dbutf8")
Set su = Server.CreateObject("newObjects.utilctls.StringUtilities")
dt = Now
Set r = db.Execute(su.Sprintf("SELECT * FROM T WHERE " & _
          "OleDateDiff('Y',Created,ParseOleDate('%hT')) = 1",dt))
For I = 1 To r.Count
    <% For J = 1 To r(I).Count %>
      <TD><%= r(I)(J) %></TD>
    <% Next %>
newObjects Copyright 2001-2006 newObjects [ ]