Executes one or more SQL statements. This are the most used methods of the SQLite3 COM. Through it all the database operations are performed. See the Remarks section below for more details over the different applications of the Execute method.

Syntax:

Set result = object.Execute(query [, firstrow [, rowcount]])
Set result = object.VExecute(query , firstrow , rowcount [, val1 [, val2 [, ...]]])
Set result = object.AExecute(query , array, [firstrow [, rowcount ]])
Set result = object.CExecute(query , collection, [ firstrow [, rowcount ]])

Parameters:

query - String containing one or more SQL queries to be executed. The separate queries (if more than one is passed) must be separated with ";". For VExecute, AExecute and CExecute the query may contain one or more parameters in one of the following forms:

?NNN		A question mark followed by a number NNN holds a spot for the NNN-th parameter. NNN must be between 1 and 999. Note that the numbers are interpreted as names in SQLite3 COM.
?		A question mark that is not followed by a number holds a spot for the next unused parameter.
:AAAA or $AAAA		A colon or a dollar-sign followed by an identifier name holds a spot for a named parameter with the name AAAA. Named parameters are also numbered. The number assigned is the next unused number. To avoid confusion, it is best to avoid mixing named and numbered parameters. Each name is assigned an unique index, thus you can repeat the same parameter more than once in the query and pass only one value through the method. The name of the argument does not include the $ or : character (for CExecute)

VExecute can be called with any number of arguments above 3. The 4-the argument of the function is the 1-st query parameter, the 5-th is the 2-nd and so on. Any value can be passed as argument. It will be converted to the closest suitable database type and substituted in the query.

AExecute is like VExecute, but instead of variable number of arguments it expects an array as second argument and uses its 0 element as 1-st query parameter, 1-st element as 2-nd query parameter and so on.

CExecute expects as 2-nd argument a collection. For example you can pass a VarDictionary collection and ADO Fields collection or any other collection that meets the requirements (see below). As the collection's elements have names this is the only method that makes use of the named arguments by their name. It is strongly recommended to use only named parameters with CExecute, but if you do not have a choice see CollectionsBaseIndex property for instructions on how to configure its behavior for different kinds of collections.

The named arguments are useful not only with CExecute. Even when used with the other two methods that substitute parameters by index the named parameters enable you to put one parameter more than once in the query.

When the number of the supplied arguments (VExecute) or the number of the elements of the array (AExecute) and the collection (CExecute) does not match the number of the parameters in the query NULL is used for the parameters that have no matching values. If the arguments (VExecute)/elements (AExecute, CExecute) are more than the parameters actually used in the query some of them remain unused and no error occurs. This behavior enables you to execute several queries with a single call to a given method and pass generalized list of parameters. 

The parameter substitution is done separately for each SQL statement passed to the method. I.e. if the query parameter contains more than one SQL statement the substitution (and respectively the indices of the parameters) is made the same way as if you have called the method several times with each query separately. 

firstrow - If the executed query(ies) return results this tells the method from which row of the result will be fetched. Default is 1 which is the result will be composed beginning with the first result row.

rowcount - If the firstrow is set this specifies how many rows of the actual resulting data will be fetched in total. If the firstrow is 1 and rowcount is 0 (default) all the resulting data is fetched. A combined usage of the both optional parameters allows very convenient usage in scenarios where the results must be shown in pages containing limited number of entries.

array - AExecute only. An array of values for the parameters used in the queries. The elements are substituted as follows - 0 element goes into the 1-st parameter, 1-st element to the 2-nd parameter and so on. If the elements are not enough for all the parameters NULL-s are used for the remaining portion.

collection - CExecute only. Almost any collection object can be used as parameter source. The named parameters from the query are searched in the collection by name and the unnamed parameters are searched by index. This makes this the most powerful parameterization technique and also provides an easy way to pass values for a query parameters from external objects. For instance an application that uses both ADO and SQLite3 COM can pass directly an ADO recordset row (ADO Fields collection). 

What kind of collections can be used as argument? The collection object must have a default indexed property which should support both extracting elements by name and by index. And this is the only requirement. Some collections use 1-based indexing (VarDictionary, ASP Request collections), others use 0-based indices (ADO recrodset). To overcome this problem SQLite3 COM supports the property CollectionsBaseIndex where you can specify the collection's base index. By default 1-based indexing is assumed.

Return value: result is a collection of collections implemented by VarDictionary objects. Thus the result itself is a VarDictionary object which contains one item for each returned result row which in turn is again VarDictionary object. 

For example assume we have 3 records in a table named T and db is SQLite COM object created and initialized earlier, then:
Set r = db.Execute("SELECT * FROM T")
Will return collection r which contains 3 items which are collections too. Each of them will represent one result row. The following sample code will iterate throughout the entire result:

' Cycle through all the rows
For I = 1 To r.Count
  ' For each row cycle through all the fields.
  For J = 1 To r(I).Count
    Response.Write "Field(" & r(I).Key(J) & "=" & r(I)(J) & ";"
  Next
  ' Place a line between the rows to make them more readable.
  Response.Write "<HR>"
Next 

Se more details in the Remarks section and in the Execute results page.

Examples:

The fields in each result row can be addressed by index or by name as preferred. For example if the table T has the following structure and data in it:

ID	Name	Age
1	John	12
2	Smith	16
4	Anna	11

We can use the following code to display it:

Set db = Server.CreateObject("newObjects.sqlite.dbutf8")
If db.Open(Server.MapPath("/db/mydb.db")) Then
  Set r = db.Execute("SELECT * FROM T")
  %>
  <TABLE>
      <TR>
        <TH>Name</TH>
        <TH>Age</TH>
      </TR>
    <%
    For Row = 1 To r.Count
      %>
      <TR>
        <TD><%= r(Row)("Name") %></TD>
        <TD><%= r(Row)("Age") %></TD>
      </TR>
      <%
    Next
    %>
  </TABLE>
  <%
Else
  %>
  <H4>Cannot open the database</H4>
  <% 
End If

Suppose we have one more linked table that contains the e-mail addresses for each of these persons:

PersonID	EMail
1	john@yahoo.com
3	anna@hotmail.com
1	john@hotmail.com
2	smith@yahoo.com
3	ann@mail.com

If we want to generate a list where the number of the e-mail addresses each person owns is listed we can use a query like this one:

SELECT T.Name AS Name, Count(EMail) AS Emails FROM T LEFT OUTER JOIN E ON T.ID=E.PersonID GROUP BY T.ID;

Where it is convenient to name the result fields explicitly using AS SQL keyword. As the names of the fields in the result collections are fetched from the result they will have the names we set in the query and the above sample code can be changed this way to produce the list:

Set db = Server.CreateObject("newObjects.sqlite.dbutf8")
If db.Open(Server.MapPath("/db/mydb.db")) Then
  Set r = db.Execute("SELECT T.Name AS Name, Count(EMail) AS Emails " & _
                     "FROM T LEFT OUTER JOIN E ON T.ID=E.PersonID GROUP BY T.ID;")
  %>
  <TABLE>
      <TR>
        <TH>Name</TH>
        <TH>Number of e-mail addresses</TH>
      </TR>
    <%
    For Row = 1 To r.Count
      %>
      <TR>
        <TD><%= r(Row)("Name") %></TD>
        <TD><%= r(Row)("Emails") %></TD>
      </TR>
      <%
    Next
    %>
  </TABLE>
  <%
Else
  %>
  <H4>Cannot open the database</H4>
  <% 
End If

The results returned in these samples are small - only a few rows. When we expect bigger results non-applicable for full listing in a single page we may need to generate pages and show on each page let say 10 records. The ability to run more than one query in a single call to Execute allows us to fetch the total number of the records and the records we need in one step. For example if we pass to Execute the following queries:

SELECT Count(ID) AS RecordCount FROM T;
SELECT Name, Age FROM T ORDER BY Name;

We will receive mixed result. Its first row will contain only one field - RecordCount which will tell us how many records we have in total and the following rows will contain actual results. Using the optional parameters of execute we can fetch the results for the current page only (suppose we have much more records in table T - for example hundreds). Appropriate call to the Execute will look like this:

' Lets initialize the pageNumber with 0 if Page parameter does not exist or with the parameter's
' value if it is passed.
If Request("Page").Count > 0 Then pageNumber = Request("Page") Else pageNumber = 0
query = "SELECT Count(ID) AS RecordCount FROM T;"
query = query & "SELECT Name, Age FROM T ORDER BY Name;"
Set r = db.Execute(query,pageNumber * 10 + 2, 11)

Why 11 rows when we want 10 per page? Because the first row comes from the first query always and it always returns only one-row result. Also because of this we add 2 to the firstpage argument instead of 1 - in order to include it into the result as well. Also note that we assume 0-based page number in this sample code. Then to list the current page we need code like this:

<TABLE>
      <TR>
        <TH>Name</TH>
        <TH>Age</TH>
      </TR>
    <%
    For Row = 2 To r.Count
      %>
      <TR>
        <TD><%= r(Row)("Name") %></TD>
        <TD><%= r(Row)("Age") %></TD>
      </TR>
      <%
    Next
    %>
  </TABLE>

We begin with the second row of the result as the first row contains the result of the first query which is the total number of the result records.

To generate a set of links invoking the same page with a parameter that specifies the page number to be displayed we may do something like this:

For I = 0 To r(1)("RecordCount") / 10 + 1
  %>
    <A HREF="<%= Server.ServerVariables("SCRIPT_NAME") %>?Page=<%= I %>">
      Page <%= I + 1 %>
    </A>
Next

Which will display links: Page 1 Page 2 ... and so on.

In code like the above if a page number bigger than the last page is specified no error will occur - just the results will be 0 - e.g. only one row will be returned by the Execute and it will contain the records count calculated by the first query. 

Using parameters

Most of the queries an application would use cannot be composed statically. Virtually all the applications need to compose at least part of the WHERE clauses of the SELECT queries at run time. For example add clause to compare an ID with a variable or something more complicated. Most applications also need to compose INSERT, UPDATE queries and put the new values in them.

Of course this can be done by constructing a string. The StringUtilities object can help by enabling the application to format the query as string. Still, there is a better way in SQLite3 COM. For example:

Set r = db.VExecute("INSERT INTO T (NAME,AGE) VALUES (?,?)",1,0,"Nick",23)

will add a new record to the table T where the NAME column will contain "Nick" and the Age column will be set to 23. 

Thus by placing ? (question mark) in every place you want to substitute an external value you can link variables from the application to the query. The only inconvenience with VExecute is that you always need to specify the firstrow (argument 2) and the rowcount (argument 3). The method allows variable number of arguments - one for each parameter used in the query.

What if you want to put the same parameter in more than one place in the query? If we have a table T1 that has columns for the First, Middle and the Family names of some people.  

NameWeDoNotWant = "Smith"
Set r = db.VExecute("SELECT * FROM T WHERE First != $Name AND Middle != $Name AND Family != $Name",1,0,NameWeDoNotWant)

This will extract all the people not having Smith as their First, Middle or Family name. We passed only one variable but in the statement we used the same named parameter 3 times. As it is the only parameter all the 3 occurrences will refer to the same parameter - 1

Many times you will have the values you want to substitute in the query in an array or a collection. They may be result of the work of the other parts of the application, returned by an object you have called previously and so on. To illustrate this here we will fill them manually:

Dim a(1)
a(0) = "John"
a(1) = "Joan"
Set r = db.AExecute("SELECT * FROM T WHERE NAME != $Name1 AND NAME != $Name2",a)

This will extract only the persons not named John or Joan. The same result can be achieved without named parameters:

Set r = db.AExecute("SELECT * FROM T WHERE NAME != ? AND NAME != ?",a)

And we can make real use of the parameter names:

Set v = Server.CreateObject("newObjects.utilctls.VarDictionary")
v.Add "MyParam1", "John"
v.Add "MyParam2", "Joan"
Set r = db.CExecute("SELECT * FROM T WHERE NAME != $MyParam1 AND NAME != &MyParam2",v)

A trivial example where you can see the effect can be done with SELECT statement without FROM clause:

Set r = CExecute("SELECT $MyParam1 AS P1, $MyParam2 AS P2",v)

By changing the contents of the collection you can see how the query behaves and where the values will appear in the result. For instance the above will return:

P1	P2
John	Joan

 

Remarks:

The last insert ID

The returned result contains more useful information. For example the Info property of the result contains the last insert ID and for example:

Set r = db.Execute("INSERT INTO T (Name,Age) VALUES ('Jack',16)")
lastid = r.Info
Set r = db.Execute("INSERT INTO E (PersonID,EMail) VALUES (" & lastid & ",'" & strEmail & "');

It can be used to determine the id for the next insert in a linked table. If there is more than one INSERT statements in the executed query the ID from the last one is returned. If there are no INSERT statements in the executed query Info is 0 or the ID of a previously executed INSERT statement. The application should rely on this value only after execution of an INSERT SQL statement. 

The names of the fields

Each row of the result is a VarDictionary collection which contains named elements - one element per each field in the order defined by the corresponding query in the string passed to Execute, CExecute, VExecute or AExecute. To learn the field name, knowing the field index you can use the Key property:

fieldname = r(row).Key(field)

Where the row is the row number and the field is the field index. Both row number and the field index are 1-based.

The count of the rows in the result and the number of the fields in a row 

Each VarDictionary collection has property Count which returns the count of the elements in it. Therefore the Count of the result (r from the samples above) will be the total number of rows in the result. The Count property of each row collection will return the number of the fields in the row:

rowcount = r.Count ' Obtain the number of the rows in the result
fieldcount = r(n).Count ' The number of the fields in the n-th result row.

The type of the data contained in each field

Each row contains information about the field types in its Info property. The information can be returned as a collection of type indicators (constants or names) one per each field or as semi-colon separated list of type names or as a collection with number of elements equal to the number of the fields. See details in the Execute results.

By default the type of the returned fields is returned as type constants. The type of a field is obtained:

t = r(R).Info(F)

Where R is the row index and F is the field index or name in that row. See also the more detailed explanation and the constant values in Execute results.

Upper/Lower case issues. As the SQLite database engine is designed to work without need of local data it is not able to perform upper/lower case operations over non-ASCII characters. If you want case insensitive operations it is recommended to transform the strings to upper case (or to lower case if you prefer) when putting them into the db. Such a support will require large tables which may become much bigger in size than the DLL itself and are not planned for the near future. On the other hand the local machine may not have all the data for all the languages and it is impossible to relay on it for every language.

How to obtain more useful information? For example to obtain the field definitions for a table you can use the:

PRAGMA table_info(table-name);

Which will return a SELECT-like result with the following columns:
cid - the field id
name - the field name
type - the type specified
notnull - 0/1 - 1 means the field must not be null
dflt_value - the default value for the field (if present)
pk - 0/1 - 1 means the field is primary key

There are also other useful PRAGMA directives you can use in various situations.

How to create auto-increment ID field? One column in the table definition may be declared as of the type: INTEGER PRIMARY KEY. This makes it auto-increment key field.

How to get handle of a record if the table has no appropriate primary key field? In any query you can use one of the following internally defined key field names: ROWID", "OID", or "_ROWID_". All they do the same - they are the internal row id-s. If an INTEGER PRIMARY KEY is defined in the table definition it is the same as any of the above fields. If no such field exists one of these can be used in any query to construct WHERE clause or another expression where handle of the row is needed. For example:

r = db.Execute("SELECT OID,* FROM E WHERE EMail LIKE '%yahoo%;")
For I = 1 To r.Count
  Set r1 = db.Execute("UPDATE E SET EMail = 'removed' WHERE OID=" & r(I)("OID"))
Next 

will change the yahoo emails to "removed" (of course this can be done in a simple query - but here purpose is to show how OID can be used to address a row).

How to simplify the SQL statements generation? You can use the Sprintf or SQprintf methods of the StringUtilities object to generate strings over specified format and variable set of arguments. For example an insert SQL statement for the table T from the above samples can be generated this way:

sql = Sprintf("INSERT INTO T (Name,Age) VALUES (%q,%u);",Request.Form("Name"),Request.Form("Age"))

Which we suppose is called with the parameters entered in a form submitted to this page. As the entered values may contain ' apostrophes (for example O'Hara) we need to escape them. the %q escape sequence used above will not only double any apostrophes in the string but will also automatically enclose it with apostrophes. For example if we make this call:

sql = Sprintf("INSERT INTO T (Name,Age) VALUES (%q,%u);","O'Hara',23) 

we will receive this string:

"INSERT INTO T (Name,Age) VALUES ('O''Hara',23);"

What to do with date/time values? There is no special support for date/time values but you can get what you need. There are several ways depending on what functionality will be needed. For example if only sorting will be needed then you can use textual values. If you use date format like this one "Year-Month-Date Hour:Minute:Seconds" this will sort fine but you will not be able to use operations over the dates. One of the bes

Applies to: SQLite COM object

See also: Open, SQL syntax reference, Pragma reference

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