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', '[email protected]', 2);
insert into employees values('tharris', 'ralph', 1, 'Todd Harris', '(303) 555-9482', '[email protected]', 2);
insert into employees values('sriley', '$mindy$', 2, 'Sean Riley', '(303) 555-3412', '[email protected]', 4);
insert into employees values('jgoodwill', '$pass$', 1, 'James Goodwill', '(303) 555-1214', '[email protected]', 3);
insert into employees values('tgray', 'password', 2, 'Tim Gray', '(303) 555-9876', '[email protected]', 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.
Chapter 14 Code example in Oracle DB
