Gamma/MySQL : Using Gamma™ with MySQL, Version 1.1
	Chapter 2. Tutorials

Chapter 2. Tutorials

Table of Contents

2.1. Tutorial 1: Interactive Session
2.2. Tutorial 2: create_pets.g
2.3. Tutorial 3: create_foodsinc.g
2.4. Example function: table_list.g
2.5. Example function: query_table.g

2.1. Tutorial 1: Interactive Session

Gamma code can be run in two ways: in interactive mode, and as an executable program. This first tutorial demonstrates an interactive session and introduces some of the basic Gamma/MySQL methods and functions needed to get started.

To begin, we start Gamma at a shell prompt and then load the Gamma/MySQL support library.

[sh]$ gamma
This software is free for non-commercial use, and no valid commercial license
is installed.  For more information, please contact info@cogent.ca.

Gamma(TM) Advanced Programming Language

Copyright (C) Cogent Real-Time Systems Inc., 1995-2005.  All rights reserved.
Gamma Version 5.2 Build 5 at Feb 11 2005 10:21:37
Gamma> require ("MySQLSupport");
"/usr/cogent/require/MySQLSupport.g"

This require statement loads the MySQLSupport.g library, which contains all the Gamma/MySQL methods and functions.

Next we create an instance of the MYSQL class, using the Gamma function new.
```
Gamma> mysql = new MYSQL();
{MYSQL (client_flag . 0) (connector_fd) (db) (field_alloc .
... rest of class definition}
```
The MYSQL class supports the connection to the database, and most of the methods and functions documented in this manual relate to this class.
The next step is to actually make the connection, using the MYSQL.connect method.
```
Gamma> mysql.connect ("localhost", "test", "");
{MYSQL (client_flag . 8325) (connector_fd) (db) (field_alloc .
...rest of class definition}
```
The MYSQL.connect method has three parameters: host, user, and password. We have chosen localhost for the host, test for the user, and an empty string for the password so that this example can be run on any host by any user.
Once the connection is made, we can create a new database, select it, and then create a table for it.
```
Gamma> mysql.query(string("CREATE DATABASE ", "test_interactive"));
0
Gamma> mysql.select_db ("test_interactive");
0
Gamma> try mysql.create_table("friends"); catch;
nil
```
Creating the database was done with the MYSQL.query method, which can be used to pass SQL commands to MySQL. However, there is no similar SQL command for tables available in the version of MySQL we are currently running, so we use the .create_table method for tables. We surround this statement with a Gamma try/catch statement, which lets us ignore an error and continue if the table already exists. This is essentially the same as using the IF NOT EXISTS command option. (We could have also used the .create_db method in a similar way to create the database.)

Now that a table is created, we can add columns to it, using Gamma's MYSQL.add_column method.

Gamma> mysql.add_column ("friends", "name", "VARCHAR(20)", nil, nil);
nil
Gamma> mysql.add_column ("friends", "phone", "VARCHAR(20)", nil, nil);
nil
Gamma> mysql.add_column ("friends", "email", "VARCHAR(20)", nil, nil);
nil

The next step is to populate the table with data. Gamma uses an object-oriented approach to the data in a MySQL database, which is achieved by first creating a class named Friend from the friends table, using the MYSQL.class_from_table method.
```
Gamma> mysql.class_from_table (#Friend, nil, "friends");
(defclass Friend nil [(__columns . #0=[id name phone email]) (__primary_key . id)
    (__table . #1="friends")][email id name phone])
```
The class variables: __columns, __primary_key, and __table contain the essential information about the table. The instance variables are named after the column names; in this case: email, id, name, and phone, with the id variable being added automatically, and incremented as necessary. Each instance of this class corresponds to one row of the table. For example, to put a new row into the table, we simply make an instance of the class, and assign the instance variables (except id), as shown in the next step.

Once the Friend class has been created, we can create instances and assign values to them. Each instance corresponds to a row in the friends database.

Gamma> newrow = new(Friend);
{Friend (email) (id) (name) (phone)}
Gamma> newrow.name = "Kisbet Grimes";
"Kisbet Grimes"
Gamma> newrow.phone = "414-595-3389";
"414-595-3389"
Gamma> newrow.email = "kisbetg@netmail.com";
"kisbetg@netmail.com"
Gamma> mysql.insert(newrow);
nil
Gamma> newrow = new(Friend);
{Friend (email) (id) (name) (phone)}
Gamma> newrow.name = "Merle Royer";
"Merle Royer"
Gamma> newrow.phone = "605-783-2045";
"605-783-2045"
Gamma> newrow.email = "mark55@interisp.com";
"mark55@interisp.com"
Gamma> mysql.insert(newrow);
nil

The Gamma function new is used to create instances of the class. As each instance is created, the system automatically assigns the id number. The Gamma method MYSQL.insert is used to insert the row into the table.

With values in the database, perhaps now we want to make a query. Gamma/MySQL provides several ways of doing this, one of which is the MYSQL.query_and_store method.

Gamma> all_friends = mysql.query_and_store("SELECT * FROM friends");
{MYSQL_RES (current_field . 0) (eof . 1) (field_count . 4)
    (fields . [{MYSQL_FIELD (decimals . 0) (def) (flags . 49667)
                            (length . 11) (max_length . 1) (name . "id")
                            (table . "friends") (type . 3)}
               {MYSQL_FIELD (decimals . 0) (def) (flags . 1) (length . 20)
                            (max_length . 13) (name . "name") (table . "friends")
                            (type . 253)} {MYSQL_FIELD (decimals . 0) (def)
                            (flags . 1) (length . 20) (max_length . 12) (name . "phone")
                            (table . "friends") (type . 253)}
               {MYSQL_FIELD (decimals . 0) (def) (flags . 1) (length . 20)
                            (max_length . 19) (name . "email") (table . "friends")
                            (type . 253)}])}

The results of the query are returned as an instance of the MYSQL_RES class. The fields instance variable of that class holds the data in an array of MYSQL_FIELD instances, one instance per row of data.

To see the query results, we use the Gamma mysql_pretty_print function.

Gamma> mysql_pretty_print(stdout, all_friends);
id name          phone        email               
-- ------------- ------------ ------------------- 
 1 Kisbet Grimes 414-595-3389 kisbetg@netmail.com 
 2 Merle Royer   605-783-2045 mark55@interisp.com 
nil

At the end of our tutorial session, we can drop (remove) our test database. This makes it possible to run the tutorial again later.
```
Gamma> mysql.query(string("DROP DATABASE ", "test_interactive"));
0
Gamma> 
```
Dropping the database can be done with the MYSQL.query method, as shown above. If the DROP DATABASE command is not available for some reason, you can use the the MYSQL.drop_db method.

These are the fundamentals of using Gamma/MySQL. The next tutorial (Section 2.2, “Tutorial 2: create_pets.g”) is an executable program that follows a similar sequence, and introduces more Gamma/MySQL methods and functions. As a review and for your convenience, all the input for the tutorial you have just completed is as follows:

require ("MySQLSupport");
mysql = new MYSQL();
mysql.connect ("localhost", "test", "");
mysql.query(string("CREATE DATABASE ",  "test_interactive"));
mysql.select_db ("test_interactive");
try mysql.create_table("friends"); catch;
mysql.add_column ("friends", "name", "VARCHAR(20)", nil, nil);
mysql.add_column ("friends", "phone", "VARCHAR(20)", nil, nil);
mysql.add_column ("friends", "email", "VARCHAR(20)", nil, nil);
mysql.class_from_table (#Friend, nil, "friends");
newrow = new(Friend);
newrow.name = "Kisbet Grimes";
newrow.phone = "414-595-3389";
newrow.email = "kisbetg@netmail.com";
mysql.insert(newrow);
newrow = new(Friend);
newrow.name = "Merle Royer";
newrow.phone = "605-783-2045";
newrow.email = "mark55@interisp.com";
mysql.insert(newrow);
all_friends = mysql.query_and_store("SELECT * FROM friends");
mysql_pretty_print(stdout, all_friends);
mysql.query(string("DROP DATABASE ", "test_interactive"));


1.3. Where can I get help?		2.2. Tutorial 2: create_pets.g