GAMBAS BOOK 3.19.5

The Connection class is part of the gb.db component and represents a connection to a database. An instance can be created using source code.

Note: The classes DB (also part of the gb.db component) and Connection have the same properties:

Charset Databases Error IgnoreCharset Opened Tables Users Version

and have the same methods:

Begin Close Commit Create Delete Edit Exec Find FormatBlob Limit Open Quote Rollback Subst 

The common properties and methods are described in section 22.4.3.

This chapter is dedicated to the special properties of the Connection class:

Host Login Name Password Port Timeout Type User

The Connection class has the special properties

Table 22.4.1.1.1 : Properties of the Connection class

Notes:

• The host can be a host name or an IP address. The default host is 'localhost'. For SQLite, the host name is the path for the database file. For MySQL/MariaDB, the host name can contain the path to a socket file. In this case, the character string must begin with a slash “/”. For ODBC, the host name is the DSN name definition. Information on ODBC can be found in chapter → 22.9.0 ODBC (Open Database Connectivity).
• If you do not specify a database name, a default system database is used. For PostgreSQL the default database is template1, for MySQL it is mysql and for SQLite it is /tmp/sqlite.db. For SQLite it is an in-memory database /tmp/sqlite.db. For SQLite, you can also create a database in memory by specifying ':memory:' as the database name. Of course, the MySQL or PostgreSQL user needs at least read and connection access to these databases.
• The type of database server must be specified in lower case! The currently supported database servers are PostgreSQL: “postgresql”, MySQL: “mysql”, SQLite 2: “sqlite2”, SQLite 3: “sqlite3”, SQLite 2 or SQLite 3: “sqlite” and ODBC: “odbc”.
• The default port depends on the connection type. For MySQL it is '3306' and for PostgreSQL '5432'. The Port property is not used for SQLite.
• The password is empty for SQLite because SQLite does not recognise a DB user concept. Access to an SQLite database is controlled by the file permissions of the database file. The SQLite DB user is always the system user who is currently executing the DB programme.

You can use the following procedure to check whether the MySQL or PostgreSQL DB server to which a DB connection is to be established has been started when the programme is started:

'' Determining the status of the DB server<br>
'' Argument: DBMS types mysql or postgresql
Public Sub DBServerStatus(argType As String)
 
    Dim sMessage As String
 
    If Not argType Or (Lower(argType) <> "mysql" And Lower(argType) <> "postgresql") Then 
       sMessage = "<b><font size='+1', color='DarkRed'>"
       sMessage &= ("Error!")
       sMessage &= "</b></font><hr>"
       sMessage &= ("The DB server name is missing or has been written incorrectly.")
       sMessage &= gb.NewLine & gb.NewLine
       sMessage &= "<b>" & ("The application is therefore terminated!") & "</b>"
       Message.Error(sMessage)
   '-- The (main) program is terminated. -------------------------------------------------
       Quit 
    Endif 
 
    Exec ["systemctl", "status", argType] Wait For Read Write
 
'-- EXIT STATUS IN PROCESS.LASTVALUE
'------------------------------------------------------------
'-- Documented in `man systemctl` 
'-- 0 -> program is running or service is OK │ unit is active
'-- 3 -> program is not running | unit is not active 
'-- 4 -> program or service status is unknown │ no such unit
 
    Select Case Process.LastValue
      Case 4
        sMessage = "<b><font size='+1', color='DarkRed'>"
        sMessage &= ("Error!")
        sMessage &= "</b></font><hr>"
        sMessage &= Subst("&1 &2 &3", ("The DB server"), Upper(argType), ("is obviously not installed!"))
        sMessage &= gb.NewLine & gb.NewLine
        sMessage &= "<b>" & ("The application is therefore terminated!") & "</b>"
        Message.Error(sMessage)
    '-- The (main) program is terminated. -----------------------------------------
        Quit
      Case 3
        sMessage = "<b><font size='+1', color='DarkRed'>"
        sMessage &= ("Error!")
        sMessage &= "</b></font><hr>"
        sMessage &= Subst("&1 &2 &3", ("The DB server"), Upper(argType), ("is not started!"))
        sMessage &= gb.NewLine & gb.NewLine
        sMessage &= "<b>" & ("The application is therefore terminated!") & "</b>"
        Message.Error(sMessage)
    '-- The (main) program is terminated. -----------------------------------------
        Quit
    End Select
 
End

To establish a connection to a database, first create a connection object, then set the required properties and then call the 'Open' method. The following section shows you different ways of creating a DB connection. It is assumed that a DB user (name, password) and a database exist for the DBMS used. The database can also be empty - i.e. without DB tables.

Dim hConnection As Connection
hConnection = New Connection ( [ DatabaseURL As String ] )

The format of the optional parameter DatabaseURL is

$TYPE://[$USER@]$HOST[:$PORT]/$DBNAME

where the $USER and $PORT parts are optional. However, if this parameter 'DatabaseURL' is specified and valid, the connection properties are set to the transferred property values.

(1) MySQL DB connection (DB user 'test', password 'test', port 3306, database name 'test')

Dim hConnection As Connection

hConnection = New Connection("mysql://test@localhost:3306/test")
hConnection.Password = "test"

Try hConnection.Open()
If Error Then Print "The database cannot be opened! Error = "; Error.Text

Alternative:

Dim hConnection As Connection

WITH hConnection
 .Type 	= "mysql"
 .Host 	= "localhost"
 .User 	= "test"
 .Password 	= "test"
 .Name 	= "test"
END WITH

Try hConnection.Open()
If Error Then Print "The database cannot be opened! Error = "; Error.Text

(2) SQLite3 DB connection (database name 'test', path 'User.Home/Test')

Dim hConnection As Connection
hConnection = New Connection("sqlite3://" & User.Home /& "Test" &/ "test.sqlite")

Try hConnection.Open()
If Error Then Print "The database cannot be opened! Error = "; Error.Text

If no DB name is specified, a DB connection to a temporary database is created. Alternatively, you can also use this instruction:

Dim hConnection As Connection
hConnection = New Connection("sqlite3://:memory/")

(3) PostgreSQL DB connection (user 'test', password 'test', port 5432, database name 'test')

Dim hConnection As Connection

hConnection = New Connection("postgresql://test@localhost:5432/test")
hConnection.Password = "test"

Try hConnection.Open()
If Error Then Print "The database cannot be opened! Error = "; Error.Text

Alternative:

Dim hConnection As Connection

WITH hConnection
 .Type 	= "postgresql"
 .Host 	= "localhost"
 .Login 	= "test"
 .Password 	= "test"
 .Name 	= "test"
END WITH

Try hConnection.Open()
If Error Then Print "The database cannot be opened! Error = "; Error.Text

You can outsource the creation of a DB connection to a module or a static class. This turns the module or static class into a singleton. It ensures that there is only ever one DB connection to a specific database for each database in your programme. The DB connection is established the first time the database is accessed - after that it remains in place during the programme runtime! This ensures that your programme always uses this one DB connection and does not maintain simultaneous connections to the same database, which can lead to database deadlocks. In multitask systems, it would be perfectly possible to keep several DB connections to a database open at the same time. However, this can lead to situations in which one DB connection is waiting for something that is not released in the other DB connection because it is waiting for something that must first be released in the other DB connection.

This is the source code for the static class DBCS, which is always used in all example projects in the book:

[1] ' Gambas class file
[2] 
[3] Create Static
[4] 
[5] Property Read DBConnection As Connection '-- It is a 'DataBaseConnectionSingleton'
[6] Private $DBConnection As New Connection
[7] 
[8] Private Function DBConnection_Read() As Connection
[9] 
[10]   If Not $DBConnection.Opened Then
[11]    ' ---------------------------------------------------------------------------------------
[12]      $DBConnection.Type = "sqlite" 
[13]      $DBConnection.Port = Null 
[14]      $DBConnection.User = Null
[15]      $DBConnection.Password = Null
[16]      $DBConnection.Host = MCreateDir.DBHost	'-- File-Path for SQLite-Host
[17]      $DBConnection.Name = "flowers.sqlite"    	'-- Name of the SQLite database
[18]    ' ---------------------------------------------------------------------------------------
[19]    ' $DBConnection.Type = "postgresql" 
[20]    ' $DBConnection.Port = 5432
[21]    ' $DBConnection.User = "test"
[22]    ' $DBConnection.Password = "test"
[23]    ' $DBConnection.Host = "localhost"     	'-- DBHost is `localhost ` or  an IP adddress
[24]    ' $DBConnection.Name = "test"          	'-- Name of the database
[25]    '----------------------------------------------------------------------------------------
[26]    ' $DBConnection.Type = "mysql"
[27]    ' $DBConnection.Port = 3306
[28]    ' $DBConnection.User = "test"
[29]    ' $DBConnection.Password = "test"
[30]    ' $DBConnection.Host = "localhost"     	'-- DBHost is `localhost ` or an IP adddress
[31]    ' $DBConnection.Name = "test"          	'-- Name of the database
[32]    '----------------------------------------------------------------------------------------
[33] 
[34]      If $DBConnection.Type <> "sqlite" Then
[35]         If $DBConnection.User = Null Or If $DBConnection.Password = Null Then
[36]            Error.Raise(("No database credentials"))
[37]          Endif
[38]      Endif        
[39]      $DBConnection.Open()     
[40]   Endif
[41]     
[42]   Return $DBConnection
[43]     
[44]   Catch
[45]   Error.Propagate()
[46] 
[47] End

Example of the use of a DB connection defined in the DBCS class above:

Public Function GetReportData() As Result
 
'-- Returns the result of a database query as a database result.
    sSQLStatement = "SELECT vorname,nachname,wohnort FROM contats ORDER BY nachname"
 
    hDBResult = DBCS.DBConnection.Exec(sSQLStatement)   
 
    Return hDBResult
 
End

The Gambas IDE offers the option of defining and creating a DB connection (SQLite, MySQL, PostgreSQL, ODBC) for test purposes. If you have selected the gb.db component, a new 'Connections' directory is automatically created in the project directory. You can use its context menu to create a new DB connection in the IDE or update an existing DB connection:

Figure 22.4.1.4.1: Creating a new DB connection to a PostgreSQL database 'test'

If you open a DB connection in the IDE, you can use

• to view information on the fields of the selected DB table,
• additionally display system tables,
• create new DB tables,
• save all entries,
• reload all DB data,
• delete a DB table,
• rename a DB table,
• copy a DB table,
• insert a DB table or
• import a text file (dialogue → default file type: *.csv).

Figure 22.4.1.4.2: Overview of DB fields and indices of a DB table (SQLite3)

You can also edit DB data of a selected DB table. However, the prerequisite is that you switch to edit mode (ON/OFF switch).

You can

• Edit data in a DB table (insert, change, delete),
• Export data from a DB table to a csv file,
• save changes made to data,
• add a data record,
• delete one or more data records,
• select all data records or
• deselect all data records.

Figure 22.4.1.4.3: SQL query for an SQLite DB table → Connection1

The option to also make SQL queries for a selected DB table rounds off the scope of work with DB tables for a specific DB connection in the IDE.