Chapter 2. Tutorials

This tutorial demonstrates some of the DataHub ODBC methods. It creates a pets database and performs various types of queries on it.

The Code

The complete code for this tutorial is shown below. You can copy this code into a new file and run it as a script. Please refer to Copying a complete tutorial in the DataHub Scripting manual for details on how to do this.

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

class PetsApp
{
    env;
    conn;
    stmt;
}

method PetsApp.GetTables ()
{
    local    result = .stmt.Tables(nil, nil, nil, "TABLE");
    local    indx;
	
    if (result != SQL_SUCCESS)
       error (.stmt.GetDiagRec());
    result = .stmt.GetResultData();
    indx = result.ColumnIndex ("TABLE_NAME");
    with table in result.rows result = collect table[indx];
    result;
}

method PetsApp.CreatePet (name, owner, species, sex, birth, death)
{
    local    pet = new Pet ();
    pet.name = name;
    pet.owner = owner;
    pet.species = species;
    pet.sex = sex;
    pet.birth = birth;
    pet.death = death;
    .conn.Insert (pet);
    pet;
}

method PetsApp.constructor ()
{
    local    allpets, query, requery, removed_row, newrow, inserted, deleted;
    local    query_data, mapped_array, field_array;
    local    mypetclass;

    /* 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. */

    DSN := "Pets Test Database";
    user := "test";
    password := "test";

    /* Create the ODBC environment and connection */
    .env = ODBC_AllocEnvironment();
    .conn = .env.AllocConnection();
	
    /* Attempt the connection. */
    ret = .conn.Connect (DSN, user, password);
    if (ret != SQL_SUCCESS)
        error (.conn.GetDiagRec());

    /* Allocate a statement object */
    .stmt = .conn.AllocStatement();
	
    /* Remove any previously-created database tables */
    with table in .GetTables() do
        .conn.DropTable (table);
	
    /* Create the "pets" table, and add columns to it.  Note the
       AUTO_INCREMENT directive for the primary key.  You will need
       to change this depending on your back-end RDBMS.  This example
       uses MySQL.  For a Microsoft database, use "IDENTITY". */
    .conn.CreateTable ("pets",
                       "id INT PRIMARY KEY NOT NULL AUTO_INCREMENT",
                       "name VARCHAR(20) NOT NULL",
                       "owner VARCHAR(20) NOT NULL",
                       "species VARCHAR(20) NOT NULL",
                       "sex CHAR(1) NOT NULL",
                       "birth DATE NULL",
                       "death DATE NULL");

    /* Create a class from the table.  We will use this as a
       convenient way to deal with row data as instances of a
       class.  We will call the class "Pet" and hereafter it
       will exist as an instantiable class.  We can also refer to
       the class by its local variable assignment "mypetclass" */
    mypetclass = .conn.ClassFromTable (#Pet, nil, "pets");
    
    /* Create the table data. */
    .CreatePet ("Fluffy", "Harold", "cat", "f", "1993-02-04", nil);
    .CreatePet ("Claws", "Gwen", "cat", "m", "1994-03-17", nil);
    .CreatePet ("Buffy", "Harold", "dog", "f", "1989-05-13", nil);
    .CreatePet ("Fang", "Benny", "dog", "m", "1990-08-27", nil);
    .CreatePet ("Bowser", "Diane", "dog", "m", "1995-08-31", "2002-07-29");
    .CreatePet ("Chirpy", "Gwen", "bird", "f", "1998-09-11", nil);
    .CreatePet ("Whistler", "Gwen", "bird", "m", "1997-12-09", nil);
    .CreatePet ("Slim", "Benny", "snake", "m", "1996-04-29", nil);
    
    /* Test the QueryAndStore method, and print the whole table. */
    allpets = .conn.QueryAndStore ("SELECT * FROM pets");
    princ("QUERY  The QueryAndStore method returns: \n");
    pretty_print (allpets);
    terpri ();

    /* Test the QueryToTempClass method. */
    query = .conn.QueryToTempClass (mypetclass, "select * from pets where sex = 'm'");
    pretty_princ ("\nQUERY  The QueryToTempClass 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 QueryToClass method. */
    query = .conn.QueryToClass (mypetclass, "select * from pets where sex = 'f'");
    pretty_princ ("\nQUERY  The QueryToClass 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 = nil;

    /* Test the Insert method. */
    inserted = .conn.Insert(newrow);
    pretty_princ ("\nINSERT  After inserting this new row:\n", newrow, "\n");
    pretty_princ ("The Insert method returns:\n", inserted, "\n");
	
    query = .conn.QueryToClass (mypetclass, "select * from pets where sex = 'f'");
    pretty_princ ("And now the QueryToClass method ",
                  "applied to females in mypetclass returns:\n", query, "\n");
    
    /* Test the Delete method. */
    remove_row = query[0];
    deleted = .conn.Delete(remove_row);
    pretty_princ ("\nDELETE  After deleting this row:\n", remove_row, "\n");
    pretty_princ ("The Delete method returns:\n", deleted, "\n");
    query = .conn.QueryToClass (mypetclass, "select * from pets where sex = 'f'");
    pretty_princ ("And a QueryToClass 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 Requery method. */
    requery = .conn.ReQuery(query[1]);
    pretty_princ ("\nREQUERY  The ReQuery method applied to the ",
                  "second class in the above query returns:\n", requery, "\n");

    /* Test the QueryAndStore method. */
    query = .conn.QueryAndStore ("select name, birth from pets"); 
    pretty_princ("\nQUERY  The QueryAndStore method applied ",
                 "to the 'name' and 'birth' fields in mypetclass ",
                 "\nreturns this ODBCResult instance:\n", query, "\n");

    pretty_princ("Its data is ", query.rows, "\n");
    
    princ("\nTest complete.\n");
}

new PetsApp();

Output

QUERY  The QueryAndStore method returns: 
{ODBCResult (columns . [{ODBCColumn (columnsize . 1) (datatype . 4)
				    (decimaldigits . 0) (name . "id") (nullable . 1)} 
			{ODBCColumn (columnsize . 8) (datatype . 12) (decimaldigits . 0)
			(name . "name") (nullable . 0)} 
			{ODBCColumn (columnsize . 6) (datatype . 12) (decimaldigits . 0)
			(name . "owner") (nullable . 0)} 
			{ODBCColumn (columnsize . 5) (datatype . 12) (decimaldigits . 0)
			(name . "species") (nullable . 0)} 
			{ODBCColumn (columnsize . 1) (datatype . 1) (decimaldigits . 0)
			(name . "sex") (nullable . 0)} 
			{ODBCColumn (columnsize . 10) (datatype . 91) (decimaldigits . 0)
			(name . "birth") (nullable . 1)} 
			{ODBCColumn (columnsize . 10) (datatype . 91) (decimaldigits . 0)
			(name . "death") (nullable . 1)}])
	    (rows . [[1 "Fluffy" "Harold" "cat" "f" "1993-02-04" nil] 
		     [2 "Claws" "Gwen" "cat" "m" "1994-03-17" nil] 
		     [3 "Buffy" "Harold" "dog" "f" "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" "m" "1997-12-09" nil] 
		     [8 "Slim" "Benny" "snake" "m" "1996-04-29" nil]])}

QUERY  The QueryToTempClass 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 . 1997-12-09) (death) (id . 7) (name . Whistler)
	    (owner . Gwen) (sex . m) (species . bird)} 
 {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 QueryToClass applied to females in mypetclass returns:
[{Pet (birth . 1993-02-04) (death) (id . 1) (name . Fluffy)
      (owner . Harold) (sex . f) (species . cat)} 
 {Pet (birth . 1989-05-13) (death) (id . 3) (name . Buffy)
      (owner . Harold) (sex . f) (species . dog)} 
 {Pet (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:
{Pet (birth . 1925-12-25) (death) (id . 9) (name . Petunia)
     (owner . Warner Bros.) (sex . f) (species . pig)}
The Insert method returns:
{ODBCResult (columns . []) (rows . [])}
And now the QueryToClass method applied to females in mypetclass returns:
[{Pet (birth . 1993-02-04) (death) (id . 1) (name . Fluffy)
      (owner . Harold) (sex . f) (species . cat)} 
 {Pet (birth . 1989-05-13) (death) (id . 3) (name . Buffy)
      (owner . Harold) (sex . f) (species . dog)} 
 {Pet (birth . 1998-09-11) (death) (id . 6) (name . Chirpy)
      (owner . Gwen) (sex . f) (species . bird)} 
 {Pet (birth . 1925-12-25) (death) (id . 9) (name . Petunia)
      (owner . Warner Bros.) (sex . f) (species . pig)}]

DELETE  After deleting this row:
{Pet (birth . 1993-02-04) (death) (id . 1) (name . Fluffy)
     (owner . Harold) (sex . f) (species . cat)}
The Delete method returns:
{ODBCResult (columns . []) (rows . [])}
And a QueryToClass on females in mypetclass now returns:
[{Pet (birth . 1989-05-13) (death) (id . 3) (name . Buffy)
      (owner . Harold) (sex . f) (species . dog)} 
 {Pet (birth . 1998-09-11) (death) (id . 6) (name . Chirpy)
      (owner . Gwen) (sex . f) (species . bird)} 
 {Pet (birth . 1925-12-25) (death) (id . 9) (name . Petunia)
      (owner . Warner Bros.) (sex . f) (species . pig)}]
The name of the first pet is now: Buffy

REQUERY  The ReQuery method applied to the second class in the above query returns:
{Pet (birth . 1998-09-11) (death) (id . 6) (name . Chirpy)
     (owner . Gwen) (sex . f) (species . bird)}

QUERY  The QueryAndStore method applied to the 'name' and 'birth' fields in mypetclass 
returns this ODBCResult instance:
{ODBCResult (columns . [{ODBCColumn (columnsize . 8) (datatype . 12)
				    (decimaldigits . 0) (name . name) (nullable . 0)} 
			{ODBCColumn (columnsize . 10) (datatype . 91) (decimaldigits . 0)
			(name . birth) (nullable . 1)}])
	    (rows . [[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]])}
Its data 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.