Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Java > Java Open Source > BOOK: Professional Jakarta Struts
|
BOOK: Professional Jakarta Struts
This is the forum to discuss the Wrox book Professional Jakarta Struts by James Goodwill, Richard Hightower; ISBN: 9780764544378
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional Jakarta Struts section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 10th, 2005, 01:31 PM
Authorized User
 
Join Date: Oct 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Chapter 14 Code example in Oracle DB

I encountered something strange...

First off, I don't have MySQL so I have done all my code examples in either (or both) of MS Access or Oracle.

If the code for example 14, there is a table named "departments". When I log in to the database using a regular DB tool (SQL Navigator) the SQL code provided in EmployeeData.java works like a champ with the caveat that you must get rid of the ambiguous references to depid and roleid by specifying which table to pull from. This code change is also required within the .java file

But, the SQL when run from the .java file will not return any data if the join to the departments table is used. No exceptions (SQL or otherwise) are thrown.


I was able to get data in three ways. Either by changing the table name from "departments" to something else (I used "dep"), by using a view that provided al alternate name for the table, or by adding the schema name to the table (I am logged in using the schema owner ID - same as through the SQL Navigator above)

The modified SQL looks like this:

Code:
rs = stmt.executeQuery("select username, name, rolename, phone, email, employees.roleid as roleid, employees.depid as depid, depname from employees, roles, edi_own.departments where employees.roleid = roles.roleid and employees.depid = edi_own.departments.depid");
The name "edi_own" is the schema name I am presently using.

Has anyone else encountered this? It appears as if "departments" is either a keyword or matches some other table from which the join returns zero rows - but ONLY when connecting via JDBC not via an external query tool.

My connection info is changed to:
Code:
    <data-source type="org.apache.commons.dbcp.BasicDataSource">
      <set-property property="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
      <set-property property="url"             value="jdbc:oracle:thin:@server.domain.com:1521:OHOST" />
      <set-property property="username"        value="edi_own" />
      <set-property property="password"        value="password" />
    </data-source>
The SQL to build the examples has to be changed also - this this...
Code:
-- Altered to run on Oracle
create table employees
(
  username varchar(15) not null primary key,
  password varchar(15) not null,
  roleid integer not null,
  name varchar(30) not null,
  phone varchar(15) not null,
  email varchar(30) not null,
  depid integer not null
);

insert into employees values('abrickey', '$word', 1, 'Art Brickey', '(303) 555-1214', 'abrickey@where.com', 2);
insert into employees values('tharris', 'ralph', 1, 'Todd Harris', '(303) 555-9482', 'tharris@where.com', 2);
insert into employees values('sriley', '$mindy$', 2, 'Sean Riley', '(303) 555-3412', 'sriley@where.com', 4);
insert into employees values('jgoodwill', '$pass$', 1, 'James Goodwill', '(303) 555-1214', 'jgoodwill@where.com', 3);
insert into employees values('tgray', 'password', 2, 'Tim Gray', '(303) 555-9876', 'tgray@where.com', 1);

create table roles
(
  roleid integer not null primary key,
  rolename varchar(30) not null
);

insert into roles values(1, 'manager');
insert into roles values(2, 'employee');


create table departments
(
  depid integer not null primary key,
  depname varchar(30) not null
);

insert into departments values(1, 'Administration');
insert into departments values(2, 'Network');
insert into departments values(3, 'Sales');
insert into departments values(4, 'Engineering');
As Oracle uses single quotes and not double quotes for strings (double quotes are reserved for column names that contain spaces)

I have omitted the other SQL code changes, but every instance of embedded double quotes shown as \" in the code must be changed to the single quote ' in order to work.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Chapter 14 Code page 620, help needed! Safi BOOK: Beginning ASP 3.0 3 December 8th, 2010 12:43 PM
Chapter 14 Code Error? pherank BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 1 July 4th, 2008 09:05 PM
code chapter 14 & 15 pierrethienpont BOOK: Professional VB 2005 ISBN: 0-7645-7536-8 1 February 24th, 2007 03:26 PM
Problem with code on Chapter 14 aargyri BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 3 June 6th, 2006 03:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.