Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Java > Java Open Source > BOOK: Professional Jakarta Struts
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 10th, 2005, 01:31 PM
Authorized User
 
Join Date: Oct 2004
Location: , , .
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.
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:02 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.