Using java for searching for data by using part of the row in microsoft access database

Question

I have this table

And I am using the following code to retrieve data from my table that returns all the English words that its Kurdish word contains بةرز

targetText="بةرز";
try (PreparedStatement ps = conn.prepareStatement(
"SELECT English,Kurdish FROM Info " +
"WHERE Kurdish = ? " +
"OR REGEXP_MATCHES(Kurdish, ?) " +
"OR REGEXP_MATCHES(Kurdish, ?) " +
"OR REGEXP_MATCHES(Kurdish, ?) ")) {
   ps.setString(1, targetText);
   ps.setString(2, "^[. ]*" + targetText+ "[ ]*[:،,]+[ .]*$");
   ps.setString(3, "^[. ]*[:،,]+[ ]*" + targetText+ "[. ]*$");
   ps.setString(4, "^[. ]*[:،,]+[ ]*" + targetText+ "[ ]*[:،,]+[  .]*$");
   try (ResultSet rSet = ps.executeQuery()) {
      while (rSet.next()) {
         System.out.println(rSet.getString("English"));
         System.out.println(rSet.getString("Kurdish"));
      }
   }
}

So it works fine, it prints all the English words that I want.
My problem is that when I get the corresponded Kurdish word it doesn't print the complete cell. It just prints بةرز,

For example the output of the previous code should be:

aesthete
بةرز ، جوانىثةرست
aether
زوَر ناسك ، بةرز ، ثيروَز ، ئاسمانى
affair
بةرز

But it prints

aesthete
بةرز 
aether
بةرز 
affair
بةرز

What can I do to get the output that I want?

Note that I am using UCanAccess for my database connection,

I am unable to recreate your issue using UCanAccess 3.0.2. I copied and pasted your sample data into an Access table and I copied and pasted your code into an Eclipse project. When I run it the only row that matches is "affair". — Gord Thompson, Oct 23 '15 at 16:03
do you want i record my issue with video and upload it on YouTube? — Hamreen Ahmad, Oct 23 '15 at 16:08
What are your regex's supposed to do? What does `[. ]*[:،,]+[ ]*` mean to you? To me it means a sequence of `:`, `،`, or `,` characters optionally preceded by spaces or periods and/or followed by spaces. Combined with the anchor `^`, it means no other text allowed, so it definitely won't match what's before the first 2 red circles. --- Also, since the data shown has `Affair` starting with uppercase letter and your output has it starting with lowercase letter, you're *not* reading *that* data. — Andreas, Oct 23 '15 at 16:09
Since this isn't about the regex (which certainly could be improved), I suggest you remove that where clause completely and test the query without it. Then see what output you get. — Claude Martin, Oct 23 '15 at 16:10
Good suggestion from @СӏаџԁеМаятіи - When I "short circuit" the row selection with `ps.setString(2, ".*");` I match all the rows and the entire Kurdish text gets printed for each row (as in the "should be" section of the question). — Gord Thompson, Oct 23 '15 at 16:17
I also suspect that the right-to-left (RTL) reading order of Kurdish may have something to do with the matching issue. `ps.setString(2, "^ز.*");` matches "aether", so the `^` anchor does in fact respect the RTL ordering, for me at least. — Gord Thompson, Oct 23 '15 at 16:20
without that WHERE clause its fine but it prints all records... — Hamreen Ahmad, Oct 23 '15 at 16:32
But if i dont use PreparedStatement another error occurs, That you can see from this post: http://stackoverflow.com/questions/33282360/ucanaccesssqlexception-ucaexc3-0-1-data-type-of-expression-is-not-boolean — Hamreen Ahmad, Oct 23 '15 at 16:37
Your other question has nothing to do with `PreparedStatement` vs. `Statement`, you were simply using an SQL syntax that UCanAccess did not understand. — Gord Thompson, Oct 23 '15 at 17:16
thanks a lot for all i have solved it the problem is with my regx, this one is correct `ps.setString(1, currentSelectedText); ps.setString(2, "^" + currentSelectedText + "[ ]*[.،]+.*"); ps.setString(3, ".*[.،][ ]*+" + currentSelectedText + "$"); ps.setString(4, ".*[.،][ ]*+" + currentSelectedText + "[ ]*[.،]+.*");` — Hamreen Ahmad, Oct 23 '15 at 18:04
@HamreenAhmad please post that as an answer (along with more context) so it's easy for others to find and understand. — dimo414, Nov 23 '15 at 19:45

score 1 · Accepted Answer · answered Nov 23 '15 at 20:39

Tanks for all, I have solved it just with a few change within the regx

targetText="بةرز";
try (PreparedStatement ps = conn.prepareStatement(
   "SELECT English,Kurdish FROM Info " +
   "WHERE Kurdish = ? " +
   "OR REGEXP_MATCHES(Kurdish, ?) " +
   "OR REGEXP_MATCHES(Kurdish, ?) " +
   "OR REGEXP_MATCHES(Kurdish, ?) ")) {
   ps.setString(1, targetText);
   ps.setString(2, "^" + targetText+ "[ ]*(،)[.]*");
   ps.setString(3, "[.]*(،)[ ]*" + targetText+ "$");
   ps.setString(4, "[.]*(،)[ ]*" + targetText+ "[ ]*(،)[.]*");
   try (ResultSet rSet = ps.executeQuery()) {
      while (rSet.next()) {
         System.out.println(rSet.getString("English"));
         System.out.println(rSet.getString("Kurdish"));
      }
   }
}

Using java for searching for data by using part of the row in microsoft access database

1 Answers1