ucanaccess中的查询输出不正确

Question

我有一个accdb文件。我正在使用ucanaccess api从我的Java项目连接到它并处理数据。（我没有MS Access，我在Java项目中使用ucanaccess作为引用库）。

但是，当我在SQL server中导入accdb文件并运行查询select B.grd,B.[credit hours],B.term from[1i-stem dcccd student courses]B where[student id]='2169095'；时，我遇到了一个奇怪的问题：

但是当我在ucanaccess中运行查询时，我会得到以下输出：

    Please, enter the full path to the access file (.mdb or accdb): C:\\Users\\Tejas\\Desktop\\sample.accdb
log4j:WARN No appenders could be found for logger (com.healthmarketscience.jackcess.impl.DatabaseImpl).
log4j:WARN Please initialize the log4j system properly.
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\GPA - Standing.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\GPA - Standing.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:External file P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb does not exist
WARNING:given file does not exist: P:\IE\Warehouse (New 1-27-15)\MVC Data Warehouse.mdb
WARNING:Error in the metadata of the table 12i-Fac Info: table's row count in the metadata is 1642 but 1652 records have been found and loaded by UCanAccess. All will work fine, but it's better to repair your database.
WARNING:Error in the metadata of the table 3i-STEM STUDENTS LOAD BY TERM: table's row count in the metadata is 8716 but 4358 records have been found and loaded by UCanAccess. All will work fine, but it's better to repair your database.
Error occured at the first loading attempt of 12i-Faculty Latetst Information
Converted view was :CREATE VIEW "Z_12I-FACULTY LATETST INFORMATION" AS SELECT "STUDENT COURSE"."FAC ID", Last("STUDENT COURSE"."FACULTY LAST NAME") AS "LASTOFFACULTY LAST NAME", Last("STUDENT COURSE"."FACULTY FIRST NAME") AS "LASTOFFACULTY FIRST NAME"
FROM "STUDENT COURSE"
GROUP BY "STUDENT COURSE"."FAC ID"
Error message was :user lacks privilege or object not found: STUDENT COURSE
Loaded Tables:
10i-STEM STUDENT LATEST MAJOR INFORMATION, 12i-Fac Info, 13-F1 students by Term, 14i- Faculty Per Term, 15i- STEM COURSE MASTER, 15i- STEM COURSE MASTER by TERM, 1i-STEM DCCCD STUDENT COURSES, 2i - STEM latest Student Information, 3i-STEM STUDENTS LOAD BY TERM, 4i-STEM STUDENT Latest GPA, 5i-STEM MVC STUDENT COURSE 2014-15AY, 6-Financial AID By Sum of Term Amt, 7-STEM - ACAD YR MASTER, 7-STEM MVC Acad-YR TERM, 8i-STEM STUDENT COURSE STATUS BY TERM, 9-STEM TERM MASTER, Financial AID By Sum of Term Amt, STEM TERM VALUES
Loaded Queries:
2014-15AY STEM STUDENT UNIQ ID AS OF 6-4-15, 2i-STEM UNIQUE STUDENT LATEST TERM
Loaded Indexes:
Primary Key  on 1i-STEM DCCCD STUDENT COURSES Columns: (ID) 
, Primary Key  on 5i-STEM MVC STUDENT COURSE 2014-15AY Columns: (ID) 
, Primary Key  on STEM TERM VALUES Columns: (Term Code) 
, Index on 12i-Fac Info Columns: (FAC ID) 
, Index on 7-STEM - ACAD YR MASTER Columns: (CODE) 
, Index on STEM TERM VALUES Columns: (Term Code) 

Cannot load view MVC Acad_YR - TERM : user lacks privilege or object not found: STUDENT COURSE
Cannot load view 12i-Faculty Latetst Information : user lacks privilege or object not found: STUDENT COURSE
Cannot load view 4i-STEM STUDENTS Latest GPA - 3 : user lacks privilege or object not found: GPA - TERM AND CUMM
Cannot load view Update the Term Master : user lacks privilege or object not found: STUDENT COURSE
Cannot resolve table 5- STEM MVC Course
Cannot resolve table Course
Cannot resolve table First Time in College
Cannot resolve table GPA - Term and Cumm
Cannot resolve table Student
Cannot resolve table Student Course
Cannot resolve table Student Financial Aid
Cannot resolve table Student Load
Cannot resolve table Student Type
UCanAccess>
Copyright (c) 2012 Marco Amadei
UCanAccess version 3.0.1
You are connected!! 
Type quit to exit 

Commands end with ; 

use:   
   export <pathToCsv>;
for exporting into a .csv file the result set from the last executed query

UCanAccess>SELECT B.grd , b.[credit hours] , b.term from  [1i-stem dcccd student courses] B where [student id] = '2169095';
------------------------------
| GRD | Credit Hours | Term | 
------------------------------

| W | 4.0 | 2009FA | 

| F | 3.0 | 2009FA | 

| A | 3.0 | 2009FA | 

| F | 3.0 | 2009FA | 

| F | 3.0 | 2010SP | 

| F | 3.0 | 2010SP | 

| F | 3.0 | 2010SP | 

| C | 4.0 | 2010SP | 

| F | 3.0 | 2010SP | 

| D | 3.0 | 2012S2 | 

| C | 3.0 | 2012S2 | 

| A | 4.0 | 2014FA | 

| A | 4.0 | 2015SP | 

| A | 4.0 | 2015SP | 

| null | 4.0 | 2015FA | 

| null | 4.0 | 2015FA | 


UCanAccess>

还有其他类似的不忠行为。与在SQL server中一样，对于特定查询，其中一个列输出为'a'，但在ucanaccess中为'NULL'。

我怎么解决这个？

Answer 1

首先，您使用的是旧的2.0.9.5版本，因此，如果您使用最新的3.0.1，并在第一步使用3.0.1输出更新您的问题，那就太好了。其次，数据库可能已损坏，可以使用MS Access Compact and Repair工具进行修复。但是，执行以下代码（jackcess）并让我们知道您的发现（程序输出）可能非常有用。

package net.ucanaccess.test;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Iterator;

import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.Row;
import com.healthmarketscience.jackcess.Table;


public class Jackcess {
    public static void main(String[] d) throws IOException, SQLException{
        DatabaseBuilder dbd =new DatabaseBuilder(new File("Path to your file"));
        dbd.setAutoSync(false);
        Database db=dbd.open();
        Table t=db.getTable("1i-stem dcccd student courses");
        System.out.println(t);
          Iterator<Row> it=t.iterator();
          while(it.hasNext()){
              System.out.println(it.next());
          }
    }

}