ucanaccess：带有空格的意外令牌

Question

SQLException: UCAExc:::3.0.0 unexpected token: AIRCRAFT

此查询引发异常：

SELECT [Check Task Aircraft Listing].CTAL_Item_Number
,[Check Task Aircraft Listing].CTAL_Check
,[Check Task Aircraft Listing].CTAL_Delete
,[Check Task Aircraft Listing].CTAL_Effective
,[Check Task Aircraft Listing].CTAL_CheckSelect
,[Check Table].[Aircraft]
FROM [Check Table]
INNER JOIN [Check Task Aircraft Listing] ON [Check Table].CHECK = [Check Task Aircraft Listing].CTAL_Check
WHERE (
        (([Check Task Aircraft Listing].CTAL_Effective) = True)
        AND (([Check Table].[Aircraft]) = 'XXXX')
        )

我尝试用_:

SELECT [Check_Task_Aircraft_Listing].CTAL_Item_Number
    ,[Check_Task_Aircraft_Listing].CTAL_Check
    ,[Check_Task_Aircraft_Listing].CTAL_Delete
    ,[Check_Task_Aircraft_Listing].CTAL_Effective
    ,[Check_Task_Aircraft_Listing].CTAL_CheckSelect
    ,[Check_Table].[Aircraft]
FROM [Check_Table]
INNER JOIN [Check_Task_Aircraft_Listing] ON [Check_Table].CHECK = [Check_Task_Aircraft_Listing].CTAL_Check
WHERE (
        (([Check_Task_Aircraft_Listing].CTAL_Effective) = True)
        AND (([Check_Table].[Aircraft]) = 'XXXX')
        )

Ucanaccess抛出了一个新的错误：

 SQLException: UCAExc:::3.0.0 user lacks privilege or object not found: CHECK_TASK_AIRCRAFT_LISTING

null

String url = "jdbc:ucanaccess://D:/ADEL/ADEL Local/ADEL_DATA.accdb";
            try
            {
                Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

            }
            catch (java.lang.ClassNotFoundException e)
            {
                System.err.print("ClassNotFoundException: ");
                System.err.println(e.getMessage());
                ErrorFile.writeError(Thread.currentThread().getStackTrace()[2].getLineNumber(), e);
            }

            try
            {
                c = java.sql.DriverManager.getConnection(url);
            }
            catch (SQLException e)
            {
                System.err.println("SQLException: " + e.getMessage());
                ErrorFile.writeError(Thread.currentThread().getStackTrace()[2].getLineNumber(), e);
            }

请参见执行查询的以下代码：

Connection c = ConnectDB.doConnect();
String selectString = "SELECT [Check Task Aircraft Listing].CTAL_Item_Number ,[Check Task Aircraft Listing].CTAL_Check ,[Check Task Aircraft Listing].CTAL_Delete ,[Check Task Aircraft Listing].CTAL_Effective ,[Check Task Aircraft Listing].CTAL_CheckSelect ,[CHECK TABLE].[Aircraft] FROM [CHECK TABLE] INNER JOIN [Check Task Aircraft Listing] ON [CHECK TABLE].CHECK = [Check Task Aircraft Listing].CTAL_Check WHERE ( (([Check Task Aircraft Listing].CTAL_Effective) = True) AND (([CHECK TABLE].[Aircraft]) = 'XXXX') ) ";
try
        {
            stmt = c.createStatement();
            
            ResultSet data = stmt.executeQuery(selectString);
            while (data.next())
            {...use data...}
}

第一个查询成功执行，但第二个查询失败。

Answer 1

事实上，您的诊断是错误的（第二次尝试也没有意义，因为错误的表名导致的错误并不能证明什么）。

此外，空格的数量也是完全不相关的。另一个有效的例子可能是：

从[我的表bla bla bla]中选择[我的列bla bla bla]。

package xxx;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UcaT {

    /**
     * @param args
     * @throws SQLException 
     */
    public static void main(String[] args) throws SQLException {
        Connection c= DriverManager.getConnection("jdbc:ucanaccess://c:\\db\\new_database_without_tables.accdb");


        String selectString = "SELECT [Check Task Aircraft Listing].CTAL_Item_Number ,[Check Task Aircraft Listing].CTAL_Check ,[Check Task Aircraft Listing].CTAL_Delete ,[Check Task Aircraft Listing].CTAL_Effective ,[Check Task Aircraft Listing].CTAL_CheckSelect ,[CHECK TABLE].[Aircraft] FROM [CHECK TABLE] INNER JOIN [Check Task Aircraft Listing] ON [CHECK TABLE].CHECK = [Check Task Aircraft Listing].CTAL_Check WHERE ( (([Check Task Aircraft Listing].CTAL_Effective) = True) AND (([CHECK TABLE].[Aircraft]) = 'XXXX') ) ";
        try
                {
                    Statement stmt = c.createStatement();
                    stmt.execute("create table [Check Table] (id counter PRIMARY KEY,[CHECK] text,Aircraft text)");
                    stmt.execute("create table [Check Task Aircraft Listing] (id counter PRIMARY KEY,CTAL_Item_Number text,CTAL_Check text, CTAL_Delete text, CTAL_Effective yesno, CTAL_CheckSelect text)");
                    ResultSet data = stmt.executeQuery(selectString);
                    System.out.println("ok");

                }
        catch(Exception e){
            e.printStackTrace();
        }

    }



}