@Override
public ResultSet executeQuery(String sql) throws SQLException {
checkOpen();
CsvDriver.writeLog("CsvStatement:executeQuery() - sql= " + sql);
/*
* Close any previous ResultSet, as required by JDBC.
*/
try {
if (lastResultSet != null) lastResultSet.close();
} finally {
lastResultSet = null;
multipleParsers = null;
}
SqlParser parser = new SqlParser();
try {
parser.parse(sql);
} catch (Exception e) {
throw new SQLException(CsvResources.getString("syntaxError") + ": " + e.getMessage());
}
return executeParsedQuery(parser);
}
public void testWhere() throws Exception {
ExpressionEvaluator evaluator = new ExpressionEvaluator();
String testSql = "select * from aaa where /*%if false*/ename = 'aaa'/*%end */";
SqlParser parser = new SqlParser(testSql);
SqlNode sqlNode = parser.parse();
PreparedSql sql =
new NodePreparedSqlBuilder(
config, SqlKind.SELECT, "dummyPath", evaluator, SqlLogType.FORMATTED)
.build(sqlNode, Function.identity());
assertEquals("select * from aaa", sql.getRawSql());
}
public void testWhere_embeddedVariable_orderBy() throws Exception {
ExpressionEvaluator evaluator = new ExpressionEvaluator();
evaluator.add("embedded", new Value(String.class, "order by bbb"));
String testSql = "select * from aaa where /*%if false*/ename = 'aaa'/*%end */ /*#embedded*/";
SqlParser parser = new SqlParser(testSql);
SqlNode sqlNode = parser.parse();
PreparedSql sql =
new NodePreparedSqlBuilder(
config, SqlKind.SELECT, "dummyPath", evaluator, SqlLogType.FORMATTED)
.build(sqlNode, Function.identity());
assertEquals("select * from aaa order by bbb", sql.getRawSql());
}
private void addFilterClause(StringBuilder sb, HashMap parameters) {
String whereclause = parser.getWhereClauseString();
if (whereclause != null && whereclause.length() > 0) {
sb.append("( ");
sb.append(whereclause);
sb.append(" )");
}
}
public boolean processIndex(DFUFile indexfiletouse, StringBuilder keyedandwild) {
boolean isPayloadIndex =
containsPayload(indexfiletouse.getAllFieldsProps(), parser.getSelectColumns().iterator());
Vector<String> keyed = new Vector<String>();
Vector<String> wild = new Vector<String>();
// Create keyed and wild string
Properties keyedcols = indexfiletouse.getKeyedColumns();
for (int i = 1; i <= keyedcols.size(); i++) {
String keyedcolname = (String) keyedcols.get(i);
if (parser.whereClauseContainsKey(keyedcolname))
keyed.add(" " + parser.getExpressionFromName(keyedcolname).toString() + " ");
else if (keyed.isEmpty()) wild.add(" " + keyedcolname + " ");
}
if (isPayloadIndex) {
if (keyed.size() > 0) {
keyedandwild.append("KEYED( ");
for (int i = 0; i < keyed.size(); i++) {
keyedandwild.append(keyed.get(i));
if (i < keyed.size() - 1) keyedandwild.append(" AND ");
}
keyedandwild.append(" )");
}
if (wild.size() > 0) {
// TODO should I bother making sure there's a KEYED entry ?
for (int i = 0; i < wild.size(); i++) {
keyedandwild.append(" and WILD( ");
keyedandwild.append(wild.get(i));
keyedandwild.append(" )");
}
}
keyedandwild.append(" and (").append(parser.getWhereClauseString()).append(" )");
} else // non-payload just AND the keyed expressions
{
keyedandwild.append("( ");
keyedandwild.append(parser.getWhereClauseString());
keyedandwild.append(" )");
}
return isPayloadIndex;
}
public void testEmbeddedVariable_blockComment() throws Exception {
ExpressionEvaluator evaluator = new ExpressionEvaluator();
evaluator.add("name", new Value(String.class, "hoge"));
evaluator.add("salary", new Value(BigDecimal.class, new BigDecimal(10000)));
evaluator.add("orderBy", new Value(String.class, "aaa/*bbb"));
String testSql =
"select * from aaa where ename = /*name*/'aaa' and sal = /*salary*/-2000 /*#orderBy*/";
SqlParser parser = new SqlParser(testSql);
SqlNode sqlNode = parser.parse();
try {
new NodePreparedSqlBuilder(
config, SqlKind.SELECT, "dummyPath", evaluator, SqlLogType.FORMATTED)
.build(sqlNode, Function.identity());
fail();
} catch (JdbcException expected) {
System.out.println(expected.getMessage());
assertEquals(Message.DOMA2123, expected.getMessageResource());
}
}
private static void printStatement(String sql) {
println(sql.trim());
println("");
Statement statement = SQL_PARSER.createStatement(sql);
println(statement.toString());
println("");
println(SqlFormatter.formatSql(statement, Optional.empty()));
println("");
assertFormattedSql(SQL_PARSER, statement);
println(repeat("=", 60));
println("");
}
public SqlNode parseQuery(String sql) throws Exception {
SqlParser parser = SqlParser.create(sql);
SqlNode sqlNode = parser.parseQuery();
return sqlNode;
}
public ArrayList execute() throws Exception {
int sqlreqtype = parser.getSqlType();
List<EclColumnMetaData> expectedretcolumns = new ArrayList();
ArrayList<EclColumnMetaData> storeProcInParams = new ArrayList();
String hpccfilename = "";
String indexposfield = null;
StringBuilder keyedandwild = new StringBuilder();
DFUFile indexfiletouse = null;
StringBuilder eclcode = new StringBuilder("");
int totalparamcount = 0;
boolean isPayloadIndex = false;
switch (sqlreqtype) {
case SqlParser.SQL_TYPE_SELECT:
{
hpccfilename = Utils.handleQuotedString(parser.getTableName());
if (!dbMetadata.tableExists("", hpccfilename))
throw new Exception("Invalid table found: " + hpccfilename);
DFUFile dfufile = dbMetadata.getDFUFile(hpccfilename);
// in future this might need to be a container of dfufile(s)
parser.verifySelectColumns(dfufile);
expectedretcolumns = parser.getSelectColumns();
totalparamcount = parser.getWhereClauseExpressionsCount();
if (indexToUseName != null) {
indexfiletouse = dbMetadata.getDFUFile(indexToUseName);
indexposfield = indexfiletouse.getIdxFilePosField();
isPayloadIndex = processIndex(indexfiletouse, keyedandwild);
eclEnteties.put("KEYEDWILD", keyedandwild.toString());
if (isPayloadIndex) eclEnteties.put("PAYLOADINDEX", "true");
}
eclEnteties.put("PARAMCOUNT", Integer.toString(totalparamcount));
if (dfufile.hasFileRecDef()) {
if (indexfiletouse != null && indexposfield != null)
eclcode.append(
dfufile.getFileRecDefwithIndexpos(
indexfiletouse.getFieldMetaData(indexposfield), "filerecstruct"));
else eclcode.append(dfufile.getFileRecDef("filerecstruct"));
} else
throw new Exception(
"Target HPCC file (" + hpccfilename + ") does not contain ECL record definition");
if (!dfufile.isKeyFile())
eclcode
.append("fileds := DATASET(\'~")
.append(dfufile.getFullyQualifiedName())
.append("\', filerecstruct,")
.append(dfufile.getFormat())
.append("); ");
else {
eclcode.append("fileds := INDEX( ");
eclcode.append('{');
eclcode.append(dfufile.getKeyedFieldsAsDelmitedString(',', "filerecstruct"));
eclcode.append("},{");
eclcode.append(dfufile.getNonKeyedFieldsAsDelmitedString(',', "filerecstruct"));
eclcode.append("},");
eclcode.append("\'~").append(dfufile.getFullyQualifiedName()).append("\');");
}
StringBuilder selectstruct = new StringBuilder(" selectstruct:=RECORD ");
String datasource = indexToUseName == null || isPayloadIndex ? "fileds" : "fetchedds";
// boolean usescalar = expectedretcolumns.size() == 1;
for (int i = 0; i < expectedretcolumns.size(); i++) {
EclColumnMetaData col = expectedretcolumns.get(i);
if (col.getColumnType() == EclColumnMetaData.COLUMN_TYPE_CONSTANT) {
selectstruct
.append(col.getEclType())
.append(" ")
.append(col.getColumnName())
.append(" := ")
.append(col.getConstantValue())
.append("; ");
if (i == 0 && expectedretcolumns.size() == 1)
eclEnteties.put("SCALAROUTNAME", col.getColumnName());
} else if (col.getColumnType() == EclColumnMetaData.COLUMN_TYPE_FNCTION) {
if (col.getColumnName().equalsIgnoreCase("COUNT")) {
eclEnteties.put("COUNTFN", "TRUE");
selectstruct.append("countout := ");
if (parser.hasGroupByColumns()) {
selectstruct.append(col.getColumnName().toUpperCase()).append("( GROUP");
List<EclColumnMetaData> funccols = col.getFunccols();
if (funccols.size() > 0) {
String paramname = funccols.get(0).getColumnName();
if (!paramname.equals("*")
&& funccols.get(0).getColumnType()
!= EclColumnMetaData.COLUMN_TYPE_CONSTANT) {
selectstruct.append(", ");
selectstruct.append(datasource);
selectstruct.append(".");
selectstruct.append(paramname);
selectstruct.append("<> \'\'");
// if (eclEnteties.size() > 0)
// addFilterClause(selectstruct, eclEnteties);
}
}
selectstruct.append(" );");
} else {
selectstruct.append(" scalarout;");
if (expectedretcolumns.size() == 1)
eclEnteties.put("SCALAROUTNAME", col.getColumnName());
}
col.setSQLType(java.sql.Types.NUMERIC);
} else if (col.getColumnName().equalsIgnoreCase("MAX")) {
eclEnteties.put("MAXFN", "TRUE");
selectstruct.append("maxout := ");
if (parser.hasGroupByColumns()) {
selectstruct.append("MAX( GROUP ");
} else {
selectstruct.append("MAX( ").append(datasource);
if (eclEnteties.size() > 0) addFilterClause(selectstruct, eclEnteties);
}
List<EclColumnMetaData> funccols = col.getFunccols();
if (funccols.size() > 0) {
String paramname = funccols.get(0).getColumnName();
eclEnteties.put("FNCOLS", paramname);
if (!paramname.equals("*")
&& funccols.get(0).getColumnType()
!= EclColumnMetaData.COLUMN_TYPE_CONSTANT) {
selectstruct.append(", ");
selectstruct.append(datasource);
selectstruct.append(".");
selectstruct.append(paramname);
}
}
selectstruct.append(" );");
/*
if (parser.hasGroupByColumns())
{
selectstruct.append(col.getColumnName().toUpperCase()).append("( GROUP");
List<EclColumnMetaData> funccols = col.getFunccols();
if (funccols.size() > 0)
{
String paramname = funccols.get(0).getColumnName();
eclEnteties.put("FNCOLS", paramname);
if (!paramname.equals("*") && funccols.get(0).getColumnType() != EclColumnMetaData.COLUMN_TYPE_CONSTANT)
{
selectstruct.append(", ");
selectstruct.append(datasource);
selectstruct.append(".");
selectstruct.append(paramname);
}
}
selectstruct.append(" );");
}
else
{
selectstruct.append(" totalmax;");
//if (expectedretcolumns.size() == 1)
// eclEnteties.put("SCALAROUTNAME", col.getColumnName());
}
*/
/*
selectstruct.append(col.getColumnName().toUpperCase()).append(" ( ");
if (parser.hasGroupByColumns())
selectstruct.append("GROUP");
else
selectstruct.append(datasource);
List<EclColumnMetaData> funccols = col.getFunccols();
if (funccols.size() > 0)
{
String paramname = funccols.get(0).getColumnName();
if (!paramname.equals("*") && funccols.get(0).getColumnType() != EclColumnMetaData.COLUMN_TYPE_CONSTANT)
{
selectstruct.append(", ");
selectstruct.append(datasource);
selectstruct.append(".");
selectstruct.append(paramname);
}
}
selectstruct.append(" );");*/
}
} else
selectstruct
.append(col.getEclType())
.append(" ")
.append(col.getColumnName())
.append(" := ")
.append(datasource)
.append(".")
.append(col.getColumnName())
.append("; ");
// if (i == 0 && expectedretcolumns.size() == 1 && col.getColumnType() !=
// EclColumnMetaData.COLUMN_TYPE_DATA )
// eclEnteties.put("SCALAROUTNAME", col.getColumnName());
}
selectstruct.append("END; ");
eclEnteties.put("SELECTSTRUCT", selectstruct.toString());
if (parser.hasOrderByColumns()) eclEnteties.put("ORDERBY", parser.getOrderByString());
if (parser.hasGroupByColumns()) eclEnteties.put("GROUPBY", parser.getGroupByString());
if (parser.hasLimitBy()) eclEnteties.put("LIMIT", Integer.toString(parser.getLimit()));
return executeSelect(eclcode.toString(), eclEnteties, indexfiletouse);
}
case SqlParser.SQL_TYPE_SELECTCONST:
{
System.out.println("Processing test_query...");
// ArrayList<EclColumnMetaData> columns = new ArrayList();
eclcode.append("selectstruct:=RECORD ");
expectedretcolumns = parser.getSelectColumns();
// defaultEclQueryReturnDatasetName = "ConstECLQueryResult";
StringBuilder ecloutput = new StringBuilder(" OUTPUT(DATASET([{ ");
for (int i = 1; i <= expectedretcolumns.size(); i++) {
EclColumnMetaData col = expectedretcolumns.get(i - 1);
eclcode.append(col.getEclType()).append(" ").append(col.getColumnName()).append("; ");
ecloutput.append(col.getConstantValue());
if (i < expectedretcolumns.size()) ecloutput.append(", ");
}
ecloutput.append("}],selectstruct), NAMED(\'");
// ecloutput.append(defaultEclQueryReturnDatasetName);
ecloutput.append("ConstECLQueryResult");
ecloutput.append("\'));");
eclcode.append(" END; ");
eclcode.append(ecloutput.toString());
return executeSelectConstant(eclcode.toString());
}
case SqlParser.SQL_TYPE_CALL:
{
eclqueryname = Utils.handleQuotedString(parser.getStoredProcName());
if (!dbMetadata.eclQueryExists("", eclqueryname))
throw new Exception("Invalid store procedure found");
return executeCall(null);
}
default:
break;
}
return null;
}
public ArrayList executeCall(Map parameters) {
try {
urlString =
"http://"
+ props.getProperty("ServerAddress")
+ ":"
+ props.getProperty("WsECLPort")
+ "/WsEcl/submit/query/"
+ props.getProperty("Cluster")
+ "/"
+ eclqueryname
+ "/expanded";
System.out.println("WSECL:executeCall: " + urlString);
// Construct data
StringBuilder sb = new StringBuilder();
sb.append(URLEncoder.encode("submit_type_=xml", "UTF-8"));
sb.append("&").append(URLEncoder.encode("S1=Submit", "UTF-8"));
ArrayList<EclColumnMetaData> storeProcInParams =
dbMetadata.getStoredProcInColumns("", eclqueryname);
String[] procInParamValues = parser.getStoredProcInParamVals();
for (int i = 0; i < procInParamValues.length; i++) {
String key = storeProcInParams.get(i).getColumnName();
{
sb.append("&").append(key).append("=").append(procInParamValues[i]);
}
}
// if (parameters.size() > 0)
// {
// for (Object key : parameters.keySet())
// {
// Object value = parameters.get(key);
// sb.append("&").append(key).append("=").append(value);
// }
// }
long startTime = System.currentTimeMillis();
// Send data
URL url = new URL(urlString);
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.setInstanceFollowRedirects(false);
conn.setRequestProperty("Authorization", basicAuth);
conn.setRequestMethod("GET");
conn.setDoOutput(true);
conn.setDoInput(true);
OutputStreamWriter wr = new OutputStreamWriter(conn.getOutputStream());
wr.write(sb.toString());
wr.flush();
System.out.println("WsEcl Execute: " + urlString + " : " + sb.toString());
// Get the response
return parse(conn.getInputStream(), startTime);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
protected ResultSet executeParsedQuery(
SqlParser parser, Map<String, Object> parentobjectEnvironment) throws SQLException {
if (parser.getTableNames().size() > 1)
throw new SQLException(CsvResources.getString("joinNotSupported"));
String tableName = null;
if (parser.getTableNames().size() > 0) tableName = parser.getTableNames().get(0);
String tableAlias = null;
if (parser.getTableAliases().size() > 0) tableAlias = parser.getTableAliases().get(0);
String path = connection.getPath();
TableReader tableReader = connection.getTableReader();
if (path != null) CsvDriver.writeLog("Connection Path: " + path);
else CsvDriver.writeLog("Connection TableReader: " + tableReader.getClass().getName());
CsvDriver.writeLog("Parser Table Name: " + tableName);
CsvDriver.writeLog("Connection Extension: " + connection.getExtension());
DataReader reader = null;
String fileName = null;
if (tableName == null) {
/*
* Create an empty dataset with one row.
*/
String[] columnNames = new String[0];
String[] columnTypes = new String[0];
ArrayList<Object[]> rows = new ArrayList<Object[]>();
rows.add(new Object[0]);
reader = new ListDataReader(columnNames, columnTypes, rows);
} else {
if (path != null && (!connection.isIndexedFiles())) {
fileName = path + tableName + connection.getExtension();
CsvDriver.writeLog("CSV file name: " + fileName);
File checkFile = new File(fileName);
if (!checkFile.exists()) {
throw new SQLException(CsvResources.getString("fileNotFound") + ": " + fileName);
}
if (!checkFile.canRead()) {
throw new SQLException(CsvResources.getString("fileNotReadable") + ": " + fileName);
}
}
try {
if (connection.getExtension().equalsIgnoreCase(".dbf")) {
reader = new DbfReader(fileName, tableName, tableAlias, connection.getCharset());
} else {
LineNumberReader input;
if (tableReader == null) {
InputStream in;
CryptoFilter filter = connection.getDecryptingCodec();
if (connection.isIndexedFiles()) {
String fileNamePattern =
tableName + connection.getFileNamePattern() + connection.getExtension();
String[] nameParts = connection.getNameParts();
String dirName = connection.getPath();
in =
new FileSetInputStream(
dirName,
fileNamePattern,
nameParts,
connection.getSeparator(),
connection.isFileTailPrepend(),
connection.isSuppressHeaders(),
filter,
connection.getSkipLeadingDataLines() + connection.getTransposedLines());
} else if (filter == null) {
in = new FileInputStream(fileName);
} else {
filter.reset();
in = new EncryptedFileInputStream(fileName, filter);
}
if (connection.getCharset() != null) {
input = new LineNumberReader(new InputStreamReader(in, connection.getCharset()));
} else {
input = new LineNumberReader(new InputStreamReader(in));
}
} else {
/*
* Reader for table comes from user-provided class.
*/
input = new LineNumberReader(tableReader.getReader(this, tableName));
}
String headerline = connection.getHeaderline(tableName);
CsvRawReader rawReader =
new CsvRawReader(
input,
tableName,
tableAlias,
connection.getSeparator(),
connection.isSuppressHeaders(),
connection.isHeaderFixedWidth(),
connection.getQuotechar(),
connection.getCommentChar(),
headerline,
connection.getTrimHeaders(),
connection.getTrimValues(),
connection.getSkipLeadingLines(),
connection.isIgnoreUnparseableLines(),
connection.getMissingValue(),
connection.isDefectiveHeaders(),
connection.getSkipLeadingDataLines(),
connection.getQuoteStyle(),
connection.getFixedWidthColumns());
reader =
new CsvReader(
rawReader,
connection.getTransposedLines(),
connection.getTransposedFieldsToSkip(),
headerline);
}
} catch (IOException e) {
throw new SQLException(CsvResources.getString("fileReadError") + ": " + e);
} catch (SQLException e) {
throw e;
} catch (Exception e) {
e.printStackTrace();
throw new SQLException(CsvResources.getString("dataReaderError") + ": " + e);
}
}
CsvResultSet resultSet = null;
try {
resultSet =
new CsvResultSet(
this,
reader,
tableName,
parser.getColumns(),
parser.isDistinct(),
this.resultSetType,
parser.getWhereClause(),
parser.getGroupByColumns(),
parser.getHavingClause(),
parser.getOrderByColumns(),
parser.getLimit(),
parser.getOffset(),
connection.getColumnTypes(tableName),
connection.getSkipLeadingLines(),
parentobjectEnvironment);
lastResultSet = resultSet;
} catch (ClassNotFoundException e) {
CsvDriver.writeLog("" + e);
}
return resultSet;
}
