k22:k22.4:k22.4.3:start

22.4.3 Class DB (gb.db)
22.4.3.1 Properties
22.4.3.2 Methods

22.4.3 Class DB (gb.db)

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.

22.4.3.1 Properties

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.Databases.Count property of data type Integer specifies the number of databases on the DB server.
The .Connection.Databases.Add (Name As String) method creates a new database with the database name 'Name'.
The boolean function .Connection.Databases.Exist ( Key As String ) returns True if the specified database exists in the overview of managed databases.
The specified database 'T_Name' is deleted with the .Connection.Databases.Remove ( T_Name As String ) method.
The .Connection.Databases.Refresh ( ) method refreshes the overview of the managed databases. The internal cache is deleted in the process.

The .Connection.Tables class provides an overview of all tables in the database to which you have connected with the connection object.

This class inherits from .SubCollection.
This class is virtual.
This class cannot be created.
This class behaves like a read-only array.
This class can be enumerated with the keyword FOR EACH.

The property .Connection.Tables.Count (type Integer) specifies the number of tables in the overview of the managed tables in a database.
The .Connection.Tables.Add (Name As String [ , Type As String ]) method creates a new table with the table name 'Name'. The (optional) parameter 'Type' specifies the table type and is only useful if a MySQL database server is used. The following types are supported: InnoDB, BDB, HEAP, ISAM, MERGE and MYISAM. Please note: Only InnoDB and Berkley DB tables allow the use of transactions!
The boolean function .Connection.Tables.Exist ( Key As String ) returns True if the specified table exists in the overview of managed tables in a database.
The .Connection.Tables.Remove ( T_Name As String ) method deletes the specified DB table 'T_Name' in the database.
The .Connection.Tables.Refresh ( ) method refreshes the overview of the managed tables in a database. The internal cache is deleted in the process.

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 .Connection.Users.Count property (data type Integer) returns the number of DB users managed by the DB server.
The .Connection.Users.Add( Name As String [ , Password As String, Admin As Boolean ] ) method is used to register a new user on the database server. Name is the name of the DB user. Password is an optional password. Set the 'Admin' parameter to True if you want this DB user to be a DB administrator. A database administrator has the right to create or remove databases and users and can connect to any database on the server.
The function .Connection.Users.Exist ( Key As String ) (data type Boolean) returns True if the specified user exists in the overview of all registered DB users on the DB server.
With .Connection.Users.Refresh ( ) the overview of all registered DB users on the DB server is refreshed. The internal cache is deleted.
The .Connection.Users.Remove ( Name As String ) method deletes a DB user from the DB server.

22.4.3.2 Methods

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()

Quoting is dependent on the database server driver, so this method should be used if you need to write database-independent code:

'-- 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

You can use the Subst() function like this: The &1, &2… patterns in the format string are replaced by the SQL representation of the 1st, 2…. arguments.
These arguments are specified according to the underlying database SQL syntax.

PRINT DB.Subst("WHERE Name = &1 AND Date = &2", "Mayer-Motzen", Now())

Table of Contents

22.4.3 Class DB (gb.db)

22.4.3.1 Properties

22.4.3.2 Methods