Get It Done With MySQL 5&6, Appendix E. Copyright © Peter Brawley and Arthur Fuller 2010. All rights reserved.

TOC    Previous    Next   

Appendix E: Scripts

3-1: Install MySQL binary on Linux 5-1: tracker.sql 5-2: tracker_pop.sql
6-1: parents.sql 6-2: outerjoins.sql 8-1: stored_procedure_intro.sql
8-2: declare_handler.sql 8-3: if_then.sql 8-4: case_when.sql
8-5: dorepeat.sql 8-6: do_while.sql 8-7: loop_until.sql
9-1: exists.sql 9-2: logical_reduction.sql 11-1: NWIB_create.sql
11-2: Northwind_create.sql 12-1: create_docs.sqp 12-2: docs.php, doc.php
12-3: NoteMan 12-4: notesins.php 12-5: notesupd.php
12-6: tracker.inc 12-7: theUsual for mysql API 12-8: theUsual for mysqli API
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 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 master_detail window using ODBC
15-4: master_detail_direct.cs 15-5: PagingDataGrid.aspx 15-6: PagingDataGrid.aspx for VS 2005
16-1: mysqlqry.c 16-2: mysqlqry.h 16-3: mysqlqry.cpp
16-4: stdfax.h 19-1: security_create_admin_owner.sql 20-1: Earlier breadth-first treewalk algorithm

3-1: Install MySQL binary on Linux

groupadd mysql
useradd -g mysql mysql
cd /usr/local
gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
ln -s mysql-VERSION-OS mysql
cd mysql
scripts/mysql_install_db
chown -R root  /usr/local/mysql
chown -R mysql /usr/local/mysql/data
chgrp -R mysql /usr/local/mysql
chown -R root /usr/local/mysql/bin

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;


# EOF


     

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, '2004-07-04 05:15:15', '2004-07-04 05:15:15', 1);
INSERT INTO users VALUES (2, 2, 'webauth', 'webauth', 1, '2004-07-04 18:12:50', '2004-07-04 18:12:50', 1);

INSERT INTO professions VALUES (1, 'Software Developer', NULL, '2004-07-08 21:20:16', '2004-07-08 21:19:56', 2, 2, 0, NULL);
INSERT INTO professions VALUES (2, 'Programmer', NULL, '2004-07-08 21:22:16', '2004-07-08 21:21:58', 2, 2, 0, NULL);
INSERT INTO professions VALUES (3, 'Software Architect', NULL, '2004-07-08 21:23:14', '2004-07-08 21:22:49', 2, 2, 0, NULL);
INSERT INTO professions VALUES (4, 'Accountant', NULL, '2004-07-08 21:26:42', '2004-07-08 21:26:20', 2, 2, 0, NULL);

INSERT INTO skills VALUES
(1,'MySQL database architect','MySQL database architect',
'2004-02-11 22:35:34','2004-02-11 22:35:34',1,0,3,NULL),
(2,'PHP Developer','PHP application programmer and developer','2004-02-11 22:37:27','2004-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','2004-02-11 22:38:16',0,0,1,NULL),
(4,'C programmer','C programmer','2004-02-11 00:00:00','2004-02-11 22:39:44',0,0,2,NULL),
(5,'Sybase database developer','Database architect and developer for Sybase Adaptive Server','2004-02-11 00:00:00','2004-02-11 22:40:36',0,0,3,NULL),
(6,'DBA','Database architect','2004-02-17 00:00:00','2004-02-17 15:07:17',0,0,1,NULL),
(7,'DotNet Developer','MS DotNet developer','2004-01-04 00:00:00','2004-02-17 14:59:24',0,0,1,NULL),
(8,'Accountant','Accountant','2004-02-17 00:00:00','2004-02-17 15:27:10',1,0,4,NULL);

INSERT INTO party_skills VALUES (1, '2004-02-17 15:09:28', 1, 1);
INSERT INTO party_skills VALUES (2, '2004-02-17 00:00:00', 2, 2);
INSERT INTO party_skills VALUES (3, '2004-02-17 00:00:00', 3, 1);
INSERT INTO party_skills VALUES (4, '2004-02-17 00:00:00', 3, 8);
INSERT INTO party_skills VALUES (5, '2004-02-17 00:00:00', 4, 6);
INSERT INTO party_skills VALUES (6, '2004-02-17 00:00:00', 5, 6);
INSERT INTO party_skills VALUES (7, '2004-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, 2004-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, 2004-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

code begins
-- parents.sql
-- Simple tables used in chapters 6 & 9.

USE test;

CREATE TABLE parent(
   id INT UNSIGNED NOT NULL PRIMARY KEY,
   lastname CHAR(20) NULL,
   firstname CHAR(20) NULL
) ENGINE=innodb;

CREATE TABLE child (
   id INT UNSIGNED NOT NULL PRIMARY KEY,
   parent_id INT UNSIGNED NOT NULL,
   lastname CHAR(20) NULL,
   firstname CHAR(20) NULL,
   INDEX ( parent_id ),
   FOREIGN KEY ( parent_id ) REFERENCES parent(id)
) ENGINE=innodb;

CREATE TABLE grandchild (
   id INT UNSIGNED NOT NULL PRIMARY KEY,
   child_id INT UNSIGNED NOT NULL,
   lastname CHAR(20) NULL,
   firstname CHAR(20) NULL,
   INDEX ( child_id ),
   FOREIGN KEY ( child_id ) REFERENCES child(id)
) ENGINE=innodb;

INSERT INTO parent VALUES (1,'Tom','Jones'), (2,'Janet','Jones'), (3,'Bill','Smith');
INSERT INTO child VALUES (1,1,'Val','Jones'), (2,2,'Kim','Jones'), (3,2,'Karen','Jones');
INSERT INTO grandchild VALUES(1,1,'Sarah','Jones');

# EOF

  
 

6-2: outerjoins.sql

 # outerjoins.sql

USE test;
DROP TABLE IF EXISTS A;
DROP TABLE IF EXISTS B;
DROP TABLE IF EXISTS C;
DROP TABLE IF EXISTS D;
DROP TABLE IF EXISTS E;
CREATE TABLE A (a INT);
CREATE TABLE B (b INT);
CREATE TABLE C (c INT);
CREATE TABLE D (d INT);
CREATE TABLE E (e INT);
INSERT INTO A VALUES (1);
INSERT INTO B VALUES (1),(2);
INSERT INTO C VALUES (1),(2),(3);
INSERT INTO D VALUES (1),(2),(3),(4);
INSERT INTO E VALUES (1),(2),(3),(4),(5);
SELECT 'LEFT JOIN A AND B, LEFT JOIN RESULT AND C' AS '';
SELECT * FROM A
  LEFT JOIN B ON a = b
  LEFT JOIN C ON b = c;
SELECT 'LEFT JOIN A AND B, RIGHT JOIN C AND RESULT' AS '';
SELECT * FROM A
  LEFT JOIN B ON a = b
  RIGHT JOIN C ON c = b;
SELECT 'LEFT JOIN E AND D, RESULT AND C' AS '';
SELECT * FROM E
  LEFT JOIN D ON e = d
  LEFT JOIN C ON e = c;
SELECT 'LEFT JOIN E AND D, RIGHT JOIN C AND RESULT' AS '';
SELECT * FROM E
  LEFT JOIN D ON e = d
  RIGHT JOIN C ON c = e;

# EOF
  
 

8-1: Stored Procedure Intro

-- stored procedures
-- paste this script into the mysql client

USE test;
DELIMITER |                      // permit semi-colons within SP

DROP PROCEDURE IF EXISTS worldmesgproc |
CREATE PROCEDURE worldmesgproc( IN s CHAR( 10 ) )
SELECT CONCAT_WS( ' ', s, 'world!' );
|

DROP FUNCTION IF EXISTS worldmesgfunc |
CREATE FUNCTION worldmesgfunc( s CHAR( 10 ) ) RETURNS CHAR( 20 )
RETURN CONCAT_WS( s, 'world!' );
|

DELIMITER ;                     // restore semi-colon as delimiter

CALL worldmesgproc( 'Hello' );
Hello world!
SELECT worldmesgfunc( 'Hello' );
Hello world!

CALL worldmesgfunc( 'Hi' );     // funcs cannot be called
ERROR 1289 at line 18: PROCEDURE worldmesgfunc does not exist

DROP PROCEDURE worldmesgproc;
DROP FUNCTION worldmesgfunc;
 

# EOF

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

 # Here,  because DECLARE EXIT HANDLER … instructs MySQL to exit the SP
# if  SQLSTATE = 23000, the statement SET @err=-1 never executes. Notice that
# in order to be able to mark the end of the SP, the code has to set the delimiter to
# something other than a semi-colon; this is best done just before creating the SP,
# and reset immediately after. The DELIMITER command does not need a second
# terminator.

USE test ;
SET @err = 0 ;
SELECT 'Before running errhandlerdemo:', @err ;
CREATE TABLE IF NOT EXISTS testhandler (i INT, PRIMARY KEY(i)) ;

DELIMITER |
DROP PROCEDURE IF EXISTS errhandlerdemo ;
CREATE PROCEDURE errhandlerdemo()
  BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @err=23000;
    INSERT INTO testhandler VALUES( NULL) ;
    SET @err=-1 ;
  END ;
|
DELIMITER ;

CALL errhandlerdemo() |
DROP TABLE testhandler |
SELECT 'After running errhandlerdemo:', @err;

# EOF

 

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

USE test;
DROP FUNCTION IF EXISTS is_even;
DELIMITER |
CREATE FUNCTION is_even( x INT ) RETURNS INT
BEGIN
  DECLARE iRet INT DEFAULT 0;
  IF x/2 = 0 THEN
    SET iRet = 1;
  END IF;
  RETURN iRet;
END
|
DELIMITER ;

SELECT is_even( 3 );

#EOF


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

USE test;

DROP PROCEDURE IF EXISTS case1proc;
DELIMITER |
CREATE PROCEDURE case1proc( IN x INT )
BEGIN
  CASE x
    WHEN 'string' THEN SELECT 'non-matching value will not execute';
    WHEN 0 THEN SELECT 'matching value executes';
    WHEN 17 THEN SELECT 'non-matching value will not execute';
  END CASE;
END;
|

DROP PROCEDURE IF EXISTS case2proc |
CREATE PROCEDURE case2proc( IN x INT )
BEGIN
  DECLARE s CHAR( 10 );
  CASE
    WHEN x < 0 THEN SET s = 'less than zero';
    WHEN x < 10 THEN SET s = 'units';
    WHEN x < 100 THEN SET s = 'tens';
    WHEN x < 1000 THEN SET s = 'hundreds';
    ELSE SET s = 'a thousand or more';
  END CASE;
  SELECT CONCAT( ‘range is ‘, s );
END;
|

DELIMITER ;

CALL case1proc( 0 );           # output: matching value executes
CALL case2proc( 100 );         # output: range is hundreds

# EOF

 

8-5: DO .. REPEAT

DELIMITER |

DROP PROCEDURE IF EXISTS dorepeat;
CREATE PROCEDURE dorepeat( IN imax INT )
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE s CHAR( 20 ) DEFAULT 'Loops:';
REPEAT
BEGIN
SET s = CONCAT_WS( ' ', s, i );
SET i = i + 1;
END;
UNTIL i > imax END REPEAT;
SELECT s;
END
|

DELIMITER ;
CALL dorepeat( 5 );


#EOF


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

USE test;

DROP PROCEDURE IF EXISTS whileproc;
DELIMITER |
CREATE PROCEDURE whileproc( IN x INT )
BEGIN
   DECLARE i INT DEFAULT 0;
   DECLARE s CHAR( 20 ) DEFAULT "Loops:";
   WHILE i-5 DO
     BEGIN
       SET i = i + 1;
       SET s = CONCAT_WS( ' ', s, i );
     END;
   END WHILE;
   SELECT s;
END;
|

DELIMITER ;

CALL whileproc( 5 ); # output: Loops: 1 2 3 4 5



#EOF



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

USE test;
DROP TABLE IF EXISTS curtest1;
DROP TABLE IF EXISTS curtest2;
CREATE TABLE curtest1( i INT PRIMARY KEY, j INT, name CHAR(10) );
CREATE TABLE curtest2( x INT );
INSERT INTO curtest1 VALUES (0,0,'first'),(1,2,'second'),(2,1,'third');

DROP PROCEDURE IF EXISTS cursxmpl;
DELIMITER |
CREATE PROCEDURE cursxmpl()
BEGIN
  DECLARE mi INT;
  DECLARE mj INT;
  DECLARE done INT DEFAULT 0;
  DECLARE curs CURSOR FOR SELECT i, j FROM test.curtest1;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN curs;
  loop0: WHILE NOT done DO
    FETCH curs INTO mi, mj;
    IF NOT done THEN
       IF mi < mj THEN
          INSERT INTO test.curtest2 VALUES (mi);
       ELSEIF mi > mj THEN
          INSERT INTO test.curtest2 VALUES (mj);
       ELSE
          ITERATE loop0;
       END IF;
    END IF;
  END WHILE loop0;
  CLOSE curs;
END;
|
DELIMITER ;
CALL cursxmpl();
SELECT * FROM curtest2;

# EOF

   

9-1: EXISTS example

USE test;

CREATE TABLE IF NOT EXISTS ridings (
  riding CHAR(10) PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS parties (
  party CHAR(12) PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS candidates (
  id INT PRIMARY KEY,
  name CHAR(10),
  riding CHAR(10),
  party CHAR( 10 )
); 

INSERT INTO ridings VALUES ('Essex'),('Malton'),('Riverdale');
INSERT INTO parties VALUES ('Liberal'),('Conservative'),('Socialist');
INSERT INTO candidates VALUES (1,'Anne Jones','Essex','Liberal');
INSERT INTO candidates VALUES (2,'Mary Smith','Malton','Liberal');
INSERT INTO candidates VALUES (3,'Sara Black','Riverdale','Liberal');
INSERT INTO candidates VALUES (4,'Paul Jones','Essex','Socialist');
INSERT INTO candidates VALUES (5,'Ed While','Essex','Conservative');
INSERT INTO candidates VALUES (6,'Jim Kelly','Malton','Liberal');
INSERT INTO candidates VALUES (7,'Fred Price','Riverdale','Socialist');

-- What ridings have candidates?

SELECT DISTINCT riding FROM ridings
WHERE EXISTS (
  SELECT * FROM candidates
  WHERE candidates.riding = ridings.riding
);

SELECT DISTINCT ridings.riding FROM ridings
INNER JOIN candidates ON ridings.riding = candidates.riding;

-- What ridings have NO candidates?

SELECT DISTINCT riding FROM ridings
WHERE NOT EXISTS (
  SELECT * FROM candidates
  WHERE candidates.riding = ridings.riding
);

SELECT DISTINCT ridings.riding FROM ridings
INNER JOIN candidates ON ridings.riding = candidates.riding
WHERE candidates.riding IS NULL;

-- What parties have candidates in all ridings?

SELECT DISTINCT party FROM parties
WHERE NOT EXISTS (
  SELECT * FROM ridings
  WHERE NOT EXISTS (
    SELECT * FROM candidates
    WHERE candidates.party=parties.party
    AND candidates.riding=ridings.riding
  )
);

# EOF
  
 

9-2: Logical reduction

USE test;
 
CREATE TABLE persons (
 id INT UNSIGNED NOT NULL PRIMARY KEY,
 dept INT NOT NULL DEFAULT 0
);
CREATE TABLE intl_jobs (
 id INT UNSIGNED NOT NULL PRIMARY KEY,
 person_id INT NOT NULL,
 lang CHAR(2) DEFAULT NULL
);
INSERT INTO persons VALUES(1,1),(2,1),(3,2);
INSERT INTO intl_jobs VALUES
  (1,1,’de’),(2,1,’en’),(3,1,NULL),(4,2,’en’),
  (5,2,NULL),(6,2,NULL),(7,4,’sp’);

-- What persons are in Dept 1, have done a job requiring German, or have done
-- no job requiring German but have done at least one job requiring only English?

SELECT DISTINCT persons.id AS PersonID
FROM persons
  INNER JOIN intl_jobs
    ON persons.id=intl_jobs.person_id
    AND persons.dept=1
    AND (intl_jobs.lang='de' OR intl_jobs.lang IS NULL);

# EOF


11-1:  NWIB_Create.sql

DROP DATABASE IF EXISTS NWIB;
CREATE DATABASE NWIB;
USE NWIB;

CREATE TABLE Categories(
CategoryID INT NOT NULL AUTO_INCREMENT,
CategoryName VARCHAR(15) NOT NULL,
Description TEXT,
Timestamp TIMESTAMP,
PRIMARY KEY (CategoryID),
UNIQUE CategoryName (CategoryName)) ENGINE=InnoDB;

CREATE TABLE Customers(
CustomerID VARCHAR(5) NOT NULL,
CompanyName VARCHAR(40) NOT NULL,
ContactName VARCHAR(30),
ContactTitle VARCHAR(30),
Address VARCHAR(60),
City VARCHAR(15),
Region VARCHAR(15),
PostalCode VARCHAR(10),
Country VARCHAR(15),
Phone VARCHAR(24),
Fax VARCHAR(24),
Timestamp TIMESTAMP,
PRIMARY KEY (CustomerID),
INDEX City (City),
INDEX CompanyName (CompanyName),
INDEX PostalCode (PostalCode),
INDEX Region (Region)) ENGINE=InnoDB;

CREATE TABLE Employees(
EmployeeID INT NOT NULL AUTO_INCREMENT,
LastName VARCHAR(20) NOT NULL,
FirstName VARCHAR(10) NOT NULL,
Title VARCHAR(30),
TitleOfCourtesy VARCHAR(25),
BirthDate DATETIME,
HireDate DATETIME,
Address VARCHAR(60),
City VARCHAR(15),
Region VARCHAR(15),
PostalCode VARCHAR(10),
Country VARCHAR(15),
HomePhone VARCHAR(24),
Extension VARCHAR(4),
Photo VARCHAR(255),
Notes TEXT,
ReportsTo INT,
Timestamp TIMESTAMP,
PRIMARY KEY (EmployeeID),
INDEX LastName (LastName)) ENGINE=InnoDB;

CREATE TABLE Shippers(
ShipperID INT NOT NULL AUTO_INCREMENT,
CompanyName VARCHAR(40) NOT NULL,
Phone VARCHAR(24),
Timestamp TIMESTAMP,
PRIMARY KEY (ShipperID)) ENGINE=InnoDB;

CREATE TABLE Orders(
OrderID INT NOT NULL AUTO_INCREMENT,
CustomerID VARCHAR(5),
EmployeeID INT NOT NULL,
OrderDate DATETIME,
RequiredDate DATETIME,
ShippedDate DATETIME,
ShipVia INT NOT NULL,
Freight FLOAT DEFAULT 0,
ShipName VARCHAR(40),
ShipAddress VARCHAR(60),
ShipCity VARCHAR(15),
ShipRegion VARCHAR(15),
ShipPostalCode VARCHAR(10),
ShipCountry VARCHAR(15),
Timestamp TIMESTAMP,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
FOREIGN KEY (ShipVia) REFERENCES Shippers (ShipperID),
PRIMARY KEY (OrderID),
INDEX OrderDate (OrderDate),
INDEX ShippedDate (ShippedDate),
INDEX ShipPostalCode (ShipPostalCode)) ENGINE=InnoDB;

CREATE TABLE Suppliers(
SupplierID INT NOT NULL AUTO_INCREMENT,
CompanyName VARCHAR(40) NOT NULL,
ContactName VARCHAR(30),
ContactTitle VARCHAR(30),
Address VARCHAR(60),
City VARCHAR(15),
Region VARCHAR(15),
PostalCode VARCHAR(10),
Country VARCHAR(15),
Phone VARCHAR(24),
Fax VARCHAR(24),
HomePage VARCHAR(50),
Timestamp TIMESTAMP,
PRIMARY KEY (SupplierID)) ENGINE=InnoDB;

CREATE TABLE Products(
ProductID INT NOT NULL AUTO_INCREMENT,
ProductName VARCHAR(40) NOT NULL,
SupplierID INT NOT NULL,
CategoryID INT NOT NULL,
QuantityPerUnit VARCHAR(20),
UnitPrice FLOAT DEFAULT 0,
UnitsInStock SMALLINT DEFAULT 0,
UnitsOnOrder SMALLINT DEFAULT 0,
ReorderLevel SMALLINT DEFAULT 0,
Discontinued TINYINT DEFAULT 0 NOT NULL,
Timestamp TIMESTAMP,
FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID),
PRIMARY KEY (ProductID),
INDEX ProductName (ProductName)) ENGINE=InnoDB;

CREATE TABLE OrderDetails(
OrderID INT NOT NULL,
ProductID INT NOT NULL,
UnitPrice FLOAT DEFAULT 0 NOT NULL,
Quantity SMALLINT DEFAULT 1 NOT NULL,
Discount FLOAT DEFAULT 0 NOT NULL,
Timestamp TIMESTAMP,
FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
PRIMARY KEY (OrderID,ProductID)
) ENGINE=InnoDB;

SHOW TABLES;

# EOF

   

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

DROP DATABASE IF EXISTS NorthwindMySQL;
CREATE DATABASE NorthwindMySQL;
USE NorthwindMySQL;

CREATE TABLE Categories(
CategoryID INT NOT NULL AUTO_INCREMENT,
CategoryName VARCHAR(15) NOT NULL,
Description TEXT,
PRIMARY KEY (CategoryID),
UNIQUE CategoryName (CategoryName));

CREATE TABLE Customers(
CustomerID CHAR(5) NOT NULL,
CompanyName VARCHAR(40) NOT NULL,
ContactName VARCHAR(30),
ContactTitle VARCHAR(30),
Address VARCHAR(60),
City VARCHAR(15),
Region VARCHAR(15),
PostalCode VARCHAR(10),
Country VARCHAR(15),
Phone VARCHAR(24),
Fax VARCHAR(24),
PRIMARY KEY (CustomerID),
INDEX City (City),
INDEX CompanyName (CompanyName),
INDEX PostalCode (PostalCode),
INDEX Region (Region));

CREATE TABLE Employees(
EmployeeID INT NOT NULL AUTO_INCREMENT,
LastName VARCHAR(20) NOT NULL,
FirstName VARCHAR(10) NOT NULL,
Title VARCHAR(30),
TitleOfCourtesy VARCHAR(25),
BirthDate DATETIME,
HireDate DATETIME,
Address VARCHAR(60),
City VARCHAR(15),
Region VARCHAR(15),
PostalCode VARCHAR(10),
Country VARCHAR(15),
HomePhone VARCHAR(24),
Extension VARCHAR(4),
Photo VARCHAR(255),
Notes TEXT,
ReportsTo INT,
PRIMARY KEY (EmployeeID),
-- CHECK (<Date()),
INDEX LastName (LastName));

CREATE TABLE Shippers(
ShipperID INT NOT NULL AUTO_INCREMENT,
CompanyName VARCHAR(40) NOT NULL,
Phone VARCHAR(24),
PRIMARY KEY (ShipperID));

CREATE TABLE Orders(
OrderID INT NOT NULL AUTO_INCREMENT,
CustomerID VARCHAR(5),
EmployeeID INT NOT NULL,
OrderDate DATETIME,
RequiredDate DATETIME,
ShippedDate DATETIME,
ShipVia INT NOT NULL,
Freight FLOAT DEFAULT 0,
ShipName VARCHAR(40),
ShipAddress VARCHAR(60),
ShipCity VARCHAR(15),
ShipRegion VARCHAR(15),
ShipPostalCode VARCHAR(10),
ShipCountry VARCHAR(15),
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID),
FOREIGN KEY (ShipVia) REFERENCES Shippers (ShipperID),
PRIMARY KEY (OrderID),
INDEX OrderDate (OrderDate),
INDEX ShippedDate (ShippedDate),
INDEX ShipPostalCode (ShipPostalCode));

CREATE TABLE Suppliers(
SupplierID INT NOT NULL AUTO_INCREMENT,
CompanyName VARCHAR(40) NOT NULL,
ContactName VARCHAR(30),
ContactTitle VARCHAR(30),
Address VARCHAR(60),
City VARCHAR(15),
Region VARCHAR(15),
PostalCode VARCHAR(10),
Country VARCHAR(15),
Phone VARCHAR(24),
Fax VARCHAR(24),
HomePage VARCHAR(50),
PRIMARY KEY (SupplierID));

CREATE TABLE Products(
ProductID INT NOT NULL AUTO_INCREMENT,
ProductName VARCHAR(40) NOT NULL,
SupplierID INT NOT NULL,
CategoryID INT NOT NULL,
QuantityPerUnit VARCHAR(20),
UnitPrice FLOAT DEFAULT 0,
UnitsInStock SMALLINT DEFAULT 0,
UnitsOnOrder SMALLINT DEFAULT 0,
ReorderLevel SMALLINT DEFAULT 0,
-- Discontinued TINYINT DEFAULT =No NOT NULL,    <--- this is uncoool
--Discontinued TINYINT DEFAULT = 0 NOT NULL,
Discontinued TINYINT DEFAULT 0 NOT NULL,
FOREIGN KEY (CategoryID) REFERENCES Categories (CategoryID),
FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID),
PRIMARY KEY (ProductID),
--CHECK (>=0),
--CHECK (>=0),
--CHECK (>=0),
--CHECK (>=0),
INDEX ProductName (ProductName));

CREATE TABLE OrderDetails(
OrderID INT NOT NULL,
ProductID INT NOT NULL,
UnitPrice FLOAT DEFAULT 0 NOT NULL,
Quantity SMALLINT DEFAULT 1 NOT NULL,
Discount FLOAT DEFAULT 0 NOT NULL,
FOREIGN KEY (OrderID) REFERENCES Orders (OrderID),
FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
PRIMARY KEY (OrderID,ProductID)
--CHECK (>=0),
--CHECK (>0),
--CHECK (Between 0 And 1)
);

SHOW TABLES;

# EOF



# Note: NWIB_Populate.sql is in this folder.

12-1: docs database

CREATE DATABASE IF NOT EXISTS docs;
USE docs;
CREATE TABLE mysqlnotes (
 id INT(10) unsigned NOT NULL auto_increment,
 topic CHAR(72) NOT NULL DEFAULT '',
 text TEXT NOT NULL,
 PRIMARY KEY (id),
 KEY topic (topic),
 FULLTEXT KEY text (text)
);

# EOF

   

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.location='$url';</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.location='$url';</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.location='$url';</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.location='$url';</script>";
  echo "</head><body>Click <a href='$url'>here</a> to return to ", "$table.";
  echo "</body></html>";
}
?>

  

12-6: tracker.inc

<?php
function html_hdr( $title, $header, $meta ) {
  print "<html>\n<head>\n";
  print ( $title )  ? "<title>$title</title>\n" : "";
  print ( $header ) ? "<h1>$header</h1>\n" : "";
  print ( $meta )   ? "<META content=$meta/>\n" : "";
  print "\n</head>\n<body>\n";
}

function html_ftr() {
  print "</body>\n</html>\n";
}

function connect_db( $db,
                     $persistent = TRUE,
                     $host = "localhost",
                     $user = "USR",
                     $pswd = "PSWD" ) {
  $msg = "Unable to connect to MySQL";
  if( $persistent )
    $conn = mysql_pconnect( $host, $user, $pswd ) or die( $msg );
  else
    $conn = mysql_connect( $host, $user, $pswd ) or die( $msg );
  $ret = mysql_select_db( $db );
  if( $ret )
    print "<p>Connected to the tracker DB</p>\n";
  return $ret;
}

?>

  
  

12-7: theUsual for PHP mysql

Source code for TheUsual for the mysql API

12-8: theUsual for PHP mysqli

Source code for TheUsual for the mysqli API

13-1: parties.pl

 #! /usr/bin/perl
# parties.pl: list tracker.parties

use DBI;

# CONNECT
$dsn = "DBI:mysql:tracker:localhost";   
$username = "USR";                       
$password = "PSWD";                       
# QUERY
$qry = "SELECT * FROM parties ORDER BY partyID";
$dbh = DBI->connect( $dsn, $username, $password,
         { AutoCommit => 1, RaiseError => 1, PrintError => 1 } );
$sth = $dbh->prepare( $qry );
$sth->execute();
# PRESENTATION
while(my @ra = $sth->fetchrow_array() ) {
  print join( "\t", @ra ), "\n";
}
# TIDY UP
$sth->finish();
$dbh->disconnect();
exit( 0 );

#EOF

13-2: parties_web.pl

#! /usr/bin/perl
# list tracker.parties rows on a web page

use DBI;

# CONNECT
$dsn = "DBI:mysql:tracker:localhost";   
$username = "USR";        # CHANGE TO YOUR VALUE               
$password = "PSWD";        # DITTO               
$dbh = DBI->connect($dsn, $username, $password,
          { AutoCommit => 1, RaiseError => 1, PrintError => 1 } );
# QUERY
$qry = "SELECT * FROM parties ORDER BY partyID";
$sth = $dbh->prepare( $qry );
$sth->execute();
# PRESENTATION
print "Content-type: text/html\n\n";
print "<html><body><h3>DB:tracker Table:parties</h3>\n";
print "<table border='1' cellpadding='5'>";
print "<tr><th>ID</th><th>Name</th><th>Company</th></tr>\n";
while( @ra = $sth->fetchrow_array() ) {
  print "<tr><td>$ra[0]</td><td>$ra[1]</td><td>$ra[2]</td></tr>\n";
}
print "</table></body></html>\n";
# TIDY UP
$sth->finish();
$dbh->disconnect();
exit( 0 );

# EOF

  
  

13-3: parties_web_array.pl

#! /usr/bin/perl
# parties.pl saving resultset to array

use DBI;

# CONNECT
$dsn = "DBI:mysql:tracker:localhost";   
$username = "USR";        # CHANGE TO YOUR VALUE               
$password = "PSWD";        # DITTO               
$dbh = DBI->connect($dsn, $username, $password,
          { AutoCommit => 1, RaiseError => 1, PrintError => 1 } );
# QUERY
$qry = "SELECT * FROM parties ORDER BY partyID";
$sth = $dbh->prepare( $qry );
$sth->execute();
# SAVE RESULTSET TO ARRAY
@ra = ();
while( @rra = $sth->fetchrow_array() ) {
  push( @ra, [ @rra ] );
}
$sth->finish();
# DISPLAY ARRAY IN HTML TABLE
print "<tr><th>ID</th><th>Name</th><th>Company</th></tr>\n";
$rows = scalar( @ra );
$cols = ( $rows == 0 ) ? 0 : scalar( @{$ra[0]} ) ;
print "Found $cols columns, $rows rows.<br>\n";
for( $i = 0; $i < $rows; $i++ ) {
  print "<tr>\n";
  # cols: 0=partyID, 1=name, 2=companyname
  for( $j = 0; $j < $cols; $j++ ) {
    if (!defined( $ra[$i][$j] )) {
      $cell = "NULL";
    }
    elsif ( $ra[$i][$j] eq '' ) {
      $cell = "Missing data";
    }
    else {
      $cell = $ra[$i][$j];
    }
    print "<td>$cell</td>\n";
  }
  print "</tr>\n";

print "</table></body></html>\n";

# EOF

  
  

13-4: insprep.pl

#! c:/perl/bin/perl
# insprep.pl
# use SQL parameters to insert multiple rows in parties

use DBI;

$dsn = "DBI:mysql:tracker:localhost";   
$username = "webauth";                       
$password = "webauth";                       
print "Content-type: text/html\n\n";
print "<html><body><h3>DB:tracker Table:parties Action:Insert</h3>\n";
$dbh = DBI->connect( $dsn, $username, $password,
                     { RaiseError => 1, PrintError => 1 } );
# LOAD SAMPLE ARRAY
@ra = ();
push( @ra, [ 'Ansel Adams', 'Photos Inc' ] );
push( @ra, [ 'Stephen Bishop', 'Music Inc' ] );
# PREPARE STATEMENT TO RECEIVE 2 PARAMS
$sth = $dbh->prepare( "INSERT INTO parties VALUES(NULL,?,?)" );
$rowcount = 0;
# WALK THE ARRAY AND EXECUTE WITH ARRAY PARAMS
for( $i = 0; $i < 2; $i++ ) {
  $rows = $sth->execute( $ra[$i][0], $ra[$i][1] );
  $rows += 0;
  $rowcount += $rows;
}
# REPORT TO USER
printf "%d row%s added<br>\n", $rowcount, ($rowcount==1) ? "" : "s";
print "</body></html>\n";
$dbh->disconnect();
exit( 0 );

# EOF

  
  

13-5: parties_web_strict.pl

# parties_web_strict.pl
use DBI;
use strict;

my( $dsn, $username, $password, $dbh, $qry, $sth,
    @ra, @rra, $rows, $cols, $i, $j, $cell );

# CONNECT
$dsn = "DBI:mysql:tracker:localhost";   
$username = "USR";                       
$password = "PSWD";                       
$dbh = DBI->connect ($dsn, $username, $password,
        { AutoCommit => 1, RaiseError => 1, PrintError => 1 });

# QUERY
$qry = "SELECT partyID AS ID,
               name AS Name,
               companyname AS Company
        FROM parties ORDER BY partyID";
$sth = $dbh->prepare( $qry );
$sth->execute();
$cols = $sth->{NUM_OF_FIELDS};

# PRINT HTML HEADER
print "Content-type: text/html\n\n";
print "<html><body><h3>DB:tracker Table:parties</h3>\n";

# PRINT TABLE HEADER FROM sth->(NAME}
print "<table border='1' cellpadding='5'><tr>";
for ( $i = 0; $i < $cols; $i++ ) {
  print "<th>$sth->{NAME}->[$i]</th>";
}
print "</tr>\n";

# SAVE RESULT TO ARRAY
@ra = ();
while( @rra = $sth->fetchrow_array() ) {
  push( @ra, [ @rra ] );
}
$rows = scalar( @ra );
$sth->finish();

# DUMP ARRAY INTO HTML TABLE
for( $i = 0; $i < $rows; $i++ ) {
  print "<tr>\n";
  for( $j = 0; $j < $cols; $j++ ) {
    if (!defined( $ra[$i][$j] )) {
      $cell = “NULL";
    }
    elsif ( $ra[$i][$j] eq '' ) {
      $cell = "<font color='red'>Missing data</font>";
    }
    else {
      $cell = $ra[$i][$j];
    }
    print "<td>$cell</td>\n";
  }
  print "</tr>\n";

print "</table></body></html>\n";
$dbh->disconnect();
exit (0);

# EOF

  
  

14-1: LoadDriver.java

/* LoadDriver.java */
// Declare JDBC
import java.sql.*;
public class LoadDriver {
  public static void main( String[] args ) {
   // REGISTER CONNECTOR/J DRIVER
   try {
     Class.forName("com.mysql.jdbc.Driver");
   } catch( Exception E ) {
     System.err.println( "Unable to load driver." );
     E.printStackTrace();
   }
   // CONNECT TO MYSQL
   try {
     Connection conn = DriverManager.getConnection(
               "jdbc:mysql://localhost/test?user=USR&password=PSWD");
     System.out.println( "Connected to MySQL server test database" );
     // Do something with the Connection
   } catch( SQLException E ) {
     System.out.println( "SQLException: " + E.getMessage() );
     System.out.println( "SQLState:     " + E.getSQLState() );
   }
  }
}

// EOF

  
  

14-2: RetrieveFromMySQL.java

 /* RetrieveFromMySql.java */
// Declare JDBC
import java.sql.*;
public class RetrieveFromMySql {
  public static void main( String[] args ) {
    try {
      Class.forName( "com.mysql.jdbc.Driver" );
    } catch( Exception e ) {
      System.err.println( "Unable to load driver." );
      out.println("&nbsp;&nbsp;Exception: " + e.getMessage() + "<br>");
    }
    try {
      // CONNECT TO USER TABLE OF MYSQL DB
      Connection conn = DriverManager.getConnection(
        "jdbc:mysql://localhost/mysql?user=USR&password=PSWD");
      System.out.println( "Connected to MySQL\n" );
      // CREATE AND EXECUTE QUERY
      Statement stmt = conn.createStatement();
      System.out.println( "Retrieving rows from user table" );
      ResultSet rs =
        stmt.executeQuery( "SELECT * FROM mysql.user ORDER BY user" );
      // NO BROWSER. COMPUTE WIDTHS FOR ALIGNING COLUMNS
      int i, j, k, iMax[] = { 20, 20, 20, 1, 1, 1, 1, 1 };
      String s;
      while( rs.next() ) {
        for( i = 1; i <= 2; i++ ) {
          s = rs.getString( i );
          if( ( k = s.length() ) > iMax[ i ] ) { iMax[ i ] = k; }
        }
      }
      // RESET CURSOR AT TOP OF RESULTSET, RETRIEVE/DISPLAY VALUES
      rs.beforeFirst();
      while( rs.next() ) {
        for( i = 1; i <= 8; i++ ) {
          // SKIP PASSWORD
          if( i != 3 ) {
            s = rs.getString( i );
            if ( i < 3 ) { k = iMax[ i ] - s.length(); }
            else { k = 1; }
            System.out.print( s );
            for( j = 1; j <= k; j++ ) {
              System.out.print( " " );
            }
          }
        }
        System.out.println("");
      }
      rs.close();
      stmt.close();
      conn.close();
   } catch( SQLException e ) {
     System.out.println( "SQLException: " + e.getMessage() );
     System.out.println( "SQLState:     " + e.getSQLState() );
   }
  }
}

  
  

14-3: MySqlQuery.jsp


<html>
<head><b><i>MySqlQuery.Jsp</b></i></head>
<body>
<br>
<%@ page language="java" import="java.sql.*" %>
<%
  Connection conn = null;
  ResultSet rs = null;
  Statement stmt = null;
  // REGISTER THE DRIVER
  try {
    Class.forName( "com.mysql.jdbc.Driver" );
  } catch( Exception e ) {
    out.println( "Class.forName() FAILED.<br>" );
    out.println( "&nbsp;Exception: " + e.getMessage() + "<br>" );
    e.printStackTrace();
  }
  // CONNECT
  try {
    conn = DriverManager.getConnection(
           "jdbc:mysql://localhost/mysql?user=USR&password=PSWD" );
  } catch( Exception e ) {
    out.println( "DriverManager.getConnection() FAILED.<br>" );
    out.println( "&nbsp;Exception: " + e.getMessage() + "<br>" );
    e.printStackTrace();
  }
  // EXECUTE QUERY
  try {
   stmt = conn.createStatement();
  } catch( Exception e ) {
    out.println( "conn.CreateStatement() FAILED.<br>" );
    out.println( "&nbsp;Exception: " + e.getMessage() + "<br>" );
    e.printStackTrace();
  }
  String sql = "SELECT Host, User, Select_priv, Insert_priv, " +
               "Update_priv, Delete_priv, Create_priv " +
               "FROM mysql.user ORDER BY user";
  try { 
    rs = stmt.executeQuery( sql );
  } catch( Exception e ) {
    out.println( "stmt.executeQuery() FAILED.<br>" );
    out.println( "&nbsp;Exception: " + e.getMessage() + "<br>" );
    e.printStackTrace();
  }
%>
  <!-- HTML TABLE HEADERS -->
  <h2>Database: mysql &nbsp 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
  try {
   while( rs.next() ) {
     out.println( "<tr>" );
     for( int i=1; i<=7; i++ ) {
       out.println( "  <td>" + rs.getString( i ) + "</td>" );
     }
     out.println( "</tr>" );
   }
   out.println( "</table>" );
   // CLEAN UP
   rs.close();
   stmt.close();
  } catch( SQLException e ) {
   out.println( "SQLException: " + e.getMessage() );
   out.println( "SQLState:     " + e.getSQLState() );
  } catch( Exception e ) {
   out.println( "rs.next() loop FAILED.<br>" );
   out.println( e.getMessage() );
  } finally {
   try {
     if( conn != null ) {
       conn.close();
     }
   } catch( SQLException e ) {
     out.println( e.getMessage() );
   }
  }
%>

</body>
</html>

# EOF

Here is the same example with production-style error trapping, contributed by Brian Russell:
<%@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>
# EOF

14-4: server.xml connection pool entry

<!-- CREATE TRACKER WEBAPP CONTEXT -->
<Context path="/tracker"
            docBase="tracker"
            debug="5"
            reloadable="true"
            crossContext="true"> 
        
 <!-- TRACKER LOG FILE -->
 <Logger className="org.apache.catalina.logger.FileLogger"
            prefix="localhost_tracker_log."
                  suffix=".txt"
                  timestamp="true" verbosity="4" />
   
 <!-- TRACKER DATASOURCE -->
 <Resource name="jdbc/tracker"
           auth="Container"
           type="javax.sql.DataSource" /> 

 <!-- TRACKER DBCP RESOURCES AND SETTINGS -->
 <ResourceParams name="jdbc/tracker">   
  <parameter>
        <name>factory</name>
        <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
  </parameter>
  <!-- MAX DB CONNECTIONS IN POOL, 0=NO LIMIT -->
  <parameter>
         <name>maxActive</name>
         <value>100</value>
        </parameter>
        <!-- MAX IDLE DB CONNECTIONS, 0=NO LIMIT -->
        <parameter>
         <name>maxIdle</name>
         <value>30</value>
        </parameter>
     <!-- MAX TIME IN MS TO WAIT FOR DB CONNECTION, -1=FOREVER -->
     <parameter>
      <name>maxWait</name>
      <value>10000</value>
        </parameter>
        <!—REPLACE ‘USR’ AND ‘PSWD’ WITH YOUR MYSQL USERNAME & PASSWORD -->
     <parameter>
   <name>username</name>
      <value>USR</value>
  </parameter>
  <parameter>
   <name>password</name>
   <value>PSWD</value>
  </parameter>
  <!-- CLASS NAME FOR CONNECTOR/J -->
  <parameter>
      <name>driverClassName</name>
      <value>com.mysql.jdbc.Driver</value>
  </parameter>
  <!-- MYSQL CONNECTION ARGS -->
  <parameter>
      <name>url</name>
   <value>jdbc:mysql://localhost:3306/tracker?autoReconnect=true</value>
  </parameter> 
 </ResourceParams>
</Context>

14-5: web.xml connection pool entry

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app
    PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
    "http://java.sun.com/dtd/web-app_2_3.dtd">

<web-app>
  <description>Tracker</description>
  <resource-ref>
      <description>Tracker DB Connection</description>
      <res-ref-name>jdbc/tracker</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>

#EOF
  
  

14-6: parties.jsp


<%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %>

<sql:setDataSource   
     driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/tracker"
     user="USR"
     password="PSWD" />
<sql:query var="rs">
SELECT * FROM parties
</sql:query>
<html> 

<head>
<title>Tracker Parties</title>
</head>

<body> 

<h2>tracker.parties <%= new java.util.Date()%> </h2>
<%-- Get col names for table header --%>
<table border="1" cellpadding="5">
<c:forEach var="columnName" items="${rs.columnNames}">
  <th><c:out value="${columnName}"/></th>
</c:forEach>
<%-- Get value of each column for each row --%>
<c:forEach var="row" items="${rs.rows}">
  <tr>
    <td><c:out value="${row.partyID}"/></td>
    <td><c:out value="${row.name}"/></td>
    <td><c:out value="${row.companyname}"/></td>
  </tr>
</c:forEach>
</table>

</body>
</html>

# EOF

  
  

14-7: dsQuery.jsp

 <html>
<!-- dsQuery.jsp, tracker query using a DataSource -->
<%@ page language="java"
    import="java.sql.*"
    import="javax.sql.*"
    import="javax.naming.*"
%>
<head>dsQuery.Jsp <%= new java.util.Date()%></head>
<body>
<br>
<%
  Context ctx = null;
  Connection conn = null;
  DataSource ds = null;
  Statement stmt = null;
  ResultSet rs = null;
  String strctx = "java:comp/env/jdbc/tracker";
  String qry = "SELECT * FROM tracker.parties ORDER BY partyID";
  try {
    ctx = new InitialContext();
  } catch( Exception e ) {
    out.println( "Exception: " + e.getMessage() );
    e.printStackTrace();
  }
  try {
    ds = (DataSource) ctx.lookup( strctx );
    conn = ds.getConnection();
    stmt = conn.createStatement();
  } catch( SQLException e ) {
    out.println( "SQLException: " + e.getMessage() );
    e.printStackTrace();
  }
  try { 
    rs = stmt.executeQuery( qry );
  } catch( Exception e ) {
    out.println( "Exception: " + e.getMessage() );
    e.printStackTrace();
  }
%>
  <!-- FORMAT RESULT IN HTML TABLE -->
  <h2>
    Database: tracker&nbsp;&nbsp;Table: parties
  </h2>
  <table width="80%" border="2" callpadding="5">
  <tr>
    <th>PartyID</th><th>Name</th><th>Company Name</th>
  </tr>
  <!-- END OF RAW HTML BLOCK -->
<%
  // RESUME JAVA. PUT VALUES OF 1ST 8 COLS OF ALL ROWS IN HTML TABLE
  try {
   while( rs.next() ) {
     out.println( "<tr>" );
     for( int i=1; i<=3; i++ ) {
       out.println( "  <td>" + rs.getObject( i ) + "</td>" );
     }
     out.println( "</tr>" );
   }
   out.println( "</table>" );
   rs.close();
   stmt.close();
  } catch( SQLException e ) {
   out.println( "SQLException: " + e.getMessage() );
   out.println( "SQLState:     " + e.getSQLState() );
   out.println( "VendorError:  " + e.getErrorCode() ); 
  } catch( Exception e ) {
   out.println( "rs.next() loop FAILED.<br>" );
   out.println( e.getMessage() );
  } finally {
   try {
     if( conn != null ) {
      conn.close();
     }
   } catch( SQLException e ) {
     out.println( e.getMessage() );
   }
  }
%>
</body>
</html>

# EOF

  
    

15-1: HelloWorld.aspx

<%@ Page Language="vb" %>
<% Label1.Text = "Hello World from ASP.NET" %>
<HTML>
  <HEAD>
    <title>WebForm1</title>
  </HEAD>
  <BODY>
    <form id="Form1" method="post" runat="server">
      <asp:Label id="Label1" runat="server">Label</asp:Label>
    </form>
  </BODY>
</HTML>

15-2: Form1.cs for tracker_test

// Form1.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using Microsoft.Data.Odbc;

namespace tracker_test {
    /// <summary>
    /// Summary description for Form1.
    /// </summary>
    public class Form1 : System.Windows.Forms.Form {
        private System.Windows.Forms.DataGrid dataGrid1;
        private System.Windows.Forms.Button button1;
        private Microsoft.Data.Odbc.OdbcConnection oConn;
        private OdbcDataAdapter da;
        private System.Data.DataSet ds;
        private OdbcCommandBuilder cb;
        private System.Windows.Forms.Button button2;
       
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.Container components = null;

        public Form1() {
            // Required for Windows Form Designer support
            InitializeComponent();
            // TODO: Add any constructor code after InitializeComponent call
        }

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        protected override void Dispose( bool disposing ) {
            if( disposing )    {
                if (components != null) {
                    components.Dispose();
                }
            }
            base.Dispose( disposing );
        }

        #region Windows Form Designer generated code
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.dataGrid1 = new System.Windows.Forms.DataGrid();
            this.button1 = new System.Windows.Forms.Button();
            this.button2 = new System.Windows.Forms.Button();
            ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
            this.SuspendLayout();
            //
            // dataGrid1
            //
            this.dataGrid1.DataMember = "";
            this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
            this.dataGrid1.Location = new System.Drawing.Point(8, 0);
            this.dataGrid1.Name = "dataGrid1";
            this.dataGrid1.PreferredColumnWidth = 125;
            this.dataGrid1.Size = new System.Drawing.Size(576, 216);
            this.dataGrid1.TabIndex = 0;
            //
            // button1
            //
            this.button1.Location = new System.Drawing.Point(176, 232);
            this.button1.Name = "button1";
            this.button1.TabIndex = 1;
            this.button1.Text = "Load";
            this.button1.Click += new System.EventHandler(this.button1_Click);
            //
            // button2
            //
            this.button2.Location = new System.Drawing.Point(384, 232);
            this.button2.Name = "button2";
            this.button2.TabIndex = 2;
            this.button2.Text = "Update";
            this.button2.Click += new System.EventHandler(this.button2_Click);
            //
            // Form1
            //
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(592, 273);
            this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                                          this.button2,
                                                                          this.dataGrid1,
                                                                          this.button1});
            this.Name = "Form1";
            this.Text = "Form1";
            ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
            this.ResumeLayout(false);

        }
        #endregion

        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main() {
            Application.Run(new Form1());
        }

        private void button1_Click(object sender, System.EventArgs e) {
            string sConn = "DRIVER={MySQL ODBC 3.51 Driver};" +
                           "SERVER=localhost;" +
                           "DATABASE=tracker;" +
                           "UID=webauth;" +
                           "PASSWORD=webauth;" +
                           "OPTION=3";
            oConn = new OdbcConnection( sConn );
            try {
                oConn.Open();
            } catch( Exception ex ) {
                MessageBox.Show( ex.Message );
            }
            // string sSQL = "SELECT * FROM parties ORDER BY name";
            string sSQL = "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";
             da = new OdbcDataAdapter( sSQL, oConn );
            ds = new System.Data.DataSet();
            cb = new OdbcCommandBuilder( da );
            da.Fill( ds, "Parties" );
            dataGrid1.DataSource = ds.Tables[ "parties" ];
            // DataGridTableStyle ts = new DataGridTableStyle();
            // dataGrid1.TableStyles.Add( ts );
            // dataGrid1.TableStyles[0].GridColumnStyles[0].Width = 50;

        }

        private void button2_Click(object sender, System.EventArgs e) {
            da.Update( ds, "parties" );
        }
    }
}

// EOF

  

15-3: master_detail window using ODBC

code begins
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using Microsoft.Data.Odbc;

namespace master_detail {
    public class Form1 : System.Windows.Forms.Form {
        private System.Windows.Forms.DataGrid dataGrid1;
        private System.Windows.Forms.DataGrid dataGrid2;
        private System.Windows.Forms.Button button2;
        private Microsoft.Data.Odbc.OdbcConnection oConn;
        private OdbcDataAdapter daCus, daOrd;
        private System.Data.DataSet ds;
        private DataRelation dr;
        private OdbcCommandBuilder cbCus, cbOrd;
        private string sConn = "DRIVER={MySQL ODBC 3.51 Driver};" +
                               "SERVER=localhost;" +
                               "DATABASE=nwib;" +
                               "UID=USR;" +
                               "PASSWORD=PWD;" +
                               "OPTION=3";
        private string sqlCus = "SELECT * FROM customers";
        private string sqlOrd = "SELECT * FROM orders";                              

        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.Container components = null;

        public Form1()    {
            // Required for Windows Form Designer support
            InitializeComponent();
            oConn = new OdbcConnection( sConn );
            try {
                oConn.Open();
            } catch( Exception ex ) {
                MessageBox.Show( ex.Message );
            }
            ds = new System.Data.DataSet();   

            daCus = new OdbcDataAdapter( sqlCus, oConn );
            cbCus = new OdbcCommandBuilder( daCus );
            daCus.Fill( ds, "customers" );
   
            daOrd = new OdbcDataAdapter( sqlOrd, oConn );
             cbOrd = new OdbcCommandBuilder( daOrd );
            daOrd.Fill( ds, "orders" );
         
            dr = new DataRelation( "CustOrd",
                      ds.Tables["Customers"].Columns["CustomerID"],
                      ds.Tables["Orders"].Columns["CustomerID"]);
            // Add the relation to the DataSet.
            ds.Relations.Add( dr );
            dataGrid1.SetDataBinding( ds, "Customers" );
            dataGrid2.SetDataBinding( ds, "Customers.CustOrd" );
        }

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        protected override void Dispose( bool disposing ) {
            if( disposing )    {
                if (components != null) {
                    components.Dispose();
                }
            }
            base.Dispose( disposing );
        }

        #region Windows Form Designer generated code
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent() {
            this.dataGrid1 = new System.Windows.Forms.DataGrid();
            this.dataGrid2 = new System.Windows.Forms.DataGrid();
            this.button2 = new System.Windows.Forms.Button();
            ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).BeginInit();
            this.SuspendLayout();
            //
            // dataGrid1
            //
            this.dataGrid1.DataMember = "";
            this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
            this.dataGrid1.Location = new System.Drawing.Point(16, 24);
            this.dataGrid1.Name = "dataGrid1";
            this.dataGrid1.Size = new System.Drawing.Size(592, 128);
            this.dataGrid1.TabIndex = 0;
            //
            // dataGrid2
            //
            this.dataGrid2.DataMember = "";
            this.dataGrid2.HeaderForeColor = System.Drawing.SystemColors.ControlText;
            this.dataGrid2.Location = new System.Drawing.Point(16, 168);
            this.dataGrid2.Name = "dataGrid2";
            this.dataGrid2.Size = new System.Drawing.Size(592, 152);
            this.dataGrid2.TabIndex = 1;
            //
            // button2
            //
            this.button2.Location = new System.Drawing.Point(248, 352);
            this.button2.Name = "button2";
            this.button2.Size = new System.Drawing.Size(120, 23);
            this.button2.TabIndex = 3;
            this.button2.Text = "Update";
            this.button2.Click += new System.EventHandler(this.button2_Click);
            //
            // Form1
            //
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(616, 405);
            this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                                          this.button2,
                                                                          this.dataGrid2,
                                                                          this.dataGrid1});
            this.Name = "Form1";
            this.Text = "Form1";
            ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).EndInit();
            this.ResumeLayout(false);

        }
        #endregion

        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main() {
            Application.Run(new Form1());
        }

        private void button2_Click(object sender, System.EventArgs e) {
          daCus.Update( ds, "customers" );
          daOrd.Update( ds, "orders" );
        }
    }
}

// EOF


15-4: master_detail_direct.cs

(See below for Connector/NET-Visual Studio 2005 version)
// Form1.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

namespace master_detail_direct {
    /// <summary>
    /// Summary description for Form1.
    /// </summary>
    public class Form1 : System.Windows.Forms.Form {
        private CoreLab.MySql.MySqlConnection mySqlConnection1;
        private System.Windows.Forms.DataGrid masterGrid;
        private System.Windows.Forms.DataGrid detailGrid;
        private System.Windows.Forms.Button update;
        private System.Data.DataSet dataSet1;
        private CoreLab.MySql.MySqlCommand masterCommand;
        private CoreLab.MySql.MySqlCommand detailCommand;
        private CoreLab.MySql.MySqlDataAdapter masterDataAdapter;
        private CoreLab.MySql.MySqlDataAdapter detailDataAdapter;
        private CoreLab.MySql.MySqlCommandBuilder masterCommandBuilder;
        private CoreLab.MySql.MySqlCommandBuilder detailCommandBuilder;
        private System.Data.DataColumn dataColumn1;
        private System.Data.DataColumn dataColumn2;
        private System.Data.DataColumn dataColumn3;
        private System.Data.DataColumn dataColumn4;
        private System.Data.DataColumn dataColumn5;
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.Container components = null;

        public Form1() {
            // Required for Windows Form Designer support
            InitializeComponent();
            // TODO: Add any constructor code after InitializeComponent call
            mySqlConnection1.Open();
            dataSet1.Relations.Clear();
            masterDataAdapter.Fill( dataSet1, "customers" );
            detailDataAdapter.Fill( dataSet1, "orders" );
            dataSet1.Relations.Add( "CustOrd",
                                   dataSet1.Tables["customers"].Columns["customerID"],
                                   dataSet1.Tables["orders"].Columns["customerID"] );
            masterGrid.SetDataBinding( dataSet1, "customers" );
            detailGrid.SetDataBinding( dataSet1, "customers.CustOrd" );
        }

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        protected override void Dispose( bool disposing ) {
            if( disposing )    {
                if (components != null) {
                    components.Dispose();
                }
            }
            base.Dispose( disposing );
        }

        #region Windows Form Designer generated code
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent() {
            this.mySqlConnection1 = new CoreLab.MySql.MySqlConnection();
            this.masterGrid = new System.Windows.Forms.DataGrid();
            this.detailGrid = new System.Windows.Forms.DataGrid();
            this.update = new System.Windows.Forms.Button();
            this.dataSet1 = new System.Data.DataSet();
            this.dataColumn1 = new System.Data.DataColumn();
            this.dataColumn2 = new System.Data.DataColumn();
            this.dataColumn3 = new System.Data.DataColumn();
            this.dataColumn4 = new System.Data.DataColumn();
            this.dataColumn5 = new System.Data.DataColumn();
            this.masterCommand = new CoreLab.MySql.MySqlCommand();
            this.detailCommand = new CoreLab.MySql.MySqlCommand();
            this.masterDataAdapter = new CoreLab.MySql.MySqlDataAdapter();
            this.detailDataAdapter = new CoreLab.MySql.MySqlDataAdapter();
            this.masterCommandBuilder = new CoreLab.MySql.MySqlCommandBuilder();
            this.detailCommandBuilder = new CoreLab.MySql.MySqlCommandBuilder();
            ((System.ComponentModel.ISupportInitialize)(this.masterGrid)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.detailGrid)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataSet1)).BeginInit();
            this.SuspendLayout();
            //
            // mySqlConnection1
            //
            this.mySqlConnection1.ConnectionString =
                 "User Id=USR;Password=PSWD;Host=localhost;Database=nwib;";
            this.mySqlConnection1.Name = "mySqlConnection1";
            //
            // masterGrid
            //
            this.masterGrid.DataMember = "";
            this.masterGrid.DataSource = this.dataSet1;
            this.masterGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
            this.masterGrid.Location = new System.Drawing.Point(16, 8);
            this.masterGrid.Name = "masterGrid";
            this.masterGrid.Size = new System.Drawing.Size(520, 104);
            this.masterGrid.TabIndex = 0;
            //
            // detailGrid
            //
            this.detailGrid.DataMember = "";
            this.detailGrid.DataSource = this.dataSet1;
            this.detailGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText;
            this.detailGrid.Location = new System.Drawing.Point(16, 120);
            this.detailGrid.Name = "detailGrid";
            this.detailGrid.Size = new System.Drawing.Size(520, 120);
            this.detailGrid.TabIndex = 1;
            //
            // update
            //
            this.update.Location = new System.Drawing.Point(232, 256);
            this.update.Name = "update";
            this.update.TabIndex = 2;
            this.update.Text = "Update";
            //
            // dataSet1
            //
            this.dataSet1.DataSetName = "NewDataSet";
            this.dataSet1.EnforceConstraints = false;
            this.dataSet1.Locale = new System.Globalization.CultureInfo("en-US");
            //
            // masterCommand
            //
            this.masterCommand.CommandText =
              "SELECT \r\n  nwib.customers.CustomerID, \r\n  nwib.customers.companyname, \r\n  nwib.cu" +
              "stomers.contactname, \r\n  nwib.customers.city, \r\n  nwib.customers.country\r\nFROM\r\n" +
              "  nwib.customers\r\nORDER BY\r\n  nwib.customers.companyname";
            this.masterCommand.Connection = this.mySqlConnection1;
            this.masterCommand.Name = "masterCommand";
            //
            // detailCommand
            //
            this.detailCommand.CommandText =
              "SELECT \n  orders.OrderID, \n  orders.CustomerID, \n  orders.OrderDate, \n  orders.Sh" +
              "ipName, \n  orders.ShippedDate\nFROM\n  orders";
            this.detailCommand.Connection = this.mySqlConnection1;
            this.detailCommand.Name = "detailCommand";
            //
            // masterDataAdapter
            //
            this.masterDataAdapter.MissingSchemaAction = System.Data.MissingSchemaAction.AddWithKey;
            this.masterDataAdapter.SelectCommand = this.masterCommand;
            //
            // detailDataAdapter
            //
            this.detailDataAdapter.MissingSchemaAction = System.Data.MissingSchemaAction.AddWithKey;
            this.detailDataAdapter.SelectCommand = this.detailCommand;
            //
            // masterCommandBuilder
            //
            this.masterCommandBuilder.DataAdapter = this.masterDataAdapter;
            //
            // detailCommandBuilder
            //
            this.detailCommandBuilder.DataAdapter = this.detailDataAdapter;
            //
            // Form1
            //
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(544, 293);
            this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                                          this.update,
                                                                          this.detailGrid,
                                                                          this.masterGrid});
            this.Name = "Form1";
            this.Text = "Form1";
            ((System.ComponentModel.ISupportInitialize)(this.masterGrid)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.detailGrid)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataSet1)).EndInit();
            this.ResumeLayout(false);

        }
        #endregion

        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main() {
            Application.Run(new Form1());
        }
    }
}

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

/*
  mysqlqry.c
  Compile in Linux: gcc -c -Iusr/local/include/mysql mysqlqry.c
          in Windows: lcc mysqlqry1.c -o %mysqlqry.obj  
  Link in Linux: gcc -o mysqlqry.o -L/usr/local/lib/mysql -lmysqlclient -lm
       in Windows: lcc mysqlqry.obj -Lmysqlclient.lib
 */

#include <mysqlqry.h>

#define MYSQL_TRUE 0

// CONNECTION PARAMS
char host[] = "localhost";
char user[] = "root";
char pswd[] = "";
char db[] = "nwib";
int port = 3306;

// QUERIES
#define Q1 "SELECT customers.customerID AS CustID,"
#define Q2 "       orders.orderID AS OrderID,"
#define Q3 "       orderdate AS 'Order Date'"
#define Q4 "FROM customers INNER JOIN orders USING(customerID)"
#define COL_COUNT 3

const char updqry[] = "UPDATE customers SET country='Méjico' WHERE country='Mexico'";

// INFO STRINGS
#define INFO_CONN   "CONNECTION CALL to mysql_init, mysql_real_connect\n"
#define INFO_QRY    "EXECUTE QUERY WITH mysql_query\n"
#define INFO_FETCH  "FETCH RESULT into resultset with mysql_store_result\n"
#define INFO_COUNT  "GET ROW, COL COUNTS with mysql_num_fields, mysql_num_rows\n"
#define INFO_COLS   "CALCULATE MAX COLUMN WIDTHS with mysql_fetch_field\n"
#define INFO_TABLE  "PRINT RESULTSET header and data rows\n\n"

// ERROR STRINGS
#define ERR_CONN    "Connection failed"
#define ERR_QUERY   "Call to mysql_query failed"
#define ERR_RESULT  "NULL resultset from mysql_store_result"

unsigned int cols;
unsigned long rows;
int res;
char qry[1024];

// MYSQL STRUCTURES
MYSQL *conn;
MYSQL_RES *rset;
MYSQL_FIELD *column, *columns[COL_COUNT];
MYSQL_ROW row;

int main(int argc, char *argv[] ) {
  unsigned int icol;
  unsigned long lrow;

  // GRAB COMMAND LINE ARGS IF ANY
  arg_parse( argc, argv );

  // INFO HEADER
  printf( "HOST:%s USER:%s DB:%s QUERY:\n", host, user, db );
  printf( "%s\n%s\n%s\n%s\n\n", Q1, Q2, Q3, Q4 );

  // ASSEMBLE QUERY
  strcpy( qry, Q1 );
  strcat( qry, Q2 );
  strcat( qry, Q3 );
  strcat( qry, Q4 );

  printf( INFO_CONN );

  // CONNECT
  if(( conn = mysql_init( NULL )) == NULL ) {
    err_exit( "Cannot initialise connection object" );
  }
  if( mysql_real_connect( conn,
                          host,
                          user,
                          pswd,
                          db,
                          port, NULL, 0 ) == NULL ) {
    err_exit( ERR_CONN );
  }

  // DISPLAY MYSQL CONNECTION INFO
  printf( "%s%s\n%s%ul\n%s%s\n%s%u\n%s%s\n%s%s\n",
          "SERVER STATUS FROM mysql_stat(conn): ",
          mysql_stat( conn ),
          "THREAD ID FROM mysql_thread_id(conn): ",
          mysql_thread_id( conn ),
          "HOST, METHOD FROM mysql_get_host_info(conn): ",
          mysql_get_host_info( conn ),
          "PROTOCOL VERSION FROM mysql_get_proto_info(conn): ",
          mysql_get_proto_info( conn ),
          "SERVER VERSION FROM mysql_get_server_info(conn): ",
          mysql_get_server_info( conn ),
          "CLIENT VERSION FROM mysql_get_client_info(): ",
          mysql_get_client_info()
      );


  // EXECUTE AN UPDATE
  if( mysql_real_query( conn, updqry, strlen( updqry )) != MYSQL_TRUE ) {
    err_exit( "Customer update failed" );
  } else {
    printf( "%u rows affected by update\n",
            (long ) mysql_affected_rows( conn ) );
  }

  // EXECUTE A QUERY
  printf( INFO_QRY );
  if(( res = mysql_query( conn, qry )) != MYSQL_TRUE ) {
    err_exit( ERR_QUERY );
  }

  // CHECK THAT QUERY RESULT IS NOT EMPTY
  printf( INFO_FETCH );
  if(( rset = mysql_store_result( conn )) == NULL ) {
    err_exit( ERR_RESULT );
  }

  // ASSEMBLE COLUMN INFO, CALCULATE COL WIDTHS, PRINT COL HEADERS
  printf( INFO_COUNT );
  cols = mysql_num_fields( rset );
  rows = (long) mysql_num_rows( rset );
  printf( INFO_COLS );
  mysql_data_seek( rset, 0 );

  mysql_field_seek( rset, 0 );
  for( icol = 0; icol < cols; icol++ ) {
    column = mysql_fetch_field( rset );
    column->max_length = (unsigned long) strlen( column->name );
    columns[icol] = column;
  }

  for( lrow = 0; lrow < rows; lrow++ ) {
    if(( row = mysql_fetch_row( rset )) != NULL ) {
      for( icol = 0; icol < cols; icol++ ) {
        column = columns[icol];
        if( column != NULL ) {
          if( strlen( row[icol] ) > column->max_length ) {
            column->max_length = (unsigned long) strlen( row[icol] );
          }
        }
      }
    }
  }
  printf( INFO_TABLE );
  for( icol = 0; icol < cols; icol++ ) {
    printf( "%-*s ", columns[icol]->max_length, strupr( columns[icol]->name ));
  }
  printf( "\n" );

  // PRINT RESULTSET DATA
  mysql_data_seek( rset, 0 );
  for( lrow = 0; lrow < rows; lrow++ ) {
    if(( row = mysql_fetch_row( rset )) != NULL ) {
    //  mysql_field_seek( rset, 0 );
      for( icol = 0; icol < cols; icol++ ) {
        // column = mysql_fetch_field( rset );
        printf( "%-*s ", columns[icol]->max_length, row[icol] );
      }
      printf( "\n" );
    }
  }

  // SUMMARY
  printf( "%d %s\n", rows, "rows returned." );

  // CLEAN UP
  mysql_free_result( rset );
  mysql_close( conn );
  return 0;
}

void arg_parse( int argc, char **argv ) {
  int i, j, k, len;
  char *arg;
  char *hostargs[3] = { host, "-h", "host=" };
  char *userargs[3] = { user, "-u", "user=" };
  char *pswdargs[3] = { pswd, "-p", "password=" };
  char *dbargs[3]   = { db,   "-D", "database=" };
  char **args[4];
  args[0] = hostargs;
  args[1] = userargs;
  args[2] = pswdargs;
  args[3] = dbargs;
  for( i = 1; i < argc; i++ ) {
    arg = argv[i];
    for( j = 0; j < 4; j++ ) {
      len = (int) strlen( args[j][2] );
        for( k = 0; k < 3; k++ ) {
        if(( strncmp, arg, args[j][2], 1 ) == 0 ) {
          strcpy( args[j][0], arg+2 );
        } else if( strncmp( arg, args[j][2], len ) == 0 ) {
          strcpy( args[j][0], arg+len );
        }
      }
    }
  }
}

void err_exit( char *prefix ) {
  fprintf( stderr, "%s: Error #%d %s\n",
           prefix,
           mysql_errno( conn ),
           mysql_error( conn ) );
  exit( 1 );
}

// EOF

16-2: mysqlqry.h

/*
  mysqlqry.h
 */

#include <stdio.h>
#include <strings.h>
#include <stdlib.h>

#define __LCC__    // MYSQL.H BUG WORKAROUND
#include <mysql.h>

void arg_parse( int argc, char *argv[] );
void err_exit( char *s );

// EOF

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

#include "stdafx.h"

#define MYSQL_TRUE 0

// CONNECTION PARAMS
char host[] = "localhost";
char user[] = "root";
char pswd[] = "";
char db[] = "nwib";
int port = 3306;

// QUERY
#define Q1 "SELECT customers.customerID AS CustID,"
#define Q2 "       orders.orderID AS OrderID,"
#define Q3 "       orderdate AS 'Order Date'"
#define Q4 "FROM customers INNER JOIN orders USING(customerID)"
#define COL_COUNT 3

const char updqry[] = "UPDATE customers SET country='Méjico' WHERE country='Mexico'";

// INFO STRINGS
#define INFO_CONN   "CONNECTION CALL to mysql_init, mysql_real_connect\n"
#define INFO_QRY    "EXECUTE QUERY WITH mysql_query\n"
#define INFO_FETCH  "FETCH RESULT into resultset with mysql_store_result\n"
#define INFO_COUNT  "GET ROW, COL COUNTS with mysql_num_fields, mysql_num_rows\n"
#define INFO_COLS   "CALCULATE MAX COLUMN WIDTHS with mysql_fetch_field\n"
#define INFO_TABLE  "PRINT RESULTSET header and data rows\n\n"

// ERROR STRINGS
#define ERR_CONN    "Connection failed"
#define ERR_QUERY   "Call to mysql_query failed"
#define ERR_RESULT  "NULL resultset from mysql_store_result"

unsigned int cols;
unsigned long rows;
int res;
char qry[1024];

// MYSQL STRUCTURES
MYSQL *conn;
MYSQL_RES *rset;
MYSQL_FIELD *column, *columns[COL_COUNT];
MYSQL_ROW row;

int _tmain(int argc, _TCHAR* argv[] ) {
  unsigned int icol;
  unsigned long lrow;

  // GRAB COMMAND LINE ARGS IF ANY
  arg_parse( argc, argv );

  // INFO HEADER
  printf( "HOST:%s USER:%s DB:%s QUERY:\n", host, user, db );
  printf( "%s\n%s\n%s\n%s\n\n", Q1, Q2, Q3, Q4 );

  // ASSEMBLE QUERY
  strcpy( qry, Q1 );
  strcat( qry, Q2 );
  strcat( qry, Q3 );
  strcat( qry, Q4 );

  // CONNECT
  printf( INFO_CONN );
  if(( conn = mysql_init( NULL )) == NULL ) {
    err_exit( "Cannot initialise connection object" );
  }
  if( mysql_real_connect( conn,
                          host,
                          user,
                          pswd,
                          db,
                          port, NULL, 0 ) == NULL ) {
    err_exit( ERR_CONN );
  }

  // DISPLAY MYSQL CONNECTION INFO
  printf( "%s%s\n%s%ul\n%s%s\n%s%u\n%s%s\n%s%s\n",
          "SERVER STATUS FROM mysql_stat(conn): ",
          mysql_stat( conn ),
          "THREAD ID FROM mysql_thread_id(conn): ",
          mysql_thread_id( conn ),
          "HOST, METHOD FROM mysql_get_host_info(conn): ",
          mysql_get_host_info( conn ),
          "PROTOCOL VERSION FROM mysql_get_proto_info(conn): ",
          mysql_get_proto_info( conn ),
          "SERVER VERSION FROM mysql_get_server_info(conn): ",
          mysql_get_server_info( conn ),
          "CLIENT VERSION FROM mysql_get_client_info(): ",
          mysql_get_client_info()
      );


  // EXECUTE AN UPDATE
  if( mysql_real_query( conn, updqry, strlen( updqry )) != MYSQL_TRUE ) {
    err_exit( "Customer update failed" );
  } else {
    printf( "%u rows affected by update\n",
            (long ) mysql_affected_rows( conn ) );
  }

  // EXECUTE A QUERY
  printf( INFO_QRY );
  if(( res = mysql_query( conn, qry )) != MYSQL_TRUE ) {
    err_exit( ERR_QUERY );
  }

  // CHECK THAT QUERY RESULT IS NOT EMPTY
  printf( INFO_FETCH );
  if(( rset = mysql_store_result( conn )) == NULL ) {
    err_exit( ERR_RESULT );
  }

  // ASSEMBLE COLUMN INFO, CALCULATE COL WIDTHS, PRINT COL HEADERS
  printf( INFO_COUNT );
  cols = mysql_num_fields( rset );
  rows = (long) mysql_num_rows( rset );
  printf( INFO_COLS );
  mysql_data_seek( rset, 0 );

  mysql_field_seek( rset, 0 );
  for( icol = 0; icol < cols; icol++ ) {
    column = mysql_fetch_field( rset );
    column->max_length = (unsigned long) strlen( column->name );
    columns[icol] = column;
  }

  for( lrow = 0; lrow < rows; lrow++ ) {
    if(( row = mysql_fetch_row( rset )) != NULL ) {
      for( icol = 0; icol < cols; icol++ ) {
        column = columns[icol];
        if( column != NULL ) {
          if( strlen( row[icol] ) > column->max_length ) {
            column->max_length = (unsigned long) strlen( row[icol] );
          }
        }
      }
    }
  }
  printf( INFO_TABLE );
  for( icol = 0; icol < cols; icol++ ) {
    printf( "%-*s ", columns[icol]->max_length, strupr( columns[icol]->name ));
  }
  printf( "\n" );

  // PRINT RESULTSET DATA
  mysql_data_seek( rset, 0 );
  for( lrow = 0; lrow < rows; lrow++ ) {
    if(( row = mysql_fetch_row( rset )) != NULL ) {
    //  mysql_field_seek( rset, 0 );
      for( icol = 0; icol < cols; icol++ ) {
        // column = mysql_fetch_field( rset );
        printf( "%-*s ", columns[icol]->max_length, row[icol] );
      }
      printf( "\n" );
    }
  }

  // SUMMARY
  printf( "%d %s\n", rows, "rows returned." );

  // CLEAN UP
  mysql_free_result( rset );
  mysql_close( conn );
  return 0;
}

void arg_parse( int argc, _TCHAR **argv ) {
  int i, j, k, len;
  char *arg;
  char *hostargs[3] = { host, "-h", "host=" };
  char *userargs[3] = { user, "-u", "user=" };
  char *pswdargs[3] = { pswd, "-p", "password=" };
  char *dbargs[3]   = { db,   "-D", "database=" };
  char **args[4];
  args[0] = hostargs;
  args[1] = userargs;
  args[2] = pswdargs;
  args[3] = dbargs;
  for( i = 1; i < argc; i++ ) {
    arg = argv[i];
    for( j = 0; j < 4; j++ ) {
      len = (int) strlen( args[j][2] );
        for( k = 0; k < 3; k++ ) {
        if(( strncmp, arg, args[j][2], 1 ) == 0 ) {
          strcpy( args[j][0], arg+2 );
        } else if( strncmp( arg, args[j][2], len ) == 0 ) {
          strcpy( args[j][0], arg+len );
        }
      }
    }
  }
}

void err_exit( char *prefix ) {
  fprintf( stderr, "%s: Error #%d %s\n",
           prefix,
           mysql_errno( conn ),
           mysql_error( conn ) );
  exit( 1 );
}

// EOF

16-4: stdafx.h for mysqlqry.cpp

// stdafx.h
#pragma once

#define WIN32_LEAN_AND_MEAN       
#include <stdio.h>
#include <stdlib.h>
#include <tchar.h>

// TODO: reference additional headers your program requires here
#define __LCC__       // MYSQL.H BUG: WITHOUT THIS, WINSOCK IS NOT INCLUDED
#include <mysql.h>

void arg_parse( int argc, _TCHAR *argv[] );
void err_exit( char *s );

// EOF

 


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 ;