SQLite3 COM Parameters object
 
Holds the session SQLite3 COM parameters.

Accessible as 
object.Parameters.<prop_ormethod>
where object is SQLite3 COM object.

The parameters object is a typical collection plus a few members that manage some specific features. All the elements added to this collection are accessible in any query executed on the SQLite3 COM object to which this Parameters object belongs. This includes views and triggers as well. The parameters are accessed from the SQL statements using the Parameter function. 

You can add any kind of element to the collection - string, numeric value or object. If given element is an object it can be accessed from the SQL statements also by using the CallObject function. This function enables you to call arbitrary property or a method of the element and even pass arguments to it if the method requires such. 

The main purpose of the Parameters object is to enable the developers to set general database parameters for the current session. Unlike the query parameters in VExecute, CExecute and AExecute the session parameters are available anywhere within the database. If used in a view for example they remain transparent for that part of the application that executes queries that use those views.

Methods and properties reference

Wherever object or db is used in the sample lines of code below it is assumed to be a previously created SQLite3 COM object.

Name Syntax Description
Add object.Parameters.Add name, param Adds an element to the Parameters collection. The name is the name under which the parameter can be accessed later and from the database using the Parameter function. For example:
db.Add "MyParam","John"
will add parameter named MyParam with value John. After that it can be used in queries, views and triggers like this: 
SELECT * FROM People WHERE Name=Parameter("MyParam")
In this example the query will return all the John-s. The parameter can be changed at later time:
db("MyParam") = "Michael"
and the above query will return the Michael-s instead of Johns. If this query is used to define a view the view will change as the parameter's value is changed. 
Clear object.Parameters.Clear Removes all the elements from the collection. Usually needed when you want to re-initialize all the parameters.
Remove object.Parameters.Remove name_or_index Removes the element specified by name or index (name_or_index) from the Parameters collection. Note that if the parameter is used in the database the Parameter functions will begin issuing a db errors after the parameter is removed.
Item v = object.Parameters.Item(name_or_index)
v = object.Parameters(name_or_index)
Set o = object.Parameters(name_or_index)
object.Parameters(name_or_index) = v
Set object.Parameters(name_or_index) = o
Default property. Returns or changes the parameter specified by the name_or_index argument by name or 1-based index. When assigning a new value for a parameter:
db.Parameters("MyParam") = "something"
the referred parameter is created if it does not exist. Thus the parameters can be defined without using the Add method if preferred.
Count c = object.Parameters.Count Returns the count of the elements in the collection. The collection is 1-based so if you want to cycle through the parameters you can use:
For I = 1 To db.Parameters.Count
  ' Do something with db.Parameters(I)
Next
ParamExists If object.Parameters.ParamExists(name) Then
...
Returns true if the parameter named name exists in the collection.
Enumerator For Each p In object.Parameters
 ...
Next
Enables you to enumerate the elements.
Reference date/time standard parameter
ReferenceDate object.Parameters.ReferenceDate = v
v = object.ReferenceDate 
Sets/gets the value of the reference date parameter (see below).
UseReferenceDate object.Parameters.UseReferenceDate = boolVal
b = object.Parameters.UseReferenceDate 
Boolean. If set to true the reference date from the ReferenceDate property is used. If false the current date/time is used.
The ReferenceDate parameter is accessible in the SQL queries through the special functions: RefDate() and RefDateSys(). They put the date/time value in the queries from the ReferenceDate property or the current date/time depending on the value of the UseReferenceDate property.
This feature enables you to create views or triggers that depend on a reference date/time (for example return the data up to a given date). The reference date can be changed for the session and enabled/disabled. Disabling it (UseReferenceDate = false) causes the queries to work with the current date/time.

This feature is implemented as separate special parameter because it is often used in databases that provide time-machine functionality or other functionality that is usually provided for the current moment, but sometimes needs to be queried for an earlier date.

 

Remarks:

The Parameters enable the developer to create reusable view and triggers that depend on parameters which can be different each time the database is used. Without them the views for instance would always return the same result (unless the data they query has changed) and if you want their function to depend on an application parameter the only choice will be to abandon the view and include the SQL as a sub-query in each statement that needs it. Obviously this leads to huge and cumbersome SQL statements passed directly to the Execute functions. With parameters you can design permanent views (and triggers) which use certain session parameter(s) and refer to them as tables in the queries executed with the Execute methods, thus making the use of session wide parameters transparent for the major part of the application code.

Applies to: SQLite3 COM object

See also: SQL functions

Supported on:

Windows 95/NT and later
Windows CE 3.0 and later
Pocket PC/Windows Mobile 2003 and later
Windows CE.NET 4 and later

 

newObjects Copyright 2001-2006 newObjects [ ]