
/* odbc_examp.q: examples for the ODBC interface */

/* This program allows you to create some sample tables in your default
   database (`init' function) and invoke a simple SQL interpreter (`mysql').
   After editing the config information below, try something like `init;
   mysql' and then type some SQL queries. The sample database consists of the
   two tables `pet' and `event' from the MySQL documentation, Chapter 3. */

import odbc;

/* CONFIG SECTION -- Customize this for your local setup. *******************/

/* Here you have to specify the connect string which lets you connect to the
   database you want to use. The details are system-specific; on some systems
   it is sufficient to fill in the DSN field, on others you may have to also
   specify HOST, DATABASE, UID and PWD. See odbc.q for the available options.
   Some examples are provided below. */

def CONNECT = "DSN=myodbc"; // default MySQL database and user
// def CONNECT = "DSN=myodbc;UID=root;PWD="; // specify user and password
// def CONNECT = "DSN=myodbc;DATABASE=test"; // specify the default database
// def CONNECT = "FILEDSN=test.dsn"; // use a .dsn file (Windows)
// def CONNECT = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=test.mdb";
		// connect to an existing MS access database (Windows)

/****************************************************************************/

/* Handle ODBC errors. */

odbc_error MSG STATE	= throw (MSG,STATE);

report ERR		= fprintf ERROR "Error: %s (%s).\n" ERR ||
			  fflush ERROR;

/* The database connection. */

def DB = catch report (odbc_connect CONNECT);

/* Determine the identifier quote char. */
def QCH = bstr $ odbc_getinfo DB SQL_IDENTIFIER_QUOTE_CHAR;

/****************************************************************************/

/* Structure and data for the sample "menagerie" tables. This has been
   pilfered from the MySQL documentation. */

def PET_DESC =
  [("name",	"VARCHAR(20)"),
   ("owner",	"VARCHAR(20)"),
   ("species",	"VARCHAR(20)"),
   ("sex",	"CHAR(1)"),
   ("birth",	"DATE"),
   ("death",	"DATE")],
  EVENT_DESC =
  [("name",	"VARCHAR(20)"),
   ("date",	"DATE"),
   ("type",	"VARCHAR(15)"),
   ("remark",	"VARCHAR(255)")];

def PET_DATA =
  [("Fluffy",	"Harold",	"cat",	 "f",	"1993-02-04",	()),
   ("Claws",	"Gwen",		"cat",	 "m",	"1994-03-17",	()),
   ("Buffy",	"Harold",	"dog",	 "f",	"1989-05-13",	()),
   ("Fang",	"Benny",	"dog",	 "m",	"1990-08-27",	()),
   ("Bowser",	"Diane",	"dog",	 "m",	"1979-08-31",	"1995-07-29"),
   ("Chirpy",	"Gwen",		"bird",	 "f",	"1998-09-11",	()),
   ("Whistler",	"Gwen",		"bird",	 (),	"1997-12-09",	()),
   ("Slim",	"Benny",	"snake", "m",	"1996-04-29",	())],
  EVENT_DATA =
  [("Fluffy",	"1995-05-15",	"litter",	"4 kittens, 3 female, 1 male"),
   ("Buffy",	"1993-06-23",	"litter",	"5 puppies, 2 female, 3 male"),
   ("Buffy",	"1994-06-19",	"litter",	"3 puppies, 3 female"),
   ("Chirpy",	"1999-03-21",	"vet",		"needed beak straightened"),
   ("Slim",	"1997-08-03",	"vet",		"broken rib"),
   ("Bowser",	"1991-10-12",	"kennel",	()),
   ("Fang",	"1991-10-12",	"kennel",	()),
   ("Fang",	"1998-08-28",	"birthday",	"Gave him a new chew toy"),
   ("Claws",	"1998-03-17",	"birthday",	"Gave him a new flea collar"),
   ("Whistler",	"1998-12-09",	"birthday",	"First birthday")];

/* Initialize the tables. Run this once to create the tables and populate them
   with data. */

init			= catch report
			  (init_table "pet" PET_DESC PET_DATA ||
			   init_table "event" EVENT_DESC EVENT_DATA);

init_table NAME DESC DATA
			= sql DB (sprintf "create table %s (%s)"
				  (NAME, fields DESC)) () ||
			  do (sql DB QUERY) DATA
			    where QUERY =
			      sprintf "insert into %s values (%s)"
			      (NAME,params DESC);

fields DESC		= join "," (map (sprintf FORMAT) DESC)
			    where FORMAT = sprintf "%s%%s%s %%s" (QCH,QCH);

params DESC		= join "," (map (cst "?") DESC);

/****************************************************************************/

/* Poor man's SQL interpreter. Prompts you for SQL queries, executes them and
   displays the results. Enter an empty line or end-of-file to quit the
   interpreter. */

mysql			= exec prompt;

prompt			= writes "\nSQL> " || flush || reads;

exec QUERY:String	= catch report (display (msql DB QUERY ())) || mysql
			    if not null QUERY;
exec _			= () otherwise;

/* Display results. */

display SETS:List	= do display_set SETS;

/* Display a result set. */

display_set ROWS:Int	= printf "%d rows affected.\n" ROWS;
display_set RESULTS:List
			= writes "\n" ||
			  // header
			  display_row WIDTHS HEADER ||
			  display_row WIDTHS RULES ||
			  // data
			  do (display_row WIDTHS) DATA ||
			  // footer
			  display_row WIDTHS RULES ||
			  printf "%d rows in set.\n" (#DATA)
			    if not null DATA
			    where WIDTHS = widths RESULTS,
			      [HEADER|DATA] = RESULTS,
			      RULES = (tuple (map rule WIDTHS));
			= writes "Empty set.\n" otherwise;

/* Determine column widths. */

widths RESULTS		= foldl maxwidths INIT RESULTS
			    where INIT = map (cst 0) (list (hd RESULTS));

maxwidths Xs Ys		= zipwith max Xs (map ((#).value_str) (list Ys));

/* Display a data row. */

display_row WIDTHS ROW	= printf "%s\n"
			  (join "   " (zipwith column WIDTHS (list ROW)));

/* Rule of given width. */

rule WIDTH		= cat (map (cst "-") [1..WIDTH]);

/* Format a data value. */

column WIDTH VALUE	= format WIDTH (value_str VALUE);

format WIDTH S		= sprintf (sprintf "%%-%ds" WIDTH) S;

/* Convert data values to strings. */

value_str ()		= "NULL";
value_str VALUE:Real	= str VALUE;
value_str VALUE:String	= VALUE;
value_str _		= "***" otherwise;
