PostgreSQL JDBC Empty Result set when queried with string condition in WHERE clause

Question

While working with Postgres JDBC, I have encountered a problem about query with strings. Following query is working with tools like Auqadata Studio but not working in code.

Code is as following;

// strSqlStatement is passed from a framework as String like this and executed untouched
strSqlStatement = "SELECT columnA FROM abc.table1 WHERE columnB= '%KLMN%' limit 1;"; 
...
strDBUrl = "jdbc:postgresql://x.x.x.x:1234/instance?useUnicode=yes&characterEncoding=UTF-8";   
...
rs = st.executeQuery(strSqlStatement);

In Postgres database records exists for columnB as;

... ColumnA    ...      ColumnB             ...   ColumnC
    -------             -------                   -------
    value1      |       TEST_AA_KLMN_BB_S     |      25
    value2      |       TEST2_AA_KLMN_BB_S    |      79
    value3      |       TEST3_AA_KLMN_BB_S    |     124

Problem is like this, normally querying value2 with WHERE clause as ColumnB = 'TEST2_AA_KLMN_BB_S' should work but this simple query is not working. Also LIKE clause as ColumnB LIKE '%KLMN%' is not working at all.

There is no problem when other columns as ColumnC is used for WHERE clause.

I have checked that our Postgres database have no locale setting(lc_ctype=C) and encoding of database is UTF-8(server_encoding=UTF8).

Background;

This code piece exists in a standalone executable jar file.
Datatype of ColumnB is varchar(64).
Java 1.7 (Also tried with 1.8 and 1.6).
JDBC is postgresql-9.4.1211.jre6 (also tried with 7 and 8).

Edit: @Francisco Puga, I mistakenly write WHERE columnB= '%KLMN%' in question, it is correct in real code as WHERE columnB LIKE '%KLMN%', nevertheless thank you for your quick reply.

I have narrowed the problem. I find out = and LIKE are actually working but there is a limit issue about string.

If compared string is shorter than 25 characters everything is fine but when compared string is longer than 25 characters then result is empty. Actually 25 character rings no bell for me.

Try removing the trailing semicolon from your statement. These are not required for JDBC, and can cause problems with certain drivers sometimes. — Florian Albrecht
– Florian Albrecht, Commented Nov 3, 2016 at 8:28
Please show us the complete Java code. How do you process the ResultSet — user330315
– user330315, Commented Nov 3, 2016 at 8:42
Unrelated, but useUnicode and characterEncoding are MySQL's JDBC parameters (any maybe other RDBMS). PostreSQL uses the charSet and that is only relevant for versions before (the pretty ancient) 7.3 — pozs
– pozs, Commented Nov 3, 2016 at 9:45

Dominic Cleal · Accepted Answer · 2016-11-03 09:32:23Z

1

Not sure if it is a typo but your where clause is:

WHERE columnB= '%KLMN%'

when it should be:

WHERE columnB LIKE '%KLMN%'

or even using ILIKE if you want to ignore uppercase.

Also take into account that if you use uppercase in your column names you should use double quotes around it when making the queries (but it should throw an error if this is the problem, not an empty record-set)

SELECT "ColumnA" FROM abc.table1 WHERE "ColumnB" ILIKE '%KLMN%' limit 1;

edited Nov 3, 2016 at 9:32

Dominic Cleal

3,20521 silver badges22 bronze badges

answered Nov 3, 2016 at 8:29

Francisco Puga

25.4k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Florian Albrecht Over a year ago

OP writes that LIKE didn't work as well, but that would also be my guess...

Francisco Puga Over a year ago

Iep. But it says that the query comes from a framework, so maybe she is launching the like query by hand but not using it in their real code. But is only a shot in the dark.

Collectives™ on Stack Overflow

PostgreSQL JDBC Empty Result set when queried with string condition in WHERE clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related