Sun May 19, 2019 8:26 pm
Login Register Lost Password? Contact Us


Including Special Characters in SQL query

Topics around the use of the JDBC driver for HPCC Systems

Mon Apr 06, 2015 5:13 pm Change Time Zone

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
 
Posts: 15
Joined: Thu Mar 15, 2012 5:57 am

Mon Apr 06, 2015 5:24 pm Change Time Zone

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()));
Mragesh
 
Posts: 15
Joined: Thu Mar 15, 2012 5:57 am

Wed Apr 08, 2015 5:19 pm Change Time Zone

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.
rodrigo.pastrana
 
Posts: 24
Joined: Tue Jun 10, 2014 2:19 pm


Return to Java Tools

Who is online

Users browsing this forum: No registered users and 0 guests

cron