Appendix E: Scripts

5-1: tracker.sql

-- Create tracker database
CREATE DATABASE IF NOT EXISTS tracker;
USE tracker;
CREATE TABLE IF NOT EXISTS parties(
partyID INT NOT NULL AUTO_INCREMENT,
name CHAR(50) NOT NULL,
companyname CHAR(50) NOT NULL,
PRIMARY KEY (partyID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS roles(
roleID INT NOT NULL AUTO_INCREMENT,
name CHAR(50) NOT NULL,
roleRank SMALLINT NOT NULL,
PRIMARY KEY (roleID),
UNIQUE UC_roleRank (roleRank)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS usecases(
usecaseID INT NOT NULL AUTO_INCREMENT,
name CHAR(50) NOT NULL,
roleID INT NOT NULL,
FOREIGN KEY (roleID) REFERENCES roles (roleID),
PRIMARY KEY (usecaseID),
INDEX roleID (roleID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS users(
userID INT NOT NULL AUTO_INCREMENT,
partyID INT,
username CHAR(10) NOT NULL,
password CHAR(10) NOT NULL,
status SMALLINT NOT NULL,
date_created DATETIME NOT NULL,
date_edited DATETIME,
entered_by INT,
FOREIGN KEY (partyID) REFERENCES parties (partyID),
PRIMARY KEY (userID),
INDEX userpartyID (partyID),
INDEX userenterID (entered_by)) ENGINE=InnoDb;
CREATE  TABLE  IF  NOT  EXISTS userroles(
 userroleID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 roleID INT NOT NULL ,
 userID INT NOT NULL ,
 INDEX ur_roleID( roleID ) ,
 INDEX ur_userID( userID ) ,
 FOREIGN KEY ( roleID ) REFERENCES roles( roleID ) ,
 FOREIGN KEY ( userID ) REFERENCES users( userID )
) ENGINE = InnoDb;
CREATE TABLE IF NOT EXISTS professions(
professionID INT NOT NULL AUTO_INCREMENT,
name CHAR(50) NOT NULL,
description TEXT,
date_created DATETIME NOT NULL,
last_edited DATETIME,
created_by INT NOT NULL,
edited_by INT NOT NULL,
dependsonID INT NOT NULL,
modifiable TINYINT,
PRIMARY KEY (professionID),
INDEX usercreateID (created_by),
INDEX usereditID (edited_by),
INDEX profdependsID (dependsonID),
FOREIGN KEY (created_by) REFERENCES users (userID),
FOREIGN KEY (edited_by) REFERENCES users (userID)
) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS skills(
skillID INT NOT NULL AUTO_INCREMENT,
name CHAR(50) NOT NULL,
description TEXT,
date_created DATETIME NOT NULL,
last_edited DATETIME,
created_by INT NOT NULL,
edited_by INT NOT NULL,
professionID INT,
modifiable TINYINT,
FOREIGN KEY (professionID) REFERENCES professions (professionID),
PRIMARY KEY (skillID),
INDEX skillprofID (professionID)
) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS party_professions(
partyprofID INT NOT NULL AUTO_INCREMENT,
creation_date DATETIME,
partyID INT,
professionID INT,
FOREIGN KEY (partyID) REFERENCES parties (partyID),
FOREIGN KEY (professionID) REFERENCES professions (professionID),
PRIMARY KEY (partyprofID),
INDEX partyID (partyID),
INDEX professionID (professionID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS party_skills(
partyskillID INT NOT NULL AUTO_INCREMENT,
date_created DATETIME,
partyID INT,
skillID INT,
FOREIGN KEY (partyID) REFERENCES parties (partyID),
FOREIGN KEY (skillID) REFERENCES skills (skillID),
PRIMARY KEY (partyskillID),
INDEX pspartyID (partyID),
INDEX psskillID (skillID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS addresstypes(
addresstypeID INT NOT NULL AUTO_INCREMENT,
name CHAR(10),
PRIMARY KEY (addresstypeID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS addresses(
addressID INT NOT NULL AUTO_INCREMENT,
partyID INT,
street_address CHAR(50),
address_detail CHAR(50),
city CHAR(50),
state_prov CHAR(10),
postal_code CHAR(10),
phone_voice CHAR(14),
phone_fax CHAR(14),
phone_cell CHAR(14),
email CHAR(100),
url CHAR(100),
addresstypeID INT,
FOREIGN KEY (addresstypeID) REFERENCES addresstypes (addresstypeID),
FOREIGN KEY (partyID) REFERENCES parties (partyID),
PRIMARY KEY (addressID),
INDEX addrpartyID (partyID),
INDEX addresstypeID (addresstypeID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS contractor_client(
contcliID INT NOT NULL AUTO_INCREMENT,
clientpartyID INT,
contractorpartyID INT,
linktype CHAR(10),
Comment TEXT,
FOREIGN KEY (clientpartyID) REFERENCES parties (partyID),
FOREIGN KEY (contractorpartyID) REFERENCES parties (partyID),
PRIMARY KEY (contcliID),
INDEX contractorpartyID (contractorpartyID),
INDEX clientpartyID (clientpartyID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS projects(
projectID INT NOT NULL AUTO_INCREMENT,
name CHAR(50),
contcliID INT,
date_created DATETIME,
created_by INT,
date_edited DATETIME,
edited_by INT,
FOREIGN KEY (contcliID) REFERENCES contractor_client (contcliID),
FOREIGN KEY (created_by) REFERENCES users (userID),
FOREIGN KEY (edited_by) REFERENCES users (userID),
PRIMARY KEY (projectID),
INDEX projcontcliID (contcliID),
INDEX projcreateID (created_by),
INDEX projeditID (edited_by)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS budgitemtypes(
budgtypeID INT NOT NULL AUTO_INCREMENT,
code CHAR(10),
description TEXT,
PRIMARY KEY (budgtypeID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS tasktypes(
tasktypeID INT NOT NULL AUTO_INCREMENT,
name CHAR(50),
PRIMARY KEY (tasktypeID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS project_tasks(
taskID INT NOT NULL AUTO_INCREMENT,
name CHAR(50),
description TEXT,
date_to_start DATETIME,
date_to_finish DATETIME,
start_date DATETIME,
completion_date DATETIME,
status SMALLINT,
projectID INT,
tasktypeID INT,
created_by INT,
date_created DATETIME,
edited_by INT,
date_edited DATETIME,
FOREIGN KEY (projectID) REFERENCES projects (projectID),
FOREIGN KEY (tasktypeID) REFERENCES tasktypes (tasktypeID),
FOREIGN KEY (created_by) REFERENCES users (userID),
FOREIGN KEY (edited_by) REFERENCES users (userID),
PRIMARY KEY (taskID),
INDEX projtaskID (projectID),
INDEX projtasktypeID (tasktypeID),
INDEX taskcreateID (created_by),
INDEX taskeditID (edited_by)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS budgetitems(
budgitemID INT NOT NULL AUTO_INCREMENT,
amount DECIMAL(10,2),
external_code CHAR(25),
description TEXT,
date_created DATETIME,
created_by INT,
budgtypeID INT,
date_edited DATETIME,
edited_by INT,
taskID INT,
FOREIGN KEY (budgtypeID) REFERENCES budgitemtypes (budgtypeID),
FOREIGN KEY (taskID) REFERENCES project_tasks (taskID),
FOREIGN KEY (created_by) REFERENCES users (userID),
FOREIGN KEY (edited_by) REFERENCES users (userID),
PRIMARY KEY (budgitemID),
INDEX budgitemcreateID (created_by),
INDEX budgitemeditID (edited_by),
INDEX budgtypeID (budgtypeID),
INDEX taskbudgID (taskID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS chargetypes(
chargetypeID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (chargetypeID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS charges(
chargeID INT NOT NULL AUTO_INCREMENT,
external_code CHAR(25),
transaction_date DATE,
enter_date DATETIME,
description TEXT,
chargetypeID INT,
budgitemID INT,
correctionID INT,
FOREIGN KEY (budgitemID) REFERENCES budgetitems (budgitemID),
FOREIGN KEY (chargetypeID) REFERENCES chargetypes (chargetypeID),
PRIMARY KEY (chargeID),
INDEX chgtypeID (chargetypeID),
INDEX budgitemID (budgitemID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS suspense(
suspenseID INT NOT NULL AUTO_INCREMENT,
external_code CHAR(25),
transaction_date DATE,
enter_date DATETIME,
description TEXT,
chargetypeID INT,
budgitemID INT,
created_by INT,
edited_by INT,
FOREIGN KEY (created_by) REFERENCES users (userID),
FOREIGN KEY (edited_by) REFERENCES users (userID),
PRIMARY KEY (suspenseID),
INDEX suspcreateID (created_by),
INDEX suspeditID (edited_by)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS charge_corrections(
correctionID INT NOT NULL AUTO_INCREMENT,
external_code CHAR(25),
transaction_date DATE,
enter_date DATETIME,
description TEXT,
created_by INT,
suspenseID INT,
FOREIGN KEY (suspenseID) REFERENCES suspense (suspenseID),
FOREIGN KEY (created_by) REFERENCES users (userID),
PRIMARY KEY (correctionID),
INDEX corrcreateID (created_by),
INDEX suspID (suspenseID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS invoices(
invoiceID INT NOT NULL AUTO_INCREMENT,
invdate DATE,
invamount DECIMAL(10,2),
fedtax DECIMAL(10,2),
statetax DECIMAL(10,2),
interest DECIMAL(10,2),
date_created DATETIME,
external_ref CHAR(25),
created_by INT,
edited_by INT,
FOREIGN KEY (created_by) REFERENCES users (userID),
FOREIGN KEY (edited_by) REFERENCES users (userID),
PRIMARY KEY (invoiceID),
INDEX invcreateID (created_by),
INDEX inveditID (edited_by)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS invoiceitems(
invitemID INT NOT NULL AUTO_INCREMENT,
amount DECIMAL(10,2),
external_code CHAR(25),
description TEXT,
date_created DATETIME,
created_by INT,
date_edited DATETIME,
edited_by INT,
taskID INT,
invoiceID INT,
FOREIGN KEY (invoiceID) REFERENCES invoices (invoiceID),
FOREIGN KEY (taskID) REFERENCES project_tasks (taskID),
PRIMARY KEY (invitemID),
INDEX invtaskID (taskID),
INDEX invitemID (invoiceID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS pmt_types(
pmttypeID INT NOT NULL AUTO_INCREMENT,
name CHAR(10),
number_len SMALLINT,
PRIMARY KEY (pmttypeID)) ENGINE=InnoDb;
CREATE TABLE IF NOT EXISTS payments(
paymentID INT NOT NULL AUTO_INCREMENT,
pmtdate DATE,
pmtamount DECIMAL(10,2),
date_created DATETIME,
date_edited DATETIME,
external_ref CHAR(25),
pmt_code CHAR(25),
expiry CHAR(5),
pmt_aux_code CHAR(10),
description TEXT,
invoiceID INT,
pmttypeID INT,
FOREIGN KEY (invoiceID) REFERENCES invoices (invoiceID),
FOREIGN KEY (pmttypeID) REFERENCES pmt_types (pmttypeID),
PRIMARY KEY (paymentID),
INDEX pmtinvID (invoiceID),
INDEX pmttypID (pmttypeID)) ENGINE=InnoDb;

5-2: tracker_pop.sql

USE tracker;
INSERT INTO parties VALUES (1, 'Arthur Fuller', 'Artful Development');
INSERT INTO parties VALUES (2, 'Peter Brawley', 'Artful Software Development');
INSERT INTO parties VALUES (3, 'Fred Campbell', '');
INSERT INTO parties VALUES (4, 'Sam Spade', 'Literary Detectives Inc');
INSERT INTO parties VALUES (5, 'Jeanne Moreau', 'Exotic Voices Inc');
INSERT INTO parties VALUES (6, 'Edith Piaf', 'Great French Singers Inc');
INSERT INTO parties VALUES (2005, 'Sean O\'Brien', 'Pols Inc');
INSERT INTO parties VALUES (2006, 'Ansel Adams', 'Art Photos Inc');
INSERT INTO parties VALUES (2007, 'Ansel Adams', 'Photography Inc');
INSERT INTO parties VALUES (2008, 'Ansel Adams', 'Many Photos Inc.');
INSERT INTO parties VALUES (2009, 'Ansel Adams', 'New Photos Inc.');
INSERT INTO parties VALUES (2010, 'Ansel Adams', 'Photo Inc');
INSERT INTO parties VALUES (2011, 'Ansel Adams', 'Photo Inc');
INSERT INTO parties VALUES (2012, 'Ansel Adams', 'Photo Inc');
INSERT INTO parties VALUES (2013, 'Stephen Bishop', 'Music Inc');
INSERT INTO users VALUES (1, 2, 'webauth', 'webauth', 1, '2024-07-04 05:15:15', '2024-07-04 05:15:15', 1);
INSERT INTO users VALUES (2, 2, 'webauth', 'webauth', 1, '2024-07-04 18:12:50', '2024-07-04 18:12:50', 1);
INSERT INTO professions VALUES (1, 'Software Developer', NULL, '2024-07-08 21:20:16', '2024-07-08 21:19:56', 2, 2, 0, NULL);
INSERT INTO professions VALUES (2, 'Programmer', NULL, '2024-07-08 21:22:16', '2024-07-08 21:21:58', 2, 2, 0, NULL);
INSERT INTO professions VALUES (3, 'Software Architect', NULL, '2024-07-08 21:23:14', '2024-07-08 21:22:49', 2, 2, 0, NULL);
INSERT INTO professions VALUES (4, 'Accountant', NULL, '2024-07-08 21:26:42', '2024-07-08 21:26:20', 2, 2, 0, NULL);
INSERT INTO skills VALUES
(1,'MySQL database architect','MySQL database architect',
'2025-02-11 22:35:34','2025-02-11 22:35:34',1,0,3,NULL),
(2,'PHP Developer','PHP application programmer and developer','2025-02-11 22:37:27','2025-02-11 22:37:27',1,0,1,NULL),
(3,'MS Access Developer','Access database program architect and developer','0000-00-00 00:00:00','2025-02-11 22:38:16',0,0,1,NULL),
(4,'C programmer','C programmer','2025-02-11 00:00:00','2025-02-11 22:39:44',0,0,2,NULL),
(5,'Sybase database developer','Database architect and developer for Sybase Adaptive Server','2025-02-11 00:00:00','2025-02-11 22:40:36',0,0,3,NULL),
(6,'DBA','Database architect','2025-02-17 00:00:00','2025-02-17 15:07:17',0,0,1,NULL),
(7,'DotNet Developer','MS DotNet developer','2025-01-04 00:00:00','2025-02-17 14:59:24',0,0,1,NULL),
(8,'Accountant','Accountant','2025-02-17 00:00:00','2025-02-17 15:27:10',1,0,4,NULL);
INSERT INTO party_skills VALUES (1, '2025-02-17 15:09:28', 1, 1);
INSERT INTO party_skills VALUES (2, '2025-02-17 00:00:00', 2, 2);
INSERT INTO party_skills VALUES (3, '2025-02-17 00:00:00', 3, 1);
INSERT INTO party_skills VALUES (4, '2025-02-17 00:00:00', 3, 8);
INSERT INTO party_skills VALUES (5, '2025-02-17 00:00:00', 4, 6);
INSERT INTO party_skills VALUES (6, '2025-02-17 00:00:00', 5, 6);
INSERT INTO party_skills VALUES (7, '2025-02-17 00:00:00', 6, 3);
INSERT INTO roles VALUES (1, 'owner', 1);
INSERT INTO roles VALUES (2, 'manager', 2);
INSERT INTO roles VALUES (3, 'clerk', 3);
# INSERT INTO views VALUES (1, 'parties_skillls', 'SELECT \r\n  skills.name AS skill,\r\n  skills.description AS Description,\r\n  parties.name AS \'Party Name\',\r\n  parties.companyname AS Company\r\nFROM party_skills\r\n  LEFT JOIN parties ON party_skills.partyID = parties.partyID\r\n  LEFT JOIN skills ON party_skills.skillID = skills.skillID\r\nORDER BY skills.name ASC \r\nLIMIT 0,5', ' ', 2, 2024-08-01 11:54:52, 0, '0000-00-00 00:00:00', 0);
# INSERT INTO views VALUES (2, 'parties_skillls_modifiable', 'SELECT\r\n  party_skills.partyskillID AS ID,\r\n  skills.skillID AS SkillID,\r\n  skills.name AS skill,\r\n  skills.description AS Description,\r\n  parties.partyID AS PartyID,\r\n  parties.name AS \'Party Name\',\r\n  parties.companyname AS Company\r\nFROM party_skills\r\n  LEFT JOIN parties ON party_skills.partyID = parties.partyID\r\n  LEFT JOIN skills ON party_skills.skillID = skills.skillID\r\nORDER BY skills.name ASC \r\n LIMIT 0,10  ', ' ', 2, 2024-09-14 16:10:30, 1, '0000-00-00 00:00:00', 0);
DELIMITER |
CREATE PROCEDURE `padminlogin`(
  IN uname varchar(16),IN upass char(32),OUT admin_id smallint,OUT err_chk varchar(32)
)
BEGIN
  SELECT userid INTO admin_id
  FROM users
  WHERE username = uname;
  IF admin_id IS NULL THEN
    SET err_chk = 'username unknown';
  END IF;
END |
CREATE PROCEDURE `parties_skills_list`()
  SELECT
    skills.name AS skill,
    skills.description AS Description,
    parties.name AS 'Party Name',
    parties.companyname AS Company
  FROM party_skills
    LEFT JOIN parties ON party_skills.partyID = parties.partyID
    LEFT JOIN skills ON party_skills.skillID = skills.skillID
  ORDER BY skills.name ASC |
DELIMITER ;

6-1: parents.sql

6-2: outerjoins.sql

8-1: Stored Procedure Intro

8-2: DECLARE ... HANDLER in an SP

8-3:IF...THEN... in an SP

8-4: CASE...WHEN...ENDCASE in an SP

8-5: DO .. REPEAT

8-6: DO ... WHILE ...

8-7: Cursor, handler, loop and iteration in an SP

9-1: EXISTS example

9-2: Logical reduction

11-1:  NWIB_Create.sql

11-2: Northwind_Create.sql (no InnoDB tables)

12-1: docs database

12-2: docs.php, doc.php

<!-- docs.php -->
<html>
<head>
  <title>Docs</title>
  <style>
    p { color: #990000; }
  </style>
</head>
<body>
<?php
require_once( "session_start.php" );
if( isset( $_GET['table'] )) {
  $url = "doc.php?_sess=" . $_GET['_sess'] . "¬estopic;=" . $_GET['table'];
  echo "<script language='JavaScript'>parent.</script>";
} else {
  $url = "";
}
// SUBSTITUTE YOUR USERNAME & PASSWORD:
$conn = mysql_pconnect( "localhost", "USR", "PWD" )
        or die( "Failed to connect to MySQL server" );
mysql_select_db( "docs", $conn ) or die( "Could not select docs database" );
$sql = "SELECT table_name FROM information_schema.tables " .
       "WHERE table_schema = 'docs' AND table_name <> 'docs'";
$res = mysql_query( $sql );
// TABLE-VIEW SELECTOR FORM
$tbl = (isset( $_SESSION['table'] )) ? $_SESSION['table'] : "";
echo "<br><form name='viewForm' id='prompt' action='docs.php' method='GET'>\n";
echo "  <INPUT type='hidden' name='_sess' value='$session_name'>\n";
echo "  <p><b>Select docs table:</b></p> \n";
echo "  <SELECT name='table' onChange='submit()'>\n";
while( $row = mysql_fetch_row( $res )) {
  $sel = ( $tbl === $row[0] ) ? "id='sel' selected" : "";
  printf( "    <option %s value='%s'>%s</option>\n", $sel, $row[0], $row[0] );
}
echo "  </select>\n";
echo "  <input id='table' type='button' value='Select a table' onClick='submit()'>\n";
?>
</body></html>
<?php
// doc.php
require_once( "session_continue.php" );
if( isset(  $_GET["notestopic"] )) {
  $_SESSION['db']     = 'docs';
  $_SESSION['hdr']    = $_GET["notestopic"];
  $_SESSION['table']  = $_GET["notestopic"];
  $_SESSION['keycol'] = "topic";
  $_SESSION['txtcol'] = "text";
}
echo "<html><head><title>NoteMan</title></head><body>\n";
if( !isset( $_SESSION['db'] )) die( "NoteMan: incorrect configuration" );
require_once( "noteman.php" );
echo "</body></html>\n";
?>

12-3: noteman.php

<html>
<head>
  <title><?php $_SESSION['hdr'] . "Notes" ?></title>
  <style>
    body, p, form, option, select, input, textarea { color: #3300ff; }
    p { width: 50em; }
    h3 { color: #990000; }
    .code { width:200%; font-size:8pt; font-family:courier; }
  </style>
  <script>
  function cancel( f, url ) {
    f.action=url;
    f.submit();
  }
  </script>
</head>
<body>
<?php
// noteman.php
// included in doc.php so session_contunue call needed
if( isset( $_GET['back'] )) header( "Location: docs.php" );
else if( isset( $_POST['back'] )) header( "Location: docs.php" );
$db    = $_SESSION['db'];
$hdr   = $_SESSION['hdr'];
$table = $_SESSION['table'];
$url   = $_SERVER['PHP_SELF'];
$urlreturn = $url."?_sess=".$session_name;
// CONNECT
$conn  = mysql_connect( "localhost", "webauth", "webauth" );
mysql_select_db( $db, $conn ) or exit( "Cannot connect to $db database" );
// ID
if( isset( $_POST['id'] )) $_SESSION['id'] = $_POST['id'];
else if( isset( $_GET['id'] )) $_SESSION['id'] = $_GET['id'];
else if( !isset( $_SESSION['id'] )) $_SESSION['id'] = 0;
$id = $_SESSION['id'];
echo "<font color='#990000'><b><i>Please select a $hdr topic...</i></b></font>\n";
// ACTION/TOPIC SELECTOR FORM. RESULT POSTS TO SELF.
// echo "<FORM action='$urlreturn' METHOD='POST'>\n";
echo "<FORM action='$url' METHOD='GET'>\n";
echo "<INPUT type='hidden' name='_sess' value='$session_name'>\n";
echo "<SELECT name='id' SIZE='8' onChange='submit()'>\n";
$query = "SELECT topic,id FROM $table ORDER BY topic";
$result = mysql_query( $query, $conn );
if ( $myrow = mysql_fetch_array( $result )) {
  $spc = "       ";
  do {
    $sel = ( $myrow[1] == $id ? "SELECTED='SELECTED'" : "" );
    printf( "<option %s value='%s'>%s %s</option>\n", $sel, $myrow[1], $myrow[0], $spc );
  } while ($myrow = mysql_fetch_array($result));
}
echo "</SELECT><p></p>\n";
echo "<INPUT type='Submit' name='view'   value='View'>   
      <INPUT type='Submit' name='viewer' value='Read in Viewer'>   
      <INPUT type='Submit' name='edit'   value='Edit'>   
      <INPUT type='Submit' name='add'    value='Add'>   \n";
echo "<INPUT type='Submit' name='back'   value='Back to Docs'>\n";
echo "</FORM>\n"; // END FORM
// DEAL WITH USER CHOICE
if( isset( $_GET['viewer'] )) {
  $viewer = $_GET['viewer'];
} else {
  $viewer = 0;
}
if( isset( $_GET['view'] )) {
  $view = $_GET['view'];
} else {
  $view = 0;
}
if( isset( $_GET['cancel'] )) {    // ALWAYS A GET
  $edit = 0;
} else if( isset( $_GET['edit'] )) {
  $edit = $_GET['edit'];
} else {
  $edit = 0;
}
if( isset( $_GET['add'] )) {
  $add = $_GET['add'];
} else {
  $add = 0;
}
if (isset( $_GET['topic'] )) {
  $topic = $_GET['topic'];
}
// VIEW IS THE DEFAULT
if( $edit || $add || $viewer ) {
  $view = 0;
  $_SESSION['view'] = 0;
} else {
  $view = 'View';
  $_SESSION['view'] = $view;
}
// ADD TOPIC
if ($add) {
  echo "<FORM action='notesins.php' method='POST'>\n";
  echo "<INPUT type='hidden' name='_sess' value=\"$session_name\"'>\n";
  echo "Topic: <INPUT name='topic' size=72>\n";
  echo "<br>Text:<br>\n";
  echo "<TEXTAREA name='text' rows=20 cols=96>\n";
  echo "";
  echo "</TEXTAREA>\n";
  echo "<p></p>\n";
  echo "<INPUT type='hidden' name='table' value=$table>";
  echo "<INPUT type='submit' name='update' value='Update'>";
  echo "  \n";
  echo "<input type='button' value='Cancel' onClick='cancel(this.form,\"$urlreturn\")'>\n";
  echo "</FORM>\n";
}
elseif( $id ) {
  $query = "SELECT topic,text FROM $table WHERE id=$id";
  $result = mysql_query( $query, $conn );
  if( $result ) {
    $myrow = mysql_fetch_array( $result );
    $topic = $myrow[0];
    $text  = $myrow[1];
  }
  // VIEW TOPIC TEXT
  if ($view) {
    echo "<h3 font-color='#990000'>".$topic."</h3>\n";
    if( $myrow ) {
   // echo "<p style='width:50em;'>" . nl2br( $myrow[0] ) . "</p>\n";
   // echo "<p style='width:50em;'>" . $myrow[0] . "</p>\n";
      echo "<pre width='100'>" . $text . "</pre>\n";
    }
  // SEND TO THE VIEWER
  } elseif( $viewer ) {
    if( $myrow ) {
      $_SESSION['txt'] = $text;
      $_SESSION['title'] = $topic;
      $url = "txtbrows/txtbrows.php?_sess=".$session_name;
      echo "<script language='JavaScript'>parent.</script>";
      // echo $url,"<br/>\n";
      // header( "Location: $url" );
    }
  // EDIT TOPIC TEXT
  } elseif( $edit ) {
      if ( $myrow ) {
        echo "<FORM action='notesupd.php' method='POST'>\n";
        echo "<INPUT type='hidden' name='_sess' value=\"$session_name\"'>\n";
        echo "<font color='#990000'><b>Header: <INPUT name='topic' value='$topic' size=72></b></font><br/>\n";
        echo "<TEXTAREA name='text' rows=20 cols=96>\n";
        echo $text;
        echo "</TEXTAREA>\n";
        echo "<p></p>\n";
        echo "<INPUT type='hidden' name='table' value=$table>\n";
        echo "<INPUT type='hidden' name='id'    value=$id>\n";
        echo "<INPUT type='submit' name='update' value='Save'>  \n";
        echo "<INPUT type='button' value='Cancel' onClick='cancel(this.form,\"$urlreturn\")'>\n";
        echo "</FORM>\n";
      } // fetch loop
  } // edit loop
} // topic loop
mysql_close();
?>
</body>
</html>
#EOF

12-4: notesins.php

<?
// notesins.php
require_once( "session_continue.php" );
$conn = mysql_pconnect( "localhost", "webauth", "webauth" );
mysql_select_db( $_SESSION['db'], $conn );
// Initialise vars from POST
$table = $_POST['table'];
$topic = $_POST['topic'];
$text  = $_POST['text'];
$url   = "doc.php?_sess=$session_name";
if( isset( $_POST['update'] )) {
  // ESCAPE QUOTES, INSERT:
  $text = addslashes( str_replace( "<", "<", $text ));
  $topic = addslashes( $topic );
  $sql = "INSERT INTO $table (topic,text) VALUES('$topic','$text')";
  $result = mysql_query($sql);
  if ($result) {
    echo "Thank you! Information entered.<br>\n";
    $_SESSION['topic'] = $topic;
    $_SESSION['view']  = 'View';
    gohome();
  }
  else {
    echo mysql_error();
    echo "Click <a href='$url'>here</a> to return to ", "$table.";
  }
}
else {
  gohome();
}
function gohome() {
  GLOBAL $url;
  echo "<html><head>";
  echo "<script language='JavaScript'>parent.</script>";
  echo "</body></html>";
}
?>
#EOF

12-5: notesupd.php

<?
// notesupd.php
require_once( "session_continue.php" );
$conn = mysql_pconnect( "localhost", "webauth", "webauth" )
        or exit( "Cannot connect to localhost: " . mysql_error() );
mysql_select_db( $_SESSION['db'], $conn ) or exit( "Cannot select $db: " . mysql_error() );
// Initialise vars from POST
$table  = $_POST['table'];
$id     = $_POST['id'];
$topic  = $_POST['topic'];
$text   = $_POST['text'];
$update = $_POST['update'];
$url    = "doc.php?_sess=$session_name";
if( $id ) {
  // ESCAPE QUOTES, UPDATE:
  $topic = addslashes( $topic);
  $text = addslashes( str_replace( "<", "<", $text ));
  $sql = "UPDATE $table SET topic='$topic',text='$text' WHERE id=$id";
  // STUB
  // printf( "<PRE>Query = " . "$sql" . "</PRE><br>\n" );
  $result = mysql_query($sql);
  if ($result) {
    // THIS PAGE IS INVISIBLE IF UPDATE SUCCEEDS
    $_SESSION['id'] = $id;
    $_SESSION['view'] = 'View';
    echo "Thank you! Information entered.<br>\n";
    go_home();
  }
  else {
    // ERROR REPORT
    echo "<html><head>Update error page</head><body>";
    echo mysql_error()."<BR>";
    echo "Could not complete the update.<br>";
    echo "Click the <i>Back</i> button to return to $table.";
    echo "</body></html>";
  }
}
else {
  go_home();
}
function go_home() {
  GLOBAL $url;
  echo "<html><head>";
  echo "<script language='JavaScript'>parent.</script>";
  echo "</head><body>Click <a href='$url'>here</a> to return to ", "$table.";
  echo "</body></html>";
}
?>

12-6: tracker.inc

12-8: theUsual for PHP mysqli

13-1: parties.pl

13-2: parties_web.pl

13-3: parties_web_array.pl

13-4: insprep.pl

13-5: parties_web_strict.pl

14-1: LoadDriver.java

14-2: RetrieveFromMySQL.java

14-3: MySqlQuery.jsp

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>

<%--
    I offer the following pattern for JDBC applications.
    By using try/finally you avoid the null state for conn, stmt, and rs.
    They will always close except when the thread dies.
    Limiting the declaration scope of stmt and rs has benefits.
--%>
<html>
<head><b><i>MySqlQuery.Jsp</b></i></head>
<body>
<br>
<%
// PAGE-WIDE EXCEPTION HANDLER
try {
  // REGISTER THE DRIVER
  Class driverClass = com.mysql.jdbc.Driver.class;
  java.sql.Connection conn = java.sql.DriverManager.getConnection(
                             "jdbc:mysql://localhost/mysql?user=USR&password;=PSWD" );
  // HANDLE EXCEPTIONS FROM CREATING STATEMENT
  try {
    java.sql.Statement stmt = conn.createStatement();
    // HANDLE EXCEPTIONS FROM EXECUTING QUERY
    try {
      String sql = "SELECT Host, User, Select_priv, Insert_priv, " +
                   "Update_priv, Delete_priv, Create_priv " +
                   "FROM mysql.user ORDER BY user";
      java.sql.ResultSet rs = stmt.executeQuery( sql );
%>
  <!-- HTML TABLE HEADERS -->
  <h2>Database: mysql   Table: user</h2>
  <table width="80%" border="2">
  <tr>
    <th>Host</th>
    <th>User</th>
    <th>Select</th>
    <th>Insert</th>
    <th>Update</th>
    <th>Delete</th>
    <th>Create</th>
  </tr>
  <!-- END RAW HTML BLOCK -->
<%
      // RESUME JAVA. POPULATE HTML TABLE WITH ALL COLS & ROWS
      // HANDLE EXCEPTIONS PROCESSING RESULT
      try {
        // getColumnCount() protects against column count coding errors
        int columnCount = rs.getMetaData().getColumnCount();
        while( rs.next() ) {
          out.println( "" );
          // zero based
          for( int i = 0; i < columnCount; i++ ) {
            out.println( "  " + rs.getString( i+1 ) + "" );
          }
          out.println( "" );
        }
        out.println( "" );
      }
      finally {
        rs.close();
      }
    }
    finally {
      stmt.close();
    }
  }
  finally {
    conn.close();
  }
// CATCH PAGE-WIDE EXCEPTIONS
} catch( Exception x ) {
  // MAKE STACK TRACE READABLE
  out.println("
");
  if ( x instanceof java.sql.SQLException ) {
    out.println( "SQLException: " + ((java.sql.SQLException)x).getMessage());
    out.println( "SQLState:     " + ((java.sql.SQLException)x).getSQLState());
  }
  x.printStackTrace( new java.io.PrintWriter( out ));
  out.println( "
" );
  // TO DEBUG, UNCOMMENT:
  // throw (x);
}
%>
</body>
</html>

14-4: server.xml connection pool entry

14-5: web.xml connection pool entry

14-6: parties.jsp

14-7: dsQuery.jsp

15-1: HelloWorld.aspx

15-2: Form1.cs for tracker_test

15-3: master_detail window using ODBC

15-4: master_detail_direct.cs

Same app, for Connector/NET and Visual Studio 2005

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace master_detail_conn {
  public partial class GridForm : Form {
    MySqlDataAdapter daMaster, daDetail;
    MySqlCommandBuilder cmdMas, cmdDet;
    private BindingSource bindSrcMaster = new BindingSource();
    private BindingSource bindSrcDetail = new BindingSource();
    DataSet ds;
    DataRelation relCustOrd;
    string sConn, sMasterTbl, sDetailTbl;
    public GridForm() {
      InitializeComponent();
      // CUSTOM SPECS BEGIN: EDIT USR & PWD TO SUIT
      sConn = "server=127.0.0.1;uid=USR;pwd=PWD;database=nwib;";
      sMasterTbl = "Customers";
      sDetailTbl = "Orders";
      string sMasterKey = "customerID";
      string sDetailKey = sMasterKey;
      string sRelationName = sMasterTbl + sDetailTbl;
      string sqlMaster = "SELECT * FROM " + sMasterTbl;
      string sqlDetail = "SELECT * FROM " + sDetailTbl;
      // CUSTOM SPECS END
      try {
        MySqlConnection oConn;
        oConn = new MySqlConnection(sConn);
        oConn.Open();
        statusMessages.Text = "Connected";
        dataGridView1.DataSource = bindSrcMaster;
        dataGridView2.DataSource = bindSrcDetail;
        ds = new DataSet();
        daMaster = new MySqlDataAdapter(sqlMaster, oConn);
        cmdMas = new MySqlCommandBuilder(daMaster);
        daMaster.Fill(ds, sMasterTbl);
        daDetail = new MySqlDataAdapter(sqlDetail, oConn);
        cmdDet = new MySqlCommandBuilder(daDetail);
        daDetail.Fill(ds, sDetailTbl);
        ds.Relations.Clear();
        relCustOrd = new DataRelation(
                           sRelationName,
                           ds.Tables[sMasterTbl].Columns[sMasterKey],
                           ds.Tables[sDetailTbl].Columns[sDetailKey]);
        ds.Relations.Add(relCustOrd);
        bindSrcMaster.DataSource = ds;
        bindSrcMaster.DataMember = sMasterTbl;
        bindSrcDetail.DataSource = bindSrcMaster;
        bindSrcDetail.DataMember = sRelationName;
        bindingNavigator1.BindingSource = bindSrcMaster;
        this.Load += new System.EventHandler(GridForm_Load);
        this.btnUpdate.Click += new System.EventHandler(btnUpdate_Click);
      }
      catch (Exception e) {
        MessageBox.Show(e.Message);
      }
    }
    private void btnUpdate_Click(object sender, System.EventArgs e) {
      try {
        if (ds.HasChanges()) {
          daMaster.Update(ds, sMasterTbl);
          daDetail.Update(ds, sDetailTbl);
          statusMessages.Text = "Updated";
        }
        else {
          statusMessages.Text = "No changes to write";
        }
      }
      catch (Exception ex) {
        MessageBox.Show(ex.Message);
      }
    }
    private void GridForm_Load(object sender, System.EventArgs e) {
      dataGridView1.AutoResizeColumns();
      dataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
    }
  }
}
// EOF

15-5: PagingDataGrid.aspx

<%@ Page Language="C#" Debug="true" %>
<%@ Register TagPrefix="Custom" Namespace="CustomControls" Assembly="CustomControls" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Drawing" %>
<%@ import Namespace="MySql.Data.MySqlClient" %>
<script runat="server">
    MySqlConnection mySqlConnection =
       new MySqlConnection( "Host=localhost;Database=nwib;User Id=USR;Password=PWD" );
    MySqlCommand mySqlCommand;
    MySqlDataAdapter mySqlDataAdapter;
    DataSet dataSet;
    int firstRow;
    int rowCount;
    string sql;
    string sOrderBy = "";
    override protected void OnInit(EventArgs e) {
      base.OnInit(e);
      sql = "SELECT customerID,CompanyName,ContactName,ContactTitle,City,Country FROM customers ";
      bFirst.Visible = false;
      bPrevious.Visible = false;
      bNext.Visible = false;
      bLast.Visible = false;
    }
    void Page_Load(object sender, EventArgs e) {
      tbSQL.Text = sql;
      GridFill();
    }
    void GridFill() {
      try{
        mySqlConnection.Open();
        rowCount = querySize();
        // GET/SET ORDERBY AND FIRSTROW VIEWSTATES, BUILD SQL SUFFIX
        if( ViewState["sOrderBy"] != null ) {
          sOrderBy = ViewState["sOrderBy"].ToString();
        } else {
          ViewState["sOrderBy"] = sOrderBy;
        }
        if( ViewState["firstRow"] != null ) {
          firstRow = int.Parse( ViewState["firstRow"].ToString() );
        } else {
          firstRow = 0;
          ViewState["firstRow"] = firstRow;
        }
        string sqlSuffix = OrderByClause() + LimitClause();
        tbSQL.Text = sql + System.Environment.NewLine + sqlSuffix;
        mySqlCommand = new MySqlCommand( tbSQL.Text );
        mySqlCommand.Connection = mySqlConnection;
        dataSet = new DataSet();
        mySqlDataAdapter = new MySqlDataAdapter();
        mySqlDataAdapter.SelectCommand = mySqlCommand;
        mySqlDataAdapter.Fill(dataSet, "Table");
        dataGrid.DataSource = dataSet.Tables["Table"];
        DataBind();
        if( rowCount > dataGrid.PageSize ) {
          bFirst.Visible = true;
          bPrevious.Visible = true;
          bNext.Visible = true;
          bLast.Visible = true;
        }
      } catch (Exception exception) {
        lbError.Text = exception.Message;
      }
    }
    protected string OrderByClause() {
      string s = ( sOrderBy == "" ? "" : "ORDER BY " + sOrderBy + " " );
      return s;
    }
    void GridSort( object sender, DataGridSortCommandEventArgs e ) {
      string s = e.SortExpression;
      if( ViewState["sOrderBy"] == null ) {
        ViewState["sOrderBy"] = s;
      } else if( s == ViewState["sOrderBy"].ToString() ) {
        ViewState["sOrderBy"] = s + " DESC";
      } else {
        ViewState["sOrderBy"] = s;
      }
      GridFill();
    }
    protected string LimitClause() {
      string s = " LIMIT " + firstRow.ToString() + "," +
                 dataGrid.PageSize.ToString();
      return s;
    }
    int querySize() {
      int n;
      string s;
      string sqlCount = "SELECT COUNT(*) FROM (" + tbSQL.Text + ") AS qry";
      MySqlCommand cmd = new MySqlCommand( sqlCount );
      cmd.Connection = mySqlConnection;
      s = cmd.ExecuteScalar().ToString();
      n = int.Parse( s ) ;
      return n;
    }
    void bFirst_Click( object sender, EventArgs e ) {
      firstRow = 0;
      ViewState["firstRow"] = firstRow;
      GridFill();
    }
    void bPrevious_Click( object sender, EventArgs e ) {
      firstRow = System.Math.Max( firstRow - dataGrid.PageSize, 0 );
      ViewState["firstRow"] = firstRow;
      GridFill();
    }
    void bNext_Click( object sender, EventArgs e ) {
      firstRow = System.Math.Min( firstRow + dataGrid.PageSize,
                                  rowCount - dataGrid.PageSize );
      ViewState["firstRow"] = firstRow;
      GridFill();
    }
    void bLast_Click( object sender, EventArgs e ) {
      firstRow = System.Math.Max( rowCount - dataGrid.PageSize, 0 );
      ViewState["firstRow"] = firstRow;
      GridFill();
    }
</script>
<html>
<head>
</head>
<body>
  <form id="Form1" method="post" runat="server">
    <table id="Table1" style="Z-INDEX: 104; LEFT: 10px; WIDTH: 700px; POSITION: absolute; TOP: 17px" cellspacing="5" cellpadding="0" bgcolor="#ccccff">
      <tbody>
        <tr>
          <td>
            <asp:Label id="lbTitle" runat="server" font-names="Verdana" font-size="12pt" enableviewstate="False" font-bold="True" forecolor="Navy"> Paging
              DataGrid </asp:Label></td>
        </tr>
      </tbody>
    </table>
    <asp:Label id="Label2" style="Z-INDEX: 106; LEFT: 9px; POSITION: absolute; TOP: 64px" runat="server" font-names="Verdana" font-size="10pt" enableviewstate="False" font-bold="True" forecolor="Navy">SQL</asp:Label>
    <asp:textbox id="tbSQL" style="Z-INDEX: 101; LEFT: 9px; POSITION: absolute; TOP: 81px; Height:89; Width:554" runat="server" Font-Names="Courier New" wrap="False" textmode="MultiLine"></asp:textbox>
    <asp:Label id="lbError" style="Z-INDEX: 104; LEFT: 13px; POSITION: absolute; TOP: 178px" runat="server" font-names="Verdana" font-size="10pt" enableviewstate="False" font-bold="True" forecolor="Red"></asp:Label>
    <p>
    <br />
    <br />
    <asp:datagrid id="dataGrid" style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 214px" runat="server" Font-Names="Verdana" Width="700px" OnSortCommand="GridSort" PageSize="5" Font-Size="8pt" CellPadding="3" BorderColor="Black" BackColor="#CCCCFF" AllowSorting="True">
      <HeaderStyle backcolor="#AAAADD"></HeaderStyle>
    </asp:datagrid>
    </p>
    <asp:LinkButton id="bNext" style="Z-INDEX: 100; LEFT: 200px; POSITION: absolute; TOP: 352px" onclick="bNext_Click" runat="server">Next</asp:LinkButton>
    <asp:LinkButton id="bPrevious" style="Z-INDEX: 101; LEFT: 120px; POSITION: absolute; TOP: 352px" onclick="bPrevious_Click" runat="server">Previous</asp:LinkButton>
    <asp:LinkButton id="bFirst" style="Z-INDEX: 102; LEFT: 42px; POSITION: absolute; TOP: 352px" onclick="bFirst_Click" runat="server">First</asp:LinkButton>
    <asp:LinkButton id="bLast" style="Z-INDEX: 103; LEFT: 280px; POSITION: absolute; TOP: 352px" onclick="bLast_Click" runat="server">Last</asp:LinkButton>
    <br />
    <br />
  </form>
  <br />
  <br />
</body>
</html>

15-6: PagingDataGrid.aspx for VS 2005

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Drawing" %>
<%@ import Namespace="MySql.Data.MySqlClient" %>
<script runat="server">
    MySqlConnection mySqlConnection = new MySqlConnection( "Host=localhost;Database=nwib;User Id=USR;Password=PWD" );
    MySqlCommand mySqlCommand;
    MySqlDataAdapter mySqlDataAdapter;
    DataSet dataSet;
    int firstRow;
    int rowCount;
    string sql = "";
    string sOrderBy = "";
    override protected void OnInit(EventArgs e) {
      base.OnInit(e);
      sql = tbSQL.Text + OrderByClause() + LimitClause();
      bFirst.Visible = false;
      bPrevious.Visible = false;
      bNext.Visible = false;
      bLast.Visible = false;
    }
    protected void tbSQL_TextChanged(object sender, EventArgs e) {
      sql = tbSQL.Text + OrderByClause() + LimitClause();
    }
    private void Page_Load(object sender, EventArgs e) {
      GridFill();
    }
    void GridFill() {
      try {
        mySqlConnection.Open();
        sql = tbSQL.Text;
        rowCount = querySize();
        // GET/SET ORDERBY AND FIRSTROW VIEWSTATES, BUILD SQL SUFFIX
        if (ViewState["sOrderBy"] != null) {
          sOrderBy = ViewState["sOrderBy"].ToString();
        } else {
            ViewState["sOrderBy"] = sOrderBy;
        }
        if (ViewState["firstRow"] != null) {
            firstRow = int.Parse(ViewState["firstRow"].ToString());
        } else {
            firstRow = 0;
            ViewState["firstRow"] = firstRow;
        }
        string sqlSuffix = OrderByClause() + LimitClause();
        sql += System.Environment.NewLine + sqlSuffix;
        mySqlCommand = new MySqlCommand(sql);
        mySqlCommand.Connection = mySqlConnection;
        dataSet = new DataSet();
        mySqlDataAdapter = new MySqlDataAdapter();
        mySqlDataAdapter.SelectCommand = mySqlCommand;
        mySqlDataAdapter.Fill(dataSet, "Table");
        dataGrid.DataSource = dataSet.Tables["Table"];
        DataBind();
        if (rowCount > dataGrid.PageSize) {
          bFirst.Visible = true;
          bPrevious.Visible = true;
          bNext.Visible = true;
          bLast.Visible = true;
        }
      } catch (Exception exception) {
          lbError.Text = exception.Message;
      } finally {
          mySqlConnection.Close();
      }
    }
    protected string OrderByClause() {
      string s = ( sOrderBy == "" ? "" : "ORDER BY " + sOrderBy + " " );
      return s;
    }
    void GridSort( object sender, DataGridSortCommandEventArgs e ) {
      string s = e.SortExpression;
      if( ViewState["sOrderBy"] == null ) {
        ViewState["sOrderBy"] = s;
      } else if( s == ViewState["sOrderBy"].ToString() ) {
        ViewState["sOrderBy"] = s + " DESC";
      } else {
        ViewState["sOrderBy"] = s;
      }
      GridFill();
    }
    protected string LimitClause() {
      string s = " LIMIT " + firstRow.ToString() + "," + dataGrid.PageSize.ToString();
      return s;
    }
    int querySize() {
      int n;
      string s;
      string sqlCount = "SELECT COUNT(*) FROM (" + tbSQL.Text + ") AS qry";
      MySqlCommand cmd = new MySqlCommand( sqlCount );
      cmd.Connection = mySqlConnection;
      s = cmd.ExecuteScalar().ToString();
      n = int.Parse( s ) ;
      return n;
    }
    void bFirst_Click( object sender, EventArgs e ) {
      firstRow = 0;
      ViewState["firstRow"] = firstRow;
      GridFill();
    }
    void bPrevious_Click( object sender, EventArgs e ) {
      firstRow = System.Math.Max( firstRow - dataGrid.PageSize, 0 );
      ViewState["firstRow"] = firstRow;
      GridFill();
    }
    void bNext_Click( object sender, EventArgs e ) {
      firstRow = System.Math.Min( firstRow + dataGrid.PageSize, rowCount - dataGrid.PageSize );
      ViewState["firstRow"] = firstRow;
      GridFill();
    }
    void bLast_Click( object sender, EventArgs e ) {
      firstRow = System.Math.Max( rowCount - dataGrid.PageSize, 0 );
      ViewState["firstRow"] = firstRow;
      GridFill();
    }
    void bRefresh_Click(object sender, EventArgs e) {
      int i;
      sql = tbSQL.Text;
      i = sql.IndexOf( "LIMIT " );
      if( i > 0 ) sql = sql.Substring( 0, i-1 );
      tbSQL.Text = sql;
      sql += OrderByClause() + LimitClause();
      firstRow = 0;
      GridFill();
    }
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Paging DataGrid with Connector/NET</title>
</head>
<body  runat="server">
    <form id="Form2" method="post" runat="server">
        <table id="Table1" style="Z-INDEX: 104; LEFT: 10px; WIDTH: 700px; POSITION: absolute; TOP: 17px" cellspacing="5" cellpadding="0" bgcolor="#ccccff">
            <tbody>
                <tr>
                    <td>
                        <asp:Label id="lbTitle" runat="server" forecolor="Navy" font-bold="True" enableviewstate="False" font-size="12pt" font-names="Verdana"> Paging
                        DataGrid </asp:Label></td>
                </tr>
            </tbody>
        </table>
        <asp:Label id="Label2" style="Z-INDEX: 106; LEFT: 9px; POSITION: absolute; TOP: 64px" runat="server" forecolor="Navy" font-bold="True" enableviewstate="False" font-size="10pt" font-names="Verdana">SQL (edit, then click the <i>Refresh</i> linkbutton)</asp:Label>
        <asp:textbox id="tbSQL" style="Z-INDEX: 101; LEFT: 12px; POSITION: absolute; TOP: 82px" runat="server" textmode="MultiLine" wrap="False" Font-Names="Courier New" Width="554" Height="89" OnTextChanged="tbSQL_TextChanged">SELECT * FROM nwib.orders</asp:textbox>
        <asp:Label id="lbError" style="Z-INDEX: 104; LEFT: 13px; POSITION: absolute; TOP: 178px" runat="server" forecolor="Red" font-bold="True" enableviewstate="False" font-size="10pt" font-names="Verdana"></asp:Label>
        <p>
            <br />
            <br />
            <asp:datagrid id="dataGrid" style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 214px" runat="server" Font-Names="Verdana" Width="700px" AllowSorting="True" BackColor="#CCCCFF" BorderColor="Black" CellPadding="3" Font-Size="8pt" PageSize="5" OnSortCommand="GridSort">
                <HeaderStyle backcolor="#AAAADD"></HeaderStyle>
            </asp:datagrid>
        </p>
        <asp:LinkButton id="bNext" style="Z-INDEX: 100; LEFT: 200px; POSITION: absolute; TOP: 550px" onclick="bNext_Click" runat="server">Next</asp:LinkButton>
        <asp:LinkButton id="bPrevious" style="Z-INDEX: 101; LEFT: 120px; POSITION: absolute; TOP: 550px" onclick="bPrevious_Click" runat="server">Previous</asp:LinkButton>
        <asp:LinkButton id="bFirst" style="Z-INDEX: 102; LEFT: 42px; POSITION: absolute; TOP: 550px" onclick="bFirst_Click" runat="server">First</asp:LinkButton>
        <asp:LinkButton id="bLast" style="Z-INDEX: 103; LEFT: 280px; POSITION: absolute; TOP: 550px" onclick="bLast_Click" runat="server">Last</asp:LinkButton>
        <asp:LinkButton ID="bRefresh" runat="server" Height="19px" style="Z-INDEX: 104; LEFT: 360px; POSITION: absolute; TOP: 550px" onclick="bRefresh_Click">Refresh</asp:LinkButton>
        <br />
        <br />
    </form>
    <br />
    <br />
</body>
</html>

16-1: mysqlqry.c

16-2: mysqlqry.h

16-3: mysqlqry.cpp for Visual C++

16-4: stdafx.h for mysqlqry.cpp

19-1: security_create_admin_owner.sql

USE tracker;
DROP PROCEDURE IF EXISTS security_create_admin_owner;
DELIMITER |
CREATE PROCEDURE security_create_admin_owner(
  tracker_usr CHAR( 10), usr CHAR(16), pswd CHAR(41), hst CHAR(60))
  BEGIN
    DECLARE s CHAR( 10 ) DEFAULT NULL;
    DECLARE s2 CHAR( 10 ) DEFAULT NULL;
    DECLARE s3 CHAR(77);
    SELECT username
      INTO s
      FROM users
      WHERE username = tracker_usr;
    IF ISNULL( s ) = 0 THEN
      SELECT user
      INTO s2
      FROM mysql.user
      WHERE user = s;
      IF ISNULL( s ) = 0 THEN
        SET s = CONCAT_WS( ’@’, usr, hst );
        GRANT ALL ON tracker.* TO s IDENTIFIED BY ’pswd’
          WITH GRANT OPTION;
      END IF;
    END IF;
  END;
|
DELIMITER ;
// EOF

3-1: Earlier breadth-first treewalk algorithm

DELIMITER go
CREATE PROCEDURE ListDescendants( ancestor CHAR(20) )
BEGIN
  DECLARE rows, iLevel, iMode INT DEFAULT 0;
  -- create temp tables
  DROP TEMPORARY TABLE IF EXISTS descendants,nextparents,prevparents;
  CREATE TEMPORARY TABLE descendants( childID INT, parentID INT, level INT );
  CREATE TEMPORARY TABLE nextparents ( parentID INT );
  CREATE TEMPORARY TABLE prevparents LIKE nextparents;
  -- seed nextparents
  IF ancestor RLIKE '[:alpha:]+' THEN      -- ancestor passed as a string
    INSERT INTO nextparents SELECT id FROM family WHERE name=ancestor;
  ELSE
    SET iMode = 1;                         -- ancestor passed as a numeric
    INSERT INTO nextparents VALUES( CAST( ancestor AS UNSIGNED ));
  END IF;
  SET rows = ROW_COUNT();
  WHILE rows > 0 DO
    -- add children of nextparents
    SET iLevel = iLevel + 1;
    INSERT INTO descendants
      SELECT t.childID, t.parentID, iLevel
      FROM familytree AS t
      INNER JOIN nextparents USING(parentID);
    SET rows = ROW_COUNT();
    -- save nextparents to prevparents
    TRUNCATE prevparents;
    INSERT INTO prevparents
      SELECT * FROM nextparents;
    -- next parents are children of these parents:
    TRUNCATE nextparents;
    INSERT INTO nextparents
      SELECT childID FROM familytree JOIN prevparents USING (parentID);
    SET rows = rows + ROW_COUNT();
  END WHILE;
  -- result
  IF iMode = 1 THEN
    SELECT CONCAT(REPEAT( ' ', level), parentID ) As Parent, GROUP_CONCAT(childID) AS Child
    FROM descendants GROUP BY parentID ORDER BY level;
  ELSE
    SELECT CONCAT(REPEAT( ' ', level), PersonName(parentID) ) As Parent, PersonName(childID) AS Child
    FROM descendants;
  END IF;
END
go
DELIMITER ;