This tutorial demonstrates more DataHub ODBC methods. It creates a foods 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.

require ("ODBCSupport");

/* Derive a class from MYSQL just to prove that we can
   do it.  There is no real need in this case, but if we
   want to specialize the connection somehow, we can. */

class FoodsApp
{
    env;
    conn;
    stmt;
}

method FoodsApp.make_row (klass, name, address, phone, entrydate)
{
    local row = new klass();
  
    row.name = name;
    row.address = address;
    row.phone = phone;
    row.entrydate = entrydate;
    .conn.Insert (row);
    row;
}

method FoodsApp.constructor ()
{
    local    cust, sup, mistakes, common;
    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();
  
    /* Drop the test tables.  Ignore errors. */
    try .conn.DropTable ("customers"); catch;
    try .conn.DropTable ("suppliers"); catch;
  
    /* Create new customers and suppliers tables. */
    .conn.CreateTable ("customers",
                       "id INT PRIMARY KEY NOT NULL AUTO_INCREMENT",
                       "name VARCHAR(100) NOT NULL",
                       "address TEXT NULL",
                       "phone TEXT NULL",
                       "entrydate DATE NULL");
    .conn.CreateTable ("suppliers",
                       "id INT PRIMARY KEY NOT NULL AUTO_INCREMENT",
                       "name VARCHAR(100) NOT NULL",
                       "address TEXT NOT NULL",
                       "phone TEXT NULL",
                       "entrydate DATE NULL");
  
    /* Create Gamma classes for Customer and Supplier, using the
       ODBC tables "customers" and "suppliers" as templates for
       the classes. */
    .conn.ClassFromTable (#Customer, nil, "customers");
    .conn.ClassFromTable (#Supplier, nil, "suppliers");
  
    /* Create several customers.  Hang onto one of them for later
       manipulation. */
    cust = .make_row (Customer, "Baker Bob's Pastries",
                      "New York", "111-555-1212", "1999-09-09");
    .make_row (Customer, "Willy Wonka's Chocolate Factory",
               "Somewhere in Kansas", "222-555-1212", "2000-01-01");
    .make_row (Customer, "This is a mistake",
               "Nowhere", "000-000-0000", "2001-02-02");
  
    /* Create several suppliers. */
    .make_row (Supplier, "Sugar Hill \"North\" Farms",
               "Kingston, Jamaica", "333-555-1212", "2002-03-03");
    sup = .make_row (Supplier, "Mack and Molly Milk Farms",
                     "Dublin, Ireland", "777-555-1212", "2003-04-04");
    .make_row (Supplier, "Baker Bob's Pastries",
               "New York", "111-555-1212", "2004-05-05");
    .make_row (Supplier, "Oops, another mistake",
               "Anywhere", "999-555-1212", "2005-06-06");
  
    /* Change the customer phone number for Baker Bob */
    cust.phone = "444-555-1212";
    .conn.Update(cust);
  
    /* Change the city for Mack and Molly */
    sup.address = "Clonmacnoise, Ireland";
    .conn.Update(sup);
  
    /* Remove all customers whose address is Nowhere */
    mistakes = .conn.QueryToClass
        (Customer, "select * from customers where address = 'Nowhere'");
	
    with cust in mistakes do
    {
        princ ("Deleting ", cust.name, "\n");
        .conn.Delete (cust);
    }
  
    /* Remove all suppliers whose name includes the word "mistake" */
    mistakes = .conn.QueryToClass
                (Supplier, "select * from suppliers where name like '%mistake%'");
    with sup in mistakes do
    {
        princ ("Deleting ", sup.name, "\n");
        .conn.Delete (sup);
    }
  
    /* Find all customers who are also suppliers, using the name as
       the comparison field.  Print the results.  Yes, we could have
       retrieved both phone numbers in the original select, but that
       would not be as demonstrative.
     
       Note that column names are allowed to contain spaces and
       characters that are not valid Gamma identifier characters.  To
       work with these column names, you must escape the invalid
       characters within the instance variable identifier.  Look at
       "supplier id" for an example.  Life is easier if you avoid
       this.
    */
  
    common = .conn.QueryToTempClass
        (nil, "select c.id as cid, s.id as 'supplier id', c.name, c.address,
         c.phone from customers c, suppliers s where c.name = s.name");

    with match in common do
    {
        princ (match.name, " is both a customer (id ", match.cid,
               ") and a supplier (id ", match.supplier\ id, ")\n");
        cust = .conn.QueryToClass
               (Customer, string ("select * from customers where id = ", match.cid));
        sup = .conn.QueryToClass
               (Supplier, string ("select * from suppliers where id = ",
                                   match.supplier\ id));
        princ ("  Purchasing phone number is ", cust[0].phone, "\n");
        princ ("  Sales phone number is ", sup[0].phone, "\n");
    }
}

new FoodsApp();

Output

Deleting This is a mistake
Deleting Oops, another mistake
Baker Bob's Pastries is both a customer (id 1) and a supplier (id 3)
  Purchasing phone number is 444-555-1212
  Sales phone number is 111-555-1212