#========================================
# Phonebook Demo Program
# ----------------------
# this program shows the functionality
# of Unicon ODBC interface
#
# Federico Balbi - fbalbi@cs.utsa.edu
#
# Note:
# -----
# the program has been tested under
# MySQL server and MyODBC ODBC driver
# using a table called "phones" having
# the following columns:
#
# name, phone, address
#
# The code also uses a data source name
# (DSN) called "mysql" added using
# Windows ODBC manager under control
# panel.
# 
#----------------------------------------

# global variables

global db
global user, password

record person(name, phone, address) # database row columns

procedure main()

 write("*** IODBC phonebook ***\n\n")

 login() # get user name and password

 # connect to "mysql" data source
 
 db:=open("mysql","o",user,password)

 if &errornumber~=0 then { # error during login
   write(&errortext)
 }
 else {
   getdbinfo() # print database information

   repeat {

     menu() # print menu options 

     option:=read()
 
     case option of {
       "i": insertphone()
       "d": deletephone()
       "u": updatephone()
       "l": listphones()
       "q": break

       default: write("*** wrong selection ***")
     }
   }
 
   close(db) # close table and database connection
 }

 write("bye")
end


#
# user information
#
procedure login()
 writes("user: ")
 user:=read()
 
 writes("password: ")
 password:=read() # I know, there's echo here
end

#
# get database name and version
#
procedure getdbinfo()
  info:=dbproduct(db)

  write("\nDBMS: ", info["name"])
  write("version: ", info["ver"])
end

#
# display menu options
#
procedure menu()
  write("\nI)nsert")
  write("D)elete")
  write("U)pdate")
  write("L)ist")
  write("Q)uit\n")
end

#
# insert a new record
#
procedure insertphone()

  writes("name: ")
  name:=read()

  writes("phone: ")
  ph:=read()

  writes("address: ")
  addr:=read()

  row:=person(name,ph,addr)

  sql(db,"INSERT INTO phones (name,phone,address) VALUES (" ||
         quote(row["name"]) || "," ||
         quote(row["phone"]) || "," || quote(row["address"]) || ")"

  if &errornumber~= 0 then
    write("*** couldn't insert person ***")
end


#
# remove a record 
#
procedure deletephone()
  writes("name to remove: ")
  name:=read()

  # delete row with specified name column
  sql(db,"DELETE FROM phones WHERE name=" || quote(name))
end

#
# update a record
#
procedure updatephone()
  writes("name to update: ")
  name:=read()

  # select all columns of rows with specified name column

  sql(db,"SELECT * FROM phones WHERE name=" || quote(name))

  if (row:=fetch(db)) then { # data found
    writes("phone (",row["phone"],"): ")
    row["phone"]:=read()

    writes("address (",row["address"],"): ")
    row["address"]:=read()

    sql(db,"UPDATE phones SET phone=" || quote(row["phone"]) ||
           ", address=" || quote(row["address"]) ||
           "WHERE name=" || quote(name)
  }
  else write("\n\n*** person not found ***")
end

#
# list all people in the database
#
procedure listphones()
 # select all columns/rows & sort by name
 sql(db,"SELECT * FROM phones ORDER BY name")

 while(row:=fetch(db)) do { # while data found
   # write row fields
   every i:=(1 to *row) do writes("[",row[i],"]")
   write()
 }
end

#
# quote a string
#
procedure quote(s)
  return "'" || s || "'"
end