The class offers the option of generating a basic SQL query on a (single) table.
The class preserves the independence of the Gambas DBMS. The class unifies the syntactic differences between the SQL requirements of the various database systems (SQLite, PostgreSQL and MySQL) in a standardised syntax. This class can be used as a function. The function `SQLRequest( )` returns the SQL request as a string on which the SQLRequest object is based. This is done via a link to the Get() method.
The class can be created:
Dim hSQLRequest As SQLRequest hSQLRequest = New SQLRequest ( Connection As Connection )
The SQLRequest class only has these methods:
| Method | Return type | Description |
|---|---|---|
| Select ( [ Fields As Variant ], … ) | SQLRequest | Indicates that the request is a SELECT request. The arguments describe the fields that are returned by the request. You can either specify a field name as an argument or all field names in a single argument as a string array. The current request is returned so that you can concatenate method calls. |
| Delete ( ) | SQLRequest | Indicates that the request is a DELETE request. The current request is returned so that you can concatenate method calls. |
| From ( Table As String ) | SQLRequest | Determines the name of the table whose data is to be processed. The current request is returned so that you can concatenate method calls. |
| And ( ) | SQLRequest | Specifies that the next WHERE clause is linked with the AND operator. The current request is returned so that you can concatenate method calls. |
| Or ( ) | SQLRequest | Specifies that the next WHERE clause is linked with the OR operator. The current request is returned so that you can concatenate method calls. |
| Where ( Where As String, … ) | SQLRequest | Determines a criterion of the Where clause. The function works exactly like the Connection.Subst() method. The current request is returned so that you can concatenate method calls. If you concatenate multiple WHERE clauses, you must specify the operator between them using the AND or OR method. |
| OrderBy ( OrderBy As Variant, … ) | SQLRequest | Describes the ORDER BY clause of the query. The arguments describe the ORDER BY fields. You can either specify a field name as an argument or all field names in a single argument as a string array. The current request is returned so that you can concatenate method calls. For example, you can specify that a field must be sorted in descending order by adding a space and the string “DESC” after the field name. |
| Get ( ) | String | Delivers the SQL query on which the SQLRequest object is based. |
Table 22.4.9.1.1 : Methods of the SQLRequest class
Example:
The following source code section creates a new SQL request object. The database connection is `hDBConnection`, for which the query applies. An SQL statement is generated that is always correct for the database type specified by the DB connection hDBConnection:
Dim hDBConnection As Connection Dim iAgeMin As Integer = 18 Dim iAgeMax As Integer = 30 Dim hSQLRequest As SqlRequest Dim hDBResult As Result ' ... Initializing the connection hSQLRequest = New SqlRequest(hDBConnection) hDBResult = hDBConnection.Exec(hSqlRequest.Select("id", "name", "age").From("people").Where("age >= &1", iAgeMin).And().Where("age <= &1", iAgeMax).OrderBy("name", "age DESC").Get())
Here is the variant used by the author:
Dim hDBConnection As Connection Dim sSQLStatement As String Dim hDBResult As Result ' ... Initializing the connection sSQLStatement = "SELECT id, name, age FROM people WHERE age >= 18 AND age <= 30 ORDER BY name,age DESC" hDBResult = hDBConnection.Exec(sSQLStatement)
The variable hDBResult (data type Result) contains all data returned by the database server or the DB library in the case of SQLite.