Gambas mysql

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   read­only   property   that   returns   the   charset   used   by   the
database.   It is possible for you to store your strings in the database directly in
UTF­8 format.   However, you can also can use this property combined with the
Conv$() subroutine to convert from UTF­8 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 string­based SQL functions and built­in 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 read­only 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  ACID­compliant.   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 re­open 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   read­only  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 read­only Result
object containing the result of the request.  Standard calling convention is:
FUNCTION Exec ( Request AS String, Arguments AS , ... ) AS Result