Technology

Structured Query Language (SQL)

SQL commands are sent to the database using either the Open method of the Recordset object or the Execute method of the Connection object. The Open method opens a Recordset object based on the SQL command sent to the database. The recordset that is returned can be read, sorted, and modified. The Execute method does not return a recordset.

There are two main types of SQL statements:

• Data Definition Language (DDL) Statements
• Data Manipulation Language (DML) Statements

DDL statements are used to change the database structure, and DML statements are used to read, sort, and modify information. For more information on the statements supported by SQL, see the topic Supported SQL Statements.

Using SQL Statements

The information contained in this section is supported by CEDB providers only. SQL is used with ADOCE to create a subset of existing tables that can be queried and updated without an extensive programming effort. An instance of this subset is created in the form of a Recordset object. In the form of a Recordset object, data can be read, changed, and deleted. Information is returned from the database based on the rules specified in a SQL query. In ADOCE, the table returned by a SELECT statement is called a recordset.

The following code example shows how a recordset is opened using a SQL statement:

Dim rs, SQLcommand Set rs = CreateObject("ADOCE.Recordset.3.1") SQLcommand = "SELECT * FROM address WHERE lastname = 'Smith' ORDER BY lastname, firstname" rs.Open SQLcommand, "", adOpenKeyset, adLockOptimistic, adCmdText

The previous SQL statement generates a recordset using every field from the address table. Once the structure of the new recordset is defined, ADOCE fills the recordset with data. In this example, ADOCE populates the recordset with records that contain the string "Smith" in the lastname column. It also sorts the rows of data by last name and first name, making it easy to find a particular name, such as James Smith. The SQL command in the previous code example consists of the following commands and conditions:

SELECT

The SELECT statement generates a recordset from existing tables according to the parameters that follow the statement.

FROM

The * FROM command notifies the database engine to return all the fields in the addresses table. The asterisk (*) wildcard character refers to all fields. The fields specified in the SQL statement become the columns in the new recordset.

WHERE

The WHERE condition restricts the rows returned to only rows containing the string specified in the SQL statement. The rows specified become the rows in the new recordset. If there are no fields that match the query in the address table, the recordset is created with no rows.

ORDER BY

The ORDER BY command notifies the database engine to sort the records before returning them. The engine sorts records according to the specified columns. If multiple columns are listed, each successive column sorts the recordset. In the previous example, the recordset is sorted by last name, then further sorted by first name. Up to three columns can be specified in the ORDER BY statement.