This tutorial demonstrates some of the Gamma-specific MySQL methods. It uses the petdata.txt data file (shown below) to recreate a pets table similar to the one in the MySQL documentation tutorial.

/* Get the Gamma library functions and methods for MySQL. */
require ("MySQLSupport");

/* Set up variables.  To make the example most universal, we define
the host, user, and password as seen below.  These make the database
and tables available to all users, and let them be changed by
anyone. */
host := "localhost";
user := "test";
password := "";
database := "test_pets";

function main ()
{
  
  /* Make a connection to the database by creating a new instance
     of the MYSQL class, and localize all variables.*/
  local	mysql = new MYSQL();
  local	allpets, query, requery, removed_row, newrow, inserted, deleted;
  local query_data, mapped_array, field_array;

  /* Test the connection. */
  if (!mysql.connect (host, user, password))
    error (string ("Could not connect to host: ", host,
		   " as user: ", user));

  /* Remove any previously-created database with the name we want to use
     and then create and select the database.*/
  mysql.query(string("DROP DATABASE IF EXISTS ", database));  
  mysql.query(string("CREATE DATABASE ", database));
  mysql.select_db (database);

  /* Remove any previously-created table called "pets" using the MYSQL.drop_table
     method, in a try/catch statement.  This is essentially the same as issuing
     the command: mysql.query("DROP TABLE IF EXISTS pets");*/
  try mysql.drop_table ("pets"); catch;  

  /* Create the "pets" table, and add columns to it. */
  mysql.create_table("pets");
  mysql.add_column ("pets", "name", "VARCHAR(20)", nil, nil);
  mysql.add_column ("pets", "owner", "VARCHAR(20)", nil, nil);
  mysql.add_column ("pets", "species", "VARCHAR(20)", nil, nil);
  mysql.add_column ("pets", "sex", "CHAR(1)", nil, nil);
  mysql.add_column ("pets", "birth", "DATE", nil, t);
  mysql.add_column ("pets", "death", "DATE", nil, t);

  /* Load the data from the "petdata.txt" file. */
  mysql.query("LOAD DATA LOCAL INFILE 'petdata.txt' INTO TABLE pets");

  /* Test the MYSQL.query_and_store method, and print the whole table. */
  allpets = mysql.query_and_store("SELECT * FROM pets");
  princ("QUERY  The .query_and_store method returns: \n");
  mysql_pretty_print(stdout, allpets);
  
  /* Test the MYSQL.class_from_table method. */
  mysql.class_from_table (#mypetclass, nil, "pets");
  pretty_princ ("\nCLASS  The .class_from_table method ",
		"returns the mypetclass:\n", mypetclass, "\n");

  /* Test the MYSQL.query_to_temp_class method. */
  query = mysql.query_to_temp_class (mypetclass, "select * from pets where sex = 'm'");
  pretty_princ ("\nQUERY  The .query_to_temp_class method applied ",
		"to males in mypetclass returns:\n", query, "\n");
  pretty_princ ("The name of the first pet is: ", query[0].name, "\n");
    
  /* Test the MYSQL.query_to_class method. */
  query = mysql.query_to_class (mypetclass, "select * from pets where sex = 'f'");
  pretty_princ ("\nQUERY  The .query_to_class method applied ",
		"to females in mypetclass returns:\n", query, "\n");
  pretty_princ ("The name of the first pet is: ", query[0].name, "\n");

  /* Create a new row. */ 
  newrow = new(mypetclass);
  newrow.name = "Petunia";
  newrow.owner = "Warner Bros.";
  newrow.species = "pig";
  newrow.sex = "f";
  newrow.birth = "1925-12-25";
  newrow.death = "\N";

  /* Test the MYSQL.insert method. */
  inserted = mysql.insert(newrow);
  pretty_princ ("\nINSERT  After inserting this new row:\n", newrow, "\n");
  pretty_princ ("The .insert method returns:\n", inserted, "\n");
  query = mysql.query_to_class (mypetclass, "select * from pets where sex = 'f'");
  pretty_princ ("And now the .query_to_class method ",
		"applied to females in mypetclass returns:\n", query, "\n");
    
  /* Test the MYSQL.delete method. */
  remove_row = query[0];
  deleted = mysql.delete(remove_row);
  pretty_princ ("\nDELETE  After deleting this row:\n", remove_row, "\n");
  pretty_princ ("The .delete method returns:\n", deleted, "\n");
  query = mysql.query_to_class (mypetclass, "select * from pets where sex = 'f'");
  pretty_princ ("And a query_to_class on females ",
		"in mypetclass now returns:\n", query, "\n");
  pretty_princ ("The name of the first pet is now: ", query[0].name, "\n");
    
  /* Test the MYSQL.requery method. */
  requery = mysql.requery(query[1]);
  pretty_princ ("\nREQUERY  The .requery method applied to the ",
		"second class in the above query returns:\n", requery, "\n");

  /* Test the MYSQL.query_and_store method. */
  query = mysql.query_and_store ("select name, birth from pets"); 
  pretty_princ("\nQUERY  The .query_and_store method applied ",
		"to the 'name' and 'birth' fields in mypetclass ",
	       "\nreturns this MYSQL_RES istance:\n", query, "\n");
  query_data = query.Data();
  pretty_princ("Its data, recovered using ",
	       "the .Data() method, is:\n", query_data, "\n");
    
  princ("\nTest complete.\n");
}

Input file: petsdata.txt

	Fluffy	Harold	cat	f	1993-02-04	\N
	Claws	Gwen	cat	m	1994-03-17	\N
	Buffy	Harold	dog	\N	1989-05-13	\N
	Fang	Benny	dog	m	1990-08-27	\N
	Bowser	Diane	dog	m	1995-08-31	2002-07-29
	Chirpy	Gwen	bird	f	1998-09-11	\N
	Whistler	Gwen	bird	\N	1997-12-09	\N
	Slim	Benny	snake	m	1996-04-29	\N

There is a tab at the beginning of each line, to allow for the auto-insertion of id field values.

Output

QUERY  The .query_and_store method returns: 
id name     owner  species sex birth      death      
-- -------- ------ ------- --- ---------- ---------- 
 1 Fluffy   Harold cat     f   1993-02-04 nil        
 2 Claws    Gwen   cat     m   1994-03-17 nil        
 3 Buffy    Harold dog         1989-05-13 nil        
 4 Fang     Benny  dog     m   1990-08-27 nil        
 5 Bowser   Diane  dog     m   1995-08-31 2002-07-29 
 6 Chirpy   Gwen   bird    f   1998-09-11 nil        
 7 Whistler Gwen   bird        1997-12-09 nil        
 8 Slim     Benny  snake   m   1996-04-29 nil        

CLASS  The .class_from_table method returns the mypetclass:
(defclass mypetclass nil 
[(__columns . #0=[id name owner species sex birth death]) 
 (__primary_key . id) (__table . #1=pets)]
[birth death id name owner sex species])

QUERY  The .query_to_temp_class method applied to males in mypetclass returns:
[{TempQuery (birth . 1994-03-17) (death) (id . 2) (name . Claws)
	    (owner . Gwen) (sex . m) (species . cat)} 
 {TempQuery (birth . 1990-08-27) (death) (id . 4) (name . Fang)
	    (owner . Benny) (sex . m) (species . dog)} 
 {TempQuery (birth . 1995-08-31) (death . 2002-07-29) (id . 5)
	    (name . Bowser) (owner . Diane) (sex . m) (species . dog)} 
 {TempQuery (birth . 1996-04-29) (death) (id . 8) (name . Slim)
	    (owner . Benny) (sex . m) (species . snake)}]
The name of the first pet is: Claws

QUERY  The .query_to_class method applied to females in mypetclass returns:
[{mypetclass (birth . 1993-02-04) (death) (id . 1) (name . Fluffy)
	     (owner . Harold) (sex . f) (species . cat)} 
 {mypetclass (birth . 1998-09-11) (death) (id . 6) (name . Chirpy)
	     (owner . Gwen) (sex . f) (species . bird)}]
The name of the first pet is: Fluffy

INSERT  After inserting this new row:
{mypetclass (birth . 1925-12-25) (death . N) (id . 9) (name . Petunia)
	    (owner . Warner Bros.) (sex . f) (species . pig)}
The .insert method returns:
nil
And now the .query_to_class method applied to females in mypetclass returns:
[{mypetclass (birth . 1993-02-04) (death) (id . 1) (name . Fluffy)
	     (owner . Harold) (sex . f) (species . cat)} 
 {mypetclass (birth . 1998-09-11) (death) (id . 6) (name . Chirpy)
	     (owner . Gwen) (sex . f) (species . bird)} 
 {mypetclass (birth . 1925-12-25) (death . 0000-00-00) (id . 9)
	     (name . Petunia) (owner . Warner Bros.) (sex . f)
	     (species . pig)}]

DELETE  After deleting this row:
{mypetclass (birth . 1993-02-04) (death) (id . 1) (name . Fluffy)
	    (owner . Harold) (sex . f) (species . cat)}
The .delete method returns:
nil
And a query_to_class on females in mypetclass now returns:
[{mypetclass (birth . 1998-09-11) (death) (id . 6) (name . Chirpy)
	     (owner . Gwen) (sex . f) (species . bird)} 
 {mypetclass (birth . 1925-12-25) (death . 0000-00-00) (id . 9)
	     (name . Petunia) (owner . Warner Bros.) (sex . f)
	     (species . pig)}]
The name of the first pet is now: Chirpy

REQUERY  The .requery method applied to the second class in the above query returns:
{mypetclass (birth . 1925-12-25) (death . 0000-00-00) (id . 9)
	    (name . Petunia) (owner . Warner Bros.) (sex . f)
	    (species . pig)}

QUERY  The .query_and_store method applied to the 'name' and 'birth' fields in mypetclass 
returns this MYSQL_RES istance:
{MYSQL_RES (current_field . 0) (eof . 1) (field_count . 2)
	   (fields . [{MYSQL_FIELD (decimals . 0) (def) (flags . 1)
				   (length . 20) (max_length . 8) (name . name)
				   (table . pets) (type . 253)} 
		      {MYSQL_FIELD (decimals . 0) (def) (flags . 0) (length . 10)
			    (max_length . 10) (name . birth) (table . pets) (type . 10)}])}
Its data, recovered using the .Data() method, is:
[[Claws 1994-03-17] [Buffy 1989-05-13] [Fang 1990-08-27] 
 [Bowser 1995-08-31] [Chirpy 1998-09-11] [Whistler 1997-12-09] 
 [Slim 1996-04-29] [Petunia 1925-12-25]]

Test complete.