尽管SQL查询在Access中返回结果，但是SQL查询未从Java返回任何结果 [英] SQL query returns no result from Java although it returns a result in Access

问题描述

当我运行此查询时:

select character from tbl_Unknown where format(fw,'.###')='48.143' and code='0001'

它会在Access查询界面中返回结果，但是当我尝试从Java运行它时，它不会返回结果.

it returns a result in the Access query interface but when I try to run it from Java it doesn't return a result.

我的桌子(tbl_未知):

My table (tbl_Unknown):

char_id: autonumber   value:1
fw: short text        value:'48.1425'   Hint:after format it become '48.143'.
code: short text      value:'0001' 
character: short text value: 'x'

我的Java代码:

public static String getLostedCharacter(String font,String fw, String code) {
      Connection conn = ConnectDB.getConnection();
      String character = null;
       try {
        Statement statement = conn.createStatement();
        String query = "select character from tbl_"+font+" where format(fw,'.###')='"+fw+"' and code='" + code + "'";
        ResultSet rs = statement.executeQuery(query);
         while (rs.next()) {
            character = rs.getString(1);
            return character;
        }
        statement.close();
        rs.close();
    } catch (SQLException ex) {
        return "";
    }
    return "";   
} 

推荐答案

在Access应用程序本身中在中运行的Access SQL查询可以使用各种可能不可用的VBA功能(或可能会有所不同)在其他应用程序运行的Access SQL查询中.

Access SQL queries that are run from within the Access application itself can use a wide variety of VBA functions that may not be available (or may behave a bit differently) in Access SQL queries that are run from other applications.

作为解决方法，我建议这样做:

As a workaround, I would suggest this:

String query = String.format(
        "select character from tbl_%s " +
        "where Trunc((Val(fw)*1000)+0.5)=? and code=?", 
        font);
PreparedStatement ps = conn.prepareStatement(query);
ps.setInt(1, (int)(1000 * Double.parseDouble(fw)));  // e.g. 48143
ps.setString(2, code);
ResultSet rs = ps.executeQuery();

编辑以下内容:评论

如jamadei所述，在这种特殊情况下，在UCanAccess版本< = 2.0.6.2中实现的Format()函数所产生的结果与Access/VBA实现所产生的结果略有不同.具体来说，Format(48.1425,".###)在Access查询中返回48.143，但在UCanAccess查询中返回48.142. 在将来的UCanAccess版本中可能会更正.在UCanAccess 2.0.6.3中已更正.

Edit re: comments

As explained by jamadei, the Format() function as implemented in UCanAccess versions <= 2.0.6.2 produces slightly different results than the Access/VBA implementation for this particular case. Specifically Format(48.1425,".###) returns 48.143 in an Access query but it returns 48.142 in a UCanAccess query. This may be corrected in a future release of UCanAccess. This has been corrected in UCanAccess 2.0.6.3.

这篇关于尽管SQL查询在Access中返回结果，但是SQL查询未从Java返回任何结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！