##-
# Author: Brian Tiffin
# Dedicated to the public domain
#
# Date: September, 2016
# Modified: 2016-10-03/22:47-0400
##+
#
# dbcolumns.icn, ODBC table column information
#
# tectonics: ~/.odbc.ini setup required for [unicon] 
#            assuming the [unicon] ODBC setup, SQLite3
#
link ximage

procedure main()
    # mode 'o' open, ODBC SQL, default table and connection at defaults 
    db := open("unicon", "o", "", "") | stop("no ODBC for \"unicon\"")

    # Information below was created as part of examples/odbc.icn
    # sql(db, "create table contacts (id integer primary key, name, phone)")

    # display ODBC view of table schema
    write("\ndbtables:")     
    every write(ximage(dbtables(db)))

    # show dbcolumns information from first table
    tables := dbtables(db)
    write("\ndbcolumns.", tables[1].name, ":")     
    every write(ximage(dbcolumns(db, tables[1].name)))

    # access some of the reflective record data
    write()
    write("First column")
    dbc := dbcolumns(db, tables[1].name)
    write("tablename: ", dbc[1].tablename,
          ", colname: ", dbc[1].colname,
          ", type: ", dbc[1].typename)

    write("Second column")
    write("tablename: ", dbc[2].tablename,
          ", colname: ", dbc[2].colname)

    # generate a query
    write()
    write("Query for ", dbc[2].colname, " (using dbc[2].colname)")
    sql(db, "select " || dbc[2].colname || " from " || dbc[2].tablename)

    # I know my name is first, but Jafar deserves the press
    rec := fetch(db)
    rec := fetch(db)

    # skipping generic info now, the column is known and it is 'name'  
    write("Access name field from fetched record")
    write("Name: ", rec.name)
    
    close(db)
end