gpt4 book ai didi

java - 使用 Java Ucanaccess 在 ms-Access 中恢复数据和存储的查询

转载 作者:行者123 更新时间:2023-12-02 03:40:40 34 4
gpt4 key购买 nike

我有一个 Access 数据库。该数据库包含表和存储的查询。我的目标是使用 Java Ucanaccess(一个 JDBC 连接器)来使用 Access 文件中存储的数据并使用 Jaspersoft 创建报告。

在普通表上查询有效:

Connection conn = ConnexionUtils.getConnection();
Statement stmt = conn.createStatement();
String query = "select * from Tab_BOUT";
ResultSet rs = stmt.executeQuery(query);
int cpt = 0;
while ( rs.next() ) {
int numColumns = rs.getMetaData().getColumnCount();
for ( int i = 1 ; i <= numColumns ; i++ ) {
if (i > 1) System.out.print(", ");
String columnValue = rs.getString(i);
System.out.print(columnValue + " " + rs.getMetaData().getColumnName(i));
}
}

但问题是我有一些对象在 Access 中使用其他查询。因此,当我尝试调用查询时,它不起作用:

例如,“RQT_STORED”包含:

"SELECT Tab_STO_livraisons.[LIV-TIE_num], Req_BOUT_articles_tous.ART_code_EAN FROM (Tab_STO_livraisons INNER JOIN Tab_STO_détails ON (Tab_STO_livraisons.LIV_num = Tab_STO_détails.[STO-LIV_num]) AND (Tab_STO_livraisons.[LIV-TIE_num] = Tab_STO_détails.[STO-TIE_num])) INNER JOIN Req_BOUT_articles_tous ON Tab_STO_détails.[STO-ART_id] = Req_BOUT_articles_tous.ART_code ORDER BY Tab_STO_livraisons.[LIV-TIE_num], Tab_STO_livraisons.LIV_num, Tab_STO_détails.[STO-ART_id]";

该查询在 Access 中有效,但在 Java 中无效。有人知道如何做到这一点或有更好的解决方案吗?

编辑:以下是一个查询示例:Select * from Req_VENT_lirairie_nouvelles_pages_analysis

access中的真实查询:

SELECT Tab_STO_livraisons.[LIV-TIE_num], Tab_STO_livraisons.LIV_num, Tab_STO_livraisons.LIV_date_livraison, Tab_STO_livraisons.LIV_type_facturation, Tab_STO_détails.[STO-ART_id], Req_BOUT_articles_tous.ART_code_EAN, Tab_STO_détails.STO_nombre, Tab_STO_livraisons.LIV_bdc, Tab_STO_livraisons.LIV_fact_51, Tab_STO_livraisons.LIV_fact_31
FROM (Tab_STO_livraisons INNER JOIN Tab_STO_détails ON (Tab_STO_livraisons.LIV_num = Tab_STO_détails.[STO-LIV_num]) AND (Tab_STO_livraisons.[LIV-TIE_num] = Tab_STO_détails.[STO-TIE_num])) INNER JOIN Req_BOUT_articles_tous ON Tab_STO_détails.[STO-ART_id] = Req_BOUT_articles_tous.ART_code
ORDER BY Tab_STO_livraisons.[LIV-TIE_num], Tab_STO_livraisons.LIV_num, Tab_STO_détails.[STO-ART_id];

和错误消息:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.4 user lacks privilege or object not found: REQ_VENT_LIBRAIRIE_NOUVELLES_PAGES_ANALYSE at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:211) at view.QueryData.main(QueryData.java:32) Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: REQ_VENT_LIBRAIRIE_NOUVELLES_PAGES_ANALYSE 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) ... 1 more Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: REQ_VENT_LIBRAIRIE_NOUVELLES_PAGES_ANALYSE at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.SchemaManager.getTable(Unknown Source) at org.hsqldb.ParserDQL.readTableName(Unknown Source) at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source) at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source) at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source) at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source) at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source) at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source) at org.hsqldb.ParserDQL.XreadQueryExpression(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) ... 4 more

最佳答案

some of my views are querying a MySQL database. Would that be a big problem?

是的。 UCanAccess 可以使用 Access 链接表(指向另一个 Access 数据库中的表的链接表),但不能使用 ODBC 链接表(指向 ODBC 数据源(如 MySQL 或 SQL Server)中的表的链接表)。

关于java - 使用 Java Ucanaccess 在 ms-Access 中恢复数据和存储的查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36869143/

34 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com