I set up and connected a derby database called timezonedb
Then I used the SQL scrapbook to create the table Users.
As you can see, the table was successfully created, added to, and queried via the scrapbook. The problem is, it doesn't seem to be in any of the schemas listed in timezonedb. When I try to run my program, it seems to think that password is a schema. Java code involved below:
package cis407;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.Date;
import java.util.TimeZone;
import java.util.logging.Logger;
import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.sql.DataSource;
/**
This bean formats the local time of day for a given date
and city.
*/
@ManagedBean
@SessionScoped
public class TimeZoneBean
{
@Resource(name="jdbc/__default")
private DataSource source;
private String userName;
private String password;
private DateFormat timeFormatter;
private String city;
private TimeZone zone;
private String errorMessage;
/**
Initializes the formatter.
*/
public TimeZoneBean()
{
timeFormatter = DateFormat.getTimeInstance();
}
public String getPassword()
{
return password;
}
/**
* setter for password property
* there is no getter for the password because there is no reason to ever
* return the password
* @param password the password for a given user
*/
public void setPassword(String pass)
{
password = pass;
}
/**
* getter for applicable error message
* @return the error message
*/
public String getErrorMessage()
{
return errorMessage;
}
/**
* Setter for username property
* @param name the user who is logged in
*/
public void setUserName(String name)
{
userName = name;
}
/**
* getter for username property
* @return the user who is logged in
*/
public String getUserName()
{
return userName;
}
/**
Setter for city property.
@param aCity the city for which to report the local time
*/
public void setCity(String aCity)
{
city = aCity;
}
/**
Getter for city property.
@return the city for which to report the local time
*/
public String getCity()
{
return city;
}
/**
Read-only time property.
@return the formatted time
*/
public String getTime()
{
if (zone == null) return "not available";
timeFormatter.setTimeZone(zone);
Date time = new Date();
String timeString = timeFormatter.format(time);
return timeString;
}
/**
Action for checking a city.
@return "next" if time zone information is available for the city,
"error" otherwise
* @throws SQLException
*/
public String checkCity() throws SQLException
{
zone = getTimeZone(city);
if (zone == null) return "error";
addCity();
return "next";
}
public String newUser() throws SQLException
{
if (source == null)
{
Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(null, "No database connection");;
return null;
}
Connection conn = source.getConnection();
try
{
PreparedStatement stat = conn.prepareStatement(
"SELECT UserName "
+ "FROM Users "
+ "WHERE UserName=?");
stat.setString(1, userName);
ResultSet result = stat.executeQuery();
if (result.next())
{
errorMessage = "There is already a user with that name. Please choose another.";
return "login";
}
else
{
errorMessage = "";
stat = conn.prepareStatement(
"INSERT INTO Users"
+ "VALUES (?, ?, ?)");
stat.setString(1, userName);
stat.setString(2, password);
stat.setString(3, null);
stat.executeUpdate();
return "index";
}
}
finally
{
conn.close();
}
}
public String logIn() throws SQLException
{
if (source == null)
{
Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(null, "No database connection");;
return null;
}
Connection conn = source.getConnection();
try
{
PreparedStatement stat = conn.prepareStatement(
"SELECT FavCity FROM Users WHERE UserName=? AND Password=?");
stat.setString(1, userName);
stat.setString(2, password);
ResultSet result = stat.executeQuery();
if (result.next())
{
city = result.getString("FavCity");
errorMessage = "";
return "next";
}
else
{
errorMessage = "Wrong username or password";
return "login";
}
}
finally
{
conn.close();
}
}
private void addCity() throws SQLException
{
if (source == null)
{
Logger.getLogger(Logger.GLOBAL_LOGGER_NAME).log(null, "No database connection");;
return;
}
Connection conn = source.getConnection();
try
{
PreparedStatement stat = conn.prepareStatement(
"UPDATE Users "
+ "SET FavCity=? "
+ "WHERE UserName=?");
stat.setString(1, city);
stat.setString(2, userName);
stat.executeUpdate();
}
finally
{
conn.close();
}
}
/**
Looks up the time zone for a city.
@param aCity the city for which to find the time zone
@return the time zone or null if no match is found
*/
private static TimeZone getTimeZone(String aCity)
{
String[] ids = TimeZone.getAvailableIDs();
for (int i = 0; i < ids.length; i++)
if (timeZoneIDmatch(ids[i], aCity))
return TimeZone.getTimeZone(ids[i]);
return null;
}
/**
Checks whether a time zone ID matches a city.
@param id the time zone ID (e.g. "America/Los_Angeles")
@param aCity the city to match (e.g. "Los Angeles")
@return true if the ID and city match
*/
private static boolean timeZoneIDmatch(String id, String aCity)
{
String idCity = id.substring(id.indexOf('/') + 1);
return idCity.replace('_', ' ').equals(aCity);
}
}
Upon executing logIn() from clicking a button in the webapp (there are some xhtml files that I am pretty sure are not the problem so I didn't include them) I get an error saying that there is no schema "password". I'm really confused about what is going on here, as this isn't something I've done before and I'm trying to figure it out on my own. I think it might not be finding the table. I'm using GlassFish, and I've been messing around with the JDBC settings in its admin console, but there's so much stuff that I'm having a hard time figuring out where or what the problem is.
The exact error is:
type Exception report
messageInternal Server Error
description The server encountered an internal error that prevented it from fulfilling this request.
exception
javax.servlet.ServletException: java.sql.SQLSyntaxErrorException: Schema 'PASSWORD' does not exist
root cause
javax.faces.el.EvaluationException: java.sql.SQLSyntaxErrorException: Schema 'PASSWORD' does not exist
root cause
java.sql.SQLSyntaxErrorException: Schema 'PASSWORD' does not exist
root cause
org.apache.derby.client.am.SqlException: Schema 'PASSWORD' does not exist
