We will use a semi-freeware "basically free" SQL database server, MySQL, originally written by Michael (Monty) Widenius and MM.mysql JDBC driver written by Mark Matthews for our exercises on Web-Database interface. mysql.pm is used to access the MySQL database from Perl scripts.
Help from Shanti Peyathevar on the JDBC-ODBC interface is greatly appreciated.
Here % is a wild card character to match any host.
We have set up crestone.uccs.edu to allow database access to its mysql database server from any uccs.edu machines, including PC dial up from home. Use cs522p1 as database login and cn98cs as database password. Please refrain from creating huge tables.
#!/usr/bin/perl
# This is a test of simple sql for creating a table
use Mysql;
$|= 1;
# Autoflush
$host = "crestone.uccs.edu";
$test_db="test";
$dbh = Mysql->Connect($host,
$test_db, "cs522p1", "cn98cs") || die "Can't connect:$Mysql::db_errstr\n";
$dbh->SelectDB($test_db) || die "Can't use database $test_db: $Mysql::db_errstr\n";
$firsttable = "COOKIESIE";
$secondtable = "SUPPLIERS";
$dbh->Query("drop
table $firsttable");
$dbh->Query("drop table $secondtable");
print "Creating tables $firsttable and $secondtable in database $test_db\n";
$dbh->Query("create
table $firsttable (COOKIE_NAME varchar(32) not null, SUP_ID int(6), PRICE
float(8), SALES int(9), TOTAL int(9))") or die $Mysql::db_errstr;
# float can only be float(4) or float(8)
$dbh->Query("create
table $secondtable (SUP_ID int(6) not null, SUP_NAME varchar(32), ADDRESS
varchar(50), key(SUP_ID))") or die $Mysql::db_errstr;
$dbh=0;
# Close handler
# Insert process
$dbh = Mysql->Connect($host) || die "Can't connect: $Mysql::db_errstr\n";
$dbh->SelectDB($test_db) || die "Can't use database $test_db:
$Mysql::db_errstr\n";
$first_id=1; $second_id=1;
$first_count=$second_count=0;
print "Writing started\n";
$sth=$dbh->Query("insert
into $firsttable values ('CHOCOLATE', 101, 1.30, 0, 0)") || die
"Got error on insert: $Mysql::db_errstr\n";
die "Row not inserted, aborting\n" if
($sth->affected_rows != 1);
....
# reading start
$dbh = Mysql->Connect($host) || die "Can't connect: $Mysql::db_errstr\n";
$dbh->SelectDB($test_db) || die "Can't use database $test_db:
$Mysql::db_errstr\n";
$sth=$dbh->Query("select
COOKIE_NAME, PRICE FROM $firsttable WHERE PRICE < 1.50")
|| die "Got error on select: $Mysql::db_errstr\n";
print "result of select COOKIE_NAME, PRICE FROM $firsttable
WHERE PRICE < 1.50\n";
for ($i=0; $i<$sth->numrows; $i++)
{
@row = $sth->fetchrow
or
warn "$firsttable didn't find a matching row";
print "@row\n";
}
$sth=$dbh->Query("select * FROM SUPPLIERS") || die "Got error
on select: $Mysql::db_errstr\n";
for ($i=0; $i<$sth->numrows; $i++) {
@row = $sth->fetchrow or warn "$firsttable
didn't find a matching row";
print "@row\n";
}
Inside init():
try {
/* register the driver, avoiding a bug
in some JDK1.1 implementation
such as IE or early
version of Netscape 4.0
used to be // Class.forName("org.gjt.mm.mysql.Driver");
*/
D = (Driver) Class.forName("org.gjt.mm.mysql.Driver").newInstance();
// org.gjt.mm.mysql.Driver
is the JDBC driver class file name, where 'org' is a subdirectory name,
// located in the directory
as the CookieJDBC.class. You can also use soft link.
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException:
");
System.err.println(e.getMessage());
}
try {
// con = DriverManager.getConnection(url);
// avoid bugs in IE, it complains cannot
find the driver
// use the following D.connect()
String url = protocol+machineTf.getText()+portNo+"/"+dbTf.getText()+dbParameters;
con = D.connect(url,
null);
stmt = con.createStatement();
String sqlcmd
= "DROP TABLE COOKIES";
stmt.executeUpdate(sqlcmd);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException in dropping
table: " + ex.getMessage());
skipCreateTable = false;
// not abel to drop table, assume not found
}
if (!skipCreateTable) {
sqlResult.setText("creating table...");
try {
String url = protocol+machineTf.getText()+portNo+"/"+dbTf.getText()+dbParameters;
con = D.connect(url, null);
stmt = con.createStatement();
String
sqlcmd2 = "create table COOKIES (COOKIE_NAME varchar(32), SUP_ID int(6),
PRICE float(8), SALES int(9), TOTAL int(9))";
stmt.executeUpdate(sqlcmd2);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException
in creating table: " + ex.getMessage());
skipInsertTableEntry = true;
// not able to create table, use old one
}
}
if (!skipInsertTableEntry) {
sqlResult.setText("insert rows to COOKIES
table...");
try {
String url = protocol+machineTf.getText()+portNo+"/"+dbTf.getText()+dbParameters;
con = D.connect(url, null);
stmt
= con.createStatement();
String
sqlcmd1 = "insert into COOKIES values ('CHOCOLATE', 101, 1.30, 0, 0)";
String
sqlcmd2 = "insert into COOKIES values ('M&M', 49, 2.49, 0, 0)";
String
sqlcmd3 = "insert into COOKIES values ('MINT', 150, 1.00, 0, 0)";
stmt.executeUpdate(sqlcmd1);
stmt.executeUpdate(sqlcmd2);
stmt.executeUpdate(sqlcmd3);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException
in inserting table: " + ex.getMessage());
}
}
try {
String url = protocol+machineTf.getText()+portNo+"/"+dbTf.getText()+dbParameters;
con = D.connect(url, null);
stmt = con.createStatement();
String sqlselect = "select
* from COOKIES";
ResultSet rs =
stmt.executeQuery(sqlselect);
sqlResult.setText("
COOKIES TABLBE\nCOOKIE_NAME SUP_ID PRICE\n");
sqlResult.append("========================\n");
while (rs.next())
{
cookieName = new StringBuffer(rs.getString(1));
# here 1 is column 1 data of return row.
cookieName = center(cookieName,
10);
# we use StringBuffer so can modify it.
supplierID = new StringBuffer(rs.getString(2));
supplierID = right(supplierID,
6);
price = new StringBuffer(rs.getString(3));
sqlResult.append(cookieName+"
"+supplierID+" "+price+"\n");
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException in reading table: " +
ex.getMessage());
}
In public boolean action(Event e, Object o) {
try {
//con = DriverManager.getConnection(url);
String url = protocol+machineTf.getText()+portNo+"/"+dbTf.getText()+dbParameters;
// String
url = protocol+machineTf.getText()+portNo+dbParameters;
con = D.connect(url, null);
stmt = con.createStatement();
String sqlselect = sqlStatement.getText();
ResultSet rs =
stmt.executeQuery(sqlselect);
ResultSetMetaData
rsmd = rs.getMetaData(); // get the column names
StringBuffer sname;
StringBuffer rsTitle = new StringBuffer("");
String bar = "============";
StringBuffer titleBar = new StringBuffer("");
int icount = rsmd.getColumnCount();
int i;
for (i = 1; i <= icount; i++) {
sname = new StringBuffer(rsmd.getColumnName(i));
sname = center(sname, 12);
rsTitle.append(sname);
titleBar.append(bar);
}
sqlResult.setText(rsTitle+"\n"+titleBar+"\n");
StringBuffer rsEntry = new StringBuffer("");
while (rs.next())
{
for (i = 1; i <= icount;
i++) {
sname = new
StringBuffer(rs.getString(i));
sname = center(sname,
12);
rsEntry.append(sname);
}
sqlResult.append(rsEntry+"\n");
rsEntry = new StringBuffer("");
}
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException in select: " + ex.getMessage());
}