This class represents the current database connection, which by default is the connection to the first open database. You can change the database connection by modifying the value of the `DB.Current` property.
The DB class has the following properties:
Property | Data type | Description |
---|---|---|
Charset | String | Returns the character set used by the database. |
Current | Connection | Sets the current DB connection or returns the current DB connection. |
Debug | Boolean | Sets with True that the database component is set to debugging mode. If this flag is set, every database query is output on the standard error output. |
Error | Integer | Returns the error code of the last error triggered by the database driver. |
Handle | Pointer | Returns the pointer to the default database connection. This is a pointer to the underlying object - associated with the default database connection. |
IgnoreCharset | Boolean | Returns True if the database character set is to be ignored or sets the value of the property. |
Opened | Boolean | Returns True if the current DB connection is open. |
Databases | .Connection.Databases | Returns an overview of all databases managed by the DB server. |
Users | .Connection.Users | Returns an overview of the DB users in a database. |
Tables | .Connection.Tables | Returns an overview of the managed tables in a database. |
Table 22.4.3.1.1 : Properties of the DB class
The .Connection.Databases class represents a collection of all databases that are managed by the database server. You may not see every database if the user with whom you have connected to the server does not have sufficient rights.
The .Connection.Tables class provides an overview of all tables in the database to which you have connected with the connection object.
The .Connection.Users class provides an overview of all registered DB users on the DB server. SQLite databases, however, do not have any special DB users; they accept any user because there is no designated security concept.
The DB class has these methods:
Method | Description |
---|---|
Begin ( ) | Starts a transaction. |
Close ( ) | Closes an existing DB connection between DB client and DB server. |
Open ( ) | Opens a (new) connection to the database specified in the connection properties. The Open() method has no parameters. The connection properties must therefore be set before the Open() method is called. |
Commit ( ) | Changes to the database are applied. |
Rollback ( ) | Changes to the database during a session are cancelled. |
Delete ( Table As String [ , Request As String, Arguments As , … ] ) | Deletes data records from a DB table (implementation of an SQL statement…). Table is the name of the DB table. Request is a SQL WHERE clause for filtering the table and arguments are enclosed in inverted commas according to SQL syntax and replaced within the query string. |
Edit ( Table As String [ , Request As String, Arguments As , … ] ) As Result | Returns a read/write result object for editing records in the specified table. Table is the name of the DB table. Request is a SQL WHERE clause to filter the table and Arguments are quoted according to SQL syntax and replaced within the query string. |
Exec ( Request As String, Arguments As , … ) As Result | Executes any SQL query and returns the result of the query as a read-only result. Request is any valid SQL statement and Arguments are quoted according to SQL syntax and replaced within the query string. |
Find ( Table As String [ , Request As String, Arguments As , … ] ) As Result | Returns a read-only result object that is used to query records in the specified table. Table is the name of the DB table. Request is a SQL WHERE clause to filter the table and Arguments are quoted according to SQL syntax and replaced within the query string. |
Function Limit ( Limit As Integer ) As Connection | Limits the number of records returned by the next query. After the query has been executed, the limit is automatically cancelled. The function value is of type Connection, so you can write something like the following: DB.Limit(12).Exec(…). |
FormatBlob ( Data As String ) As String | Formats some blob data so that it can be inserted into an SQL query. |
Quote ( Name As String [ , Table As Boolean ] ) As String | Returns an identifier in quotes so that you can freely insert it into a query. This identifier can be a table or field name. |
Function Subst ( Format As String, Arguments As , … ) As String | Creates an SQL record by inserting its arguments into a format string. Format is the SQL statement and Arguments is the list of arguments to be substituted. |
Table 22.4.3.2.1 : Methods of the DB class
Example
DIM $hDBConnecton As NEW Connection WITH $hDBConnecton .Type = "postgresql" .Host = "localhost" .Login = "loginname" .Password = "password" .Port = "5432" .Name = "testdb" END WITH TRY $hDBConnecton.Open() IF Error THEN PRINT "Cannot Open Database! Error = "; Error.Text
Notes:
With the Delete(…) method, you can write SQL statements that are independent of the underlying database type. This is an advantage that should not be underestimated when it comes to developing and testing reusable software. Once you have received the result object, you can change some of the fields. You can then call the Result.Update() method to send the changes to the database.
DIM hResult AS Result DIM sCriteria AS String DIM iParemeter AS Integer sCriteria = "id = &1" iParameter = 1012 $hDBConnecton.Begin() '-- Same as "SELECT * FROM table_name WHERE id = 1012" hResult = $hDBConnecton.Edit("table_name", sCriteria, iParameter) '-- Set field value hResult!Name = "Mayer" '-- Update the value hResult.Update() $$hDBConnecton.Commit()
'-- Returns the number of data records in a query. sTable is the name of the table. '-- It may contain reserved characters, so you must enclose it in inverted commas! rResult = Handle.Exec("SELECT COUNT(*) AS nRecord FROM " & DB.Quote(sTable, True)) PRINT rResult!nRecord
PRINT DB.Subst("WHERE Name = &1 AND Date = &2", "Mayer-Motzen", Now())