问当表和列存在时，为什么uCanAccess不能执行带有错误的查询“用户缺乏特权或没有发现”？
EN

Stack Overflow用户

提问于 2018-08-30 04:59:39

回答 1查看 898关注 0票数 1

我有一个数据库，其中有几个表，其中三个如下：

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)
...

我有以下的Java代码来检查一个表是否存在，以及它是否包含一个特定的列，基于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

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-08-30 08:57:25

我能够在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