代码之家 › 专栏 › 技术社区 › Agi Hammerthief

当表和列存在时,uCanAccess为什么无法执行错误为“user loaks privilege or object not found”的查询?

ucanaccess hsqldb java-7 jdbc

Agi Hammerthief · 技术社区 · 6 年前

我有一个数据库,有许多表,其中三个表如下:

Controllers_Readers
-------------------
ControllerID: Number (Long Integer), Default 0, Indexed (Y, no Duplicated)
ReaderID: Number (Long Integer), Default 0, Indexed (Y, No Duplicates)

Controllers
-----------
ControllerID: Number (Long Integer), Default 0, Indexed (Y, No Duplicates)
Description: Short Text (32)
Name: Short Text (32)
...

Readers
-------
ReaderID: Number (Long Integer), Default 0, Indexed (Y, No Duplicates)
Direction: Number (Long Integer)
Description: Short Text (32)
Name: Short Text (32)
...

this answer to a question about table metadata :

/* Use the metadata to check if a table exists and has a specific column */
  public static boolean tableHasColumn(Connection con, String tableName, String colName)
    throws ClassNotFoundException, SQLException, InstantiationException,
    IllegalAccessException 
  {
    boolean exists = false, hasCol = false;

    ResultSet rs = null, r = null;
    Statement s = null;

    try {
      DatabaseMetaData dbm = con.getMetaData();
      rs = dbm.getTables(null, null, tableName, null);
      if (rs.next() && rs.getRow() > -1) {
        exists = true;
        s = con.createStatement();
        r = s.executeQuery("SELECT * FROM " + tableName + " LIMIT 1");
        if (r.next()) {
          ResultSetMetaData rsmd = r.getMetaData();
          for (int col = 1; col < rsmd.getColumnCount(); col++) {
            String name = rsmd.getColumnName(col);
            logger.debug("Found column \"" + name + "\" in \"" + tableName + "\".");
            if (name.equals(colName)) {
              hasCol = true;
              break;
            }
          }
        }
      }
    } finally {
      if (null != rs) {
        try {
          rs.close();
        } catch (SQLException ex) {
          logger.warn(
            "Failed to close Resultset to DB while checking if table exists!"
          );
        }
      }
    }
    logger.debug(
      "Table (" + tableName + ") Exists: " + String.valueOf(exists).toUpperCase()
      + "\tHas Column (" + colName + "): " + String.valueOf(hasCol).toUpperCase()
    );

    return (exists && hasCol);
  }

提供价值 "Controllers_Readers" 和 "ControllerID" 对于 tableName 和 colName true . 但是,当我运行下面的查询时,我得到一个错误消息 org.hsqldb.HsqlException: user lacks privilege or object not found: CONTROLLERS_READERS.CONTROLLERID :

SELECT C.Description AS cName, C.ControllerID AS CID,
  D.DirectionText as dName, D.Direction AS dirNum, R.Description AS rName,
  R.ReaderID AS RID, AP.Name AS aName, AP.Description as aDescrip, AP.PointID
FROM Controllers AS C, Controllers_Readers AS CR, Readers AS R,
  Directions AS D, AccessPoints AS AP, Points_Controllers as PC
WHERE Controllers_Readers.ControllerID = Controllers.ControllerID 
  AND Readers.ReaderID = Controllers_Readers.ReaderID
  AND Directions.Direction = Readers.Direction AND 
  Points_Controllers.ControllerID = Controllers.ControllerID
  AND AccessPoints.PointID = Points_Controllers.PointID
GROUP BY Controllers.Description, Controllers.ControllerID, 
  Directions.DirectionText, Directions.Direction, Readers.Name, 
  Readers.Description, Readers.ReaderID,  AccessPoints.Name, 
  AccessPoints.Description, AccessPoints.PointID 
ORDER BY Controllers.Description, Controllers.ControllerID, 
  Directions.DirectionText, Directions.Direction, Readers.Name, 
  Readers.Description, Readers.ReaderID,  AccessPoints.Name,
  AccessPoints.Description, AccessPoints.PointID

2018-08-30 14:24:35 [DEBUG] db.Locations:35 - Location: LOCAL   DB Path: \\sentinel.domain\TempStorage\Me\dbs\Redacted.mdb
2018-08-30 14:24:35 [DEBUG] db.Digger:767 - Connect to DB on path: \\sentinel.domain\TempStorage\Me\dbs\Redacted.mdb
2018-08-30 14:24:35 [INFO ] db.Digger:790 - Connecting to jdbc:ucanaccess:////sentinel.domain/TempStorage/Me/dbs//Redacted.mdb;openExclusive=false;concatNulls=false;ignoreCase=true
2018-08-30 14:24:37 [DEBUG] impl.DatabaseImpl:887 - Finished reading system catalog.  Tables: [AccessNumbers, AccessNumbers_Controllers, AccessNumbers_Groups, AccessNumbers_Readers, AccessPoints, Areas, Areas_Controllers, Areas_Points, Areas_Timezones, Controllers, Controllers_Expanders, Controllers_Readers, Directions, Events, Expanders_Readers, Groups, Groups_Areas, Groups_Controllers, Groups_Timezones, Holidays, Points_Controllers, Points_Readers, Points_Timezones, ReaderExpanders, Readers, SelectedAreas, SelectedDates, Sites, Sites_Areas, Status, Storage, Timezones, Transactions, UserInformation, Users] (Db=Redacted.mdb)
2018-08-30 14:25:10 [DEBUG] db.Digger:884 - Found column "ControllerID" in "Controllers_Readers".
2018-08-30 14:25:10 [DEBUG] db.Digger:901 - Table (Controllers_Readers) Exists: TRUE    Has Column: TRUE
2018-08-30 14:25:10 [ERROR] db.Digger:1180 - [UcanaccessSQLException]: UCAExc:::4.0.2 user lacks privilege or object not found: CONTROLLERS_READERS.CONTROLLERID
    net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.2 user lacks privilege or object not found: CONTROLLERS_READERS.CONTROLLERID
    at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:210)
    at db.Digger.loadAllControllerReaders(Digger.java:947)
    at db.Digger.main(Digger.java:1177)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: CONTROLLERS_READERS.CONTROLLERID
    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: CONTROLLERS_READERS.CONTROLLERID
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ExpressionColumn.checkColumnsResolved(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)

注: db.Digger 和 db.Locations loadAllControllerReaders 是执行SQL查询的方法。

通过MS-Access(office365)对DB运行相同的查询会得到一个列中有值的结果集。为什么uCanAccess/HSQLDB不能做同样的事情(找不到 ControllerID 中的列 Controllers_Readers )? 如何让它识别表中存在该列?

1 回复 | 直到 6 年前

Gord Thompson 6 年前

我能够在HSQLDB本身(版本2.4.1)下重新创建您的问题。您在FROM子句中使用了表名的别名

... FROM Controllers AS C, Controllers_Readers AS CR, ...

但是WHERE子句使用非别名表名而不是别名

... WHERE Controllers_Readers.ControllerID = Controllers.ControllerID ...

因此无法识别实际的表名。这可以通过

Statement st = conn.createStatement();
st.execute("CREATE TABLE MY_TABLE (ID INT PRIMARY KEY, DT DATETIME)");
st.execute("INSERT INTO MY_TABLE (ID, DT) VALUES (1, '2018-12-23 00:11:22')");
ResultSet rs = st.executeQuery("SELECT X.DT FROM MY_TABLE AS X WHERE MY_TABLE.ID=1");

这也产生了

java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: MY_TABLE.ID
...
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: MY_TABLE.ID

您还需要在其余子句(WHERE、GROUP BY、ORDER BY)中使用适当的别名。