Executes one or more SQL statements. This is the most used method
of the SQLite 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.
query - String containing one or more SQL queries to be
executed. The separate queries (if more than one is passed) must be
separated with ";"
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.
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.
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.
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.
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. To learn the field name, knowing the field index
you can use the Key property:
fieldname = r(row)(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.
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.
Each row contains information about the field types in its Info
property - for example for the n-th result row it will be:
strtypes = r(n).Info
It is semicolon separated list - for example you may receive
something like these:
"TEXT;;numeric"
"TEXT(10);;;INTEGER"
"INTEGER;TEXT;numeric"
Because the SQLite engine is type-less the types returned can be
empty if the corresponding field is defined without type
specification (allowed). The field type is:
- The column type as defined when the table has been
created (see CREATE TABLE in SQL syntax reference)
- TEXT or numeric if the result field is a result of an
expression - whatever the expression returns.
This comes from the way SQLite treats the types. When a
computation is required by an expression it converts each field
participating in the expression to the type required by the
expression and performs the calculation. Thus if you need to
determine the field types at run time you can limit your code to
recognition of numeric and TEXT type keywords only and
ignore the others which are most probably known for the application
because they are part of the table definitions. Furthermore note
that type notations like TEXT(10) in the CREATE TABLE will be
recorded but no limit will be enforced on the values inserted/set in
the table. If you actually need a limit you should do this from
outside (using something as simple as Left(str,10) for example or
something more sophisticated if errors must be generated).
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
