This is my first time using ucanaccess since I just found out that my previous version of Eclipse would not work with Java 1.8 so I can get to my Access DB. I am familiar with Java programming and RDBMS (currently use Oracle) so I don't understand why I'm getting an error with my code.
This is the code I use to connect to the DB (variable dbFile is a File object used to store the Access DB path):
try
{
String urlDB = "jdbc:ucanaccess://"+ dbFile.getAbsolutePath();
Connection conn = DriverManager.getConnection(urlDB, "", "");
System.out.println("Connection Successful\n");
Statement test = conn.createStatement();
/* ResultSet rs = test.executeQuery("SELECT e.* "
+ "FROM Employees e"
+ "WHERE e.EmployeeNo LIKE 'H%'"
+ "ORDER BY e.LastName");
System.out.println(); //spacing
//retrieve column data
while(rs.next())
{
//can get data by column name instead of ID
String id = rs.getString("ID");
String fName = rs.getString("FirstName");
String lName = rs.getString("LastName");
String dName = rs.getString("DisplayName");
System.out.println(id + "\t" + fName + "\t" + lName + "\t");
}
*/
ResultSet rs = test.executeQuery("SELECT e.* FROM Employees e WHERE e.EmployeeNo LIKE '1%' ORDER BY e.LastName");
//get table information (i.e. column names)
ResultSetMetaData rsmd = rs.getMetaData();
int dbColumnCount = rsmd.getColumnCount();
/* //db columns starts at 1 not 0
for (int count = 1; count <= dbColumnCount; count++)
{
System.out.print(rsmd.getColumnName(count) + "\t");
}
*/
System.out.format("%5s%10s%15s%20s%n", rsmd.getColumnName(1), rsmd.getColumnName(2),
rsmd.getColumnName(3), rsmd.getColumnName(4));
//System.out.println();
while(rs.next())
{
//can get data by column name instead of ID
String id = rs.getString("ID");
String fName = rs.getString("FirstName");
String lName = rs.getString("LastName");
String dName = rs.getString("DisplayName");
//System.out.println(id + "\t" + fName + "\t" + lName + "\t");
System.out.format("%5s%10s%15s%20s%n", id, fName, lName, dName);
}
}
catch (Exception e)
{
e.printStackTrace();
System.err.println("Could not connect to the Database");
System.err.println(e.getMessage());
}
}The first call to ResultSet(multiline query) throws the following exception:
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.0 user lacks privilege or object not found: E
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:210)
at DBConnect.<init>(InOutBoard.java:78)
at InOutBoard.main(InOutBoard.java:47)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: E
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:208)
... 2 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: E
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.QuerySpecification.resolveColumnReferencesForAsterisk(Unknown Source)
at org.hsqldb.QuerySpecification.resolveReferences(Unknown Source)
at org.hsqldb.QueryExpression.resolve(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 5 more
Could not connect to the Database
UCAExc:::4.0.0 user lacks privilege or object not found: Ewhereas if I run the second call to ResultSet (single-line query), the data is properly returned from the table. Now as far as I can tell (other than spanning multiple lines in the first one) both queries are the same. So why is it that the first one throws the error? I can't imagine that the choice of formatting would make a difference, but as I said, I'm knew to using ucanaccess.
Any help would be appreciated. Thanks in advance!
