Including Special Characters in SQL query

[Mragesh]

Hi,

Here is the Java code that I use to access a dataset in HPCC:

Code: Select all

String mysql = "select * from table where value = ?";
PreparedStatement p;
p = (PreparedStatement) hpccTestObject .connectionByProperties.prepareStatement(mysql);
String value="abc+";
p.clearParameters();
p.setString(1, value.trim());
HPCCResultSet hrs = (HPCCResultSet) ((HPCCPreparedStatement) p).executeQuery();


Now when I execute the query, the "+" gets dropped in the workunit created on my HPCC cluster.

Code: Select all

STRING PARAM1 := 'abc '; //Dropped +
import std;
TblDS0RecDef := RECORD string value END;
TblDS0 := DATASET('~abc::xyz::table', TblDS0RecDef,FLAT);
TblDS0Filtered := TblDS0( pat = PARAM1 );
SelectStruct := RECORD
string value := TblDS0Filtered.value;
END;
TblDS0FilteredTable := TABLE( TblDS0Filtered, SelectStruct );
OUTPUT(CHOOSEN(TblDS0FilteredTable,ALL),NAMED('JDBCSelectQueryResult'));



How do I preserve the "+" sign, I have tried using prepared statement and escaping the "+" in the string?

[Mragesh]

Sorry for the unnecessary question, found the solution.
Realized that everything is passed to HPCC as an URL so used URL encoding to preserve the "+" sign.

Code: Select all

p.setString(1, URLEncoder.encode(value.trim()));


[rodrigo.pastrana]

Hi Mragesh, thanks for pointing out this issue. You'll be glad to know the next version of the JDBC Connector (0.3.0) will resolve this issue by ensuring all values which need to be encoded are indeed encoded. Thanks.