Mysql Gambas db connection
property is null, a database is opened in memory. If the Name specified is an
absolute path, the pathname is used. If the Name specified is a relative path and
the Host property is null, the database is located in the application temporary
directory /tmp/gambas.$UID/sqlite. Otherwise, Host will contain the database
directory name and the database file path is the result of concatenating the Host
and Name property values. This class is creatable and the calling convention is:
DIM hConnection AS Connection
hConnection = NEW Connection()
Connection Properties
When the hConnection object is instantiated, it creates a new void
connection object. You will then need to set properties for this object. The
properties that you can use with a connection object include Charset, Databases,
Host, Login, Password, Name, Port, Tables, Type, Users and Version. We will
explain each of these in the following sections.
Charset
Charset is a readonly property that returns the charset used by the
database. It is possible for you to store your strings in the database directly in
UTF8 format. However, you can also can use this property combined with the
Conv$() subroutine to convert from UTF8 format to the database charset format.
Using the database charset comes at a cost in performance however. This is
because you must use the Conv$() subroutine each and every time you read or
write string data to the database fields. For now, this is the only way you can do
this if you want to use the stringbased SQL functions and builtin sort routines of
the underlying database management system (MySQL, PostgreSQL, or SQLite).
Future versions of Gambas may automatically make the conversion between
Gambas strings and the database charset.
Databases
The Databases property can be checked to obtain a collection object that
will contain names of all databases that are managed by the database server.
Syntax is:
PROPERTY Databases AS .ConnectionDatabases
The resulting collection of data (which is enumerable using the FOR EACH
328
A Beginner's Guide to Gambas
statement) would be the same as if you typed SHOW DATABASES from the mysql
client CLI.
Host
You can use the Host property to set or get the host name of where the
database server is located. The host name can be either a machine name or an IP
address. The default host is named localhost. In SQLite, host name is not
relevant since SQLite is not a client/server database sytstem. However, from
version 0.95 forward, it is possible for you to set the home path for the SQLite
database by using this property. Syntax for using the Host property is:
PROPERTY Host AS String
Login
The Login property is used to set or get the user login data that is used for
establishing the connection to the database. Once again, this applies to MySQL
and PostgreSQL, not SQLite. Since SQLite has no concept of users, access to a
SQLite database is controlled by the file permission settings of the database file
itself. This means that the Login property is always going to be set to the user id
of the user that is executing the Gambas program that uses the SQLite database.
Syntax is:
PROPERTY Login AS String
Name
The Name property sets or gets the name of the database you want to
connect to. If you do not specify a name, a default system database is used. For
PostgreSQL the default database is named template1 and for mySQL it is named
mysql. For SQLite, the default name is /tmp/sqlite.db. For any database access,
however, the user needs to have at least read and connect access privileges to use
these databases. Sqlite will locate the database first by trying to use the full
pathname if it has been given. If not, it will check to see if the Host variable has
been set. If that is the case, SQLite will look at the path name that was set in the
variable. If the environment variable GAMBAS_SQLITE_DBHOME has been set,
then SQLite will use that as the current working directory. It is also possible to
create and use a database in memory by setting the Name property to
“:memory:” rather than “mysql” or “postgresql” or “sqlite”. Syntax is:
329
PROPERTY Name AS String
Password
The Password property will return or set the password used for establishing
a connection to the database. Calling convention is:
PROPERTY Password AS String
Port
The Port property is used to get or set the TCP/IP port used for establishing
the connection to a database. The default port depends on the connection Type.
Syntax is:
PROPERTY Port AS String
Tables
The Tables property is used to obtain a virtual collection that can be used
for managing all of the tables of a database. Syntax is:
PROPERTY Tables AS .ConnectionTables
Type
The Type property represents the type of the database server you want to
connect to. In Gambas, the currently supported databasetype are: "postgresql",
"mysql", and "sqlite". The type property uses this syntax:
PROPERTY Type AS String
Users
The Users property returns a collection of all users registered in the
database server. Like any other collection object, it can be enumerated with FOR
EACH. Syntax is:
PROPERTY Users AS .ConnectionUsers
Version
The Version property is a readonly value that returns the version of the
330
A Beginner's Guide to Gambas
database that the driver has connected with. Syntax is:
PROPERTY READ Version AS Integer
The Concept of a Transaction
There are times when the order in which database actions (or queries) are
executed is important. Sometimes, a program must ensure all queries in a group
are run successfully and in order or process none of them at all. A classic example
is taken from the banking environment. If a given amount of money (for
example, 100 dollars) is taken from one account and posted to another account,
we would expect the following actions to occur:
UPDATE account1 SET balance = balance 100;
UPDATE account2 SET balance = balance + 100;
Both queries must execute successfully or neither must execute. Money
cannot be transferred out of one account and fail to be posted to the other
account. Both of these queries form a single transaction that is composed of two
separate actions (debit 100 from account1 and credit 100 to account2). A
transaction is simply one or more individual database queries grouped together
between BEGIN and COMMIT statements. Without a COMMIT statement, the
transaction is not permanent and can be reversed with the ROLLBACK statement.
MySQL automatically commits statements that are not part of a transaction
process. The results of any SQL UPDATE or INSERT statement that is not
preceded with BEGIN will immediately be visible to all connections to the
database because the commit is automatically performed. Within MySQL,
transactions are only known with “transaction safe” tables (i.e., BDB and InnoDB).
The other table types COMMIT immediately. Most databases that are able to
accomplish this are said to be ACIDcompliant. The ACID model is one of the
oldest and most important concepts of database theory. The four goals that must
be met for any database to be considered reliable are Atomicity, Consistency,
Isolation and Durability (ACID). Let’s examine each of these four characteristics
in detail.
Atomicity means database modifications must follow an “all or nothing”
rule. Every transaction is considered an “atomic” unit. If any part of the
transaction fails, the entire transaction fails. It is critical the DBMS maintain the
atomic nature of transactions in any circumstance.
331
A Beginner's Guide to Gambas
Consistency states only valid data will be written to a database. If a
transaction is executed that violates database consistency rules, the entire
transaction will be rolled back and the database will be restored to a state
consistent with those rules. Conversely, if a transaction executes successfully, it
will maintain a state of consistency.
Isolation requires multiple transactions that occur simultaneously not
impact each other. For example, if User A issues a transaction against a database
at the same time User B issues a transaction, both transactions should operate on
the database in isolated bliss. The database should perform one transaction
before the other. This prevents a transaction from reading intermediate data
produced as a side effect of another transaction. Isolation does not ensure either
transaction will execute first, just that they will not interfere with each other.
Durability ensures any transaction committed to the database will not be
lost. This is accomplished via database backups and transaction logs that
facilitate restoration of committed transactions despite any subsequent failures.
Now, let's take a look at Gambas Connection class methods available to you in
your programs.
Connection Class Methods
The Connection class provides most of the methods you will need to
establish a connection to a database and execute one or more transactions to find,
add, change, or delete data.
Open/Close
The Open method is used to open a connection to the database. Prior to
making the call to Open, you should set the connection properties with the data
necessary to use the database. Generally, at a minimum you will need to specify
the type of database, the host, a login ID and password, and the name of the
database you wish to use. The syntax for open is:
FUNCTION Open ( ) AS Boolean
The code example below shows how to use the Open method to connect
user jrittinghouse to a MySQL database named “GambasBugs” that is stored on
Host localhost:
DIM $hConn As NEW Connection
332
A Beginner's Guide to Gambas
WITH $hConn
.Type = "mysql"
.Host = "localhost"
.Login = "jrittinghouse"
.Password = "ab32e44"
.Name = "GambasBugs"
END WITH
TRY $hConn.Open
IF Error THEN PRINT "Database cannot be opened. Error = "; Error.Text
To close the connection, simply invoke the Close method. In our example above,
you could use this code:
$hConn.Close
and the connection would be closed. In order to subsequently use any data stored
in the database, you would need to reopen the database using the Open method.
Begin/Commit/Rollback
As we stated previously, a transaction is one or more individual database
queries that are grouped together between BEGIN and COMMIT statements.
Without a COMMIT statement, the transaction is not permanent and can be
reversed with a ROLLBACK statement. Remember that MySQL automatically
commits statements that are not part of a transaction process (defined by BEGIN
and COMMIT). In Gambas, it is recommended that you use the Find, Create, and
Edit methods to make changes to the database. This helps to maintain code
independence and it allows you to write a single piece of code that will work with
any database supported by Gambas. Gambas uses a result object to return the
results of a SQL query.
Find
The Find method returns a readonly Result object used for querying
records in the specified table. The calling convention for Find is:
FUNCTION Find (Table AS String [, Request AS String, Arguments AS , ... ] ) AS Result
Request represents a SQL WHERE statement that is used for querying the
table. Arguments are quoted as necessary (dictated by SQL syntax), and
substituted inside the Request string. It works like the Subst() function in this
333
A Beginner's Guide to Gambas
case. Using the Find method allows you to write requests that are independent of
the underlying database, meaning it will work with PostgreSQL or MySQL without
any code change required.
Create
The Create method is used to build a read/write Result object which can
used to create records in a table. Standard calling convention is:
FUNCTION Create ( Table AS String ) AS Result
Edit
The Edit method returns a read/write Result object used for editing records
in the specified table. Query is equivalent to an embedded SQL WHERE clause
used for filtering the table, and the Arguments are quoted as needed by the SQL
syntax and substituted in the Request string (like in sscanf in C) as we explained
previously. Standard calling convention is:
FUNCTION Edit ( Table AS String [ , Query AS String, Arguments AS , ... ] ) AS Result
Here is a code sample to show how Edit() works:
DIM MyResult AS Result
DIM sQuery AS String
DIM iParm AS Integer
sQuery = "id=&1"
' we will insert a parameter value (12) at the end of the query string
iParm = 12
$hConn.Begin
MyResult=$hConn.Edit("tblDEFAULT", sQuery, iParm)
MyResult!Name = "Mr Rittinghouse" ' Set a field value
$hConn.Update ' Update with the new value
$hConn.Commit ' make the changes permanent
$hConn.Close ' close the database connection
Exec
The Exec method executes a SQL request and returns a readonly Result
object containing the result of the request. Standard calling convention is:
FUNCTION Exec ( Request AS String, Arguments AS , ... ) AS Result
