SQLite DB Engine Execute
 
Execute method 

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.

Syntax:

Set result = object.Execute(query [, 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 ";"

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.

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. 

Remarks:

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

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

 

newObjects Copyright 2001-2006 newObjects [ ]