3

When I run this query:

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

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

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'

My java code:

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 "";   
}
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Ayman
  • 81
  • 2
  • 10
  • I think you get an exception. Please add `ex.printstacktrace()' in your `catch` block to see whats happend. – Jens Jun 01 '14 at 08:04
  • There is no exception just don't return result(no match found) – Ayman Jun 01 '14 at 08:10
  • @user3649116 How do you know there is no exception? Your catch block only has `return ""`, which is exactly the same thing you do when there is no result. – Mark Rotteveel Jun 01 '14 at 10:37

2 Answers2

2

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();

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thanks for your effort Mr Gord i will try it – Ayman Jun 01 '14 at 12:25
  • I can use `Format()` in a query with VBScript and Access 2007. I don't understand why it would not be available to queries run from Java or .NET. – HansUp Jun 01 '14 at 16:43
  • @HansUp This person is using [UCanAccess](http://ucanaccess.sourceforge.net/site.html) in their Java application and `Format()` won't work under that configuration. Still, you're right: I should adjust the wording of my answer. – Gord Thompson Jun 01 '14 at 16:54
  • OK, I don't understand UCanAccess ... I expected it to hand off a statement to the Access db engine ... but apparently something else/more is going on. And apparently UCanAccess translates `Trunc()` to something Access supports ... is that right? I should probably just avoid this Java stuff since I don't know squat about it. ;-) – HansUp Jun 01 '14 at 17:04
  • @HansUp Yes, UCanAccess apparently supports `Trunc()` but not `Int()`. I discovered that when I was testing my code before posting it. UCanAccess uses [Jackcess](http://jackcess.sourceforge.net/) to read from and write to the Access database. No ACE/Jet is involved; it even works on Linux and Mac. – Gord Thompson Jun 01 '14 at 17:12
  • Aha, "No ACE/Jet is involved"! No wonder I'm lost. Thanks, GT. – HansUp Jun 01 '14 at 17:14
  • In the format function implementation, when a pattern like '.###' is specified instead of a specific format like 'fixed', 'standard' and so on, UCanaccess delegates to DecimalFormat.format the formatting. This last method, in this very specific and "on the line" case, returns the string 48.142 and not 48.143 as Access does. If the number were 48.14251, all would work as well as in Access. I'm thinking of a generic way to solve this issue. – jamadei Jun 03 '14 at 10:41
  • @GordThompson Int function is supported by UCanAccess but it doesn't work in your example because my implementation returns a short instead of an Integer: I'll fix it in the 2.0.6.3. providing a different UCanAccess implementation. – jamadei Jun 03 '14 at 14:03
1

Both the mentioned issues(int function and rounding mode "half up" in the format function) have been fixed in the 2.0.6.3.

jamadei
  • 1,700
  • 9
  • 8