#Platforms: Windows 98
#Perl version: 5.8.0
#MySQL version: 4.0.16

sub create_account {
   my ($username, $level) = @_;
   $dbh->{RaiseError} = 1;
   $dbh->begin_work;
 
   eval {
   ## (1) Insert username into members (TYPE=INNODB)
   $sth = $dbh->prepare(qq{
	INSERT INTO members VALUES(?, ?, ?) });
   $sth->execute(undef,$username,$level);

   ## (2) Select from members to get last id
   $sql = qq{ SELECT LAST_INSERT_ID() FROM members };
   $sth = $dbh->prepare($sql);
   my $last_id = $sth->execute();

   # Tweaking $last_id to test rollback
   #$last_id = 5;

   ## (3) Insert last_id into preference  (TYPE=INNODB)
   $sth = $dbh->prepare(qq{
	INSERT INTO preference VALUES(?, ?, ?) });
   $sth->execute($last_id,undef,undef);

   }; # eval
   if ($@) {
	eval { 
          $dbh->rollback();
          # Call bail_out and die
          bail_out("Error at new_accout.");
        };
   }
   $dbh->commit();
   $dbh->{RaiseError} = 0;
}
## Definitions of tables
DROP TABLE IF EXISTS members;
CREATE TABLE members (
	member_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(20) NULL,
	level CHAR(2) NULL,
) TYPE=INNODB;

DROP TABLE IF EXISTS preference;
CREATE TABLE preference (
	member_id MEDIUMINT UNSIGNED NOT NULL,
	pref1 VARCHAR(20) NOT NULL,
	pref2 CHAR(10) NOT NULL,
	PRIMARY KEY (member_id, pref1),
	FOREIGN KEY (member_id) REFERENCES members (member_id) ON DELETE CASCADE,
	UNIQUE (pref1)
) TYPE=INNODB;